RE: Index rebuilding

Good Morning Richard,

        Well 67% is good enough for me. At least you've got the discussion going which is in and of itself a good thing.

        By Unbalanced I mean an index having more leaf blocks on either the right or left side than the other. This therefore leads to more blocks being accessed as one heads in one direction than the other. I see this fairly regularly with tables that have millions of rows of data that are constantly increasing in row count in one particular direction. Basically it's an index on date where each new record added has sysdate as it's value. It appears that Oracle has some kind of 60-40 rule built into it such that if an index is less than 40% out of balance (as I've stated above) then it will do little if anything at the current moment & wait till it gets more free time before correcting the problem. That squares with Oracle's write less philosophy & I can agree with that. The problem comes where the table in question has hundreds of millions of rows of data at which 40% can become a rather large number. What brings me to believe this is two things:

1. the explain plan for data that is "history" (greater than 60 days old) is different from one that accesses current data where the data is within say the last 10 days. In the first case an "index range scan" is used where as in the former it will defer to a "full table scan". Now that is a bit fishy, but it gets even more interesting. OK, so the optimizer likes full table scans, then take the table away & see what happens. Easy since the data table & index are in separate tablespaces, take the data tablespaces off line for a bit. Again a simple "select test_date, count(*)" will suffice. Well If I'm looking for historical data the explain plan uses the "Index range scan", but current data utilizes a "fast full index scan". ODD at best. Possibly the histograms hold a clue? Well yes they do, they are distinctly unbalanced (think of a scale or balance beam here) with the buckets for the current data being heavier than the those for historical data. Maybe your presentation covers this condition, if so I missed it. BTW: give the database a quite weekend, like our Labor Day weekend 3 nice quite days of just sitting there doing nothing, and things change to a more equal representation. Namely an index range scan is used for historical and recent data. Therefore I believe that Oracle tolerates an unbalanced index, until it has either exceeded some internal limit or there is sufficient time to clear the problem.
2. the second thing that leads me to this belief is a pile of dumps of index data, similar although not identical to yours, that I've done over time to try and figure the above out. Again it appears from the dumps, sorry I can't post them their big enough to give our local mail server a fit, that index blocks can be added to one side while the height of the index does not increase.

        As for real evidence of that I believe I can come up with that in a couple of weeks when we have maintenance on the server in mind scheduled. Computers don't understand holidays you know, even if I do.

        On your third point, I'll have a serious look again at your presentation on block reuse. In Oracle 6 and 7 it was totally possible & actually did happen, that blocks of index data in a highly transactional index (lots of inserts, update, delete) would empty out and the dbms would branch around them leaving them as "leaf blocks" that were not counted and not free. Basically they were zombies. You could see it happening as an index that had rather steady growth over time all of a sudden started growing at a logarithmic rate. I was rather "upset" with OTS's explanation that "this is Impossible" that I went through a lot of work similar to yours to prove them wrong. Finally in 7 I was able to get OTS to admit that the possibility did exist and that they had finally verified my test case. Lets just say it was one royal pain in the $%%. I have not seen anything like this since Oracle 8.0 therefore I believe the problem was addressed & fixed. That may well be the basis for your contention & I'll yield on the subject.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Richard Foote [mailto:richard.foote_at_bigpond.com]=20 Sent: Monday, November 15, 2004 9:11 AM
To: oracle-l_at_freelists.org
Subject: Re: Index rebuilding

Hi Dick

Comments embedded

> Looked at Richard Foote's paper. Don't know about that.

That's fair enough, I'll be happy with a 67% hit ratio ;)

> I did prove to
> OTS several years ago that a block could get "lost" in an index due to
> deletion/updates that left it empty.

Hell, I lose stuff all the time.

>I believe that got finally fixed
> in Oracle 8i.

Phew !!

> I've still seen cases of index's becoming unbalanced, I
> know the docs day it's impossible, but it does happen without the
index
> height increasing.

Are you confusing an "unbalanced" index, with a skewed one (a term I picked=20
up from Jonathan Lewis) ?

Because, believe me, it just can't happen. I would love to see just one=20 example, I honestly truly woolly would. Next time you "see" one, please=20 document it, take a photo, whatever. Some evidence please, else such claims=20
are just myth spreading hype...

> And I still believe that index deletes don't get
> flushed so efficiently, as Richard suggests. If that was the case
then
> I can't explain why an index rebuild can cause an index to shrink by
30%
> or more.

Umm, perhaps because the deleted space that's cleaned out is not=20 subsequently re-used. Remember, Oracle sometimes cleans out the deleted=20 space but as the presentation suggests, there may be scenarios where this=20
space is not subsequently reused (eg. when a table/index is dramatically

shrink in size).

The presentation quite clearly describes how an index can shrink by 30%,

it's no mystery ...

> And recent experience still shows that a rebuild can cause
> significant performance improvement.

Can you perhaps share with us all these recent experiences ? What were the=20
characteristics of the index and associated table, what was the=20 pre-performance, what was the post performance, were there any changes to=20
the execution plans, how was the index accessed, what triggered the rebuild,=20
what proof do you have that is was the rebuild and not other factors. Your=20
13 little words above make an interesting claim, but without any details,=20
they remain 13 little words...

The presentation discusses various scenarios when an index rebuild may be=20
beneficial. I've never said an index rebuild won't ever help, I clearly=20 define scenarios when they could. Are you suggesting that your "recent=20 experiences" don't match up with the scenarios discussed, if so please=20 elaborate.

> And Oracle has provided the
> capability to rebuild indexes which is not trivial.

Of course. Great for moving indexes from one tablespace to another. Great=20
for many other maintenance operations and in some rare cases, great for=20 improving performance.

Thank you Oracle !!

> Therefore, NEVER
> use the word "never" unless your absolutely certain that under all
> circumstances it will be absolutely true. And in the current context,
> that is the truth, that is, never can never be an absolute.

Don't want to be all philosophical here, but please note I've never claimed=20
that one *never* needs to rebuild an index. In fact, the presentation goes=20
to some pains to explain why it *might* be necessary. The presentation=20 mentions a number of common myths and tries to explain and show why they're=20
a nonsense. If one's justification for regularly rebuilding is any of these=20
myths, then one may just want to reconsider ...

>
> BTW: Since we've a few "myth busters" in the group. I appreciate the
> effort these people put into "myth busting", even if they are later
> proven to have erred.

>At a very minimum they start discussion and
> re-examination of commonly held beliefs that can have changed or lost
> significance over the years(like it's best to have all of a tables
data
> in the first extent). Such discussion, although sometimes the start
of
> "Holy Wars", is healthy (not the Holy War though) and a necessary part
> of all of us growing.

God, I hate "Holy Wars" (no pun intended).

>That being said, let it be noted that I agree to
> disagree, in part, with Mr Foote.
>

And that is your right.

Just let me know which "parts" and I'll gladly show why you're wrong :)

Cheers

Richard=20

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l