[20120209] SET AUTOTRACE TRACEONLY EXPLAIN的问题.txt

1.测试环境
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

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> select count(*) from emp where hiredate=to_date('&day-02-2011','DD-MM-YYYY');
Enter value for day: 30
old   1: select count(*) from emp where hiredate=to_date('&day-02-2011','DD-MM-YYYY')
new   1: select count(*) from emp where hiredate=to_date('30-02-2011','DD-MM-YYYY')

Execution Plan

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

Plan hash value: 2083865914

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     1 |     8 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |

|*  2 |   TABLE ACCESS FULL| EMP  |     1 |     8 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("HIREDATE"=TO_DATE('30-02-2011','DD-MM-YYYY'))


SQL> set autotrace traceonly ;
SQL> select count(*) from emp where hiredate=to_date('&day-02-2011','DD-MM-YYYY');
Enter value for day: 30
old   1: select count(*) from emp where hiredate=to_date('&day-02-2011','DD-MM-YYYY')
new   1: select count(*) from emp where hiredate=to_date('30-02-2011','DD-MM-YYYY')
select count(*) from emp where hiredate=to_date('30-02-2011','DD-MM-YYYY')
                                                *
ERROR at line 1:
ORA-01839: date not valid for month specified

区别在那里呢?很明显,在SET AUTOTRACE TRACEONLY EXPLAIN下，select并没有执行，仅仅执行explain plan for。而在set autotrace traceonly ;情况下是先执行在调用explain plan for.

2.接着测试:
我加入注解，为了进行硬分析，我仅仅执行一次：

SQL> set autotrace traceonly explain;
SQL> Select /*+ testme */ count(*) from emp where hiredate=to_date('30-02-2011','DD-MM-YYYY');

Execution Plan

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

Plan hash value: 2083865914

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     1 |     8 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |

|*  2 |   TABLE ACCESS FULL| EMP  |     1 |     8 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("HIREDATE"=TO_DATE('30-02-2011','DD-MM-YYYY'))

SQL> set autotrace off ;

SQL> column aa format a140
SQL> select executions,sql_id,substr(sql_text,1,150) aa from v$sql where lower(sql_text) like '%testme%' and lower(sql_text) not like '%sql_text%';
EXECUTIONS SQL_ID        AA
---------- ------------- --------------------------------------------------------------------------------------------------------------------------------------------
         0 2hj45mw2bn6dq Select /*+ testme */ count(*) from emp where hiredate=to_date('30-02-2011','DD-MM-YYYY')
         1 gwrj6jf4wd44r EXPLAIN PLAN SET STATEMENT_ID='PLUS127058' FOR Select /*+ testme */ count(*) from emp where hiredate=to_date('30-02-2011','DD-MM-YYYY')

--可以发现我并没有执行，而在v$sql中已经存在这样的光标，而EXECUTIONS=0。也就是讲这条语句已经完成了分析步骤。
--这样会带来一个问题，因为光标已经存在，这样再次执行的时候的时候，就不再分析了，这样可能导致执行计划选择不好的执行语句。


3.建立一个测试例子：
create table t as select rownum id ,'test' name  from dual connect by levelinsert into t select 1001,'aaaa' from dual connect by levelcommit ;
create index i_t_id on t(id);
exec dbms_stats.gather_table_stats(OWNNAME =>user,TABNAME =>'T',Method_Opt=>'FOR COLUMNS id SIZE 254');

SQL> column data_type format a20

SQL> SELECT table_name, column_name, data_type, histogram FROM dba_tab_cols WHERE table_name ='T' ;

TABLE_NAME                     COLUMN_NAME                    DATA_TYPE            HISTOGRAM

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

T                              NAME                           CHAR                 NONE

T                              ID                             NUMBER               FREQUENCY

我建立的表id分布很不均匀。


