执行计划小结，真是琳琅满目啊！
1，SET AUTOT[RACE] {ON| OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]
参考另一文档：http://blog.csdn.net/jc_benben/article/details/17270105
SQL>set autotrace on exp
SQL> select * from dual;
DU
--
X
执行计划
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1| TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> set auto off exp;
2，explain for…dbms_xplan.display()/dbms_xplan.display_cursor()
另一篇参考：http://blog.csdn.net/jc_benben/article/details/51683149
SQL>explain plan for select * from dual;
已解释。
SQL> select * fromtable(dbms_xplan.display_cursor(format=>'ALLSTAT,LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: format 'ALLSTAT,LAST' not valid forDBMS_XPAN.DISPLAY_CURSOR()
SQL> select * fromtable(dbms_xplan.display_cursor(format=>'ALLSTATs,LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 52ta4vf0q9xtd, child number 0
-------------------------------------
select * fromtable(dbms_xplan.display_cursor(format=>'ALLSTAT,LAST'))
Plan hash value: 3713220770
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------
| 0| SELECT STATEMENT | | |
| 1| COLLECTION ITERATOR PICKLER FETCH|DISPLAY_CURSOR | 8168 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------
Note
-----
-Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or systemleve
l
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
已选择 19 行。
3,使用sql_trace
SQL> alter session set sql_trace=true;
会话已更改。
SQL> select 1111,id,name from t2;
1111 ID NAME
---------- ---------- --------------------
1111 1 china
1111 2 china
SQL> alter session set sql_trace=false;
会话已更改。
路径：
SQL> select value from v$diag_info wherename='Default Trace File';
VALUE
--------------------------------------------------------------------
G:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_ora_8392.trc
4，使用 10046
Event 10046 - Enable SQL Statement Trace
This event can be used to dump SQL statements executed by a session withexecution plans and statistics.
Bind variable and wait statistics can optionally be included. Level 12 is themost detailed.
For example
ALTER SESSION SET EVENTS
'10046 trace name context forever, level 12';
Levels are
Level Action
1 Print SQL statements, execution plans and execution statistics –其实就相当于sql_trace
4 As level 1 plus bind variables
8 As level 1 plus wait statistics
12 As level 1 plus bind variables and wait statistics
还有其他很多事件，可以在linux安装目录中一个文件中查看到$ORACLE_HOME/rdbms/mesg/oraus.msg
SQL> alter session set events '10046trace name context forever,level 12';
会话已更改。
SQL> select id,name from t2;
ID NAME
---------- --------------------
1 china
2 china
SQL> alter session set events '10046trace name context off';
会话已更改。
5使用dbms_system.set_sql_trace_in_session
SQL> select sid,serial#,username fromv$session where username='LOGE';
SID SERIAL#
---------- ----------
USERNAME
------------------------------------------------------------
129 56923
LOGE
n 使用其他用户启动监控
SQL> execdbms_system.set_sql_trace_in_session(129,56923,TRUE);
PL/SQL 过程已成功完成。
n 关闭监控
SQL> execdbms_system.set_sql_trace_in_session(129,56923,FALSE);
PL/SQL 过程已成功完成。
6，系统视图
SELECT * FROM V$SQL_PLAN
SELECT * FROM V$RSRC_PLAN_CPU_MTH
SELECT * FROM V$SQL_PLAN_STATISTICS
SELECT * FROM V$SQL_PLAN_STATISTICS_ALL
SELECT * FROM V$SQLAREA_PLAN_HASH
SELECT * FROM V$RSRC_PLAN_HISTORY
7，图形化工具
比如TOAD，PL/SQL等
二，跟踪文件部分解析
1，Trace文件也是苦涩，比如：
=====================
PARSING IN CURSOR #138996696 len=22 dep=0uid=110 oct=3 lid=110 tim=150225868821 hv=1481359936 ad='7ff7a29af28'sqlid='4yj0q9xc4rhk0'
select id,name from t2
END OF STMT
PARSE#138996696:c=0,e=5730,p=0,cr=8,cu=0,mis=1,r=0,dep=0,og=1,plh=1513984157,tim=150225868819
EXEC#138996696:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1513984157,tim=150225868995
WAIT #138996696: nam='SQL*Net message toclient' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=150225869106
FETCH#138996696:c=0,e=65,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=1513984157,tim=150225869229
WAIT #138996696: nam='SQL*Net message fromclient' ela= 492 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=150225869806
WAIT #138996696: nam='SQL*Net message toclient' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=150225869924
FETCH #138996696:c=0,e=110,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=1513984157,tim=150225870010
STAT #138996696 id=1 cnt=2 pid=0 pos=1obj=92795 op='TABLE ACCESS FULL T2 (cr=8 pr=0 pw=0 time=54 us cost=3 size=40card=2)'
*** 2016-06-16 09:08:06.026
WAIT #138996696: nam='SQL*Net message fromclient' ela= 49975525 driver id=1413697536 #bytes=1 p3=0 obj#=-1tim=150275845667
CLOSE#138996696:c=0,e=18,dep=0,type=0,tim=150275846057
简单解读，PARSING IN CURSOR部分
len sql 语句长度
dep sql 语句递归深度
uid user id
oct oracle command type
lid privilege user id
tim timestamp，时间戳，v$timer视图
hv hash id
ad sql address 地址, 用在 v$sqltext
sqlid sql id
parse部分：
c CPU消耗的时间
e Elapsed time
p number of physical reads 物理读的次数
cr number of buffers retrieved for CRreads 逻辑读的数据块
cu numberof buffers retrieved in current mode (current 模式读取的数据块)
mis cursor missed in the cache 库缓存中丢失的游标, 硬解析次数
r number of rows processed 处理的行数
dep 递归深度
og optimizer mode 【1：all_rows, 2：first_rows,3：rule, 4：choose】
plh plan hash value
tim timestamp
WAIT 部分：
nam an event that we waited for 等待事件
ela 消耗的时间
p3 块号
trm 时间戳
STAT 执行计划：
cnt 当前行源返回的行数
pid parent id of this row source 当前行源的父结点 id
pos position in explain plan 执行计划的位置
obj object id of row source (if this is a baseobject)
op the row source access operation
2，使用tkprof格式化
跟tracefile区别，它不包含绑定变量西西你，不包含真正的sql执行顺序，他的优势是可以看到根据CPU时长，磁盘读取的SQL很直观的内容，是经过格式化后的内容
使用：
tkprof orcl_ora_8392.trc g:/trace.trc -- 生成trace.trc文件
可以看具体：http://blog.csdn.net/jc_benben/article/details/10530799