Index ITL fix

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.

Index ITLs

After wrting a short series of notes on a problem with indexes wasting a lot of space and growing to three (or even four) times the size you might expect due to a problem with concurrency and ITL (interested transaction list) entries accumulating, I thought it would be a good idea to create a little index for the series to make it easier to read them in order:

• Index Quiz 1 : An introductory question
• Index Quiz 2 :  Answering the question with block dumps
• Index Explosion : Demonstrating the issue
• Index Explosion – 2 : Explaining the problem
• Index Explosion – 3 : Solving the problem
• Index Explosion – 4 : Working around the problem
• treedump : A useful (though sometimes expensive) option for looking into index structures
• treedump 2: Another look at tree dumps.

Index Explosion – 4

After describing how to deal most effectively – but only after approval from Oracle Support – with the problem of indexes wasting space on unnecessary ITL entries, I left you with a short list of “supportable” options for addressing the problem. In this note I’m going to outline a few pros and cons of each of those options. The list was as follows:

Index Explosion 3

At the end of part 2 of Index Explosion I left you with three questions: 

• how can you set maxtrans on an index in 10g (and above)
• what strategies exist for minimising the potential for damage if you actually have to face this problem
• why haven’t I noticed the space-wasting phenomenon more often despite seeing indexes where it could have been happening

In this post I’ll address the maxtrans issue. If you try to set maxtrans on an index in 10g it simply doesn’t work, and here’s a quick cut-n-paste from SQL*Plus running 10.2.0.3 to demonstrate the point:

Index Explosion 2

As we saw in part 1 of Index Explosion, we can lose a lot of space in index leaf blocks because

1. at even fairly low levels of concurrency Oracle can manage to introduce a large number of (redundant ?) ITL entries into a leaf block and …
2. once a single leaf block has a large number of ITL entries the entire ITL is copied into both target blocks every time the leaf block splits – so one brief accident can have a significant long term effect.

But what’s happening, and what should we do about it ?

Index Explosion

In Index Quiz 1 and Index Quiz 2 I demonstrated a couple of details of how the ITL (interested transaction list) behaves in indexes. In this note I’m going to explain how these details can result in a nasty waste of space in indexes.

The two points I made in the previous posts were:

1. at high levels of concurrency you can “lose” a lot of space in an index leaf block to the ITL
2. when a leaf block splits the ITL of the newer block is a copy of the ITL from the older block

IQ2 – Answers

I was planning to supply the answers to Index Quiz 2 as a comment – but there’s a lot of block dumps involved, and it’s easier to do that in postings.

Question 1: I’ve created a table and index with initrans 4, then inserted one row into the table. How many ITL (interested transaction list – see glossary) entries will there be in the first block of the index when you dump it.

Answer 1: The table block will show 4 entries in the ITL, obeying your setting for initrans, but the index block will show only two entries – unless you’re using Oracle 8i or earlier (Basically indexes tend to ignore the setting for initrans  except when you rebuild an index, or create it on existing data.):

Index Quiz 2

This quiz is easier than Index Quiz 1 because you can just run the code, dump blocks, and find the answer. But see if you can work out what’s going to happen before you do the test. Note that I’ve set initrans to 4 on both the table and index.

Index Quiz 1

I’m encroaching on Richard Foote’s territory here – with plans to write a few details about some of the implementation details of Oracle’s B-tree indexes. My strategy, though, is to entertain by asking a few questions that might prompt a little speculation before giving some answers. So …

After running validate index against a particular index on my system, I select the following columns from view index_stats: (using Tom Kyte’s invaluable “one column per line” routine).