SQL> set autotrace traceonly explain;
SQL> variable a number;
SQL> exec :a := 1001;
PL/SQL procedure successfully completed.
SQL> select /*+ this_is_a_test */ * from t where id = :a ;

Execution Plan

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

Plan hash value: 4153437776

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

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |        |    47 |   423 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T      |    47 |   423 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | I_T_ID |    47 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=TO_NUMBER(:A))

SQL> set autotrace off;

SQL> column aa format a140
SQL> select executions,sql_id,substr(sql_text,1,150) aa from v$sql where lower(sql_text) like '%this_is_a_test%' and lower(sql_text) not like '%sql_text%';
EXECUTIONS SQL_ID        AA
---------- ------------- --------------------------------------------------------------------------------------------------------------------------------------------
         1 6cgfnfpaq0uyg EXPLAIN PLAN SET STATEMENT_ID='PLUS127070' FOR select /*+ this_is_a_test */ * from t where id = :a
         0 1qy45kg5422mr select /*+ this_is_a_test */ * from t where id = :a

SQL> select * from table(dbms_xplan.display_cursor('1qy45kg5422mr',NULL,'allstats last peeked_binds'));

PLAN_TABLE_OUTPUT

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

SQL_ID  1qy45kg5422mr, child number 0

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

select /*+ this_is_a_test */ * from t where id = :a

Plan hash value: 4153437776

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

| Id  | Operation                   | Name   | E-Rows |

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

|   0 | SELECT STATEMENT            |        |        |

|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     47 |

|*  2 |   INDEX RANGE SCAN          | I_T_ID |     47 |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=:A)

Note

-----

   - 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

25 rows selected.

--我们可以发现我定义是:a :=1001,而如果按照正常，第1次peeked_binds,选择的最佳的执行计划是全表扫描。而不是走索引。
SQL> print :a
         A
----------
      1001

SQL> select /*+ this_is_a_test */ * from t where id = :a ;
...

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'allstats last peeked_binds'));

PLAN_TABLE_OUTPUT

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

SQL_ID  1qy45kg5422mr, child number 0

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

select /*+ this_is_a_test */ * from t where id = :a

Plan hash value: 4153437776

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

| Id  | Operation                   | Name   | E-Rows |

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

|   0 | SELECT STATEMENT            |        |        |

|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     47 |

|*  2 |   INDEX RANGE SCAN          | I_T_ID |     47 |

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

Predicate Information (identified by operation id):

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

   2 - access("ID"=:A)

Note

-----

   - 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

25 rows selected.

SQL> select executions,sql_id,substr(sql_text,1,150) aa from v$sql where lower(sql_text) like '%this_is_a_test%' and lower(sql_text) not like '%sql_text%';
EXECUTIONS SQL_ID        AA
---------- ------------- --------------------------------------------------------------------------------------------------------------------------------------------
         1 6cgfnfpaq0uyg EXPLAIN PLAN SET STATEMENT_ID='PLUS127070' FOR select /*+ this_is_a_test */ * from t where id = :a
         1 1qy45kg5422mr select /*+ this_is_a_test */ * from t where id = :a


--可以发现执行计划选择了索引，一个不好的执行计划。

4.再做一个测试：
SQL> variable b number;
SQL> exec :b := 1001;
PL/SQL procedure successfully completed.

SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  3mn6m7yf29wht, child number 0

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

select /*+ this_is_a_test */ * from t where id = :b

Plan hash value: 1601196873

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

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |      |        |     4 (100)|

|*  1 |  TABLE ACCESS FULL| T    |   1000 |     4   (0)|

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

Peeked Binds (identified by position):

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

   1 - (NUMBER): 1001

Predicate Information (identified by operation id):

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

   1 - filter("ID"=:B)

Note

-----

   - 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

29 rows selected.

--看来以后在测试与显示执行计划最好不要使用set autotrace traceonly explain;
--做多仅仅使用set autotrace traceonly查看执行计划的统计信息。