[20140109]顯示執行計劃的問題.txt

昨天看別人調優,我跟對方講使用SET Autotrace看執行計劃可能不真實的.
自己做一個簡單的例子來說明:

1.建立測試環境:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t pctfree 99 as select rownum id ,'test' name from dual connect by level<=1e3;
create index i_t_id on t(id);

exec dbms_stats.gather_table_stats(user, 'T',  method_opt=>'for all columns size 1 ',no_invalidate => false);

variable n number;
exec :n := 10;
select * from t where id>=:n;

2.測試:
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0ck6kww1pfuw8, child number 0
-------------------------------------
select * from t where id>=:n
Plan hash value: 1601196873
--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |    71 (100)|
|*  1 |  TABLE ACCESS FULL| T    |    992 |    71   (0)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID">=:N)

--因為n=10,很小,oracle 的繫結變數peek發現選擇全表掃描更加合理.

SCOTT@test> set autotrace traceonly
SCOTT@test> select * from t where id>=:n;
991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    50 |   450 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |    50 |   450 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T_ID |     9 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=TO_NUMBER(:N))
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        250  consistent gets
          0  physical reads
          0  redo size
      10185  bytes sent via SQL*Net to client
        563  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        991  rows processed

3.突然想起以前遇到的問題,set autotrace traceonly explain問題,這個還可能導致執行計劃的改變.
http://blog.itpub.net/267265/viewspace-716004/

SCOTT@test> alter system flush shared_pool;
System altered.

SCOTT@test> select * from v$sql where sql_id='0ck6kww1pfuw8';
no rows selected

SCOTT@test> set autotrace traceonly explain
SCOTT@test> select * from t where id>=:n;
Execution Plan
---------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    50 |   450 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |    50 |   450 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T_ID |     9 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=TO_NUMBER(:N))

SCOTT@test> set autotrace off
SCOTT@test> select sql_id,child_number,sql_text from v$sql where sql_id='0ck6kww1pfuw8';
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ ------------------------------------------------------------
0ck6kww1pfuw8            0 select * from t where id>=:n
--可以發現已經生成了執行計劃.

SCOTT@test> select * from t where id>=:n;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0ck6kww1pfuw8, child number 0
-------------------------------------
select * from t where id>=:n
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id  | Operation                   | Name   | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |        |     5 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     50 |     5   (0)|
|*  2 |   INDEX RANGE SCAN          | I_T_ID |      9 |     2   (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=:N)
  
--可以發現執行計劃改變.

總結:
--可以發現看到的執行計劃選擇索引,而實際上根本不是這麼回事.實際上如果你使用 explain plan for...,在使用
--select * from table(dbms_xplan.display());查詢也是一樣的問題.

--總之10g以後看執行計劃最好使用dbms_xplan.display_cursor來看.或者不使用引數看執行計劃,也許這樣會準一點.