Re: How to know when an index needs rebuild ?

From: Anurag Varma <avarmadba.skipthis_at_yahoo.com>
Date: Sat, 27 Sep 2003 18:40:49 GMT
Message-ID: <RQkdb.20152$ZR1.334@nwrddc01.gnilink.net>

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3f72d590$0$14559$afc38c87_at_news.optusnet.com.au...
> Mark wrote:
>
> > Hi all dba users,
> > I'd like to know if there's some good sql script / indicator to detect
if
> > an index need to be rebuilt.
> > Thanks a lot
> > Mark
>
> Indexes seldom need to be rebuilt. Simple as that. And you won't go far
> wrong if you just forget about rebuilding indexes altogether. Oracle's
> indexing mechanism is pretty damn efficient without periodic rebuilds. And
> rebuilds are expensive options, causing a lot of I/O, and a lot of
> exclusive table locking. The balance of costs and benefits is definitely
on
> the 'leave them alone' side.
>
> However, it can get a bit subtler than that.
>
> If you have an index which is built upon a sequence number, and you do
> occasional deletes from the table, then that probably needs periodic
> rebuilding, because you'll likely never re-use the index space freed by
the
> deletes. If you do bulk deletes on that same table, however, then entire
> index blocks will be cleared by the deletes, and that space can be
re-used,
> so even there you won't need to rebuild the index.
>
> But if you want the 'official' line, which is not particularly a wise
move,
> then analyze index blah validate structure, and then query the index_stats
> view. If DEL_LF_ROWS is more than 15% (9i performance tuning course notes)
> or 25% (elsewhere in the same performance tuning course notes!) then a
> rebuild *might* be in order.
>
> But even Oracle can't stick to one percentage figure, and that's because
> there *is* no magic answer.
>
> Regards
> HJR
>

Adding to the precious information Howard gave. Let me show you an example of a problem index
in our database.

The current size of this index is 400M (note: Table size itself is 90M). The correct size after rebuild should be around 10M (i.e. it is 40 times bigger).
Now this index does not really follow the sequence problem that Howard describes. But the way this table is populated is probably where the problem arises (I won't go into those details presently).

Anyway:
SQL> select bytes from user_segments where segment_name = 'PRICING_KEY_IDX';

BYTES

436207616

Which gives me the initial warning to further check what the problem might be.
So I run:
SQL> analyze index PRICING_KEY_IDX validate structure; and here is the info I get:
SQL> select height, blocks, lf_rows, lf_rows_len, del_lf_rows, del_lf_rows_len, pct_used from index_stats;

HEIGHT BLOCKS LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN PCT_USED
---------- ---------- ---------- ----------- ----------- --------------- ---

3 53248 5535474 88540541 3770615 60311986 22

... shows 22% as the pct_used figure, and an abnormal amount of deleted leaf rows.

Lets now look at what the index structure looks like by taking a treedump: To do that: First find the object_id as follows: select object_id from user_objects where object_name = 'PRICING_KEY_IDX'; OBJECT_ID

51267

followed by:
alter session set events 'immediate trace name treedump level 51267';

where level NNNN denotes the object_id.

After taking the tree dump, here is what it looks like: ----- begin tree dump
branch: 0xc3830a 12813066 (0: nrow: 164, level: 2)

   branch: 0x103134c 16978764 (-1: nrow: 280, level: 1)
      leaf: 0xc3830b 12813067 (-1: nrow: 0 rrow: 0)
      leaf: 0x101952c 16880940 (0: nrow: 277 rrow: 119)
      leaf: 0x101952d 16880941 (1: nrow: 0 rrow: 0)
      leaf: 0x1019530 16880944 (2: nrow: 213 rrow: 213)
      leaf: 0x10188e7 16877799 (3: nrow: 409 rrow: 204)
      leaf: 0x242df31 37936945 (4: nrow: 204 rrow: 0)
      leaf: 0x10212a1 16913057 (5: nrow: 84 rrow: 0)

........all other brances/leafs look pretty much like this only

In the above tree dump: nrow denotes the number of rows in the leaf block and rrow denotes the number of rows which are valid (i.e. not deleted).

You can see above that some leaf blocks are completely empty and some blocks are partially empty. However, we see a lot of leaf blocks which are completely empty!

So lets say now I remember Connors advice of using coalesce.... SQL> alter index PRICING_KEY_IDX coalesce;

Looking at the size of the index now (after re-analyzing it): SQL> @tmp

BYTES

436207616

.. didn't change (as expected, since we did a coalesce)

and lets see what the index stats look like now:

HEIGHT BLOCKS LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN PCT_USED
---------- ---------- ---------- ----------- ----------- --------------- ---

3 53248 1770387 28317003 5528 88448 73

... Hmm... that seems to have worked. Our stats look much better now.

Lets look at the treedump now:
----- begin tree dump
branch: 0xc3830a 12813066 (0: nrow: 164, level: 2)

   branch: 0x103134c 16978764 (-1: nrow: 29, level: 1)
      leaf: 0xc3830b 12813067 (-1: nrow: 332 rrow: 332)
      leaf: 0x10188e7 16877799 (0: nrow: 405 rrow: 405)
      leaf: 0x142d2a8 21156520 (1: nrow: 407 rrow: 407)
      leaf: 0x10188e5 16877797 (2: nrow: 399 rrow: 399)
      leaf: 0xc29dcb 12754379 (3: nrow: 415 rrow: 415)
      leaf: 0x142c831 21153841 (4: nrow: 415 rrow: 415)
      leaf: 0x14327ac 21178284 (5: nrow: 421 rrow: 421)
      leaf: 0x10218b9 16914617 (6: nrow: 205 rrow: 205)

... sweet :)

