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。

来源：http://yangtingkun.itpub.net/post/468/470639