Oracle Scratchpad

August 9, 2011


Filed under: CBO,Index Rebuilds,Indexing,Oracle,Statistics — Jonathan Lewis @ 8:34 pm BST Aug 9,2011

Here’s one of those quick answers I give sometimes on forums or newsgroups. I forget where I wrote this, and when, and what the specific question was – but it was something to do with rebuilding an index on a small table where data was constantly being deleted and inserted.

Another problem with high insert/delete rates appears with very small indexes.

If you have a table that is small but constantly recycles its space you may also find you have an index where the number of leaf blocks puts you close to the borderline between having blevel = 1 and blevel = 2. If the size crosses that border occasionally and the statistics are updated to reflect the change – which is quite likely for a table subject to lots of updates and deletes if you have automatic stats collection enabled – then execution plans could change, resulting in dramatic changes in performance.

The workaround is fairly obvious – don’t let Oracle collect stats automatically on that table, instead create a stats-collection strategy for eliminating the change in blevel. For example, keep the stats locked except when you run your own code to deal with the stats, making sure that you overwrite the index blevel with 1 even if it has just crossed the boundary to 2.

Footnote: the reason why a change from 1 to 2 is dramatic is because Oracle ignores the blevel in the optimizer arithmetic when it is set to 1; so the change from 1 to 2 actually has the impact of a change from zero to 2. Then the cost of a nested loop access is “cost of single access multiplied by number of times you do it” – so the sudden appearance of a 2 in the formula gives an increment in cost of  “2 * number of times you visit the table” if your small table is the second table in a nested loop join – and suddenly a nested loop becomes much more expensive without a real change in the data size.

Footnote 2: it should be obvious that you don’t need to rebuild the index once you know what the problem is; but since we’re talking about a small index with a blevel that is usually 1 it probably won’t take more than a fraction of a second to rebuild the index and there’s a fair chance you can find a safe moment to do it. In terms of complexity the solution is just as simple as the stats solution – so you might as well consider it. The only thing you need to be careful about is that you don’t happen to rebuild the index at a time when the blevel is likely to be 2.

Footnote 3: For an example of the type of code that will adjust the blevel of an index see this URL. (Note, the example talks about copying stats from one place to another – but the principle is the same.)


    • Joel,

      Weird, I thought it was OTN so I did a quick search on OTN before posting this, and failed to find it.

      Thanks for the link to the context.

      Slightly worrying that it was only a couple of weeks ago and I had forgotten it.

      Comment by Jonathan Lewis — August 10, 2011 @ 6:17 am BST Aug 10,2011 | Reply

      • OTN search is borked, if that was what you are using, there are recent comments about it in the community forum. I simply googled part of the last italicized sentence, within quotes, ignoring the aggregator hits.

        Being aware of memory lapses means you don’t have to worry about it. Memory lapses aren’t really even a problem. When people call you at home asking why you aren’t doing the keynote, and it’s the first you recall hearing about it, that’s a problem. :-D

        Comment by jgarry — August 10, 2011 @ 10:39 pm BST Aug 10,2011 | Reply

  1. So, if we force the blevel to 1, we can “force” Oracle to use an index?


    Comment by Lascolto del venerdì — August 12, 2011 @ 9:09 am BST Aug 12,2011 | Reply

  2. [...] Lewis recently wrote a really nice blog piece blevel=1 on the dangers of an index toggling between BLEVEL 1 and BLEVEL 2. I thought it would be useful [...]

    Pingback by BLEVEL 1 => BLEVEL 2 (Teenage Wildlife) « Richard Foote’s Oracle Blog — August 23, 2011 @ 11:17 am BST Aug 23,2011 | Reply

  3. Hi
    Can you help explain this “making sure that you overwrite the index blevel with 1 even if it has just crossed the boundary to 2.”

    I understood the part where you mention about the stats collection portion (if you collect the stats after blevel=2, then you might have issues with some queries. But if you rebuild the index and then collect the stats you would be ok)

    - Kumar

    Comment by Kumar Madduri — August 24, 2011 @ 4:55 am BST Aug 24,2011 | Reply

    • Kumar,

      In the quoted text I was talking about dealing with the problem by controlling the statistics and wasn’t making any comment about rebuilding the index (I’ve done that in Footnote 2).

      I’ve added another footnote to the article pointing to an example of the type of code that could be used.

      Comment by Jonathan Lewis — August 24, 2011 @ 9:20 am BST Aug 24,2011 | Reply

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 Rubric Theme Blog at


Get every new post delivered to your Inbox.

Join 3,453 other followers