Re: How many people here rebuid index regularly?

"joel garry" <joel-garry_at_home.com> wrote in message news:1191881301.346940.212200_at_v3g2000hsg.googlegroups.com...
>
>>From production:
>
> [snippage]
>
> ROWS_PER_BLOCK BLOCKS
> -------------- ----------
> 113 1877
> 114 4984
> 115 3167
> 116 28250
> 117 2096
> 118 4314
> 119 2301
> 120 5501
> 121 2526
> 122 9402
> 123 1803
> 124 3403
> 125 1255
> 126 2421
> 127 919
> 128 2528
> 129 985
> 130 1775
> 131 714
> 132 2354
> 133 727
> 134 1589
>
> [snippage of more rows like this]
>
> ROWS_PER_BLOCK BLOCKS
> -------------- ----------
> ----------
> sum 172843
>
>
> 176 rows selected.
>
>>From test:
>
> ROWS_PER_BLOCK BLOCKS
> -------------- ----------
> 113 1
> 121 1
> 131 1
> 160 1
> 165 1
> 188 210
> 189 2
> 192 1
> 193 697
> 197 1
> 198 2
> 199 52109
> 200 230
> 201 288
> 202 468
> 203 692
> 204 2554
> 205 10419
> 206 8775
> 207 3780
> 208 1040
> 209 463
> 210 32797
> 211 3
> 212 3
> 213 5
> 214 2
> 215 5
> 216 1
> 217 3012
> 218 1
> 219 1
> 220 1
> 223 358
> 224 1
> 225 1
> 227 1
> 228 1
> 230 1
> 231 222
> 233 1
> 235 1
> ----------
> sum 118154
>
> 42 rows selected.
>
> 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

1. It was extremely popular and I was fairly confident that
2. 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.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html