Oracle Scratchpad

December 13, 2010

Index ITL fix

Filed under: Index Explosion,Indexing,Oracle,Troubleshooting — Jonathan Lewis @ 6:29 pm BST Dec 13,2010

Over the last year I’ve written a short collection of articles describing how a defect in the code for handling index leaf (block) node splits can cause a significant waste of space in indexes that are subject to a high degree of concurrent inserts. Finally I can announce some good news – Oracle has a patch for the code.

The problem is described in MOS (the database formerly known as Metalink) under bug 8767925 which is reported as “fixed in version 12.1″.

Backports may become available – I’ve already asked for one for 11.1.0.7 on AIX for one of my clients (but it has been a little slow in arriving) and another client should have put in a bid for a backport to 10.2.0.4 (also on AIX) in the last couple of days.

5 Comments »

  1. Jonathan, it’s a really good news.

    This would be an extremely good example of how an expert outside Oracle corp. can give a good influence on Oracle database.

    Comment by Dion Cho — December 14, 2010 @ 1:55 am BST Dec 14,2010 | Reply

  2. I’ve just discovered that various patches are now available for this bug:
    AIX5l – 64-bit: 11.1.0.7.0
    AIX5L – 64-bit: 11.1.0.7.6
    Solaris 64-bit: 11.1.0.7.5

    Make sure you read the readme before installing, as you need to set an event to enable the patch. It sounds as if Oracle Corp. has implemented some code to reduce the size of the ITL in leaf blocks.

    Comment by Jonathan Lewis — September 5, 2011 @ 8:00 pm BST Sep 5,2011 | Reply

  3. Sadly, nothing would come out for 10g (10.2.0.4/10.2.0.5). Opened a SR requesting backport but because 10g is out of support, couldn’t get one.

    Comment by PC — August 14, 2012 @ 10:24 am BST Aug 14,2012 | Reply

    • PC
      Sorry to hear that, but it’s not too surprising.
      I guess the second client I mentioned must have upgraded to 11g – or decided to rebuild critical indexes regularly to work around the problem.

      Comment by Jonathan Lewis — August 19, 2012 @ 4:22 pm BST Aug 19,2012 | Reply

  4. Bad news – I decided to run my test case against 12.1.0.1 on Linux 64-bit: the problem still exists.

    10 seconds into a test running 6 concurrent insert loops on a machine with 2 CPUs I had some leaf blocks with 41 ITL entries.

    Comment by Jonathan Lewis — September 27, 2013 @ 5:00 pm BST Sep 27,2013 | 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:

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

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,528 other followers