获取sql执行计划有几种方法,现总结一下。
1.set autotrace traceonly
1) set autotrace traceonly :显示执行计划及统计信息,真实执行了sql。
2) set autotrace traceonly exp:只显示执行计划,未执行sql。
3) set autotrace traceonly stat:只显示统计信息,执行了sql。
2. dbms_xplan.display_cursor
这个函数返回是是存储在library_cache中的执行计划,下面介绍下三个参数的涵义:
1) sql_id: 指定被返回执行计划的SQL语句的父游标,默认值是NULL,表示当前会话所执行的最后一条SQL语句的执行计划被返回。
2) cursor_child_no: 表示这个父游标下的子游标的序号,默认值为0,如果指定为NULL,则表示对应sql_id所指的父游标下所有子游标的执行计划都将被返回。
3) format:同display函数,默认值仍然为Typical, 如果我们通过设置STATSTICS_LEVEL=ALL或者在sql语句中加之类的hints的话,还可以显示更多的信息,比如E-ROWS和A-ROWS,等等。
一般的用法为:
select hash_value, child_number, sql_text from v$sql s where s.SQL_ID = '866n2xzvtyndu';
或者 select hash_value, child_number, sql_text from v$sql where sql_text like '%select * from test%';
select * from table(dbms_xplan.display_cursor(hash_value, child_number, 'advanced'));
如果需要获取更多的信息可以这样使用:
SQL>set serveroutput on size 100000
SQL>spool d:/result.txt
SQL>set linesize 400
SQL>set pagesize 100
SQL>set timing on
SQL>set autotrace traceonly
SQL>把要附件的sql语句copy到这里
SQL>set autotrace off
SQL>alter session set statistics_level=all;
SQL>把要执行的sql语句copy到这里
SQL>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL>spool off;
--Starts为该sql执行的次数。
--E-Rows为执行计划预计的行数。
--A-Rows为实际返回的行数。A-Rows跟E-Rows做比较,就可以确定哪一步执行计划出了问题。
--A-Time为每一步实际执行的时间(HH:MM:SS.FF),根据这一行可以知道该sql耗时在了哪个地方。
--Buffers为每一步实际执行的逻辑读或一致性读。 --Reads为物理读。
--OMem、1Mem为执行所需的内存评估值,0Mem为最优执行模式所需内存的评估值,1Mem为one-pass模式所需内存的评估值。 --0/1/M 为最优/one-pass/multipass执行的次数。
--Used-Mem耗的内存
3.dbms_xplan.display
这个函数需要与explain plan结合使用,是从plan_table中获取的执行计划。这种方式不会执行sql,会发生硬解析。下面简单介绍下参数:
1). table_name: 默认为plan_table。
2). statement_id: 默认为NULL,在默认情况下,显示最近插入plan_table中的执行计划。
3). format:取值包括,basic,typical,serial,all和advance,默认值是typical, advance则是显示所有信息。
4). filter_preds: 默认值为NULL,指定在plan_table中添加一个约束。从Oracle 10gR2开始用这个参数。
使用例子如:
explain plan for select * from test;
select * from table(dbms_xplan.display(NULL,NULL,'advanced',NULL));
4.sql_trace
sql_trace命令会将SQL执行的整个过程输出到一个trace文件中,我们可以通过阅读这个trace文件来了解这个SQL执行过程中oracle究竟做了哪些事情。具体使用方法如下:
alter session set tracefile_identifier = 'sql_trace';
alter session set sql_trace= true;
set autotrace traceonly
select * from test t,test1 t1 where t.object_id=t1.object_id;
set autotrace off
alter session set sql_trace= false;
原始的trace文件可读性较差,通常使用tkprof工具来处理这个trace文件。 Tkprof工具是oracle自带的一个工具,用于处理原始的trace文件,它的作用是合并汇总trace文件中的一些项,规范化文件的格式,使文件更具有可读性。如:
D:\oracle\product\10.2.0\admin\ordb10\udump> tkprof ordb10_ora_4728_look_at_me.trc 4728.txt
tkprof参数:
sys:如果设置为yes,在trace文件中将输入所有的SYS用户的操作(也包含用户SQL语句引发的递归SQL),如果设置为no,则不输出这些信息。默认情况下是yes,实际上设置为no后trace文件更具有可读性。
aggregate: 默认情况下,tkprof工具将所有相同的SQL输入文件中做合并,如果设置为no,则分别列出每个SQL的信息。
5.10046
10046事件并不是oracle官方提供给用户的使用命令,但目前已经使用非常广泛,它比sql_trace能够获得更多的信息。
10046事件按照收集的信息内容,可以分为4个级别:
具体使用方法如下:
alter session set events '10046 trace name context off';
alter session set events '10046 trace name context forever,level 12';
执行sql
alter session set events '10046 trace name context off';
如果生成看不到执行计划则应该执行如下步骤:
alter session set session_cached_cursors =0;
alter session set events '10046 trace name context forever ,level 12' ;
执行SQL
alter session set events '10046 trace name context off' ;
6.10053
在查看一条SQL语句的执行计划时,只看到CBO最终告诉我们执行的结果,却不知道CBO为什么这么做。特别是当执行计划明显失真时,我们一定非常想知道什么地方导致CBO做出这样一个错误的执行计划。10053事件可以将CBO做出这个执行计划的整个过程演示给我们看。具体使用方式如下:
alter session set tracefile_identifier= '10053';
alter session set events '10053 trace name context forever,level 1';
Explain plan for select * from pub_user u, pub_department dept
where u.department_id = dept.department_id;
alter session set events '10053 trace name context off';
10053事件有时候会跟踪不到SQL执行,原因是它需要捕获硬解析,要SQL硬解析即可。