Re: Index management

"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.
>

   I assume he means a dirty block in the table is a data (as opposite to index)

   block which has changed since the last index rebuild. Presumably if the    number (and hence percentage) of

1. new blocks (i.e. rows put into the index in a sub-optimal manner)
2. 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.
3. updated blocks which have have rows moved and hence non-optimal placment of index entries.

   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. 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?

   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.

   Automating this stuff is what the big guys are essentially pushing (even if

   they wrap it in sales talk).

   USE YOUR IDLE CYCLES MAN!!    Hmm that could be the hippie salesman coming out in me!

> Anyway, we can now, finally, work out what Mike was talking about a few
> posts ago.
>
> Here is his original quote:
>
> "To distill my advice on index rebuilds, I say to only rebuild indexes
> which show, through proper analysis, to have problems. Examples of
> possible probelms are: a large clustering factor to dirty base table
> block ratio, [and he does go on to list a number of other tests which
> are not at issue in this thread]"
>
> That can now be translated to: "I say to rebuild an index if its
> clustering factor exceeds the number of used table blocks by a large
margin"
>
> That's all.
>
> I honestly thought (and hoped) it would be subtler and more cunning than
> that. But that's what he was saying. If DBA_INDEXES.CLUSTERING_FACTOR >
> DBA_TABLES.BLOCKS (by a lot) then REBUILD.
>
> The only slight problem is that an index rebuild cannot possibly change
> the number of BLOCKS for the table (hopefully, that one is obvious). And
> as I showed in my post which Mike won't actually reply to, the
> clustering factor won't change as a result of an index rebuild either.
>
> Meaning that the formula Mike is using to suggest the rebuild suggests
> it equally as well after the rebuild as before it -which by any
> definition makes it meaningless.
>
> No doubt, however, we will now discover that Mike has his own definition
> of an index clustering factor and that he doesn't use the one in
> DBA_INDEXES at all. Or some other definitional issue will arise. So in
> the interests of clarity.
>
> Mike: could you please confirm that you use the formula
> DBA_INDEXES.CLUSTERING_FACTOR > DBA_TABLES.BLOCKS (by a lot). In other
> words, to assess an index's clustering factor, you take the figure shown
> in the column of that name in the DBA_INDEXES view. And that to assess
> the number of "dirty base table blocks" you use the BLOCKS column in the
> DBA_TABLES view. If you get your figures from some other source or
> computation, could you please explain how you do it?
>
> And you claim to have seen CLUSTERING_FACTOR in DBA_INDEXES change after
> an index rebuild, right?
>
> Thanks
> HJR
Received on Sun May 09 2004 - 17:11:59 CDT