Oracle-L: Re: Checking the rebuildability of an index

From: <Jared.Still_at_radisys.com>
Date: Mon, 05 Aug 2002 16:28:20 -0800
Message-ID: <F001.004ABC79.20020805162820@fatcity.com>

Jesse,

The now quite famous paper, "How to Stop Defragmenting and Start Living", paper 711.pdf
from Open World a couple of years ago, has some good info on this.

In general, check the BLEVEL in the index stats, if greater than 5, rebuild. The paper goes
into some detail, as well as does "Perl for Oracle DBA's" where a script is implemented that
performs that check. (Ok, my details came from the paper, but I gave the authors credit, and
include the URL :) A pretty basic script, but, it *is* a toolkit.

A really robust index rebuilding system will track the last time an index was rebuilt, the
BLEVEL from before and after the rebuild, and a host of other things I haven't thought of
yet.

The logic behind tracking BLEVEL is that it's entirely possible to have an index with a
BLEVEL of 5. I'm not saying it's a good index, but it's possible. The point being that
you want to develop some metrics, such as parallel degree, rebuild time required, logging
or no logging, online or not online, etc, to give you some data for creating what should
be a rather interesting set of reports.

In the case of the BLEVEL, there's little point in rebuilding an index because it has a
BLEVEL of 5, if it will always have a BLEVEL of 5.

I've been thinking about this lately, as it's on my todo list, and I'd really like to do it. I'm not
currently rebuilding many indexes, cuz the DBA shop here is a one man show, and index
rebuilds are just now filtering to the top.

Maybe we can share. :)

Jared

"Jesse, Rich" <Rich.Jesse_at_qtiworld.com>
Sent by: root_at_fatcity.com
08/05/2002 03:38 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Checking the rebuildability of an index

Hi all,

On our ERP DB, 8.1.7.2.0 on HP/UX, I'm finally able to implement an automated index rebuild job. I've got two questions:

First, I've planned to use the gauge of b*-tree HEIGHT and "DEL_LF_ROWS / LF_ROWS) > 20%" from the SYS.INDEX_STATS view to determine what indexes to rebuild. I've read in "Gaja 101" that the leaf block density should also be
considered. Is this LF_ROWS / LF_BLKS (from INDEX_STATS)? If so, are there
any guidelines as to what the density should be? Or is it only the percentage decrease over time that matters? And if it is, what percentage decrease over what period of time should be considered? This would seem to
be very installation-dependant, but I'm just hoping for general guidelines for a starting point so I'm not rebuilding our largest indexes weekly if it's not needed.

Second, how does one determine if there's enough room to REBUILD ONLINE an index? Other than comparing MAX(BYTES) to the INITIAL clause of the index,
that is. I've looked in Metalink, "Gaja 101", and several websites, but nothing jumped out at me.

TIA!

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI

USA
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------