> Picking just one potentially interesting index, as it does > a fast full scan with aggregate of all entries.
> Code changes needed: > supply the index name in the 'select from user_objects' > (which may have to be dba_objects)
> Where I have 'column is not null' change the column > names (and add predicates if necessary) to cover the > columns in the chosen index.
> A note of the number of leaf_blocks in the index (from > dba_indexes/user_indexes) before and after would also > be useful, and the block size.
I have a situation where the production schema had a major delete at the end of last year, and has slowly been adding since. When I exp/ imp the schema in question to a test db, the size is a good 20% smaller. I then look at the largest index that OEM tells me has significantly fewer blocks with your program, and I see a much broader distribution of number of rows per index blocks.
SQL> select leaf_blocks from user_indexes where index_name='IC_MOVEMENTS_2';
LEAF_BLOCKS ----------- 118334
Therefore, exp/imp must indeed be the answer to life, indices and everything! :-)
The question that is begged: Can-or-should I just compare the leaf_blocks on all indices between production and test and rebuild those with the largest difference? Seems too simple. (Especially since I know if I mention this to this customer they will just want me to exp/imp the whole db, MS-think abounds). I've never felt any need to do anything to these indices, except due to the space issue - between hardware upgrades and such, this is the first time they've gone a couple of years with no maintenance.
On Oct 9, 5:56 am, MTNorman <mtnor...@duke-energy.com> wrote: ...
> 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.
I quite like that advice. It's not simplistic ("If index is lots bigger than table, then it needs further investigation") and it asks for cross-checking with other metrics (in this case, segment activity, but I wouldn't rule out throwing in the del_lf_rows/lf_rows measure, too). Much, much better than "if some ratio=20%, rebuild"!!
> > Picking just one potentially interesting index, as it does > > a fast full scan with aggregate of all entries.
> > Code changes needed: > > supply the index name in the 'select from user_objects' > > (which may have to be dba_objects)
> > Where I have 'column is not null' change the column > > names (and add predicates if necessary) to cover the > > columns in the chosen index.
> > A note of the number of leaf_blocks in the index (from > > dba_indexes/user_indexes) before and after would also > > be useful, and the block size.
> I have a situation where the production schema had a major delete at > the end of last year, and has slowly been adding since. When I exp/ > imp the schema in question to a test db, the size is a good 20% > smaller. I then look at the largest index that OEM tells me has > significantly fewer blocks with your program, and I see a much broader > distribution of number of rows per index blocks.
> SQL> select leaf_blocks from user_indexes where > index_name='IC_MOVEMENTS_2';
> LEAF_BLOCKS > ----------- > 118334
> Therefore, exp/imp must indeed be the answer to life, indices and > everything! :-)
> The question that is begged: Can-or-should I just compare the > leaf_blocks on all indices between production and test and rebuild > those with the largest difference? Seems too simple. (Especially > since I know if I mention this to this customer they will just want me > to exp/imp the whole db, MS-think abounds). I've never felt any need > to do anything to these indices, except due to the space issue - > between hardware upgrades and such, this is the first time they've > gone a couple of years with no maintenance.
Hi joel, i'm very surprised for this approach. i'm sorry but do you make these operation for know if your indexes must be rebuild? Tell more please. Regards Alberto
> On 9 Ott, 00:08, joel garry <joel-ga...@home.com> wrote: > > 42 rows selected.
> > SQL> select leaf_blocks from user_indexes where > > index_name='IC_MOVEMENTS_2';
> > LEAF_BLOCKS > > ----------- > > 118334
> > Therefore, exp/imp must indeed be the answer to life, indices and > > everything! :-)
> > The question that is begged: Can-or-should I just compare the > > leaf_blocks on all indices between production and test and rebuild > > those with the largest difference? Seems too simple. (Especially > > since I know if I mention this to this customer they will just want me > > to exp/imp the whole db, MS-think abounds). I've never felt any need > > to do anything to these indices, except due to the space issue - > > between hardware upgrades and such, this is the first time they've > > gone a couple of years with no maintenance.
> Hi joel, > i'm very surprised for this approach. > i'm sorry but do you make these operation for know if your indexes > must be rebuild?
No, I've never rebuilt any indexes on this db. I'm thinking about it now, for space reasons, not performance - I don't think there are any fast full index scans on this index, for instance. There's just a number of gigabytes of wasted space in this schema.
> Therefore, exp/imp must indeed be the answer to life, indices and > everything! :-)
> The question that is begged: Can-or-should I just compare the > leaf_blocks on all indices between production and test and rebuild > those with the largest difference? Seems too simple. (Especially > since I know if I mention this to this customer they will just want me > to exp/imp the whole db, MS-think abounds). I've never felt any need > to do anything to these indices, except due to the space issue - > between hardware upgrades and such, this is the first time they've > gone a couple of years with no maintenance.
Joel,
Sorry for taking so long to get back on this - I've been a bit busy for the last week.
Simple solutions are desirable - and there's no such thing as "too simple", although there is "too simple-minded".
It's an interesting set of figures on the rebuild - especially with those little spikes. I'm guessing that there's been a little bit of activity that would explain why you've got a few blocks with about 113 rows in - those look like the result of a few leaf block splits.
Could I guess that the leading column of this index has a few (perhaps just 3) values which are particularly popular - perhaps it's a single column index with a lot of skew, or a multi-column index with a skew on the first column. That might explain the spikes at 199, 205, and 210 rows per block. If that's the case, then compressing on that column would be sensible at any time.
Since you've got 176 rows reported before the rebuild that suggests you've got some blocks with 290 rows per block - which is odd because that doesn't show up after the rebuild. (At a default 10% free on a rebuild that 290 peak ought to drop to about 261, and your figures are way below that - do the counts start to taper off at about 230 rows per block so that blocks with more rows are just the odd one or two ?)
The post-rebuild figures would encourage me to look closely at the index because it isn't a "boring, random" index; but (if I hadn't seen the post-rebuild) I would have looked at the pre-rebuild figures and decided that I wasn't really going to see a DIRECT performance improvement from the rebuild - so I would only consider rebuilding it if a) It was extremely popular and I was fairly confident that b) the 60,000 (call it 40,000 after initial degeneration) drop in the block count meant I would get a constant saving in the buffer cache that paid for the work and time spent in rebuilding. However, given that blocks seem to start splitting soon after the rebuild I might then take a little care to balance the frequency of rebuilds against the pctfree I set so that I managed to rebuild before any serious amount of block splits started to occur.
Bottom line - if you see a performance issue related to db file sequential reads, and if the segment statistics (v$segstat) highlights a particular index as being a major culprit, and if the query shows the index blocks to be thinly populated (spread like yours, or with a very long near-empty tail that you think is being scanned) then consider rebuilding a thinly populated index, and coalescing an index with a long tail.
Remember: a B-tree index on randomly arriving data will spread to an average 70% utilisation. A rebuild index will (by default) run at 90% utilisation. Therefore rebuilding indexes which show a 20% drop between prod and test is too simple-minded. An index that shows an interesting pattern before or after rebuild may be worthy of special consideration.
> > Therefore, exp/imp must indeed be the answer to life, indices and > > everything! :-)
> > The question that is begged: Can-or-should I just compare the > > leaf_blocks on all indices between production and test and rebuild > > those with the largest difference? Seems too simple. (Especially > > since I know if I mention this to this customer they will just want me > > to exp/imp the whole db, MS-think abounds). I've never felt any need > > to do anything to these indices, except due to the space issue - > > between hardware upgrades and such, this is the first time they've > > gone a couple of years with no maintenance.
> Joel,
> Sorry for taking so long to get back on this - I've been > a bit busy for the last week.
No problem!
> Simple solutions are desirable - and there's no such thing as > "too simple", although there is "too simple-minded".
I run into that a lot.
> It's an interesting set of figures on the rebuild - especially > with those little spikes. I'm guessing that there's been a little > bit of activity that would explain why you've got a few blocks > with about 113 rows in - those look like the result of a few > leaf block splits.
Guessing? What would Alex say! :-)
> Could I guess that the leading column of this index has a few > (perhaps just 3) values which are particularly popular - perhaps > it's a single column index with a lot of skew, or a multi-column index > with a skew on the first column. That might explain the spikes at > 199, 205, and 210 rows per block. If that's the case, then compressing > on that column would be sensible at any time.
All indices on this table have lots of columns - Primary has 10 columns, another has 5, the one in question 6. Being a generalized ERP inventory system, they all start with a company code (just one in each instance, so far, 1 character), and something else with few values next. So that's a spot-on guess. The first 5 columns in this index are limited in value range, the last is a document number which comes from several systems with several definitions, some of which are steadily increasing.
> Since you've got 176 rows reported before the rebuild that suggests > you've got some blocks with 290 rows per block - which is odd > because that doesn't show up after the rebuild. (At a default 10% > free on a rebuild that 290 peak ought to drop to about 261, and > your figures are way below that - do the counts start to taper > off at about 230 rows per block so that blocks with more rows > are just the odd one or two ?)
Yes, sorry I didn't post the whole list, I didn't think it would be that interesting :-)
> The post-rebuild figures would encourage me to look closely at > the index because it isn't a "boring, random" index; but (if I hadn't > seen the post-rebuild) I would have looked at the pre-rebuild > figures and decided that I wasn't really going to see a DIRECT > performance improvement from the rebuild - so I would only > consider rebuilding it if > a) It was extremely popular
No evidence one way or the other, but no one complains...
> and I was fairly confident that > b) the 60,000 (call it 40,000 after initial degeneration) drop > in the block count meant I would get a constant saving in > the buffer cache that paid for the work and time spent in > rebuilding.
I have no such confidence. But I do have a routine from the dark ages that imp/exp the whole schema on a weekend night. I'm trying to figure out if I can justify to management anything less simple- minded. So far I haven't.
> However, given that blocks seem to start splitting soon after the > rebuild I might then take a little care to balance the frequency of > rebuilds against the pctfree I set so that I managed to rebuild > before any serious amount of block splits started to occur.
This does seem worthwhile to watch on certain indices.
> Bottom line - if you see a performance issue related to db file > sequential reads, and if the segment statistics (v$segstat) highlights > a particular index as being a major culprit, and if the query shows > the index blocks to be thinly populated (spread like yours, or with > a very long near-empty tail that you think is being scanned) then > consider rebuilding a thinly populated index, and coalescing an index > with a long tail.
No one's complaining... (except when I added a repository instance with the wrong sga_max_size and started swapping [D'Oh!]).
> Remember: a B-tree index on randomly arriving data will spread > to an average 70% utilisation. A rebuild index will (by default) run > at 90% utilisation. Therefore rebuilding indexes which show a 20% > drop between prod and test is too simple-minded. An index that > shows an interesting pattern before or after rebuild may be worthy > of special consideration.
Most of the indices have some sort of steadily increasing component, like a job number in each of several factories or sales order number in each of several divisions. This particular index has the document number last. I was kind of surprised I didn't see a size difference (either way) in some of the other more normal large indices. I need to look more closely at what else shrank, and what didn't.
|
