RE: Index rebuilding
- From: "Goulet, Dick" <DGoulet@xxxxxxxx>
- To: <richard.foote@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Nov 2004 09:26:22 -0500
Richard,
As I said earlier, we can agree to disagree. Until I have
evidence to the contrary we can leave it at that.=20
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Richard Foote [mailto:richard.foote@xxxxxxxxxxx]=20
Sent: Tuesday, November 16, 2004 9:32 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Index rebuilding
Hi Dick
Comments embedded.
>
> 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.
The above is not what I consider to be an unbalanced index. However,
again=20
look at my presentation and you'll see the above is simply not true.
Even=20
indexes with monotonically increasing values remain balanced, even by
your=20
definition of unbalanced. The root block happily points to all it's
branch=20
blocks (if they exist) and sits "in the middle" so to speak of the index
structure. The branch blocks point to their branch/leaf blocks and sit
in=20
the middle of their respective blocks. There is no more or less blocks
to=20
the left or right of the index per se. If an index accesses more leaf=20
blocks, it's because it's interested in the range of index entries they=20
contain, pure and simple. If entries are deleted and Oracle is forced to
read *many* empty/almost empty leaf blocks then that could present an
issue=20
as discussed in the presentation.
> 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.
This again is simply not true. There is no such rule. What on earth
would=20
Oracle do on the fly, how would Oracle determine that such a problem
exists,=20
what is the problem ? Read my presentation again, see how index entries
are=20
added, see how space is re-claimed, see how there is no such 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:
What is this 40% out of balance metric ? How can an index be 40% out of=20
balance ? This is all simply not true. Do you have one shred of evidence
to=20
support such a claim ?
This 100s of millions of rows argument. If you have millions of rows but
you=20
commonly access only a handful, what does it matter if an index were=20
unbalanced by your definition ? You'll still likely only access a leaf
block=20
or two, will that really translate to a poor response time.? And if you=20
access 100,000s of rows, your index reads will likely be dwarfed by the=20
100,000s of data/table blocks you'll need to access. Again, the
presentation=20
tries to explain that the cost of reading the index is generally
relatively=20
small when compared to the cost associated with reading the parent
table,=20
even with massive tables. Therefore reducing the costs of the index
reads=20
may only have a trivial impact on the overall response times.
>
> 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.
The only thing missing from the above story is a "Once upon a time in an
Oracle database far far away ..." at the start followed by " ... and the
indexes lived happily ever after." at the end :) I would dearly love to
see=20
how Oracle on a public holiday suddenly rebalances it's indexes ...
The access path of a query is determined by the various costs associated
with the various execution plans that the optimizer considers, pure and=20
simple. And yes, histograms can play a part in why a plan may differ for
the=20
same query referencing different data sets. But suggesting that somehow=20
Oracle tolerates an "unbalanced" index until some quiet period when
it'll=20
wave a magic wand and fix things up is, how can I say it, wrong.
>
> 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.
Well yes, if you mean that Oracle only inserts into the "right-most"
leaf=20
node, then yes, as my presentation describes, only the right most index
node=20
splits via 90-10 splits. But the index still remains balanced ...
>
> 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.
I would be very very interested to see evidence of this "phenomenon". We
must have pretty lazy Oracle indexes here in Australia because during
public=20
holidays our indexes do damn all and must sit there smoking cigars and=20
playing cards all day. Perhaps it's a union thing ?
>
> 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.
>
My "studies" only began with 7.2 as I'm only a young little thing (btw,
Pete=20
Sharman will back me up here :) Most of the examples in the presentation
were performed on both AIX Unix and Windows 2000 on both 8.1.7.4 and=20
9.2.0.4. so I admit that I didn't consider ancient bugs and
inconsistencies.=20
I however see little point in clouding today's waters with how things
may=20
have worked 10 years ago. Hopefully these "zombies" are now long dead
(sorry=20
couldn't help it). I'll have a "serious look" at things if you can
provide=20
evidence to the contrary to what's in the presentation on space reuse on
any=20
currently supported version of Oracle.
If you can produce any actual evidence to support any of these theories
of=20
yours, I would very much be interested to see it. Until then, I'll just=20
remain quietly sceptical ...
Cheers
Richard=20
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
