Coskan’s Approach to Oracle

February 6, 2007

null value impact on release 10.1.0.*

Filed under: CBO — coskan @ 9:58 am

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).

Code listing 1 : Using Nulls On 10.1.0.*

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)

pg 80-81

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at


Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: