Skip to Content
avatar image
Former Member

Monitoring Index usage

Hi,

I tried to execute the index usage monitoring based on SAP note 912620 - FAQ: Oracle indexes.

However, I'm not sure how to interpret the result.

 

SELECT SUBSTR(OBJECT_NAME, 1, 40) SEGMENT_NAME,SUBSTR(STATISTIC_NAME, 1, 20) STATISTIC_NAME,VALUE "NUMBER"
FROM V$SEGMENT_STATISTICS
WHERE OBJECT_NAME LIKE 'MSEG%' AND STATISTIC_NAME IN ('logical reads', 'physical reads') 
order by value;


SEGMENT_NAME	STATISTIC_NAME NUMBER
--------------------------------------------
MSEG~S		        physicalreads	619
MSEG~R		        physicalreads	675
MSEG~Z4		physicalreads	1140
MSEG~Z1		physicalreads	17080
MSEG~0		        physicalreads	27606
MSEG~Z3		physicalreads	38665
MSEG~R		        logicalreads	47600
MSEG~Z4		logicalreads	48960
MSEG~Z1		logicalreads	63696
MSEG~S		        logicalreads	66080
MSEG~Z2		physicalreads	96416
MSEG~M		        physicalreads	155965
MSEG~Z3		logicalreads	247152
MSEG~M		        logicalreads	393104
MSEG~0		        logicalreads	643392
MSEG		        physicalreads	22369182
MSEG		        logicalreads	23181664
MSEG~Z2		logicalreads	37081296

Can I know what the result means?

BR,

Eida

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Nov 16, 2010 at 03:36 PM

    Hi,

    you are checking the view "V$SEGMENT_STATISTICS"

    This view has noting to do with index monitoring. For it you will need to query "V$OBJECT_USAGE".

    So, the first thing to know would be what is what you want to achieve 😀

    Note that index monitoring has a bug (6798910) and the fix is provided with the SBP from October.You should install it if you want to use Index Monitoring.

    Regarding your query, it indicates how many "buffer gets" and "disk reads" were done for each of the objects since the database was started.

    For example, 619 blocks were read from disk that belong to MSEG~S and 66080 blocks were read from the cache for the same index.

    Add comment
    10|10000 characters needed characters exceeded

    • Dang!

      You're right - and it is even documented (e.g. check note #912620 - FAQ: Oracle indexes).

      If I'm not totally mistaken it was in fact me who asked Martin back then to include this...

      Anyway, working as SYSDBA is as common as it is bad practise.

      Nevertheless there exist workarounds for this, e.g. this one http://www.oracloid.com/2006/05/vobject_usage-empty/ .

      I'd really be interested to learn about how much you gained by putting in all this effort into finding unused indexes.

      My gut-feeling for this would be: not too much and maybe you'd be better off by just using 11g features like compression and deferred segment creation.

      regards,

      Lars