获取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硬解析即可。
扫一扫
669
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
