Oracle Scratchpad

December 13, 2010

Index ITL fix

Filed under: Index Explosion,Indexing,Oracle,Troubleshooting — Jonathan Lewis @ 6:29 pm GMT 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.

7 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT Sep 27,2013 | Reply

    • Hello Jonathan,

      Bug 8767925 : “ADD MORE LOGIC FOR RESERVING ITL SPACE FOR INDEX LEAF BLOCKS” was updated recently and I received that bug in MOS HOT TOPICS email.
      I remember that you described a problem in series of posts on your blog.
      Your latest post was about 12.1.0.1 and I decided to recheck the issue against 12.1.0.2 (4 CPU on Solaris SPARC64).
      With 4 concurrent sessions I had some leaf blocks with 41 ITL entries:

       Object id on Block? Y
       seg/obj: 0x7b2c1  csc: 0x00.bee4cb  itc: 41  flg: E  typ: 2 - INDEX
           brn: 0  bdba: 0x2804f80 ver: 0x01 opc: 0
           inc: 0  exflg: 0
       
       Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
      0x01   0x0001.01b.00002665  0x0100313e.04ca.01  CB--    0  scn 0x0000.00bee4cb
      0x02   0x0007.018.0000265b  0x010027f2.044f.0a  --U-    1  fsc 0x0000.00bee4cc
      0x03   0x0008.009.0000260a  0x01002e4e.04b7.10  --U-    1  fsc 0x0000.00bee4ce
      0x04   0x0004.00c.00002612  0x010033af.0427.08  --U-    1  fsc 0x0000.00bee4cf
      0x05   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x06   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x07   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x08   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x09   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x0a   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x0b   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x0c   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x0d   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x0e   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x0f   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x10   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x11   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x12   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x13   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x14   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x15   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x16   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x17   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x18   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x19   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x1a   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x1b   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x1c   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x1d   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x1e   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x1f   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x20   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x21   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x22   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x23   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x24   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x25   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x26   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x27   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x28   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      0x29   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
      

      Comment by Mikhail Velikikh — October 27, 2014 @ 4:04 am GMT Oct 27,2014 | Reply

      • Mikhail,

        Thanks for testing. I wonder if 41 is a coincidence, or whether it’s an arbitrary hard limit, or derived from the average size of the index entry.

        Update: Just did a quick test using autonomous transactions and got 169 ITL entries into an index leaf block before hitting a single-session deadlock that took a few minutes to crash and dump.

        Comment by Jonathan Lewis — October 27, 2014 @ 6:33 am GMT Oct 27,2014 | 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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,422 other followers