Re: Performance impact of "MONITORING USAGE"

> The original poster wanted to know whether 'monitoring usage' is a good way
> of seeing whether an index has been used.
[deleted]
> efficient way of resolving a query. But none of that was in discussion. All
> that was being asked was how best to quantify the use of an index. You want
> to discuss other stuff, be my guest. But don't try and make out from a
> specific answer to a specific question that I am somehow proposing this as
> the be-all and end-all of performance tuning.

Look, being the original poster, I believe I still remember what I wanted to know, which was NOT whether or not 'MONITORING USAGE' was a good way of seeing whether an index has been used. It wasn't "how best to quantify the use of an index", either. It was whether or not using this feature introduces any significant overhead that must be kept in mind when proposing it to customers. And I have my answers meanwhile -- as I said in another answer to this thread several days ago.

I also fail to see how am I making out that you are proposing any kind of be-alls and end-alls. Sorry if I have lead you to believe otherwise. As I see it, you said that v$segment_statistics is good for some specific thing, and I say it is of no use for that specific thing. No generalisations whatsoever, right? I was just opposing your view that index usefulness (not usage!) may be quantified by using segment statistics. Or by "MONITORING USAGE", for that matter. There's no way of making positive statements about usefulness using these tools -- you can only make negative statements, e.g.: this index has never been used, so it is NOT useful. And with v$segstat you cannot say even that much (with 100% confidence, that is).

> For your information, you'll find a similar approach to *quantifying* index
> usage explained in all sorts of places, including if I remember correctly,
> Jonathan Lewis' Practical Oracle 8i where he advocates building an index
> into its own tablespace, and monitoring reads and writes against that
> tablespace's datafile.

Well, there was no other way in the 8i days. Now we have v$segstat for finding potential "low hanging fruit" and MONITORING USAGE for finding redundant indexes.

> End of thread as far as I'm concerned. You appear to have a wilful desire to
> pick a fight.

Be my guest. I did my tests and got my answers. And I have no desire -- wilful or not -- to pick fights. Up to this point, this has been a very peaceful discussion, IMHO. Despite the occasional reference to my bonnet.

Cheer up!
Flado Received on Mon Dec 08 2003 - 09:53:38 CST