Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id gBTMWxY18611 for ; Sun, 29 Dec 2002 16:32:59 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id gBTMWxc18606 for ; Sun, 29 Dec 2002 16:32:59 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id LAA32922; Sun, 29 Dec 2002 11:14:57 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00523DFE; Sun, 29 Dec 2002 10:33:38 -0800 Message-ID: Date: Sun, 29 Dec 2002 10:33:38 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Tim Gorman" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Tim Gorman" Subject: Re: Rebuilding Indexes... Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_027B_01C2AF2D.C828EE70" ------=_NextPart_000_027B_01C2AF2D.C828EE70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 <=3D = 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 -----=20 From: Arup Nanda=20 To: Multiple recipients of list ORACLE-L=20 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,=20 DECODE(HEIGHT, 1, 100,=20 FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) /=20 (LF_BLK_LEN * LF_BLKS))) Comp_Factor,=20 DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1,=20 DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS),=20 LF_BLK_LEN /=20 ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) /=20 (LF_ROWS - DEL_LF_ROWS))))) - HEIGHT, -1, 1, 0))) + DECODE(LF_ROWS_LEN, 0, 0,=20 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 -----=20 From: "Jared Still" To: "Multiple recipients of list ORACLE-L" Sent: Thursday, December 26, 2002 10:13 PM Subject: Re: Rebuilding Indexes... >=20 > 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. >=20 > Why are you rebuilding indexes? About the only reason for ever > doing so is that the BLEVEL >=3D 5. >=20 > goto asktom.oracle.com, and do a search on 'index rebuild'. >=20 > Currently, the third article may be of interest. >=20 > Jared >=20 > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > Anyone have any useful scripts for doing this? > > > > TIA, > > Rich >=20 > ---------------------------------------- > Content-Type: text/html; charset=3D"iso-8859-1"; name=3D"Attachment: = 1" > Content-Transfer-Encoding: 7bit > Content-Description:=20 > ---------------------------------------- > --=20 > Please see the official ORACLE-L FAQ: http://www.orafaq.net > --=20 > Author: Jared Still > INET: jkstill@cybcon.com >=20 > 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@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). >=20 ------=_NextPart_000_027B_01C2AF2D.C828EE70 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Arup,
 
Excellent practice!  I have = written much=20 the same scripts, except I use them as an extension to STATSPACK and = named the=20 resulting persistent table CSTATS$INDEX_STATS.  Nevertheless, = I'm=20 curious about the formula and will test it out.  I'm a little = concerned=20 about the HEIGHT > 3 in the WHERE clause, as even "small" indexes can = go=20 awry (i.e. HEIGHT <=3D 3 can still involve hundreds of thousands = of rows=20 and thousands of blocks)...
 
Another use for data from INDEX_STATS = -- when the=20 value in the BLKS_GETS_PER_ACCESS column exceeds several hundred or = several thousand blocks, you have to question the effectiveness = of the=20 B*Tree index itself and whether or not it should be dropped.  = Chances=20 are good that the CBO is ignoring it anyway, so you are paying for the = storage=20 costs of the index and the processing costs of maintaining it, but not = using=20 it.  By no means is it open-and-shut that the index should be = dropped --=20 there might be SQL statements effciently using the index to take = advantage=20 of skewed data distribution -- but it should be researched and = considered for=20 the old "drop kick" nonetheless.
 
Thanks again!
 
-Tim
----- Original Message -----
From:=20 Arup=20 Nanda
Sent: Saturday, December 28, = 2002 9:08=20 PM
Subject: Re: Rebuilding = Indexes...

Jared,
 
Did you attach the = scripts?
 
I use the index = rebuilding regularly for=20 certain applications where buffer busy waits are prevalent. No, = let's not=20 go there why the buffer busy waits occur and whether reverse key = indexes would=20 help. All these are paths well trodden. I use a home grown setup = where I=20 ANALYZE VALIDATE STRUCTURE each index and immediately store the = INDEX_STATS rw=20 in a table called INDCHK_INDEX_STATS. Then I use the following script = to=20 identify the potential indexes candidate for rebuilding. The Height,=20 "Compression Factor", Delete% and "Hole Factor" as calculated = below=20 provide an indication whether the index can be considered to be = rebuilt. There=20 is no hard threshold value for each, based on all three, I decide = whether the=20 index needs to be rebuilt.
 
