Connor,
Thanks for the info, there's obviously a lot to consider when
when deciding if an index should be rebuilt.
One needs to know what, if any benefits will be obtained by
rebuilding, and possibly what the downside may be.
As far as I know, there aren't any tools available that will allow
the collection of these types of metrics. i.e. intelligent decisions
based on usage patterns.
I don't think Oracle even has any way to provide the raw data,
Are you aware of any tools that claim to do this?
The common method for this amounts to tribal knowledge. The
DBA has previously encountered performance problems with an
index, and knows that rebuilding it will improve response time, and
so it is rebuilt. Effective, but not measurable and the knowledge
can't be used to determine if there are other candidates.
I think I'll put the index rebuild project on hold, and move on to
the next item on the todo list.
Re my claim that Index blocks removed from the freelist are
not re-inserted onto the freelist until empty:
I haven't been able to prove that, and in fact have proved exactly
the opposite. I haven't gone as far as block dumps, just filling
index blocks, removing 20% of the rows from all blocks and
reinserting the same rows.
As soon as I find the Note or doc that stated that, I'm going to ask
for some evidence. :)
I even went back as far as 7.3.4 to run the tests.
Jared
Connor McDonald <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/09/2002 01:48 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: Checking the rebuildability of an index
> Q: What are the chances of knowing whether or not
> subsequent inserts will be able to reuse a slot?
Somewhere between 0 and 100% :-)
For example, take the commit's out of my demo's and
the space is not reused as well as it could be. There
are so many variations.
This is why I don't believe in rebuilding.
Occasionally if I see an index growing in size
continuously I'll pop an analyze in and see if its
full of holes, and maybe do a rebuild to see what
happens...but if it gets full of holes again, it gets
taken of the list of rebuild candidates.
Similarly, typical candidates for rebuild are indexes
that have a lot of dml activity. Your "average" index
normally ends up at around 75% full over time, and a
rebuild might take that up to 95% (assumning pctfree
5). If your app does a lot of range scanning,
performance may get a boost as a result, but that dml
could now suffer due to the need to split blocks. Can
that be predicted from stats alone? Unlikely.
If you *know* that query response time is the key for
your system, then I would say that a regular 'alter
index ... coalesce' would be a cheap and very
effective compromise.
hth
connor
--- [EMAIL PROTECTED] wrote: > > Bottom Line: I
stand by my original premise -
> > statistics alone do not determine an indexes
> candicacy
> > for rebuild. Some additional knowledge, eg "I
> know
> > the rows I've deleted will never be replaced" etc
> is
> > needed.
>
> Nice test case Connor, thanks.
>
> Q: What are the chances of knowing whether or not
> subsequent inserts will be able to reuse a slot?
>
> This would require a history of what is usually
> inserted, and making an assumption about future
> inserts based on that knowledge.
>
> I'm sure there is a good method for doing this,
> though I don't know what it is. Even so, it may
> not be practical to use.
>
> It seems like 20% of the effort needed to create a
> 'perfect' solution would provide the needed
> information
> to determine if an index should be rebuilt, and be
> correct 80% of the time.
>
> Jared
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> 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).
=====
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"Remember amateurs built the ark - Professionals built the Titanic"
__________________________________________________
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
