Performance issue caused by uncorrected statistics data

· Troubleshooting:

1st, Hosting report a program seems hung, DB server CPU raised up

2nd , Found a abnormal top SQL from statspack report
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
    110,089,488        1,555       70,797.1   40.0  2801.99   3369.20 3261003379
Module: SQL*Plus
SELECT ROWID FROM CSS_TP_SHMT_QUEUE WHERE CARRIER_ID = :B6 AND T
RDNG_PTNR_ID = :B5 AND CNTR_ID = :B4 AND SHMT_ID = :B3 AND EVENT
_TYPE_ID = :B2 AND TRANSHMT_SEQ = :B1 AND ROWNUM = 1
3rd, As we set the statspack snapshot be level 6, we generate the SQL report from statspack. Fond it’s strange that its cost in execution plan is very low, but the Buffer Gets (find in the 2nd item) in statspack report.
--------------------------------------------------------------------------------
| Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT                |----- 2466553280 ----|       |      |      5 |
|COUNT STOPKEY                   |                     |       |      |        |
| TABLE ACCESS BY INDEX ROWID    |CSS_TP_SHMT_QUEUE    |     1 |   45 |      5 |
|  INDEX RANGE SCAN              |CSS_TP_SHMT_QUEUE_ID |    68K|      |      2 |
--------------------------------------------------------------------------------
4th, Review its execution plan in production again, found the index it hit is not the best one. I suspect the stat data of index is not correct.

5th, Check the index stat data in prod, found no stat data (No CLUSTERING_FACTOR data of that index be found in the view DBA_INDEXES) in that index "CSS_TP_SHMT_QUEUE_IDX8", which is the index hit in the bad execution plan;
SELECT * FROM DBA_INDEXES
WHERE TABLE_NAME = ‘CSS_TP_SHMT_QUEUE’;
6th, Retrieve the stat data. The explain plan revised. Problem solved.

begin
 dbms_stats.gather_index_stats(ownname => 'CSSOWNER', 
                                           indname => ' CSS_TP_SHMT_QUEUE_IDX8', 
                                           estimate_percent => 20); 
end; 
/
· Root cause:

It did not compute stat data of the index when create it. That means no stat data for that index. But in CBO, oracle optimizer need know stat data of each related object. If it found missed stat data, it will generate fake data for it each time when explain plan refreshed. In this SQL, the best index is NOT "CSS_TP_SHMT_QUEUE_IDX8". However, the fake data generated by optimizer is unexpected. If result of the calculation cost on the fake data shows CSS_TP_SHMT_QUEUE_IDX8 is the "best" one, the optimizer will choose it in the explain plan, which caused the performance issue.

· Conclusion

Under 9i (including), if you adopt CBO as optimizer, you need keep the statistics data be correct/updated.