CBO的魔术 - 一个错误的索引选择会带来的后果

在对20亿记录大表的查询中，发现Oracle的执行计划选择并不稳定，当然这是CBO的正常行为，然而当选择不同时，结果是巨大的。

在以下查询中，使用指定的索引，查询快速得出结果，但是这依赖于Hints的强制指定：

SQL> select /*+  index(smsmg IDX_smsmg_DEST_MDN)  */ count(*)
   2 from smsmg where msg_to_dest_mdn='861318888888' and service_id='54';

  COUNT(*)
----------
         1

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1659057974

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                          |     1 |    18 |    98   (0)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE                     |                          |     1 |    18 |            |          |       |       |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| smsmg                    |     1 |    18 |    98   (0)| 00:00:02 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                 | IDX_smsmg_msg_to_des_mdn |   106 |       |     4   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SERVICE_ID"='54')
   3 - access("msg_to_dest_mdn"='861318888888')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         82  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

而如果不加Hints，查询是一时无法得出结果的：

SQL> select count(*) from smsmg where msg_to_dest_mdn='861318888888' and service_id='54';
 
select count(*) from smsmg where msg_to_dest_mdn='861318888888' and service_id='54'
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation


Elapsed: 00:04:27.88

其执行计划显示，这一缺省的执行方式导致了错误的索引选择：

SQL> set autotrace trace explain
SQL> select count(*) from smsmg where msg_to_dest_mdn='861318888888' and service_id='54';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1152948967

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |    18 |     5   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                     |                      |     1 |    18 |            |          |       |       |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| smsmg                |     1 |    18 |     5   (0)| 00:00:01 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                 | IDX_smsmg_SERVICE_ID |     1 |       |     4   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("msg_to_dest_mdn"='861318888888')
   3 - access("SERVICE_ID"='54')

说CBO是Oracle最为博大精深的技术一点也不为过，只是这技术越复杂越深奥出错的机会就越多了。

这个表的数据量大约是500G：

SQL> select segment_name,partition_name,bytes/1024/1024/1024 GB,blocks from dba_segments
  2  where owner='SMSMSG' and segment_name='SMSSENDMSG';

SEGMENT_NAME                   PARTITION_NAME          GB     BLOCKS
------------------------------ --------------- ---------- ----------
SMSSENDMSG                    M01                 30.625    4014080
SMSSENDMSG                    M02                 29.875    3915776
SMSSENDMSG                    M03                  43.25    5668864
SMSSENDMSG                    M04                     38    4980736
SMSSENDMSG                    M05                43.1875    5660672
SMSSENDMSG                    M06                50.6875    6643712
SMSSENDMSG                    M08                55.4375    7266304
SMSSENDMSG                    M09                 32.125    4210688
SMSSENDMSG                    M10                23.9375    3137536
SMSSENDMSG                    M11                25.6875    3366912
SMSSENDMSG                    M12                31.9375    4186112

SEGMENT_NAME                   PARTITION_NAME          GB     BLOCKS
------------------------------ --------------- ---------- ----------
SMSSENDMSG                    M13             .000061035          8
SMSSENDMSG                    M07                   58.5    7667712

13 rows selected.

SQL> select sum(bytes)/1024/1024/1024 GB from dba_segments where owner='SMSMSG' and segment_name='SMSSENDMSG';

        GB
----------
463.250061

每个分区的记录数量大约是1~2亿条：

SQL> select table_owner,table_name,partition_name,num_rows from dba_tab_partitions
  2  where table_owner='SMS9885' and table_name='SMS_TO_ISMG';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME    NUM_ROWS
------------------------------ ------------------------------ --------------- ----------
SMSMSG                        SMSSENDMSG                    M01              135605804
SMSMSG                        SMSSENDMSG                    M02              134599287
SMSMSG                        SMSSENDMSG                    M03              187959758
SMSMSG                        SMSSENDMSG                    M04              169663942
SMSMSG                        SMSSENDMSG                    M05              187435468
SMSMSG                        SMSSENDMSG                    M06              222079762
SMSMSG                        SMSSENDMSG                    M07              256482704
SMSMSG                        SMSSENDMSG                    M08              229089535
SMSMSG                        SMSSENDMSG                    M09              122453724
SMSMSG                        SMSSENDMSG                    M10              104093080
SMSMSG                        SMSSENDMSG                    M11              116095184

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME    NUM_ROWS
------------------------------ ------------------------------ --------------- ----------
SMSMSG                        SMSSENDMSG                    M12              143216009
SMSMSG                        SMSSENDMSG                    M13                      0

13 rows selected.

前面使用的都是单键值索引，其索引选择性参考前几天的记录。


-The End-