获取执行计划有以下几种方法,普遍使用前四种方法:
(1)explain plan
(2)DBMS_XPLAN包
(3)sqlplus中的autotrace命令
(4)10046
(5)10053
(6)awrsqrpt.sql
1、explain plan命令
explain plan for <目标SQL>;
select * from table(dbms_xplan.display);
2、DBMS_XPLAN包
--查看刚刚被执行SQL的执行计划
(1)select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
(2)select * from table(dbms_xplan.display_cursor('<sql_id>',<child_number>,'advanced'));
注:方法1、2,需要sql还在shared pool中
child_number通过v$sql获取:
SQL> select CHILD_NUMBER from v$sql where sql_id='c99yw1xkb4f1u';
CHILD_NUMBER
------------
0
--查看sql的历史执行计划
(3)select * from table(dbms_xplan.display_awr('sql_id'));
3、sqlplus中的autotrace命令
set autotrace on
set autotrace off
set autotrace traceonly --不显示查询结果输出
set autotrace traceonly explain --只显示执行计划部分
4、10046事件
(1)当前会话打开10046
SQL> oradebug setmypid
SQL> oradebug event 10046 trace name context forever,level 12
(2)当前会话执行sql
SQL> select * from test.test;
SQL> oradebug tracefile_name
(3)当前会话关闭10046
SQL> oradebug event 10046 trace name context off
格式化10046trc文件,使之易看易懂:
[oracle@network-bind ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14637.trc
output = orcl_ora_14637.txt
5、10053事件
(1)当前会话打开10053
alter session set events='10053 trace name context forever, level 1';
(2)当前会话执行sql
select * from test;
(3)查看10053trc文件
SELECT D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||P.SPID || '.trc' AS "trace_file_name"FROM (SELECT P.SPID FROM V$MYSTAT M,V$SESSION S,V$PROCESS P WHERE M.STATISTIC# = 1 AND S.SID = M.SID AND P.ADDR = S.PADDR) P,(SELECT T.INSTANCE FROM V$THREAD T,V$PARAMETER V WHERE V.NAME = 'thread' AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))) I,(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;
(4)当前会话关闭10053
alter session set events '10053 trace name context off';
6、awrsqrpt.sql
SQL> @?/rdbms/admin/awrsqrpt.sql
参考书籍:
崔华 著 《基于Oracle的SQL优化》