oracle之sql执行计划（一）
今天是2013-09-10，在今天学习cache buffer chain 的时候，发现了一个语句，然后使用oracle 执行计划进行分析该语句，这时候发现，原来 我对sql的执行计划这块知识欠缺非常大，在此简单的学习 一下，后续在慢慢深入研究吧。毕竟这块内容，太多了。现在我心情感觉压抑，穿不上起来，回头想想需要学习的东西太多太多了。唯有坚持不懈的去研究，日积月累才能够达到一个孰能生巧的境界。
摘录如下：
##################################
SYS@orcl#select * from v$parameter;
执行计划
----------------------------------------------------------
Plan hash value: 1128103955
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4414 | 1 (100)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 4414 | 1 (100)| 00:00:01 |
|* 2 | FIXED TABLE FULL| X$KSPPI | 1 | 249 | 0 (0)| 00:00:01 |
| 3 | FIXED TABLE FULL| X$KSPPCV | 100 | 406K| 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"."INDX"="Y"."INDX")
filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR
"KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0)
2 - filter("X"."INST_ID"=USERENV('INSTANCE') AND
TRANSLATE("KSPPINM",'_','#') NOT LIKE '##%')
SYS@orcl#
autotrace 语句研究：
该语句工具主要是研究统计sql语句的执行计划并生成报告，进而对dml语句进行检测和优化。
Generates a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is useful to monitor and tune the performance of DML statements.
set autotrace off; 不产生报告和执行计划，默认设置。
set autotrace on explain; 生成执行计划并显示查询信息；
set autotrace on statistics；只生成语句的统计信息并显示查询信息。
set autotrace on ; 即生成语句报告有显示语句的执行统计信息和计划
set autotrace traceonly; 和on一样，区别就是不显示查询信息，查询的数据依然会fetch但是不会print，只显示统计信息和执行计划；
eg：
SYS@orcl#set autotrace on explain
SYS@orcl#select * from t2;
EMPNO ENAME
---------- --------------------
7521 WARD
执行计划
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T2 | 1 | 20 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SYS@orcl#set autotrace on statistices;
SP2-0735: 未知的 SET 选项开头 "statistice..."
SYS@orcl#set autotrace on statistics;
SYS@orcl#select * from t2;
EMPNO ENAME
---------- --------------------
7521 WARD
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
600 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@orcl#select * from t2;
EMPNO ENAME
---------- --------------------
7521 WARD
执行计划
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T2 | 1 | 20 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
600 bytes sent via SQL*Net to client
&n
