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.
Oracle Scratchpad 
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:
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.00000000Comment 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 |