CBO the consequences of a wrong index selection

In the query for the 2 billion record large table, it was found that Oracle's execution plan selection was not stable, of course, this is the normal behavior of the CBO, but when the choice is different, the result is huge.

In the following query, the query quickly draws results using the specified index, but this depends on the hints mandatory designation:

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

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

If you do not add hints, the query is temporarily unable to produce results:

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

Its execution plan shows that this default execution led to the wrong index selection:

Sql> set Autotrace Trace explain

Sql> Select COUNT (*) from SMSMG where msg_to_dest_mdn= ' 861318888888 ' and service_id= ' 54 ';

Back to the column page: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/