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

落日映苍穹つ 2022-06-13 06:58 254阅读 0赞

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          0
    
    SQL> 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 executed
    cpu      = cpu time in seconds executing 
    elapsed  = elapsed time in seconds executing
    disk     = number of physical reads of buffers from disk
    query    = number of buffers gotten for consistent read
    current  = 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: c99yw1xkb4f1u
    Plan Hash: 1357081020
    
    ----执行的SQL
    select * 
    from
     test
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        2      0.00       0.00          0          3          0           1
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total        4      0.00       0.00          0          3          0           1
    
    
    ----优化器访问数据单位方式，为默认的all_rows
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing 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: 06nvwn223659v
    Plan Hash: 0
    alter 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，说明没有真正的执行