ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120209] SET AUTOTRACE TRACEONLY EXPLAIN的问题.txt

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

原创 Linux操作系统 作者:lfree 时间:2012-02-10 10:16:01 0 删除 编辑
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 level<=1000;
insert into t select 1001,'aaaa' from dual connect by level<=1000;
commit ;
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查看执行计划的统计信息。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-716004/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2317
  • 访问量
    6045649