[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- set autot traceonly與explain plan for的一點小區別AI
- EXPLAIN PLAN FOR 和 SET AUTOTRACE之間的差別AI
- [20121212]謹慎使用set autotrace traceonly檢視執行計劃[補充].txt
- [20120112]謹慎使用set autotrace traceonly檢視執行計劃.txt
- set autotrace in SQL*PlusSQL
- SQLPLUS中autotrace traceonly stat統計資訊解讀SQL
- set autotrace on [configure]
- set autotrace on時報SP2-0618錯誤的問題解決案例
- Set autotrace命令及解釋
- sql中使用函式導致explain plan for和set autotrace得到執行計劃不準確SQL函式AI
- EXPLAIN PLAN and SQL*PLUS AUTOTRACE may not generate actual plansAISQL
- autotrace explain plan 相關引數解釋AI
- 【優化】ORACLE set autotrace in SQL*Plus優化OracleSQL
- set autotrace的用法和含意及區別
- (轉)oracle效能工具包Explain plan、Autotrace、TkprofOracleAI
- 開啟執行計劃set autotrace on
- 設定autotrace的報錯問題解決
- toad顯示explain plan的問題AI
- sqlplus : set autot traceonly vs pl/sql developer : F5SQLDeveloper
- 檢視sql執行計劃--set autotraceSQL
- [20221203]sqlplus set trimspool 問題.txtSQL
- set autotrace on 產生不準確的執行計劃
- [20221202]sqlplus set trimout 問題.txtSQL
- set autotrace的選項和計劃報告的屬性
- autotrace 和explain plan for可能導致執行計劃錯誤AI
- 【原創】autotrace中statistics為0的問題的解決
- cassandra get set同步問題
- 以scott使用者執行set autotrace 出錯
- 檢視sql執行計劃--set autotrace [final]SQL
- 解決set newname 極慢的問題
- 使用set autotrace on 檢視資料庫執行計劃資料庫
- [20120109]sqlplus 與set longchunksize 設定問題.txtSQLGC
- 10g 中使用toad的sql編輯的autotrace的問題？SQL
- oracle10g_10.2.0.1_sql tuning_set autotrace配置OracleSQL
- Oracle 開啟SQL跟蹤執行SET AUTOTRACE ON命令時出錯OracleSQL
- 【AUTOTRACE】SQL優化的重要工具--AUTOTRACESQL優化
- postgresql copy UNICODE txt 問題。SQLUnicode
- Sql最佳化(十八) 調優工具（1）set autotrace和excute plan tableSQL