oracle获取sql执行计划方法

     获取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个级别:

     Level1 :  等同于sql_trace的功能。
     Level4 :  在level1的基础上增加收集绑定变量的信息。
     Level8 :  在level1的基础上增加等待事件的信息。
    Level12 : 等同于level4 + level8 。

    具体使用方法如下:

   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硬解析即可。

  ltkprof只能解析sql_trace和10046,不能解析10053 。


   总结:sql_trace、10046、10053生成的trace文件都在服务器端,一般生产环境我们是获取不到的,所以一般情况下我们使用最多的还是set autotrace traceonly 与 dbms_xplan.display_cursor,如果遇到这两种方法获取的执行计划不准确的情况下,我们就需要使用sql_trace、10046、10053工具。





  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值