Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Rebuilding Indexes...

Re: Rebuilding Indexes...

From: Jared Still <jkstill_at_cybcon.com>
Date: Sun, 29 Dec 2002 22:33:41 -0800
Message-ID: <F001.00523FD1.20021229223341@fatcity.com>


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" <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).


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: 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).
Received on Mon Dec 30 2002 - 00:33:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US