11G的SORT GROUP BY NOSORT導致錯誤執行計劃
Oracle11g在處理GROUP BY的操作的時候，可能錯誤的選擇索引全掃描加上SORT GROUP BY NOSORT執行計劃。而採用這個執行計劃，將使得SQL的執行時間大大增加。
看一個簡單的例子：
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SQL> create table t_big_table as select a.* from dba_objects a, tab;
表已建立。
SQL> create index ind_big_object_type on t_big_table(object_type);
索引已建立。
SQL> explain plan for
2 select * from
3 (
4 select object_type, count(*) num
5 from t_big_table
6 where status = 'VALID'
7 group by object_type
8 order by num desc
9 )
10 where rownum <= 5;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 584749733
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 120 | 1672 (7)| 00:00:31 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 900K| 20M| 1672 (7)| 00:00:31 |
|* 3 | SORT ORDER BY STOPKEY| | 900K| 13M| 1672 (7)| 00:00:31 |
| 4 | HASH GROUP BY | | 900K| 13M| 1672 (7)| 00:00:31 |
|* 5 | TABLE ACCESS FULL | T_BIG_TABLE | 900K| 13M| 1597 (3)| 00:00:29 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
5 - filter("STATUS"='VALID')
Note
-----
- dynamic sampling used for this statement
已選擇23行。
SQL> set timing on
SQL> select * from
2 (
3 select object_type, count(*) num
4 from t_big_table
5 where status = 'VALID'
6 group by object_type
7 order by num desc
8 )
9 where rownum <= 5;
OBJECT_TYPE NUM
-------------------------------------- ----------
SYNONYM 320040
JAVA CLASS 265236
VIEW 59328
INDEX 40572
TABLE 31608
已用時間: 00: 00: 01.07
SQL> exec dbms_stats.gather_table_stats(user, 't_big_table')
PL/SQL 過程已成功完成。
已用時間: 00: 00: 08.85
SQL> alter table t_big_table modify object_type not null;
表已更改。
已用時間: 00: 00: 00.33
SQL> explain plan for
2 select * from
3 (
4 select object_type, count(*) num
5 from t_big_table
6 where status = 'VALID'
7 group by object_type
8 order by num desc
9 )
10 where rownum <= 5;
已解釋。
已用時間: 00: 00: 00.02
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 1626512338
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 120 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 7 | 168 | 3 (0)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY | | 7 | 112 | 3 (0)| 00:00:01 |
| 4 | SORT GROUP BY NOSORT | | 7 | 112 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T_BIG_TABLE | 830K| 12M| 3 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | IND_BIG_OBJECT_TYPE | 7 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
5 - filter("STATUS"='VALID')
已選擇20行。
已用時間: 00: 00: 00.04
SQL> select * from
2 (
3 select object_type, count(*) num
4 from t_big_table
5 where status = 'VALID'
6 group by object_type
7 order by num desc
8 )
9 where rownum <= 5;
OBJECT_TYPE NUM
-------------------------------------- ----------
SYNONYM 320040
JAVA CLASS 265236
VIEW 59328
INDEX 40572
TABLE 31608
已用時間: 00: 00: 02.03
Oracle在這裡採用索引全掃描，因為這裡可以利用索引掃描的順序來避免GROUP BY的排序操作，也就是題目中所說的SORT GROUP BY NOSORT操作。但是如果表的資料量很大，那麼採用索引全掃描來避免GROUP BY排序就顯得得不償失了。因為全表掃描的DB_FILE_MULTIBLOCK_READ_COUNT引數引發的批操作效能提升，遠遠大約GROUP BY NOSORT操作的效能提升。
如果說上面的結果由於資料量比較小，還不足以看出兩個執行計劃的差別，那麼下面換成一張資料量很大的表，就可以清晰的看到兩種執行計劃所帶來的效能差別了：
SQL> SELECT COUNT(*) FROM ORD_HIT_COMM;
COUNT(*)
----------
59701565
SQL> SET AUTOT ON
SQL> SET TIMING ON
SQL> SELECT *
2 FROM
3 (
4 SELECT BUYER_ORGID, COUNT(*) NUM
5 FROM ORD_HIT_COMM
6 WHERE ENABLE_FLAG = '1'
7 AND OOS_FLAG = '0'
8 GROUP BY BUYER_ORGID
9 ORDER BY NUM DESC
10 )
11 WHERE ROWNUM <= 5;
BUYER_ORGID NUM
------------------------ ----------
DATA10000000000001232550 29385
DATA10000000000001622868 29385
DATA10000000000002590025 29385
DATA10000000000002683112 29385
DATA10000000000002590027 29385
已用時間: 01: 09: 33.45
執行計劃
----------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 195 | 5 (0)|
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 6 | 234 | 5 (0)|
|* 3 | SORT ORDER BY STOPKEY | | 6 | 174 | 5 (0)|
| 4 | SORT GROUP BY NOSORT | | 6 | 174 | 5 (0)|
|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| ORD_HIT_COMM | 40M| 1125M| 5 (0)|
| 6 | INDEX FULL SCAN | TU_ORD_HIT_COMM_BUYER_ORGID | 9 | | 4 (0)|
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
5 - filter("ENABLE_FLAG"='1' AND "OOS_FLAG"='0')
Note
-----
- 'PLAN_TABLE' is old version
統計資訊
----------------------------------------------------------
8 recursive calls
0 db block gets
5414523 consistent gets
3721820 physical reads
0 redo size
781 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
SQL> SELECT *
2 FROM
3 (
4 SELECT /*+ FULL(ORD_HIT_COMM) */ BUYER_ORGID, COUNT(*) NUM
5 FROM ORD_HIT_COMM
6 WHERE ENABLE_FLAG = '1'
7 AND OOS_FLAG = '0'
8 GROUP BY BUYER_ORGID
9 ORDER BY NUM DESC
10 )
11 WHERE ROWNUM <= 5;
BUYER_ORGID NUM
------------------------ ----------
FR20T0000005000000082670 29385
FR20T0000005000000079729 29385
FR20T0000005000000082642 29385
FR20T0000005000000079681 29385
FR20T0000005000000082661 29385
已用時間: 00: 06: 36.05
執行計劃
----------------------------------------------------------
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 195 | 1307K (2)| | |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 3938 | 149K| 1307K (2)| | |
|* 3 | SORT ORDER BY STOPKEY| | 3938 | 111K| 1307K (2)| | |
| 4 | HASH GROUP BY | | 3938 | 111K| 1307K (2)| | |
| 5 | PARTITION HASH ALL | | 40M| 1125M| 1304K (1)| 1 | 16 |
|* 6 | TABLE ACCESS FULL | ORD_HIT_COMM | 40M| 1125M| 1304K (1)| 1 | 16 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
6 - filter("ENABLE_FLAG"='1' AND "OOS_FLAG"='0')
Note
-----
- 'PLAN_TABLE' is old version
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
3409221 consistent gets
3409037 physical reads
0 redo size
781 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
透過索引全掃描需要1個小時，而透過全表掃描只需要6分半鐘的時間。
而在Oracle的9i中，同樣的SQL不會導致這種執行計劃的產生：
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM
4 (
5 SELECT BUYER_ORGID, COUNT(*) NUM
6 FROM ORD_HIT_COMM
7 WHERE ENABLE_FLAG = '1'
8 AND OOS_FLAG = '0'
9 GROUP BY BUYER_ORGID
10 ORDER BY NUM DESC
11 )
12 WHERE ROWNUM <= 5;
已解釋。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 195 | | 710K| | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 3938 | 149K| | 710K| | |
|* 3 | SORT ORDER BY STOPKEY| | 3938 | 111K| 380M| 710K| | |
| 4 | SORT GROUP BY | | 3938 | 111K| 380M| 710K| | |
| 5 | PARTITION HASH ALL | | | | | | 1 | 16 |
|* 6 | TABLE ACCESS FULL | ORD_HIT_COMM | 9950K| 275M| | 327K| 1 | 16 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
6 - filter("ORD_HIT_COMM"."ENABLE_FLAG"='1' AND "ORD_HIT_COMM"."OOS_FLAG"='0')
Note: cpu costing is off
已選擇21行。
SQL> SET TIMING ON
SQL> SELECT *
2 FROM
3 (
4 SELECT BUYER_ORGID, COUNT(*) NUM
5 FROM ORD_HIT_COMM
6 WHERE ENABLE_FLAG = '1'
7 AND OOS_FLAG = '0'
8 GROUP BY BUYER_ORGID
9 ORDER BY NUM DESC
10 )
11 WHERE ROWNUM <= 5;
BUYER_ORGID NUM
------------------------ ----------
DATA10000000000001232550 29385
DATA10000000000001622868 29385
DATA10000000000002590025 29385
DATA10000000000002683112 29385
DATA10000000000002590027 29385
已用時間: 00: 07: 36.48
查詢了一下metalink，感覺文件Bug No. 5919513中描述的bug和當前問題很像，Oracle將在11.2中解決這個bug。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-444170/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- autotrace 和explain plan for可能導致執行計劃錯誤AI
- 執行計劃錯誤導致系統負載高負載
- 統計資訊不正確導致執行計劃的錯誤選擇
- 執行計劃的偏差導致的效能問題
- 完美的執行計劃導致的效能問題
- Grant許可權導致執行計劃失效
- 12.2.0.1bug導致的Failed to register in OCRLOCAL group.錯誤AI
- 動態建立 @ViewChild 導致執行時錯誤的原因分析View
- 交流（1）-- 執行計劃錯誤問題
- MySQL5.6執行計劃錯誤案例分析MySql
- 【sql調優之執行計劃】sort operationsSQL
- 慎用sys_context，可能導致無法正確的bind_peeking，而選擇錯誤的執行計劃Context
- Oracle當number型別超過一定長度直方圖限制導致SQL執行計劃錯誤Oracle型別直方圖SQL
- 執行計劃變化導致CPU負載高的問題分析負載
- 統計資訊不準確導致執行計劃走了笛卡爾積
- 執行計劃中的COLLECTION ITERATOR PICKLER FETCH導致的效能問題
- 【sql調優之執行計劃】merge sort joinSQL
- 索引失效系列——統計量過期引起執行計劃錯誤索引
- impdp時parallel=4導致的錯誤Parallel
- 一次ORACLE SQL謂詞跨界導致的執行計劃不準OracleSQL
- 執行計劃問題導致處理速度時快時慢的問題
- 11g 改變SQL執行計劃SQL
- 8i下sort*排序大小以及執行計劃的問題？排序
- Node出錯導致執行崩潰的解決方案
- MySQL 因資料型別轉換導致執行計劃使用低效索引MySql資料型別索引
- 檢視執行計劃出現ORA-22992錯誤
- 多餘索引導致explain錯誤索引AI
- 找出導致db file scattered read等待事件發生的SQL及其執行計劃事件SQL
- 由於統計量失真造成SQL執行計劃錯誤一例SQL
- 解決 PBootCMS 中因資料庫名稱錯誤導致的“執行 SQL 發生錯誤！錯誤：no such table: ay_config”問題boot資料庫SQL
- _optimizer_invalidation_periond導致收集統計資訊後執行計劃沒有改變
- date列統計資訊陳舊導致sql沒有選擇最優執行計劃SQL
- oracle的執行計劃居然出錯[轉帖]Oracle
- 執行計劃-1：獲取執行計劃
- 【sql調優之執行計劃】merge join cartesian and buffer sortSQL
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- merge語句導致的ORA錯誤分析
- 11g改變了DELETE語句的執行計劃delete