Index Freelists

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.