Re: How many people here rebuid index regularly?

On Oct 12, 8:08 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "joel garry" <joel-ga..._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.

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 :-)

...
ROWS_PER_BLOCK BLOCKS
-------------- ----------

           223        153
           224        603
           225        137
           226        427
           227        143
           228        554
           229        141
           230        210
           231        118
           232        125
           233        134

ROWS_PER_BLOCK     BLOCKS
-------------- ----------
           234        122
           235         11
           236         12
           237         24
           238         12
           239         15
           240         15
           241         15
           244          2
           245          1
           249          1

>
> 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 simpleminded.   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.

Thanks!

jg

--
@home.com is bogus.
http://www.kasamba.com/professional/viewexpert.aspx?expid=128108&catid=10337