Re: SQL Tuning Question (8.1.7)

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:4098d412$0$12740$afc38c87_at_news.optusnet.com.au...
> Jeremy wrote:
>
> > Hi Guys, what's the best resource (on the web) to look at for info on
> > tuning SQL and defining suitable indexes for a query (at this point I
> > expect someone to say you don't create indexes for a query, they should
> > be a part of the overall application design)? I am
>
> It doesn't answer your specific questions, and is only one pointer, and
> not particularly good news at that, but there's a new article on
> identifying useful versus useless indexes on www.dizwell.com (FAQ basic
> admin).
>

Hi Howard,

Interesting script (and excellent site).

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.

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

Cheers

Richard Received on Wed May 05 2004 - 08:22:23 CDT