| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Checking the rebuildability of an index
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 <hamcdc_at_yahoo.co.uk>
Sent by: root_at_fatcity.com
08/09/2002 01:48 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
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
"Remember amateurs built the ark - Professionals built the Titanic"
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: hamcdc_at_yahoo.co.uk 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Jared.Still_at_radisys.com 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: 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 Fri Aug 09 2002 - 14:48:31 CDT
 |
 |