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


September 19, 2009

Index ITLs

Filed under: Index Explosion,Indexing,Infrastructure,Performance,Troubleshooting — Jonathan Lewis @ 3:35 pm BST Sep 19,2009

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:

September 15, 2009

Index Explosion – 4

Filed under: Index Explosion,Indexing,Infrastructure,Performance,Troubleshooting — Jonathan Lewis @ 7:30 pm BST Sep 15,2009

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: (more…)

August 26, 2009

Index Explosion 3

Filed under: Index Explosion,Indexing,Infrastructure,Troubleshooting — Jonathan Lewis @ 6:53 pm BST Aug 26,2009

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 to demonstrate the point:


August 11, 2009

Index Explosion 2

Filed under: Index Explosion,Indexing,Infrastructure,Performance,Troubleshooting — Jonathan Lewis @ 7:27 pm BST Aug 11,2009

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 ?


July 28, 2009

Index Explosion

Filed under: Index Explosion,Indexing,Infrastructure,Oracle,Performance,Troubleshooting — Jonathan Lewis @ 7:28 pm BST Jul 28,2009

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


July 24, 2009

IQ2 – Answers

Filed under: Index Explosion,Indexing,Infrastructure,trace files,Troubleshooting — Jonathan Lewis @ 9:04 pm BST Jul 24,2009

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

July 23, 2009

Index Quiz 2

Filed under: Index Explosion,Indexing,Infrastructure — Jonathan Lewis @ 7:54 am BST Jul 23,2009

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.

July 20, 2009

Index Quiz 1

Filed under: Index Explosion,Indexing,Infrastructure — Jonathan Lewis @ 7:41 am BST Jul 20,2009

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


Website Powered by