[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')
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');
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');
我建立的表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 ;
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
--我们可以发现我定义是:a :=1001,而如果按照正常，第1次peeked_binds,选择的最佳的执行计划是全表扫描。而不是走索引。
SQL> print :a
A
----------
1001
SQL> select /*+ this_is_a_test */ * from t where id = :a ;
...
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.
--看来以后在测试与显示执行计划最好不要使用set autotrace traceonly explain;
--做多仅仅使用set autotrace traceonly查看执行计划的统计信息。
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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-716004/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- [20261102]光标跟踪事件9.txt
- [20261102]光标跟踪事件8.txt
- [20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
- [20261102]改变pdb名字.txt
- [20261018]光标跟踪事件3.txt
- [20261016]光标跟踪事件2.txt
- [20261015]模拟kksfbc child completion等待事件2.txt
- [20261013]光标跟踪事件.txt
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
