|
Tim,
Thanks for the compliments. You are right, HEIGHT
> 3 is not correct. I lifted it off another place where I temporarily used
the HEIGHT > 3 predicate; in the version I always use, height is not in the
filter. Again, just because height > , it would not indicate a case
for rebuilding; rather a subjective evaluation of all four factors presented
would be inputs for decision making.
The BLKS_GETS_PER_ACCESS part is interesting; I
will research that. Thanks for sharing that information.
Arup
----- Original Message -----
Sent: Sunday, December 29, 2002 1:33
PM
Subject: Re: Rebuilding Indexes...
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 -----
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 -----
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).
>
|
