Re: Index management

David Williams wrote:

> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:409e95ac$0$13706$afc38c87_at_news.optusnet.com.au...
>

>>Mike Ault wrote:
>>
>>
>>>Anyway, I thought the concept of dirty base table blocks was
>>>self-evident to those using Oracle, but since there seems to be some
>>>confusion let me digress into an explanation. When a table is created
>>>all of its blocks are in a sense "clean", that is, they contain no
>>>data. As Oracle writes data to the database tables, their blocks
>>>become "dirty", a reference no doubt to the "dirty buffers" the
>>>dirty-buffer writer writes to them (while the politically correct
>>>"database writer" has supplanted the original "dirty buffer writer" in
>>>the original database documentation in version 6 and earlier it was
>>>"dirty buffer writer".) So, a dirty block contains data, while a clean
>>>one does not.
>>
>>
>>Extraordinary. Mike just keeps on re-writing the Oracle lexicon. A dirty
>>block is one with data in it (we all know it as a used block, or even
>>just a block below the high water mark). A clean block is an empty block
>>or a block above the high water mark. Fair enough. He has his own
>>private language on these matters that none of us can understand until
>>it's translated, but whatever keeps him happy, I suppose.
>>

There's no need to assume anything, David. Mike has told us what he means.

Quote: "So, a dirty block contains data, while a clean  >>>one does not."

> Presumably if the
> number (and hence percentage) of
>
> a) new blocks (i.e. rows put into the index in a sub-optimal manner)

Rows are not put into the index in a sub-optimal manner. They are placed where their value determines they should be placed. A new entry for "Borodin" must be inserted between one for "Beethoven" and one for "Britten". That's not going to be placed "sub-optimally".

> b) deleted blocks which leave sub-optimal free space in an index
> i.e. leave free space in a block which could be occupied by other
> index entries using optimal placement.

Deleted entries can always be re-used for entries which need to make use of it.

> c) updated blocks which have have rows moved and hence non-optimal
> placment of index entries.

I'm sorry. You're introducing a term here which has no meaning. "Sub-optimal placement of index entries" can have no possible meaning, because index entries must always be placed where their data value tells us they must be placed.

> is large relative to the size of the table (relative being a subjective
> value
> on how much optimal index placement gains you and how often sub-optimal
> index placement occurs) than you rebuild. IT IS SUBJECTIVE.

It most certainly isn't. Mike Ault says that when the clustering factor is bigger than the number of blocks in the table by a large margin (and large he has defined elsewhere as about 8 times, but we don't need to hold him to that for now), then a rebuild is called for.

It is objectively true that such a rebuild will leave both the clustering factor and the number of table blocks unchanged, and hence the measurement he is using to suggest an index rebuild will continue to suggest a rebuild.

> If you have
> an idle system all Sunday night then why not rebuild after the weekly
> full
> backup to POTENTIALLY gain a slight increase in performance
> in the next week? What do you lose by using idle machine time to
> potentially improve (and certainly not decrease) performance?

Because you can't back up that "certainly not decrease" statement, can you? I have seen perfomance slow down as a result of an index rebuild because the index now has to start reclaiming the extents it lost during the rebuild the very next time a user starts inserting into the table on the Monday morning.

But I suppose this is progress: "we have an enormous maintenance window, we have nothing better to do with our time or our CPU cycles, so why not rebuild".

It certainly beats trying to claim there is an objective measure based on clustering factor to tell you when to rebuild.

> Isn't that what these java 'hot spot' performance enhancing runtime
> on-the-fly optimzers are about. It's just doing the optimzing during
> idle time rather then the java ones trying to squeeze it in amongst
> actually
> running the application! Surely that's what Oracle self-managing and
> IBM self-optimizing stuff is all about - use idle time to help
> performance.

Regardless of the fact of the crassness of any approach that is effectively saying "I've got nothing else to do so I might as well rebuild my indexes", you are making one huge assumption: that a rebuild always improves performance and never degrades it. That assumption is simply not true.

> Automating this stuff is what the big guys are essentially pushing (even
> if
> they wrap it in sales talk).
>
> USE YOUR IDLE CYCLES MAN!!
A more sensible slogan might be "use your idle cycles intelligently, man".

Regards
HJR Received on Sun May 09 2004 - 18:20:52 CDT