Oracle-L: Re: Rebuilding Indexes...

From: Arup Nanda <arupnanda_at_hotmail.com>
Date: Sat, 28 Dec 2002 20:08:37 -0800
Message-ID: <F001.00523C39.20021228200837@fatcity.com>

Jared,

Did you attach the scripts?

I use the index rebuilding regularly for certain applications where buffer busy waits are prevalent. No, let's not go there why the buffer busy waits occur and whether reverse key indexes would help. All these are paths well trodden. I use a home grown setup where I ANALYZE VALIDATE STRUCTURE each index and immediately store the INDEX_STATS rw in a table called INDCHK_INDEX_STATS. Then I use the following script to identify the potential indexes candidate for rebuilding. The Height, "Compression Factor", Delete% and "Hole Factor" as calculated below provide an indication whether the index can be considered to be rebuilt. There is no hard threshold value for each, based on all three, I decide whether the index needs to be rebuilt.

Finally, how did I come up with the seemingly labyrinthine formulae below? Parts of them are "stolen" from the OEM tool's index check program. I snooped around when the tool was analyzing the indexes and captured the code, modified to some extent and placed in a nice script. It works for me. The indexes are placed in LMT with non-uniform extents and the database is 8.1.7.4.

Yes, I know this will probably spark all sorts of reaction; but I would appreciate any feedback on the process.

Arup Nanda

col name format a30 head "Index Name"
col comp_factor head "Compactness"
col hole_factor format 9999 head "Hole"
col del_pct format 9999 head "Del%"
col height format 99999 head "Height"
SELECT NAME, HEIGHT,
DECODE(HEIGHT, 1, 100,
  FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) /   (LF_BLK_LEN * LF_BLKS))) Comp_Factor, DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1,   DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS),   LF_BLK_LEN /
  ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) /
  (LF_ROWS - DEL_LF_ROWS))))) - HEIGHT, -1, 1, 0))) +   DECODE(LF_ROWS_LEN, 0, 0,
  FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) Hole_Factor,   round(decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS),0) del_pct FROM INDCHK_INDEX_STATS
where height > 3
or DECODE(HEIGHT, 1, 100,

        FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) /
        (LF_BLK_LEN * LF_BLKS)))  < 80
or DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1,
        DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS),
        LF_BLK_LEN /
        ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) /
        (LF_ROWS - DEL_LF_ROWS))))) - HEIGHT, -1, 1, 0))) +
        DECODE(LF_ROWS_LEN, 0, 0,
        FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) > 10

or decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS) > 9 order by 3 desc, 2, 1
/

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, December 26, 2002 10:13 PM

>
> Though I have published a script for determining indexes that
> need to be rebuilt, and then rebuilding them, I have to say that
> this is almost never necessary.
>
> Why are you rebuilding indexes? About the only reason for ever
> doing so is that the BLEVEL >= 5.
>
> goto asktom.oracle.com, and do a search on 'index rebuild'.
>
> Currently, the third article may be of interest.
>
> Jared
>
> On Thursday 26 December 2002 12:24, Richard Huntley wrote:
> > Anyone have any useful scripts for doing this?
> >
> > TIA,
> > Rich
>
> ----------------------------------------
> Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
> Content-Transfer-Encoding: 7bit
> Content-Description:
> ----------------------------------------
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
-- 
Author: Arup Nanda
  INET: arupnanda_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Sat Dec 28 2002 - 22:08:37 CST