11G的SORT GROUP BY NOSORT导致错误执行计划

最新推荐文章于 2021-09-28 14:52:55 发布

ckawt40802

最新推荐文章于 2021-09-28 14:52:55 发布

阅读量291

点赞数

文章标签：数据库 java

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;

已用时间: 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-444170/