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

2 Comments »

  1. Hola! I’ve been following your web site for a while now and
    finally got the courage to go ahead and give you a shout out from
    New Caney Texas! Just wanted to mention keep up the excellent work!

    Comment by printer repair — September 3, 2014 @ 1:14 pm

  2. I’ve been exploring for a little bit for any high quality articles or blog posts on this sort
    of space . Exploring in Yahoo I at last stumbled upon this
    site. Studying this information So i am happy to exhibit that I
    have a very good uncanny feeling I found out exactly what
    I needed. I so much unquestionably will make sure to do not disregard this web site and give it a glance on a relentless basis.

    Comment by Dj bill cody — September 7, 2014 @ 3:08 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: