From oracle-l-bounce@freelists.org Tue Nov 16 07:24:44 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id iAGDOiP21660 for ; Tue, 16 Nov 2004 07:24:44 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id iAGDOii21655 for ; Tue, 16 Nov 2004 07:24:44 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9952072C133; Tue, 16 Nov 2004 08:31:01 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 24238-41; Tue, 16 Nov 2004 08:31:01 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0237072D485; Tue, 16 Nov 2004 08:30:53 -0500 (EST) Message-ID: <031101c4cbe9$0dd55600$0100000a@FOOTE> From: "Richard Foote" To: References: <030101c4cbe2$caa07fa0$0100000a@FOOTE> Subject: Re: Index rebuilding Date: Wed, 17 Nov 2004 00:32:08 +1000 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-archive-position: 12281 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: richard.foote@bigpond.com Precedence: normal Reply-To: richard.foote@bigpond.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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 look at my presentation and you'll see the above is simply not true. Even indexes with monotonically increasing values remain balanced, even by your definition of unbalanced. The root block happily points to all it's branch 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 the middle of their respective blocks. There is no more or less blocks to the left or right of the index per se. If an index accesses more leaf blocks, it's because it's interested in the range of index entries they 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 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 Oracle do on the fly, how would Oracle determine that such a problem exists, what is the problem ? Read my presentation again, see how index entries are 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 balance ? This is all simply not true. Do you have one shred of evidence to support such a claim ? This 100s of millions of rows argument. If you have millions of rows but you commonly access only a handful, what does it matter if an index were unbalanced by your definition ? You'll still likely only access a leaf block or two, will that really translate to a poor response time.? And if you access 100,000s of rows, your index reads will likely be dwarfed by the 100,000s of data/table blocks you'll need to access. Again, the presentation tries to explain that the cost of reading the index is generally relatively small when compared to the cost associated with reading the parent table, even with massive tables. Therefore reducing the costs of the index reads 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 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 simple. And yes, histograms can play a part in why a plan may differ for the same query referencing different data sets. But suggesting that somehow Oracle tolerates an "unbalanced" index until some quiet period when it'll 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 node, then yes, as my presentation describes, only the right most index node 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 holidays our indexes do damn all and must sit there smoking cigars and 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 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 9.2.0.4. so I admit that I didn't consider ancient bugs and inconsistencies. I however see little point in clouding today's waters with how things may have worked 10 years ago. Hopefully these "zombies" are now long dead (sorry couldn't help it). I'll have a "serious look" at things if you can provide evidence to the contrary to what's in the presentation on space reuse on any currently supported version of Oracle. If you can produce any actual evidence to support any of these theories of yours, I would very much be interested to see it. Until then, I'll just remain quietly sceptical ... Cheers Richard -- http://www.freelists.org/webpage/oracle-l