Finally, how did I come up with the = seemingly=20 labyrinthine formulae below? Parts of them are "stolen" from = the OEM=20 tool's index check program. I snooped around when the tool was = analyzing the=20 indexes and captured the code, modified to some extent and placed in a = nice=20 script. It works for me. The indexes are placed in LMT with = non-uniform=20 extents and the database is 8.1.7.4.
 
Yes, I know this will probably spark = all sorts of=20 reaction; but I would appreciate any feedback on the = process.
 
Arup Nanda
 
col name format a30 head "Index = Name"
col=20 comp_factor head "Compactness"
col hole_factor format 9999 head=20 "Hole"
col del_pct format 9999 head "Del%"
col height format = 99999 head=20 "Height"
SELECT NAME, HEIGHT,
 DECODE(HEIGHT, 1, 100,=20
  FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) /=20
  (LF_BLK_LEN * LF_BLKS))) Comp_Factor, =
 DECODE(HEIGHT,=20 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1,=20
  DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / = BR_ROWS),=20
  LF_BLK_LEN /
  ((LF_ROWS_LEN - = DEL_LF_ROWS_LEN)=20 /
  (LF_ROWS - DEL_LF_ROWS))))) - HEIGHT, -1, 1, 0)))=20 +
  DECODE(LF_ROWS_LEN, 0, 0,=20
  FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100))=20 = Hole_Factor,
  round(decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_R= OWS),0)=20 del_pct
FROM INDCHK_INDEX_STATS
where height > 3
or = DECODE(HEIGHT,=20 1, 100,
        = FLOOR(((LF_ROWS_LEN -=20 DEL_LF_ROWS_LEN) * 100) = /
       =20 (LF_BLK_LEN * LF_BLKS)))  < 80
or DECODE(HEIGHT, 1, 0,=20 DECODE(LF_ROWS - DEL_LF_ROWS, 0,=20 1,
       =20 DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN /=20 BR_ROWS),
        LF_BLK_LEN=20 /
        ((LF_ROWS_LEN -=20 DEL_LF_ROWS_LEN) /
        = (LF_ROWS -=20 DEL_LF_ROWS))))) - HEIGHT, -1, 1, 0)))=20 +
        DECODE(LF_ROWS_LEN, 0, = 0,
       =20 FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) >=20 10
or decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS) > = 9
order by 3=20 desc, 2, 1
/
 
 
 
 
----- Original Message -----
From: "Jared Still" <jkstill@cybcon.com>
To: "Multiple recipients of list = ORACLE-L"=20 <ORACLE-L@fatcity.com>
Sent: Thursday, December 26, 2002 = 10:13=20 PM
Subject: Re: Rebuilding=20 Indexes...

>
> Though I have published a script for = determining indexes=20 that
> need to be rebuilt, and then rebuilding them,  I = have to say=20 that
> this is almost never necessary.
>
> Why are = you=20 rebuilding indexes?  About the only reason for ever
> doing = so is=20 that the BLEVEL >=3D 5.
>
> goto asktom.oracle.com, = and do a=20 search on 'index rebuild'.
>
> Currently, the third = article may=20 be of interest.
>
> Jared
>
> On Thursday 26 = December 2002 12:24, Richard Huntley wrote:
> > Anyone have = any=20 useful scripts for doing this?
> >
> > TIA,
> = >=20 Rich
>
> ----------------------------------------
> = Content-Type: text/html; charset=3D"iso-8859-1"; name=3D"Attachment: = 1"
>=20 Content-Transfer-Encoding: 7bit
> Content-Description:
>=20 ----------------------------------------
> --
> Please = see the=20 official ORACLE-L FAQ: http://www.orafaq.net
> --=20
> Author: Jared Still
>   INET: jkstill@cybcon.com
>
>=20 Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
> San=20 Diego, California        -- Mailing = list=20 and web hosting services
>=20 = ---------------------------------------------------------------------
= >=20 To REMOVE yourself from this mailing list, send an E-Mail = message
> to:=20 ListGuru@fatcity.com = (note EXACT=20 spelling of 'ListGuru') and in
> the message BODY, include a = line=20 containing: UNSUB ORACLE-L
> (or the name of mailing list you = want to be=20 removed from).  You may
> also send the HELP command for = other=20 information (like subscribing).
> = ------=_NextPart_000_027B_01C2AF2D.C828EE70-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: Tim@SageLogix.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@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).