[20120112]謹慎使用set autotrace traceonly檢視執行計劃.txt

自己經常在最佳化sql語句是經常使用set autotrace traceonly來最佳化sql語句.這樣的好處是如果輸出記錄很多,可以不受干擾,直接檢視執行計劃以及執行計劃的統計資訊.但是在一些特殊情況要注意也許執行計劃是不真實的.

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

1.建立測試表:
SQL> create table t2  (id number, name varchar2(100)) ;
Table created.
SQL> create index i_t2_id on t2(id);
Index created.

2.測試1:
SQL> insert into t2 select rownum id ,'test2' from dual connect by level <=10000;
10000 rows created.
SQL> commit ;
Commit complete.

SQL> set autot traceonly ;
SQL> select * from t2 where id=45;

Execution Plan
----------------------------------------------------------
Plan hash value: 3661687773
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    65 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2      |     1 |    65 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T2_ID |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=45)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        596  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



SQL> set autot off
SQL> select * from t2 where id=45;
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  03dqfy40b7t8r, child number 0
-------------------------------------
select * from t2 where id=45
Plan hash value: 3661687773
--------------------------------------------------------
| Id  | Operation                   | Name    | E-Rows |
--------------------------------------------------------
|   0 | SELECT STATEMENT            |         |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2      |      1 |
|*  2 |   INDEX RANGE SCAN          | I_T2_ID |      1 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=45)
Note
-----
   - dynamic sampling used for this statement (level=2)

--我沒有分析所以,採用的是dynamic sampling.

3.測試2:
SQL> delete from t2;
10000 rows deleted.
SQL> insert into t2 select  45 ,'test2' from dual connect by level <=10000;
10000 rows created.
SQL> commit ;


SQL> set autot traceonly ;
SQL> select * from t2 where id=45;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |   634K|     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   | 10000 |   634K|     8   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=45)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        183  consistent gets
          0  physical reads
       2224  redo size
     149746  bytes sent via SQL*Net to client
       1058  bytes received via SQL*Net from client
         51  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
--注意使用set autot traceonly 看到的是全表掃描.

SQL> set autot off
SQL> select * from t2 where id=45;
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  03dqfy40b7t8r, child number 0
-------------------------------------
select * from t2 where id=45
Plan hash value: 3661687773
--------------------------------------------------------
| Id  | Operation                   | Name    | E-Rows |
--------------------------------------------------------
|   0 | SELECT STATEMENT            |         |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2      |      1 |
|*  2 |   INDEX RANGE SCAN          | I_T2_ID |      1 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=45)
Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
26 rows selected.

實際上真正的執行計劃是INDEX RANGE SCAN!

當然這個僅僅是特例!!像使用explain plan for和dbms_xplan.display也是一樣的問題,自己在工作要注意這些細節.
SQL> explain plan for select * from t2 where id=45;

Explained.

SQL> select * from table(dbms_xplan.display(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 1513984157
-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |  10000 |
|*  1 |  TABLE ACCESS FULL| T2   |  10000 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=45)
Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
20 rows selected.