On Saturday 28 December 2002 20:08, Arup Nanda wrote: > Jared, > > Did you attach the scripts?
No, but they're free to download at http://www.oreilly.com/catalog/oracleperl/pdbatoolkit/ww.oreilly.com/catalog/oracleperl/. The script in question is idxr.pl. The algorithm was lifted ( with credit ) from the famous 'How to stop defragmenting...' paper. By the way, the scripts are all Perl. :) Jared > > 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" <[EMAIL PROTECTED]> > 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: [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). ---------------------------------------- Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: quoted-printable 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).
