Re: Performance impact of "MONITORING USAGE"

"Vladimir Andreev" <flado_at_imail.de> wrote in message news:7b208869.0312080105.418edce3_at_posting.google.com...

>
> > doesn't state an index is useful just because there are a lot of LIOs
> > against it. One measures the LIOs against the number of physical
operations.
>
> That would be the buffer hit ratio, right?

No, because you're measuring for a segment, not for a cache.

> > A wild imbalance in favour of LIOs indicates a lot of caching going on,
and
> > hence (because of the LRU mechanism) a lot of use for that index.
>
> Yes. However, it doesn't mean that the index is very useful -- just
> that it is used a lot.

Precisely, which if you read the context of the original post was what was being asked for. An index that is never used is, ipso facto, non-useful. An index that is used a lot may or may not be, but probably is.

> "A wild imbalance in favour of LIOs" means high buffer hit ratio. Just
> ignore it.

Now you're just being silly. It means nothing of the sort, because you are measuring this on a segment-by-segment basis, to indicate the access patterns on a particular segment. You are not making sweeping statements about the efficiency of a database by looking at one, highly-averaged, ratio.

> > Ergo, it's
> > a useful index (meaning that Oracle -and Users- have found a use for it
in
> > their execution plans).
> And that means only that you should not drop it.

It doesn't necessarily mean anything of the sort, actually.

>But, as you said
> earlier, there will always be some LIOs and PIOs recorded for any
> index, as long as the base table gets updated now and then. Whereas
> MONITORING USAGE can tell you whether the index was used for queries
> or not -- if not, drop it, if yes -- dive further to find whether it
> was used sensibly, or just forced by means of hints or RBO or
> whatever.

I don't know why you have a bee in your bonnet on this topic, but you're not making a lot of sense as a result. Monitoring usage simply says 'yes I have been, no I haven't been used'. It doesn't *quantify* how often an index has been used, and whether it therefore happened to be accessed 'by accident' because of one rogue ad hoc query, or whether it is consistently used as part of general query activity.

And yes, as I said, even the most useless index in the world will have some LIOs and PIOs because table maintenance requires index maintenance too. Which is why I also went on to say that it is the imbalance of the two that indicates the use of an index for extensive query work. An index which is only ever read because some maintenance work needs to be done on it as a result of table DML will have broadly similar physical writes and reads. You load the index leaf node (physical read), you do your maintenance work on it, no-one touches it again because it's a stupid index (no logical reads), and then you flush the node back to disk as it ages out (physical write). A hugely popular index will have a physical read to load it, lots of logical reads as the buffer is re-touched, and -eventually- a physical write.

Notice the existence of lots of logical reads in the second case?

> > But knowing which are used a lot is certainly a first
> > step. And yes, that's exactly what v$segment_statistics can show you.
>
> No it isn't. Knowing which queries cost you most of your LIOs, and
> which events cost you most of your non-idle wait time, would be a
> first tuning step, assuming you have made sure your bottleneck is
> indeed in the database, and not in some middle layer or the client
> itself.

The original poster wanted to know whether 'monitoring usage' is a good way of seeing whether an index has been used. It isn't, because it tells you nothing more than yes or no. I suggested the segment_statistics approach, because it allows you to quantify the use of an index, distinguishing between rarely used and very used. And it is blindingly obvious that an index that is rarely used is not necessarily a useless index, because it might only get used for producing the year-end accounts, but reduce the time to do that rare job from hours to minutes. You'd still have to wonder why such an index was permanently in place, however, and not created at year-end on an as needed basis. And it is just as blindingly obvious that an index that is accessed a lot does not necessarily mean that it is the most 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.

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. So this isn't rocket science, it isn't particularly new, and it isn't especially wrong.

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

HJR

-- 
------------------------------------
Oracle insights at www.dizwell.com
------------------------------------