Oracle之获取真正的执行计划

/*


分结论3（执行计划，你是真实的吗）：


一.关于获取执行计划的6种方法和各自区别大家在上一例子中已经大致明白了。
    1. explain plan for获取； 
    2. set autotrace on ；    
    3. statistics_level=all;
    4. 通过dbms_xplan.display_cursor输入sql_id参数直接获取
    5. 10046 trace跟踪
    6. awrsqrpt.sql
    
二. 执行计划中"真实执行计划” 是一个很重要的常识，这也就是方法1 和方法2 的最大缺陷了。 
    狠狠揪出本次即将被批斗的坏蛋：方法1的explain plan for和方法2的set autotrace on


例子主要是针对：绑定变量窥视与直方图


*/


---构建T表，数据，及主键


DROP TABLE t;
CREATE TABLE t 
AS 
SELECT rownum AS id, rpad('*',100,'*') AS pad 
FROM dual
CONNECT BY level <= 1000;
ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
---收集统计信息
BEGIN
  dbms_stats.gather_table_stats(
    ownname          => user, 
    tabname          => 'T', 
    estimate_percent => 100, 
    method_opt       => 'for all columns size 254' 
  );
END;
/


/*
  下面我们将会用多种方法来查看如下语句的执行计划
VARIABLE id NUMBER
COLUMN sql_id NEW_VALUE sql_id  
EXECUTE :id := 990;
SELECT count(pad) FROM t WHERE id < :id;
EXECUTE :id := 10;
SELECT count(pad) FROM t WHERE id < :id;


*/


----方法1(explain plan for 的方式。类似PLSQL DEVELOPE里的F5)
set linesize 1000
set pagesize 2000
VARIABLE id NUMBER
COLUMN sql_id NEW_VALUE sql_id
EXECUTE :id := 990;
explain plan for
SELECT count(pad) FROM t WHERE id < :id;
select * from table(dbms_xplan.display());


--明明应该是走全表扫描合适，居然走了索引读，原因是，这个执行计划是假的。
SQL> select * from table(dbms_xplan.display());


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |   105 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |      |     1 |   105 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    50 |  5250 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------


   3 - access("ID"<TO_NUMBER(:ID))


已选择15行。




----方法2(set autotrace on 方式)
/*set autotrace on 
 set autotrace traceonly  
 set autotrace traceonly explain 
 set autotrace traceonl statistics
*/
set autotrace traceonly
VARIABLE id NUMBER
COLUMN sql_id NEW_VALUE sql_id
EXECUTE :id := 990;
SELECT count(pad) FROM t WHERE id < :id;


执行计划
----------------------------------------------------------
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |   105 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |      |     1 |   105 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    50 |  5250 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID"<TO_NUMBER(:ID))
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
        426  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

     
         
----方法3（statistics level=all的方式)         
set autotrace off          
alter session set statistics_level=all ;
VARIABLE id NUMBER
COLUMN sql_id NEW_VALUE sql_id
EXECUTE :id := 990;
SELECT count(pad) FROM t WHERE id < :id;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  asth1mx10aygn, child number 1
-------------------------------------
SELECT count(pad) FROM t WHERE id < :id


Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      19 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      19 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |    988 |    989 |00:00:00.01 |      19 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID"<:ID)




已选择19行。




----方法4(知道sql_id后，直接带入的方式）
select * from table(dbms_xplan.display_cursor('asth1mx10aygn'));


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  asth1mx10aygn, child number 0
-------------------------------------
SELECT count(pad) FROM t WHERE id < :id
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     7 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   105 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   988 |   101K|     7   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID"<:ID)
已选择19行。
  



----方法5(10046TRACE）
set autotace off
alter session set statistics_level=typical;     
alter session set events '10046 trace name context  forever,level 12';
VARIABLE id NUMBER
COLUMN sql_id NEW_VALUE sql_id
EXECUTE :id := 990;
SELECT count(pad) FROM t WHERE id < :id;
   
alter session set events '10046 trace name context off';   
select d.value
|| '/'
|| LOWER (RTRIM(i.INSTANCE, CHR(0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
from (select p.spid
      from v$mystat m,v$session s, v$process p
      where  m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
      (select t.INSTANCE
       FROM v$thread t,v$parameter v
       WHERE v.name='thread'
       AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
       (select value
       from v$parameter
       where name='user_dump_dest') d;


exit       
tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_3144.trc    d:\10046_3.txt  sys=no sort=prsela,exeela,fchela       




--观察发现，也是真实的执行计划，全表扫描！


SQL ID: asth1mx10aygn
Plan Hash: 2966233522
SELECT count(pad) 
FROM
 t WHERE id < :id




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         19          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         19          0           1


Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 94  


Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=19 pr=0 pw=0 time=0 us)
    989   TABLE ACCESS FULL T (cr=19 pr=0 pw=0 time=494 us cost=7 size=103740 card=988)




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
  SQL*Net message from client                     2        8.06          8.06
********************************************************************************