在对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-


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html