..... Thus, the above index benefitted by just doing a coalesce. However, lets just try a rebuild also:
alter index pricing_key_idx rebuild;

Consider the size of the index now:

BYTES

32505856

and its stats:

HEIGHT BLOCKS LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN PCT_USED
---------- ---------- ---------- ----------- ----------- --------------- ---

3 3968 1764859 28228555 0 0 90

... hmm even better.

.. how about the treedump:

begin tree dump branch: 0x243788a 37976202 (0: nrow: 9, level: 2) branch: 0xc2844b 12747851 (-1: nrow: 447, level: 1) leaf: 0x243788b 37976203 (-1: nrow: 448 rrow: 448) leaf: 0x243788c 37976204 (0: nrow: 448 rrow: 448) leaf: 0x243788d 37976205 (1: nrow: 448 rrow: 448) leaf: 0x243788e 37976206 (2: nrow: 448 rrow: 448) leaf: 0x243788f 37976207 (3: nrow: 448 rrow: 448) leaf: 0x2437890 37976208 (4: nrow: 448 rrow: 448) leaf: 0x2437891 37976209 (5: nrow: 448 rrow: 448) leaf: 0x2437892 37976210 (6: nrow: 448 rrow: 448)

So what should I have done above? coalesce or rebuild? .. Here are my thoughts:

coalesce took more than 40 minutes on that slow machine. Considering the amount it had to coalesce, the operation takes time.
rebuild took approx less than a minute (though I tried it after the coalesce, however I've timed it without that also and it took around a minute even then).
coalesce helped quite a lot. However, it does not release the amount of space that index had taken. Rebuild released the space also.
If I were to do regular coalesces, then each coalesce would not take much time and it would keep the index size and its packing to an acceptable level.
However, I did not find out the real cause of this index bloating ... which is really what needs to be looked after :) .. I'm working on that. The reason of the index bloat is related to how the tables is getting loaded, fixing that would be the real fix.
Rebuild or coalesce decision should be taken after taking the treedump of the index. If you see a lot of empty leaf blocks, then coalesce might be a good choice. However, if each leaf block is only 20% full then you are looking for a rebuild.
Before you do any of rebuild or coalesce: See if your index leaf blocks are getting re-filled. In the above case, even after couple of months, the entries were just not getting re-filled.

.. so in the end I would like to say: indexes might need rebuild and sometimes rebuild is what might need
to be done (not coalesce). However, you need to understand better what is happening behind
the scenes, in order to come to a conclusion. also: I fully agree with Howards point that indexes rarely if ever need rebuilding. The above case
is of one index needing rebuild/coalescing with 1000's of other indexes not needing it at all!.
.. and remember, you need to come to a point where none of your indexes ever need rebuilding!

Hth
:)

Anurag Received on Sat Sep 27 2003 - 13:40:49 CDT