Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Episode 2: the EntMgr Green Light!!

Re: Episode 2: the EntMgr Green Light!!

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sat, 08 Nov 2003 13:23:07 GMT
Message-ID: <%66rb.2511$aT.2303@news-server.bigpond.net.au>


Hi Domenic,

Comments embedded.

"Domenic G." <domenicg_at_hotmail.com> wrote in message news:c7e08a19.0311071534.24e0a280_at_posting.google.com...
> >
> > Hi Dom,
> >
> > Interesting, you should be a playwright ;)
> >
> > I've a few of questions regarding your normal routine DBA stuff.
> >
> > How long does it take you to validate structure all your indexes, what
> > resources does it consume, what availability issues do you have as a
result,
> > how much DBA time is spent running and analysing the results ?

The answers to the above would be interesting. The costs associated with validating the structure of all indexes is quite significant *and* the fact that such objects are locked would cause many availability issues. True you can now validate structure online with 9i but not if you wish to populate index_stats.

Therefore one would want to be pretty damn sure it was all worthwhile.

> >
> > What criteria do you use to regard an index worthy of rebuilding ?
>
> deleted leaf rows / total rows > 20 per cent

This is actually a very misleading criteria and generally speaking doesn't qualify an index for a rebuild. Why, well for a number of reasons. Firstly, deleted space is generally (my fav word ;) reusable so having > 20% deleted space means the index has capacity to store future index entries. It also means index splits are unlikely as you have ample free space. After the rebuild, you would now have the index more tightly packed. This means you've now increased the likelihood of an index split as you have less free space. Not only are index splits relatively expensive operations requiring extra CPU and I/O but at the end of the split, you now have 50% free space in each index node. Carry on like this for a while and it doesn't do much for the pct used for the index. Guess it's time for another rebuild ...

Vicious rebuild cycle continues.

I question any generalisation that suggests x% deleted space or only x% used space is bad. It's invariably misleading or plain wrong.

> or
> sequence based index that isn't reverse key needs rebuilding every so
> often to rebalance it

Another popular myth. Oracle indexes are *always* balanced. Always

What you may experience is a poorly skewed index or an index with poor data distribution. If you have significant deletions *but not enough to totally empty an index node*, then yes, such space may never be reused and an index rebuild may have some merit. I say may because as I'll mention later, a coalesce of the index may be a better tactic and I say may because the costs of such a re-org may not still not be worthwhile.

> or
> level is too deep relative to # of rows in table.

How often do your rebuilds actually result in a level decrease ? What causes such an outcome ?

If the rebuild of an index reduces its level, then such a rebuild is likely to be warranted. However, such a scenario is extremely rare. In almost all cases of such a thing occurring (bulk deletes and the such) it would result in the parent table being significantly deleted as well. That being the case, it's a table rebuild that's required, not an index rebuild (although all indexes must be rebuilt regardless) in order to reset HWMs.

>
> not long to rebuild the indexes - relatively small db.
> >
> > What benchmarks do you perform before the rebuild (ie. what performance
> > issues are you experiencing), what benchmarks do you perform after the
> > rebuild (ie. what has the rebuild achieved, what performance gains are
you
> > experiencing that's justifying the rebuilds) ?
>
> that's hard to measure -- doesn't mean you shouldn't do it. like oil
> changes on your car -- do you see the immediate benefit?

So what you're suggesting is that you going through all of this without being able to actually justify it at all. You have nothing to indicate whether or not it's actually beneficial, you have no metrics at all to support your actions, nothing runs particularly faster, users don't particularly notice any difference, red lights don't start to flash green ...

It's seems like a good idea.

There is actually a big difference between a car oil change and your normal routine DBA stuff. A car oil change is clearly proven to be good for your car, scientific research has shown that not replacing oil in your engine will cause major problems in the future. Try it and you'll see.

However, your normal routine DBA stuff is not quite so clearly defined. In fact there's quite a bit of research and information that strongly suggests rebuilding of indexes is *generally* a waste of time. There are exceptions however but those are just that, exceptions. It's encouraging that you at least attempt to diagnose which indexes may or may not benefit, although I don't agree with your criteria. I can think of one scenario/exception straight away where a rebuild might be useful that is caused by a random index that performs nothing but inserts. No deletes, no updates, only inserts, in a *non*- incremental fashion and perhaps index rebuild would be beneficial. It's not cut and dry.

I also don't agree with the concept of it's hard to measure any benefits but lets just do it anyway.

Have you considered not rebuilding for a while to see if "your engine seizes up" or whether adding oil really is benefical ?

> >
> > After rebuilding these indexes, how often and within what time periods
are
> > you finding that these indexes generally need rebuilding again ?
>
> like i said, i don't rebuild all of them -- looks like you're
> abandoning your databases.

What I'm suggesting here is are you rebuilding the same indexes over differing periods of time. Are your indexes returning to a "natural" state whereby if you did nothing, they would simply stay in the current state anyway or are they really deteriorating on an ongoing basis causing performance issues.

If they're the same index, then maybe they're trying to tell you something ...

> >
> > Have you considered coalescing ?
>
> no i use local extent management

You misunderstand me. Do some research on coalescing *indexes*. You might be pleased with what you may learn.

> >
> > Are you confident that the cost-benefits of such activity is in your
favour
> > ?
>
> i have to keep busy.

I guess. Personally, rather than being busy, I prefer to be productive.

Let me leave you with this little example.

You have a poorly fragmented index with heaps of deleted space you want to get rid of. You're concerned about a certain query that performs a 1000 row range scan on this index and you think a rebuild might help. Rather than reading just the two index leaf nodes that ideally this index should use to house these 1000 index entries, the index is actually using 4 leaf nodes (note this would mean only a 50% pct usage for this index, possible if say 50% of entries were deleted).

Now you strike me as the kind of person who would just jump at the opportunity to rebuild this sucker.

Let's see how this query has now improved.

Before, it was reading say 2 branch nodes, 4 leaf nodes and 1000 table blocks. Yes the clustering factor would determine how many of these 1000 table blocks are actually different, and yes the current table caching would determine how many of these are actually physical I/Os. But that's the same before/after the index rebuild so lets call this a total of 1006 logical I/Os.

After the rebuild, we now read 2 branch nodes, 2 leaf nodes and 1000 table blocks, that's 1004 logical I/Os or a reduction of just under 0.2%.

Huummmmm, not really something we would want to thump our chests in front of the boss about. The users of this particular query notice, .... well not a lot really.

If the index is used for unique scans, then there would actually be no difference at all.

Yes, there are implications regarding how the buffer cache handles these extra leaf nodes but you hopefully get my point ...

Cheers

Richard Received on Sat Nov 08 2003 - 07:23:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US