Re: Performance impact of "MONITORING USAGE"

> The view isn't called v$segstat, so I'm not sure what you're getting at. One

09:37:22 flado_at_ax8i>desc v$segstat

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 TS#                                                NUMBER
 OBJ#                                               NUMBER
 DATAOBJ#                                           NUMBER
 STATISTIC_NAME                                     VARCHAR2(64)
 STATISTIC#                                         NUMBER
 VALUE                                              NUMBER

09:37:28 flado_at_ax8i>desc v$segment_statistics
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 TS#                                                NUMBER
 OBJ#                                      NOT NULL NUMBER
 DATAOBJ#                                           NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 STATISTIC_NAME                                     VARCHAR2(64)
 STATISTIC#                                         NUMBER
 VALUE                                              NUMBER

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

> 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.
"A wild imbalance in favour of LIOs" means high buffer hit ratio. Just ignore it.

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

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

Cheers,
Flado Received on Mon Dec 08 2003 - 03:05:27 CST