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

--------------------------------------------------------

| Id  | Operation                   | Name    | E-Rows |

|   0 | SELECT STATEMENT            |         |        |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2      |      1 |

|*  2 |   INDEX RANGE SCAN          | I_T2_ID |      1 |

--我没有分析所以,采用的是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.

10000 rows selected.

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 |

   1 - filter("ID"=45)

        183  consistent gets

       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

      10000  rows processed

--注意使用set autot traceonly 看到的是全表扫描.

   - 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'));

------------------------------------------

-------------------------------------------

| Id  | Operation         | Name | E-Rows |

|   0 | SELECT STATEMENT  |      |  10000 |

|*  1 |  TABLE ACCESS FULL| T2   |  10000 |

20 rows selected.