Nowadays i am studying on the book of Mr Jonathan Lewis , Oracle CBO Fundamentals.
I saw a good example on a bug like situation On Ch4- S IMPLE B-TREE ACCESS
Mr Lewis has proven wrong behaviour of CBO when null values exist on indexes.
When you get statistics of an index with null values Oracle lowers the num_row column of index with number of nulls and the CBO start to use value index selectivity with filters for value of index_selectivity -which has the effect of losing the cost of the leaf_block accesses (can be seen by 10053 trace file).
below is the writers note about the occurance of this case is randomly, but i couldn’t create the case about the wrong behaviour of gather_table_stats package with cascade option.
“The underlying problem is that the call to dbms_stats.gather_table_stats(), with the cascade option set to true, sometimes fails to update the index statistics. Counterintuitively, when dbms_stats gets it wrong, the execution plan comes up with the right cost (because the value of user_indexes.num_rows stays the same as user_tables.num_rows), and when dbms_stats gets it right, the execution plan comes up with the wrong costs because user_indexes.num_rows is (correctly) recorded as being less than user_tables.num_rows. ”
Refences Used :Oracle Cost Based Optimizer Fundamentals(Jonathan Lewis)