Message from discussion
How many people here rebuid index regularly?
Path: g2news2.google.com!postnews.google.com!t8g2000prg.googlegroups.com!not-for-mail
From: joel garry <joel-ga...@home.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: How many people here rebuid index regularly?
Date: Fri, 12 Oct 2007 11:49:57 -0700
Organization: http://groups.google.com
Lines: 258
Message-ID: <1192214997.844586.126860@t8g2000prg.googlegroups.com>
References: <Y1hNi.7131$DF2.4804@trndny09>
<MPG.216ffe82b29bfb0e98968f@news.dommel.be>
<1191581490.312563.10430@o80g2000hse.googlegroups.com>
<MPG.2170841994c1cc35989691@news.dommel.be>
<1191612916.550302.168250@57g2000hsv.googlegroups.com>
<DPadnWQ-lar_XZva4p2dnAA@bt.com>
<MPG.21729f89b3ff7a3e989694@news.dommel.be>
<R4ednSBlRM2OCJfaRVnytwA@bt.com>
<1191881301.346940.212200@v3g2000hsg.googlegroups.com>
<iJSdneve-bdlEJLaRVnytAA@bt.com>
NNTP-Posting-Host: 75.49.200.201
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1192214998 20970 127.0.0.1 (12 Oct 2007 18:49:58 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 12 Oct 2007 18:49:58 +0000 (UTC)
In-Reply-To: <iJSdneve-bdlEJLaRVnytAA@bt.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.0 ISA2K4-OC1
Complaints-To: groups-abuse@google.com
Injection-Info: t8g2000prg.googlegroups.com; posting-host=75.49.200.201;
posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
On Oct 12, 8:08 am, "Jonathan Lewis" <jonat...@jlcomp.demon.co.uk>
wrote:
> "joel garry" <joel-ga...@home.com> wrote in message
>
> news:1191881301.346940.212200@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 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.
Thanks!
jg
--
@home.com is bogus.
http://www.kasamba.com/professional/viewexpert.aspx?expid=128108&catid=10337
|
