[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部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-716004/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20221203]sqlplus set trimspool 問題.txtSQL
- [20221202]sqlplus set trimout 問題.txtSQL
- [20211231]set linesize and dbms_output.line輸出問題.txt
- 使用set autotrace on 檢視資料庫執行計劃資料庫
- Oracle autotrace 報 SP2-0618 PLUSTRACE role 問題解決Oracle
- [20190116]rman的老問題.txt
- [20181227]bbed的使用問題.txt
- shell 中的 set -e 導致的退出問題
- [20190314]使用strace注意的問題.txt
- [20231020]增加欄位的問題.txt
- [20191129]關於hugepages的問題.txt
- [20240807]數值累加的問題.txt
- [20190215]sqlplus set arraysize.txtSQL
- [20180924]vim set wildmenu.txt
- [20201103]set newname for datafile.txt
- [20190221]sql patch 問題.txtSQL
- [20181217]strace使用問題.txt
- [20181204]bbed修改問題.txt
- [20190313]備份問題.txt
- [20180619]bbed verify問題.txt
- [20210812]windows xcopy問題.txtWindows
- [20190116]詭異的問題2.txt
- [20231207]ls -f的顯示問題.txt
- [20181229]關於字串的分配問題.txt字串
- [20180417]奇怪的grep過濾問題.txt
- [20211214]ezconnect輸入口令的問題.txt
- [20211210]優化遇到的奇怪問題.txt優化
- [20221125]設定hugepages遇到的問題.txt
- [20201126]18c VPD的問題.txt
- [20201126]11g VPD的問題.txt
- [20181206]toad 12小問題.txt
- [20180509]函式索引問題.txt函式索引
- [20190522]rman備份問題.txt
- [20220329]windows xcopy命令問題.txtWindows
- [20221107]除錯crontab問題.txt除錯
- [20220623]linux 口令expire問題.txtLinux
- [20210303]bbed使用小問題.txt
- [20201221]spfile設定問題.txt
- Lombok生成get/set異常問題（Lombok缺陷）Lombok