[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 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 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.