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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle執行計劃Explain Plan 如何使用OracleAI
- 獲取執行計劃之Autotrace
- mysql explain 執行計劃MySqlAI
- explain執行計劃分析AI
- mysql執行計劃explainMySqlAI
- Explain執行計劃詳解AI
- explain 查詢執行計劃AI
- mysql explain 執行計劃詳解MySqlAI
- 十六、Mysql之Explain執行計劃MySqlAI
- MySQL Explain執行計劃 - 詳解MySqlAI
- 使用set autotrace on 檢視資料庫執行計劃資料庫
- (4) MySQL中EXPLAIN執行計劃分析MySqlAI
- mysql調優之——執行計劃explainMySqlAI
- MongoDb學習之Explain執行計劃MongoDBAI
- MySQL explain執行計劃詳細解釋MySqlAI
- 動態建立 @ViewChild 導致執行時錯誤的原因分析View
- MySQL——通過EXPLAIN分析SQL的執行計劃MySqlAI
- Hive底層原理：explain執行計劃詳解HiveAI
- Oracle當number型別超過一定長度直方圖限制導致SQL執行計劃錯誤Oracle型別直方圖SQL
- [20221104]執行計劃一樣Plan hash value不同.txt
- MySQL調優篇 | EXPLAIN執行計劃解讀（4）MySqlAI
- 檢視執行計劃出現ORA-22992錯誤
- 【SQL_PLAN】Oracle 透過檢視sql_plan 格式化執行計劃SQLOracle
- 在MySQL中使用explain查詢SQL的執行計劃MySqlAI
- MySQL優化從執行計劃開始(explain超詳細)MySql優化AI
- 檢視一個正在執行的sql的執行計劃(explain for connection processlist_id)SQLAI
- 解決 PBootCMS 中因資料庫名稱錯誤導致的“執行 SQL 發生錯誤！錯誤：no such table: ay_config”問題boot資料庫SQL
- [20240313]toad gather_plan_statistics執行計劃相關問題.txt
- MySQL 因資料型別轉換導致執行計劃使用低效索引MySql資料型別索引
- 執行錯誤集
- 執行計劃-1：獲取執行計劃
- 不會看 Explain執行計劃，勸你簡歷別寫熟悉 SQL優化AISQL優化
- 不會看 Explain 執行計劃，勸你簡歷別寫熟悉 SQL 優化AISQL優化
- Lombok 的@ToString導致的Maven編譯錯誤LombokMaven編譯
- ORA-04031錯誤導致當機案例分析
- ORACLE analyse table方式收集表統計資訊導致SQL執行計劃不準確而效能下降OracleSQL
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- 不會看 Explain 執行計劃，勸你簡歷別寫熟悉 SQL 最佳化AISQL
- MySQL執行計劃MySql