autotrace 和explain plan for可能導致執行計劃錯誤
[i=s] 本帖最後由 wei-xh 於 2013-9-27 15:26 編輯
使用autotrace工具和explain plan for來獲得SQL的執行計劃，有可能會出現與實際的執行計劃不符的情況。autotrace本身其實也是呼叫了explain plan for來獲取的執行計劃。
本文總結了在使用這兩種工具過程中，最常見的導致執行計劃與實際情況不符的兩個場景。
第一種情況：資料型別隱式轉換
使用explain plan for工具時，要意識到ORACLE會把你傳入的繫結變數作為varchar2型別來處理。
create table wxh_tbd(id varchar2(100));
create index wxh_ind on wxh_tbd(id);
exec dbms_stats.gather_table_stats(user,'wxh_tbd');
SQL> var c number;
SQL> var d varchar2
SQL>
SQL> exec :c :=1
PL/SQL procedure successfully completed.
SQL> exec :d :='1'
PL/SQL procedure successfully completed.
SQL> set autotrace on
SQL>
SQL> alter session set events '10046 trace name context forever ,level 12' ;
Session altered.
上面建立了一張表，表裡只有一個字元型別的欄位。宣告瞭兩個變數，一個為字元型，一個為數字型。
SQL> select count(*) from wxh_tbd where id= :d;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3024061193
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| WXH_IND | 1 | 5 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:D)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from wxh_tbd where id= :c;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3024061193
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| WXH_IND | 1 | 5 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:C)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我們看到兩次查詢走的執行計劃是一樣的，都走了索引掃描，但是產生的邏輯讀差異是很大的，這裡面有貓膩！
雖然我們定義的c變數為number，但是explain plan for忽略了這個事實，僅僅把它作為varchar2來處理，因此執行計劃顯示的，還是走了索引掃描。
我們看看10046 trace的結果：
從跟蹤檔案中很容易看到set autotrace 本身呼叫了explain plan for來產生執行計劃。
EXPLAIN PLAN SET STATEMENT_ID='PLUS456426' FOR select count(*) from wxh_tbd
where id= :d
EXPLAIN PLAN SET STATEMENT_ID='PLUS456426' FOR select count(*) from wxh_tbd
where id= :c
跟蹤檔案裡清楚的記錄了，SQL的真實執行計劃為全表掃描，發生了資料型別的隱式轉換：
select count(*)
from
wxh_tbd where id= :c
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 60 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.01 0 60 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 45
Number of plan statistics captured: 2
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=30 pr=0 pw=0 time=2886 us)
1 1 1 TABLE ACCESS FULL WXH_TBD (cr=30 pr=0 pw=0 time=2870 us cost=7 size=5 card=1)
第二種情況：繫結變數窺探，用explain plan for並不會受繫結變數窺探的影響，因此在列存在直方圖的情況下，有可能explain plan for產生的執行計劃於實際的執行計劃不符。
SQL> drop table a;
Table dropped.
SQL> create table a as select object_id,object_type from dba_objects;
Table created.
SQL> insert into a select object_id,'TABLE' object_type from dba_objects;
17537 rows created.
SQL> commit;
Commit complete.
SQL> create index a_i on a(object_type);
Index created.
SQL> begin
2 dbms_stats.gather_table_stats(ownname =>'dlsp',
3 tabname => 'a',
4 no_invalidate => FALSE,
5 estimate_percent => 100,
6 force => true,
7 degree => 5,
8 method_opt => 'for columns object_type size 10',
9 cascade => false);
10 end;
11 /
SQL> @tabstat
Please enter Name of Table Owner: dlsp
Please enter Table Name : a
**********************************************************
Table Level
**********************************************************
Table Number Empty Chain Average Global Sample Date
Name of Rows Blocks Blocks Count Row Len Stats Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
A 35,074 0,85 0 0 10 YES 35,074 09-27-2013
Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
OBJECT_ID 17,536 .00005703 1 2 35,072 09-27-2013
OBJECT_TYPE 38 .07672567 10 0 35,074 09-27-2013
Index Leaf Distinct Number AV Av Cluster Date
Name BL Blks Keys of Rows LEA Data Factor MM-DD-YYYY
------------------------------ -- ------------ -------------- -------------- ------- -------- ------------ ----------
A_I 1 86 38 35,074 2 10 398 09-27-2013
Index Column Col Column
Name Name Pos Details
------------------------------ ------------------------------ ---- ------------------------
A_I OBJECT_TYPE 1 VARCHAR2(19)
SQL> var c varchar2(100)
SQL> exec :c := 'TABLE'
PL/SQL procedure successfully completed.
經過上面的程式碼，我們在object_type上產生了一個直方圖，並且object_type存在資料傾斜，表中存在大量的object_type為TABLE的值。
SQL> select count(*) from a where object_type=:c;
COUNT(*)
----------
19555
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3009055403
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| A_I | 923 | 6461 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"=:C)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
83 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
獲得SQL_ID後，檢視真實的執行計劃。發現走的實際為全表掃描的執行計劃，還可以透過Peeked Binds部分看到此執行計劃窺探了實際的變數值
SQL> select * from table(dbms_xplan.display_cursor('7q143s0aqfm1q',null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7q143s0aqfm1q, child number 0
-------------------------------------
select count(*) from a where object_type=:c
Plan hash value: 2223038180
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| A | 21044 | 143K| 17 (6)| 00:00:01 |
---------------------------------------------------------------------------
Outline Data
Peeked Binds (identified by position):
--------------------------------------
1 - :C (VARCHAR2(30), CSID=871): 'TABLE'
使用autotrace工具和explain plan for來獲得SQL的執行計劃，有可能會出現與實際的執行計劃不符的情況。autotrace本身其實也是呼叫了explain plan for來獲取的執行計劃。
本文總結了在使用這兩種工具過程中，最常見的導致執行計劃與實際情況不符的兩個場景。
第一種情況：資料型別隱式轉換
使用explain plan for工具時，要意識到ORACLE會把你傳入的繫結變數作為varchar2型別來處理。
create table wxh_tbd(id varchar2(100));
create index wxh_ind on wxh_tbd(id);
exec dbms_stats.gather_table_stats(user,'wxh_tbd');
SQL> var c number;
SQL> var d varchar2
SQL>
SQL> exec :c :=1
PL/SQL procedure successfully completed.
SQL> exec :d :='1'
PL/SQL procedure successfully completed.
SQL> set autotrace on
SQL>
SQL> alter session set events '10046 trace name context forever ,level 12' ;
Session altered.
上面建立了一張表，表裡只有一個字元型別的欄位。宣告瞭兩個變數，一個為字元型，一個為數字型。
SQL> select count(*) from wxh_tbd where id= :d;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3024061193
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| WXH_IND | 1 | 5 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:D)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from wxh_tbd where id= :c;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3024061193
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| WXH_IND | 1 | 5 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:C)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我們看到兩次查詢走的執行計劃是一樣的，都走了索引掃描，但是產生的邏輯讀差異是很大的，這裡面有貓膩！
雖然我們定義的c變數為number，但是explain plan for忽略了這個事實，僅僅把它作為varchar2來處理，因此執行計劃顯示的，還是走了索引掃描。
我們看看10046 trace的結果：
從跟蹤檔案中很容易看到set autotrace 本身呼叫了explain plan for來產生執行計劃。
EXPLAIN PLAN SET STATEMENT_ID='PLUS456426' FOR select count(*) from wxh_tbd
where id= :d
EXPLAIN PLAN SET STATEMENT_ID='PLUS456426' FOR select count(*) from wxh_tbd
where id= :c
跟蹤檔案裡清楚的記錄了，SQL的真實執行計劃為全表掃描，發生了資料型別的隱式轉換：
select count(*)
from
wxh_tbd where id= :c
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 60 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.01 0 60 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 45
Number of plan statistics captured: 2
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=30 pr=0 pw=0 time=2886 us)
1 1 1 TABLE ACCESS FULL WXH_TBD (cr=30 pr=0 pw=0 time=2870 us cost=7 size=5 card=1)
第二種情況：繫結變數窺探，用explain plan for並不會受繫結變數窺探的影響，因此在列存在直方圖的情況下，有可能explain plan for產生的執行計劃於實際的執行計劃不符。
SQL> drop table a;
Table dropped.
SQL> create table a as select object_id,object_type from dba_objects;
Table created.
SQL> insert into a select object_id,'TABLE' object_type from dba_objects;
17537 rows created.
SQL> commit;
Commit complete.
SQL> create index a_i on a(object_type);
Index created.
SQL> begin
2 dbms_stats.gather_table_stats(ownname =>'dlsp',
3 tabname => 'a',
4 no_invalidate => FALSE,
5 estimate_percent => 100,
6 force => true,
7 degree => 5,
8 method_opt => 'for columns object_type size 10',
9 cascade => false);
10 end;
11 /
SQL> @tabstat
Please enter Name of Table Owner: dlsp
Please enter Table Name : a
**********************************************************
Table Level
**********************************************************
Table Number Empty Chain Average Global Sample Date
Name of Rows Blocks Blocks Count Row Len Stats Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
A 35,074 0,85 0 0 10 YES 35,074 09-27-2013
Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
OBJECT_ID 17,536 .00005703 1 2 35,072 09-27-2013
OBJECT_TYPE 38 .07672567 10 0 35,074 09-27-2013
Index Leaf Distinct Number AV Av Cluster Date
Name BL Blks Keys of Rows LEA Data Factor MM-DD-YYYY
------------------------------ -- ------------ -------------- -------------- ------- -------- ------------ ----------
A_I 1 86 38 35,074 2 10 398 09-27-2013
Index Column Col Column
Name Name Pos Details
------------------------------ ------------------------------ ---- ------------------------
A_I OBJECT_TYPE 1 VARCHAR2(19)
SQL> var c varchar2(100)
SQL> exec :c := 'TABLE'
PL/SQL procedure successfully completed.
經過上面的程式碼，我們在object_type上產生了一個直方圖，並且object_type存在資料傾斜，表中存在大量的object_type為TABLE的值。
SQL> select count(*) from a where object_type=:c;
COUNT(*)
----------
19555
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3009055403
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | INDEX RANGE SCAN| A_I | 923 | 6461 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"=:C)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
83 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
獲得SQL_ID後，檢視真實的執行計劃。發現走的實際為全表掃描的執行計劃，還可以透過Peeked Binds部分看到此執行計劃窺探了實際的變數值
SQL> select * from table(dbms_xplan.display_cursor('7q143s0aqfm1q',null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7q143s0aqfm1q, child number 0
-------------------------------------
select count(*) from a where object_type=:c
Plan hash value: 2223038180
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 17 (100)| |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| A | 21044 | 143K| 17 (6)| 00:00:01 |
---------------------------------------------------------------------------
Outline Data
Peeked Binds (identified by position):
--------------------------------------
1 - :C (VARCHAR2(30), CSID=871): 'TABLE'
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-773482/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sql中使用函式導致explain plan for和set autotrace得到執行計劃不準確SQL函式AI
- Oracle執行計劃Explain Plan 如何使用OracleAI
- Oracle 執行計劃（Explain Plan） 說明OracleAI
- Oracle 執行計劃（Explain Plan） 說明OracleAI
- 使用EXPLAIN PLAN來檢視執行計劃AI
- TOAD中檢視執行計劃（Explain Plan）AI
- 配置oracle 解釋執行計劃--explain planOracleAI
- 【Explain Plan】檢視SQL的執行計劃AISQL
- 執行計劃錯誤導致系統負載高負載
- 【最佳化】explain plan for 方式存取執行計劃AI
- 統計資訊不正確導致執行計劃的錯誤選擇
- 多餘索引導致explain錯誤索引AI
- 11G的SORT GROUP BY NOSORT導致錯誤執行計劃
- oracle explain plan for獲取執行計劃並不可靠.OracleAI
- EXPLAIN PLAN FOR 和 SET AUTOTRACE之間的差別AI
- 檢視執行計劃方法總結之一：explain plan命令AI
- 【執行計劃】格式化EXPLAIN PLAN的輸出結果AI
- 使用 EXPLAIN PLAN 獲取SQL語句執行計劃 （R0.1）AISQL
- Explain Plan中AUTOTRACE引起的SP2-0613和SP2-0611錯誤 (轉）AI
- explain執行計劃分析AI
- mysql執行計劃explainMySqlAI
- mysql explain 執行計劃MySqlAI
- EXPLAIN PLAN and SQL*PLUS AUTOTRACE may not generate actual plansAISQL
- autotrace explain plan 相關引數解釋AI
- Explain執行計劃詳解AI
- explain 查詢執行計劃AI
- 獲取執行計劃之Autotrace
- 開啟執行計劃set autotrace on
- (轉)oracle效能工具包Explain plan、Autotrace、TkprofOracleAI
- Oracle優化——如何檢視語句的準確的執行計劃（explain plan可能不是真實的）Oracle優化AI
- 慎用sys_context，可能導致無法正確的bind_peeking，而選擇錯誤的執行計劃Context
- 檢視sql執行計劃--set autotraceSQL
- MySQL Explain執行計劃 - 詳解MySqlAI
- 十六、Mysql之Explain執行計劃MySqlAI
- mysql explain 執行計劃詳解MySqlAI
- MySQL執行計劃EXPLAIN詳解MySqlAI
- ORACLE執行計劃 explain說明OracleAI
- oracle執行計劃的使用(EXPLAIN)OracleAI