Search on the internet, some expert say "In the overwhelming majority of cases, indexes are extremely well self-maintained and index rebuilds are NOT required, ever."
Tom Kytes in his website says "The time lag between index rebuilds should be approximately FOREVER. Tell me -- why why why WHY are you rebuilding them on a schedule? What is the scientific basis in reality that is driving you to do this???? "
And some other site says: "First rule of thumb is if the index has height greater than four, rebuild the index. For most indexes, the height of the index will be quite low, i.e. one or two. I have seen an index on a 3 million-row table that had height three. An index with height greater than four may need to be rebuilt as this might indicate a skewed tree structure. "
> Search on the internet, some expert say > "In the overwhelming majority of cases, indexes are extremely well > self-maintained and index rebuilds are NOT required, ever."
> Tom Kytes in his website says > "The time lag between index rebuilds should be approximately FOREVER. > Tell me -- why why why WHY are you rebuilding them on a schedule? What is > the scientific > basis in reality that is driving you to do this???? "
> And some other site says: > "First rule of thumb is if the index has height greater than four, rebuild > the index. For most indexes, the height of the index will be quite low, > i.e. one or two. I have seen an index on a 3 million-row table that had > height three. An index with height greater than four may need to be rebuilt > as this might indicate a skewed tree structure. "
> Which one is right?
> Thanks for your comments
> Thanks
You have to be certifiably insane to do index rebuilds just because some rule-of-thumb somewhere says that if some metric you don't understand reaches some value whose significance you don't understand says you should.
What Tom Kyte is saying is "APPROXIMATELY forever". In other words, not absolutely never, but very, very rarely when the specific circumstances require it. A skewed index would be such a circumstance (as would moving a table, doing DDL on a partitioned table with global indexes, realising your queries are for Column A, B and your index is built on Column B, A (though that's a re-create not a rebuild, but close enough) and so on).
If you simply say, "Height 4... rebuild", that is a silly way to manage a database. It's akin to saying, "Buffer Cache Hit Ratio = 75%, must increase db_cache_size!" Likewise "deleted leaf rows > 20% of leaf rows" is a daft way to go, because deleted leaf row space will sort itself out over time... unless the index is on something like a sequence (or time), and thus monotonically incrementing, because then you will never re-use that deleted leaf row space.... unless the deleted leaf row space comes in such large chunks that it causes entire blocks to be vacated, in which case you will...
I mean, in short, the subject is complicated and boiling it down to moronic rules of thumb is a mug's games.
But, generally and with due caution, it is more or less fair to say that if you HAD to have a rule of thumb that applied in a rhetorical 95% of cases for a rhetorical 95% of the time, it would be, "don't rebuild your indexes". The risks of adopting that approach far outweigh the costs associated with the other one of "rebuild your indexes routinely because otherwise your CPU cycles just go to waste".
Certainly, you do not want to be ROUTINELY rebuilding your indexes. (Having just scheduled a routine rebuild of an Intermedia index, I know there are exceptions even to that rule provided you get precise enough about it! But I'm assuming we're talking about regular b-tree indexes here otherwise).
> On Oct 5, 12:10 pm, "charlie cs" <charliecs(nospam)@hotmail.com> > wrote:
> > We are on 9i Oracle.
> > Search on the internet, some expert say > > "In the overwhelming majority of cases, indexes are extremely well > > self-maintained and index rebuilds are NOT required, ever."
> > Tom Kytes in his website says > > "The time lag between index rebuilds should be approximately FOREVER. > > Tell me -- why why why WHY are you rebuilding them on a schedule? What is > > the scientific > > basis in reality that is driving you to do this???? "
> > And some other site says: > > "First rule of thumb is if the index has height greater than four, rebuild > > the index. For most indexes, the height of the index will be quite low, > > i.e. one or two. I have seen an index on a 3 million-row table that had > > height three. An index with height greater than four may need to be rebuilt > > as this might indicate a skewed tree structure. "
> > Which one is right?
> > Thanks for your comments
> > Thanks
> You have to be certifiably insane to do index rebuilds just because > some rule-of-thumb somewhere says that if some metric you don't > understand reaches some value whose significance you don't understand > says you should.
> What Tom Kyte is saying is "APPROXIMATELY forever". In other words, > not absolutely never, but very, very rarely when the specific > circumstances require it. A skewed index would be such a circumstance > (as would moving a table, doing DDL on a partitioned table with global > indexes, realising your queries are for Column A, B and your index is > built on Column B, A (though that's a re-create not a rebuild, but > close enough) and so on).
> If you simply say, "Height 4... rebuild", that is a silly way to > manage a database. It's akin to saying, "Buffer Cache Hit Ratio = 75%, > must increase db_cache_size!" Likewise "deleted leaf rows > 20% of > leaf rows" is a daft way to go, because deleted leaf row space will > sort itself out over time... unless the index is on something like a > sequence (or time), and thus monotonically incrementing, because then > you will never re-use that deleted leaf row space.... unless the > deleted leaf row space comes in such large chunks that it causes > entire blocks to be vacated, in which case you will...
> I mean, in short, the subject is complicated and boiling it down to > moronic rules of thumb is a mug's games.
> But, generally and with due caution, it is more or less fair to say > that if you HAD to have a rule of thumb that applied in a rhetorical > 95% of cases for a rhetorical 95% of the time, it would be, "don't > rebuild your indexes". The risks of adopting that approach far > outweigh the costs associated with the other one of "rebuild your > indexes routinely because otherwise your CPU cycles just go to waste".
> Certainly, you do not want to be ROUTINELY rebuilding your indexes. > (Having just scheduled a routine rebuild of an Intermedia index, I > know there are exceptions even to that rule provided you get precise > enough about it! But I'm assuming we're talking about regular b-tree > indexes here otherwise).
I meant, of course, "...the BENEFITS of adopting that approach far outweigh the costs associated with the other one...."
> Search on the internet, some expert say > "In the overwhelming majority of cases, indexes are extremely well > self-maintained and index rebuilds are NOT required, ever."
> Tom Kytes in his website says > "The time lag between index rebuilds should be approximately FOREVER. > Tell me -- why why why WHY are you rebuilding them on a schedule? What is > the scientific > basis in reality that is driving you to do this???? "
> And some other site says: > "First rule of thumb is if the index has height greater than four, rebuild > the index. For most indexes, the height of the index will be quite low, > i.e. one or two. I have seen an index on a 3 million-row table that had > height three. An index with height greater than four may need to be rebuilt > as this might indicate a skewed tree structure. "
> Which one is right?
> Thanks for your comments
> Thanks
We do rebuild indexes, because it was measured to improve performance on our system (9i).
See the thread "Proving or Debunking the need for rebuilding" in this group for a report...
Did you ever find time to work out which indexes were particularly relevant to the massive change in the batch job, and what the difference was between the slow and the fast runs ?
Running statspack at its default level 5 once per hour across the batch job might give you some useful information about the SQL with the biggest time change, and what made the time change - level 6 would allow you to check if this was due to plan changes, and level 7 might show you where the I/O changed most.
> In article <Y1hNi.7131$DF2.4804@trndny09>, "charlie cs" <charliecs > (nospam)@hotmail.com> says...
> > We are on 9i Oracle.
> > Search on the internet, some expert say > > "In the overwhelming majority of cases, indexes are extremely well > > self-maintained and index rebuilds are NOT required, ever."
> > Tom Kytes in his website says > > "The time lag between index rebuilds should be approximately FOREVER. > > Tell me -- why why why WHY are you rebuilding them on a schedule? What is > > the scientific > > basis in reality that is driving you to do this???? "
> > And some other site says: > > "First rule of thumb is if the index has height greater than four, rebuild > > the index. For most indexes, the height of the index will be quite low, > > i.e. one or two. I have seen an index on a 3 million-row table that had > > height three. An index with height greater than four may need to be rebuilt > > as this might indicate a skewed tree structure. "
> > Which one is right?
> > Thanks for your comments
> > Thanks
> We do rebuild indexes, because it was measured to improve > performance on our system (9i).
> See the thread "Proving or Debunking the need for rebuilding" > in this group for a report...
Well, from what I read of that thread, you didn't measure very much before, you weren't entirely certain on what to measure afterwards, and it was all a bit vague and airy!
But that's standard fare in the 'I know my index rebuilds are beneficial' industry: no-one actually every properly measures a darn'd thing!
I don;t mean that in a critical way, by the way. More a frustration thing: everyone has anecdotes, few have facts and figures.
Anyway, even if you happen to be of that rare breed, someone for whom an index rebuild genuinely produces tangible, repeatable and measureable benefits, fine: I have no problem with that. So long as you, some third party with a bouffant hair-do or some poor innocent newbie passing over this thread months or years from now understands: that is not usual and it doesn't provide the basis for a simplistic rule of thumb of the sort that says, "If Height>= 4 then rebuild;"
> Search on the internet, some expert say > "In the overwhelming majority of cases, indexes are extremely well > self-maintained and index rebuilds are NOT required, ever."
> Tom Kytes in his website says > "The time lag between index rebuilds should be approximately FOREVER. > Tell me -- why why why WHY are you rebuilding them on a schedule? What is > the scientific > basis in reality that is driving you to do this???? "
> And some other site says: > "First rule of thumb is if the index has height greater than four, rebuild > the index. For most indexes, the height of the index will be quite low, > i.e. one or two. I have seen an index on a 3 million-row table that had > height three. An index with height greater than four may need to be rebuilt > as this might indicate a skewed tree structure. "
> Which one is right?
> Thanks for your comments
> Thanks
Proceed carefully in making any changes to an environment that is running in production.
Take the advice of Tom Kyte and Jonathan Lewis. Read up on the topic and the relevant history.
Beware of the silver bullet type of advice that tells you "when you have to rebuild an index". Most of the time it is un-needed in oracle.
My experience is that indexes generally do not need to be rebuilt unless: (1) I want to empty the tablespace (don't really need this anymore with 10g), or (2) we have an application that is _very_ delete intensive.
Delete intensive applications are usually trying to keep only a certain volume of data in the active database and have some process that deletes data on a frequent basis. Another type of app "reloads" data with the same key values but some additional data columns populated in later versions/data sets - instead of updates or merges - the code deletes and then inserts. In both of these cases, you will see the amount of space used by the index increase steadily while the table size remains relatively stable. Rebuilding the index results in a smaller index and improved performance on queries that use the index.
On Oct 4, 10:10 pm, "charlie cs" <charliecs(nospam)@hotmail.com> wrote:
> Search on the internet, some expert say > "In the overwhelming majority of cases, indexes are extremely well > self-maintained and index rebuilds are NOT required, ever."
> Tom Kytes in his website says > "The time lag between index rebuilds should be approximately FOREVER. > Tell me -- why why why WHY are you rebuilding them on a schedule? What is > the scientific > basis in reality that is driving you to do this???? "
> And some other site says: > "First rule of thumb is if the index has height greater than four, rebuild > the index. For most indexes, the height of the index will be quite low, > i.e. one or two. I have seen an index on a 3 million-row table that had > height three. An index with height greater than four may need to be rebuilt > as this might indicate a skewed tree structure. "
In article <RcudnXIKhJSqd5jaRVny...@bt.com>, jonat...@jlcomp.demon.co.uk says...
> Did you ever find time to work out which indexes were > particularly relevant to the massive change in the batch > job, and what the difference was between the slow and > the fast runs ?
No, Jonathan, Unfortunatly I never investigated more as of today, for lack of time and because the system is working fine as is.
I certainly agree that only a few indexes actually benefit from rebuild, and rebuilding them all is overkill and detrimental to some extent.
On the other hand, rebuilding all indexes is quick - on my system -, had no observable side effects in routine work, and we dont have to carefully analyze and measure whether a freshly added index actually needs a rebuild.
MTNorman wrote: > My experience is that indexes generally do not need to be rebuilt > unless: > (1) I want to empty the tablespace (don't really need this anymore > with 10g), or > (2) we have an application that is _very_ delete intensive.
> Delete intensive applications are usually trying to keep only a > certain volume of data in the active database and have some process > that deletes data on a frequent basis. Another type of app "reloads" > data with the same key values but some additional data columns > populated in later versions/data sets - instead of updates or merges - > the code deletes and then inserts. In both of these cases, you will > see the amount of space used by the index increase steadily while the > table size remains relatively stable. Rebuilding the index results in > a smaller index and improved performance on queries that use the > index.
But why? I would expect the new data to reuse the space of the deleted data also in the index. Especially if you use the same key values. I could imaginge the index size will increase for a longer time period than the table but in the end, the index size shall remain as stable as the table. Also your argument is not about performance, it's only about disk space.
From my experience index rebuilds are from the proactive-tuning-business which actually doesn't make sense at all. I never experienced a measurable performance gain by an index rebuild.
> Well, from what I read of that thread, you didn't measure very much > before, you weren't entirely certain on what to measure afterwards, > and it was all a bit vague and airy!
Well, I cannot really agree, Howard
As I recall, that was a very long, very controlled measurement effort, whose goal was to check whether our system did benefit from index rebuilds.
I really expected to see that index rebuilds were not needed.
To my surprise, the test showed that the rebuilds were beneficial. Trouble is that while the tests show with certainty a very measurable change, I dont have data to know with certainty why.
The best hypothesis right now is : * Many of our queries are optimized with index range scans. * Some important indexes do index monotonically increasing fields - mainly sequence and date primary keys. * The delete pattern is such that 9 out of ten rows with consecutive dates or sequences are deleted, leaving the corresponding indexes with non empty but sparsely populated leaf blocks. - Therefore range scans take longer
As I recall, and this is only an hypothesis... All the information I have is in the referenced thread - albeit in bits and pieces, sorry.
About repeatability... For various reasons it has happened that some of our customers have been running without rebuilds for a few weeks. And complained to our technical support that the system was becoming slow. The effect I have measured is therefore very real, experienced by actual users in the field.
> But that's standard fare in the 'I know my index rebuilds are > beneficial' industry: no-one actually every properly measures a darn'd > thing!
> I don;t mean that in a critical way, by the way. More a frustration > thing: everyone has anecdotes, few have facts and figures.
> Anyway, even if you happen to be of that rare breed, someone for whom > an index rebuild genuinely produces tangible, repeatable and > measureable benefits, fine: I have no problem with that. So long as > you, some third party with a bouffant hair-do or some poor innocent > newbie passing over this thread months or years from now understands: > that is not usual and it doesn't provide the basis for a simplistic > rule of thumb of the sort that says, "If Height>= 4 then rebuild;"
Sure. I am not claiming anything more than what I have observed *on our system* - and not suggesting anything more than "measure your system"
> Did you ever find time to work out which indexes were > particularly relevant to the massive change in the batch > job, and what the difference was between the slow and > the fast runs ?
> Running statspack at its default level 5 once per hour across > the batch job might give you some useful information about > the SQL with the biggest time change, and what made the > time change - level 6 would allow you to check if this was > due to plan changes, and level 7 might show you where the > I/O changed most.
Your advice has been carefully saved, btw, Jonathan ;-)
> In article <1191581490.312563.10...@o80g2000hse.googlegroups.com>, > hjr.pyth...@gmail.com says... > > > We do rebuild indexes, because it was measured to improve > > > performance on our system (9i).
> > > See the thread "Proving or Debunking the need for rebuilding" > > > in this group for a report...
> > Well, from what I read of that thread, you didn't measure very much > > before, you weren't entirely certain on what to measure afterwards, > > and it was all a bit vague and airy!
> Well, I cannot really agree, Howard
> As I recall, that was a very long, very controlled measurement effort, > whose goal was to check whether our system did benefit from index > rebuilds.
> I really expected to see that index rebuilds were not needed.
> To my surprise, the test showed that the rebuilds were beneficial. > Trouble is that while the tests show with certainty a very > measurable change, I dont have data to know with certainty why.
> The best hypothesis right now is : > * Many of our queries are optimized with index range scans. > * Some important indexes do index monotonically increasing fields - > mainly sequence and date primary keys. > * The delete pattern is such that 9 out of ten rows with consecutive > dates or sequences are deleted, leaving the corresponding indexes > with non empty but sparsely populated leaf blocks. > - Therefore range scans take longer
BTW a favorite optimization technique here is that critical queries get their data from the index alone. The underlying table is not accessed at all.
> > Well, from what I read of that thread, you didn't measure very much > > before, you weren't entirely certain on what to measure afterwards, > > and it was all a bit vague and airy!
> Well, I cannot really agree, Howard
> As I recall, that was a very long, very controlled measurement effort, > whose goal was to check whether our system did benefit from index > rebuilds.
And that may be, however your posts relating your efforts appear disjointed and vague and throw that same light on your testing and findings..
> I really expected to see that index rebuilds were not needed.
> To my surprise, the test showed that the rebuilds were beneficial. > Trouble is that while the tests show with certainty a very > measurable change, I dont have data to know with certainty why.
I realise this would take time, but have you considered a treedump of the indexes before and after the rebuild:
alter session set evnts = 'immediate trace name treedump level <index object id>';
You should see considerable differences in the dump files for a given index if the rebuild of it improves performance. And those file pairs with differences would indicate the indexes upon which you should concentrate your rebuild efforts.
> The best hypothesis right now is : > * Many of our queries are optimized with index range scans. > * Some important indexes do index monotonically increasing fields - > mainly sequence and date primary keys. > * The delete pattern is such that 9 out of ten rows with consecutive > dates or sequences are deleted, leaving the corresponding indexes > with non empty but sparsely populated leaf blocks. > - Therefore range scans take longer
> As I recall, and this is only an hypothesis... All the > information I have is in the referenced thread - albeit in > bits and pieces, sorry.
> About repeatability... For various reasons it has happened > that some of our customers have been running without rebuilds > for a few weeks. And complained to our technical support that > the system was becoming slow. The effect I have measured is > therefore very real, experienced by actual users in the field.
> > But that's standard fare in the 'I know my index rebuilds are > > beneficial' industry: no-one actually every properly measures a darn'd > > thing!
> > I don;t mean that in a critical way, by the way. More a frustration > > thing: everyone has anecdotes, few have facts and figures.
> > Anyway, even if you happen to be of that rare breed, someone for whom > > an index rebuild genuinely produces tangible, repeatable and > > measureable benefits, fine: I have no problem with that. So long as > > you, some third party with a bouffant hair-do or some poor innocent > > newbie passing over this thread months or years from now understands: > > that is not usual and it doesn't provide the basis for a simplistic > > rule of thumb of the sort that says, "If Height>= 4 then rebuild;"
> Sure. I am not claiming anything more than what I have observed > *on our system* - and not suggesting anything more than "measure your > system"- Hide quoted text -
> I realise this would take time, but have you considered a treedump of > the indexes before and after the rebuild:
> alter session set evnts = 'immediate trace name treedump level <index > object id>';
Test this one on a small index before trying it on a large index. On some versions of Oracle it does a full dump of all leaf blocks, rather than one line per block.
Remember, it reads the index one block at a time anyway, so it can be very slow even when it doesn't to a full dump.
> I realise this would take time, but have you considered a treedump of > the indexes before and after the rebuild:
> alter session set evnts = 'immediate trace name treedump level <index > object id>';
> You should see considerable differences in the dump files for a given > index if the rebuild of it improves performance. And those file pairs > with differences would indicate the indexes upon which you should > concentrate your rebuild efforts.
Comparing index sizes before and after rebuild would probably reveal all there is to know.
>> I realise this would take time, but have you considered a treedump of >> the indexes before and after the rebuild:
>> alter session set evnts = 'immediate trace name treedump level <index >> object id>';
>> You should see considerable differences in the dump files for a given >> index if the rebuild of it improves performance. And those file pairs >> with differences would indicate the indexes upon which you should >> concentrate your rebuild efforts.
> Comparing index sizes before and after rebuild would probably reveal > all there is to know.
Probably not. I wouldn't indicate that a huge waste of time and resources had just taken place which is what would be the end-result the majority of the time. -- Daniel A. Morgan University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
> >> I realise this would take time, but have you considered a treedump of > >> the indexes before and after the rebuild:
> >> alter session set evnts = 'immediate trace name treedump level <index > >> object id>';
> >> You should see considerable differences in the dump files for a given > >> index if the rebuild of it improves performance. And those file pairs > >> with differences would indicate the indexes upon which you should > >> concentrate your rebuild efforts.
> > Comparing index sizes before and after rebuild would probably reveal > > all there is to know.
> Probably not. I wouldn't indicate that a huge waste of time and > resources had just taken place which is what would be the end-result > the majority of the time. > -- > Daniel A. Morgan > University of Washington > damor...@x.washington.edu (replace x with u to respond) > Puget Sound Oracle Users Groupwww.psoug.org
Waste of time for majority of cases - yes. In a small number of cases where performance is critical and benefits are tangible, a good DBA should know which ones they are, and what he needs to do, and how often.
> > I realise this would take time, but have you considered a treedump of > > the indexes before and after the rebuild:
> > alter session set evnts = 'immediate trace name treedump level <index > > object id>';
> > You should see considerable differences in the dump files for a given > > index if the rebuild of it improves performance. And those file pairs > > with differences would indicate the indexes upon which you should > > concentrate your rebuild efforts.
> Comparing index sizes before and after rebuild would probably reveal > all there is to know.
No, it wouldn't be all there is to know.
If an index settles down at about 75% space utilisation in a steady- state scenario, it stands to reason that a rebuild will show it small after the rebuild that before. But if you were to conclude from that that the rebuild was obviously of benefit, and that the reduction in size was "all you needed to know", you'd be missing a trick or three.
Does the now-compacted index now suffer from higher contention rates than it used to? It could well do so. Do the next X-number of inserts to the table now take place slower than they did before because they now have to cause the index to re-acquire the empty space it previously already had available to it? It could well be the case.
> > > I realise this would take time, but have you considered a treedump of > > > the indexes before and after the rebuild:
> > > alter session set evnts = 'immediate trace name treedump level <index > > > object id>';
> > > You should see considerable differences in the dump files for a given > > > index if the rebuild of it improves performance. And those file pairs > > > with differences would indicate the indexes upon which you should > > > concentrate your rebuild efforts.
> > Comparing index sizes before and after rebuild would probably reveal > > all there is to know.
> No, it wouldn't be all there is to know.
> If an index settles down at about 75% space utilisation in a steady- > state scenario, it stands to reason that a rebuild will show it small > after the rebuild that before. But if you were to conclude from that > that the rebuild was obviously of benefit, and that the reduction in > size was "all you needed to know", you'd be missing a trick or three.
> Does the now-compacted index now suffer from higher contention rates > than it used to? It could well do so. Do the next X-number of inserts > to the table now take place slower than they did before because they > now have to cause the index to re-acquire the empty space it > previously already had available to it? It could well be the case.
> Size is NOT everything, in short.
Ah, the perils of making sweeping statements! Let me try again. I was replying to the treedump post and comparing looking at index sizes vs doing treedumps.
In a bulk rebuild, most indexes would flip from 75% to 90% utilisation (or whatever you set the new pctfree to be), and the sizes would vary accordingly. No surprises there.
However, some indexes would shrink in size by a far greater proportion. Those ones you examine a little closer. That and the fact that I (and I suspect the majority of people reading this, though I could be wrong) am not able to draw any meaningful conclusions as to whether a rebuild is warranted from looking at treedumps. The last time I looked, it just looked like a tree to me, something which I already knew.
Size, on the other hand, tells me that either a bulk delete has occurred, or the index is possibly suffering from monotonic inserts/ deletes. The first one needs no future action if the delete was a known one-off. The second one is a candidate for future rebuilds. And that is all I need to know.
If I inherit a database with a dubious history, I would schedule a mass rebuild for these reasons, plus the fact that you get every extent size possible under the blue sky. Especially, when you are required to add/resize datafiles at a rate out of kilter with expected growth rates. The trick however is deciding on a good pctfree for the individual larger indexes, especially when you have a quite a few to contend with. But that is another story.
csn...@gmail.com wrote: > Waste of time for majority of cases - yes. In a small number of cases > where performance is critical and benefits are tangible, a good DBA > should know which ones they are, and what he needs to do, and how > often.
I agree with you. But I doubt you will find one DBA in 10,000 that can define the metric that will tell them when that is.
Hint: I would expect the correct answer might make reference to SYS_OP_LBID. -- Daniel A. Morgan University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
> > alter session set evnts = 'immediate trace name treedump level <index > > object id>';
> Test this one on a small index before trying it on a large index. > On some versions of Oracle it does a full dump of all leaf blocks, > rather than one line per block.
> Remember, it reads the index one block at a time anyway, so > it can be very slow even when it doesn't to a full dump.
Thanks for the advice, Jonathan. No, David, I never did a treedump in my life.
Hmm.. If there is interest here, I could try to collect data from an actual production site, before and after a rebuild.
Which data would you (collective) want to see ?
Keep in mind that this would be from a customer production site. Therefore,
* Nothing destructive, please * Collected data take a reasonable amount of space * Collection takes at most ten minutes or so, preferably non blocking * You'll see changes induced by a one day activity (night job + daily activity)
When the deletion pattern creates relatively sparse clusters of deleted leafs, then the leaf blocks are not likely to be reused and the index block itself becomes fragmented (think delete 2 skip 3, then insert 4 later with keys related to the delete 2 recs). New leaf blocks are added for the insertions and the total index size just keeps extending.
Recently in a 9.2.0.6 database where the application attempts to keep only the last 4 days of completed orders online, index sizes dropped an average of 50% with an average performance improvement of 150%. Please note that "orders" enter the system in batches and stay in the systems for 4 days after they have completed; however, most of the orders loaded on day 1 are not deleted on day 5 or even by day 10. It's the widely spread deletion pattern of small numbers of records (particularly when the deletion order does not correlate to the insertion order) that creates an index that benefits from rebuilding. Other customers using the same application code with a dense insert- deletion pattern (50% of day 1 inserts are deleted on day 5) do not experience the performance drop off.
So if the index avg row length is a fraction of the table avg row length and the index storage is still a multiple of the table storage (30 bytes on the index, 200 bytes on the table, 10g for the table, 20g for the index) - you may want consider reindexing. The ratio does not necessarily mean rebuilding is needed - it's just a tool/method for identifying indexes to examine. A better method may be segment activity - fragmented indexes that are causing performance problems in OLTP also tend to be among the most active segments.
On Oct 5, 12:06 pm, Jan Krueger <j...@stud.uni-hannover.de> wrote:
> MTNorman wrote: > > My experience is that indexes generally do not need to be rebuilt > > unless: > > (1) I want to empty the tablespace (don't really need this anymore > > with 10g), or > > (2) we have an application that is _very_ delete intensive.
> > Delete intensive applications are usually trying to keep only a > > certain volume of data in the active database and have some process > > that deletes data on a frequent basis. Another type of app "reloads" > > data with the same key values but some additional data columns > > populated in later versions/data sets - instead of updates or merges - > > the code deletes and then inserts. In both of these cases, you will > > see the amount of space used by the index increase steadily while the > > table size remains relatively stable. Rebuilding the index results in > > a smaller index and improved performance on queries that use the > > index.
> But why? I would expect the new data to reuse the space of the deleted > data also in the index. Especially if you use the same key values. I > could imaginge the index size will increase for a longer time period > than the table but in the end, the index size shall remain as stable as > the table. Also your argument is not about performance, it's only about > disk space.
> From my experience index rebuilds are from the > proactive-tuning-business which actually doesn't make sense at all. I > never experienced a measurable performance gain by an index rebuild.
> Hmm.. If there is interest here, I could try to > collect data from an actual production site, before > and after a rebuild.
> Which data would you (collective) want to see ?
> Keep in mind that this would be from a customer > production site. Therefore,
> * Nothing destructive, please > * Collected data take a reasonable amount of space > * Collection takes at most ten minutes or so, > preferably non blocking > * You'll see changes induced by a one day activity > (night job + daily activity)
|
