oracle获取的执行计划区别及获取真实执行计划

oracle查看真实的执行计划

oracle查看到的执行计划，偶尔并不是真实的执行计划，
查看是否是真实的执行计划，就是看SQL有没有真正的执行，若没有被执行，那么由于统计信息与实际直接误差，或其他原因，执行计划可能是不准确的。

10046获取的是执行计划
explain plan，dbms_xplan,autotrace这三种方法获取到的执行计划，可能不是真实的执行计划

1.10046

10046事件获取到的是oracle sql真实的执行计划

使用10046查看执行计划方法
开启:
SQL> alter session set events ‘10046 trace name context forever, level 12’;
或
SQL> oradebug event 10046 trace name context forever, level 12
关闭:
SQL> alter session set events ‘10046 trace name context off’;
或
SQL> oradebug event 10046 trace name context off
SQL> select * from v$mystat where rownum=1;
SQL> SELECT tracefile FROM v$process WHERE addr IN (SELECT paddr FROM v$session WHERE sid=’xxx’);

SQL> alter session set events '10046 trace name context forever, level 12';Session altered.SQL> select * from test;....SQL> alter session set events '10046 trace name context off';Session altered.SQL> select * from v$mystat where rownum=1;       SID STATISTIC#      VALUE---------- ---------- ----------         1          0          0SQL> SELECT tracefile FROM v$process WHERE addr IN (SELECT paddr FROM v$session WHERE sid='1');TRACEFILE--------------------------------------------------------------------------------/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2905.trc

tkprof转换格式

tkprof /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2905.trc orcl_ora_2905.trc

trc文件中查看执行计划

Sort options: default----默认排序----参数说明********************************************************************************count    = number of times OCI procedure was executedcpu      = cpu time in seconds executing elapsed  = elapsed time in seconds executingdisk     = number of physical reads of buffers from diskquery    = number of buffers gotten for consistent readcurrent  = number of buffers gotten in current mode (usually for update)rows     = number of rows processed by the fetch or execute call********************************************************************************----SQL ID 已经hash值SQL ID: c99yw1xkb4f1uPlan Hash: 1357081020----执行的SQLselect * from testcall     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        2      0.00       0.00          0          3          0           1------- ------  -------- ---------- ---------- ---------- ----------  ----------total        4      0.00       0.00          0          3          0           1----优化器访问数据单位方式，为默认的all_rowsMisses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 91  ----这里可以看到实际的资源消耗：cr每一步所消耗的逻辑读pr物理读pw物理写time好肥的时间card实际返回的函数size返回的数据大小这里可以看到，该test经过3次逻辑读，没有回磁盘读，总共返回一行数据执行计划执行一步操作TABLE ACCESS FULL TEST 全表扫描Rows     Row Source Operation-------  ---------------------------------------------------      1  TABLE ACCESS FULL TEST (cr=3 pr=0 pw=0 time=0 us cost=3 size=181 card=1)Elapsed times include waiting on following events:  Event waited on                             Times   Max. Wait  Total Waited  ----------------------------------------   Waited  ----------  ------------  SQL*Net message to client                       2        0.00          0.00  Disk file operations I/O                        1        0.00          0.00  SQL*Net message from client                     2        7.76          7.77********************************************************************************SQL ID: 06nvwn223659vPlan Hash: 0alter session set events '10046 trace name context off'......

2.explain plan
目标sql并没有实际被执行，所以获取到的执行计划是不准确的
若有绑定变量，则获取到的执行计划，可以看作是个半成品，有时并不准确

3.dbms_xplan
select * from table(dbms_xplan.display)
select * from table(dbms_xplan.display_cursor(null,null,’advanced’));
select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value’,child_cursor_number,’advanced’))
select * from table(dbms_xplan.display_awr(‘sql_id’))

select * from table(dbms_xplan.display)获取到的执行计划可能是不准的，因为它获取的是explain plan的执行结果
剩下3种获得的执行计划是准确的
select * from table(dbms_xplan.display_cursor(null,null,’advanced’));
select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value’,child_cursor_number,’advanced’))
select * from table(dbms_xplan.display_awr(‘sql_id’))

4.autotrace

set autotrace on与set autotrace traceonly 目标sql已经执行，可以查看到资源消耗情况，但执行计划依然可能是不准确的，这是因为，实际来源的是explain plan
使用set autotrace traceonly explain 解析执行select操作时，sql并没有真正执行。获取的执行计划可能是不准的

SQL> set autotrace traceonly explain;SQL> select count(*) from test;Execution Plan----------------------------------------------------------Plan hash value: 1950795681-------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |-------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 ||   1 |  SORT AGGREGATE    |      |     1 |            |          ||   2 |   TABLE ACCESS FULL| TEST |     1 |     3   (0)| 00:00:01 |-------------------------------------------------------------------SQL> set autotrace off;SQL> select a.SQL_TEXT,a.EXECUTIONS from v$sqlarea a where a.SQL_TEXT like 'select count(*)%';SQL_TEXT                                                                         EXECUTIONS-------------------------------------------------------------------------------- ----------select count(*) from test                                                                 0

EXECUTIONS为0，说明没有真正的执行