Oracle Scratchpad

November 10, 2009

Index Freelists

Filed under: Indexing,Infrastructure,Performance,Troubleshooting — Jonathan Lewis @ 7:09 pm GMT Nov 10,2009

A few months ago Saibabu Devabhaktuni let me know of an interesting issue he had had with indexes misbehaving, and pointed me to a blog note he had written to describe it. In the note he supplies a test case where Oracle walks through hundreds of blocks on an index freelist to find a single block that could be used as the target for a leaf node split.

The most interesting thing to note is that Sai has demonstrated a case where issuing a commit between the bulk delete and the subsequent insert results in a performance problem. On the other hand if you read my description in this blog posting, you’ll see that I have described a scenario where the commit between the delete and the insert is a very good idea – especially if you can fit a coalesce in between to clear up the mess.

If you’re going to repeat the test shown in Sai’s blog, it’s best if you create a new tablespace with a single datafile. When I first ran it, I decided that one of my existing tablespaces was a “close enough” match to his example – and found that I couldn’t reproduce his results. His demonstration, being based on a non-unique index,  is very sensitive to the order in which extents are allocated to the table – so you need a clean tablespace with just one datafile, or a tablespace which guarantees the table will fit into a single extent.

The bug number that Sai raised (8446989) is still waiting for a base bug fix; and the base bug it refers to (8570223) is hidden. There is some further information available, though, because his bug number has become the base bug for bug 7712825 which gives you a few clues about what is going on.

In fact, when I ran Sai’s test case with some diagnostic code interspersed, I noticed that the insert he did to “refill” the empty blocks didn’t take them off the freelist when (to my mind) they should have come off the freelist – which is why the single row insert that caused a block split had to walk through and unlink a lot of blocks in its search for a free block. For various reasons, and I think one of them may be a fairly expensive structural issue, I don’t think this is going to be easy to fix.

Free space management is not a simple part of the database – especially given Oracle’s dedication to making the commit as fast as possible by minimising the “excess” work that has to take place at the same time; so we may continue to see strange  behaviour appearing in unusual conditions for both freelist management and bitmap management of free space for quite some time to come.


  1. This remember me the problems with memory and paging (i.e. finding the right page to store your program).

    And thanks for replaying to my mail.


    Comment by lascoltodelvenerdi — November 11, 2009 @ 8:05 am GMT Nov 11,2009 | Reply

  2. […] there are cases where unexpected, and expensive, side effects appear in the Oracle code path. See this posting for one such case (or two if you follow the link in the […]

    Pingback by Fragmentation 4 « Oracle Scratchpad — July 22, 2010 @ 7:02 pm BST Jul 22,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: