Re: Rebuilding Indexes...

[Tim Gorman]

Arup,   Excellent practice!  I have written much the same scripts, except I use them as an extension to STATSPACK and named the resulting persistent table CSTATS$INDEX_STATS.  Nevertheless, I'm curious about the formula and will test it out.  I'm a little concerned about the HEIGHT > 3 in the WHERE clause, as even "small" indexes can go awry (i.e. HEIGHT <= 3 can still involve hundreds of thousands of rows and thousands of blocks)...   Another use for data from INDEX_STATS -- when the value in the BLKS_GETS_PER_ACCESS column exceeds several hundred or several thousand blocks, you have to question the effectiveness of the B*Tree index itself and whether or not it should be dropped.  Chances are good that the CBO is ignoring it anyway, so you are paying for the storage costs of the index and the processing costs of maintaining it, but not using it.  By no means is it open-and-shut that the index should be dropped -- there might be SQL statements effciently using the index to take advantage of skewed data distribution -- but it should be researched and considered for the old "drop kick" nonetheless.   Thanks again!   -Tim ----- Original Message ----- From: Arup Nanda To: Multiple recipients of list ORACLE-L Sent: Saturday, December 28, 2002 9:08 PM Subject: Re: Rebuilding Indexes... 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 ----- From: "Jared Still" <[EMAIL PROTECTED]> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, December 26, 2002 10:13 PM Subject: Re: Rebuilding Indexes... > > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). >