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.
Update (Sep 2013)
See comment 4 below: the fix doesn’t solve the problem completely, it’s a damage limitation step that seems to limit the number of ITL entries to something like 40 rather then letting you hit the maximum of 169. This still leaves you an opportunity to lose something like 800 bytes per leaf block if you’re running at a level of concurrency that means you could have 4 processes trying to update an individual block.
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 |
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 |
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 |
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 |
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 |
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:
Comment by Mikhail Velikikh — October 27, 2014 @ 4:04 am GMT Oct 27,2014 |
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 |
[…] undesirable behaviour with indexes. I’ve just rediscovered it after seeing it mentioned in a comment to an old article that I had been prompted to […]
Pingback by Index ITL Limit | Oracle Scratchpad — February 18, 2022 @ 5:02 pm GMT Feb 18,2022 |
[…] Index ITL fix: Announcing the patch (which is a bit of a compromise) […]
Pingback by Index ITLs | Oracle Scratchpad — August 16, 2022 @ 1:33 pm BST Aug 16,2022 |