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.
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 |
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 |