Re: SQL Tuning Question (8.1.7)

Richard Foote wrote:

> Hi Howard,
>
> Interesting script (and excellent site).

Yeah, I eventually got it right after heavy intervention from Jonathan and Mark!

> Couple of little points. Rather than calling unused indexes as "useless",
> there have been a number of times I've come across unused indexes that I've
> described as "unloved". The indexes were created for the best intentions but
> were simply not being used by the intended applications. Identify these
> indexes was helpful in identify queries that needed to be rewritten or
> indexed columns that needed histograms, etc. I guess I'm saying that it
> wasn't so much the index that was at issue but the underlining reasons why
> they weren't being used.

Good point, actually. Perspective in these matters is king.

> On identifying such "unused" indexes, I have also found the
> v$segment_statistics family of performance views quite useful in 9i. By
> comparing the ratio (and volume) of db block changes to logical reads, it's
> shown to be a very good indicator as to which indexes are heavily used by
> DML operations (high ratio of db block changes) vs. those indexes that are
> heavily used by non direct DML operations (low ratio of db block changes but
> high logical reads).

Mmmm. Thought about that one (but two knock-downs in one paper was getting a bit sad, I thought), but decided that any quantifiable method is subject to too many ifs and buts. If it was lots of DML versus lots of query usage, I'd agree (as you say, "heavily used by DML", with the emphasis on the "heavily"). But for the borderline (ie, general) cases, it's going to be, er, borderline, I think.

I thought it important overall to avoid the suggestion that there's any simple measure in the aggregate that requires little brain power. I mean, even logical versus physical I/O or physical reads v. physical writes can do it some of the time if the wind's in the right direction. Your suggestion is utterly valid, but again I don't think it is applicable simplistically (or should I say "simply") in all cases. So having dismissed the other two methods on the same grounds, I kind of didn't want to have to dish a third possibility.

But no, it's worth saying, and you've obviously found it useful, so I might well do so on the next update at the end of the month.

What I'm hoping we'll eventually get in 11z is the ability to do something like:

explain index usage for select X, Y from T1 where V='Q';

And the output of which says:

There are 4 indexes on table T1.
This statement touches I1 and saves 3000 physical reads. This statement touches I2 and saves 500 physical reads. Index I3 was ignored by this statement
Index I4... are you kidding?!

If they can estimate physical read savings for the buffer cache advisory, I'm sure they could do it for index assessments.

Thanks for the nice words and suggestions. Regards
HJR
>
> Cheers
>
> Richard
>
>
Received on Wed May 05 2004 - 08:46:19 CDT