8i Monitoring Index-Usage ?

[Orca777]

Hello;
i know this feature is supported in 9i, is there a possiblilty to do this in any way in a 8i Database ?

Orca

[julian]

It is a 9i feature.

In 8i you have to query v$sqlarea and run explain plan to figure out which indexes are used and which ones are not used.

[JChiappa]

OR the poster can use an AFTER LOGON trigger to set
CREATE_STORED_OUTLINES to true. If the users have the CREATE ANY OUTLINE system privilege, you'll be able to see which indexes are being used in the DBA_OUTLINE_HINTS view.
Or even better : if all he wants is to know IF the index are being used, maybe he could put the index in a separated tablespace, and monitor it with the V$FILESTAT statistics.

[]s

Chiappa

[JChiappa]

Addendum : OF COURSE, V$FILESTATs show only PHYSICAL reads, so the new tablespace MUST go offline, and then online, to flush off the blocks from cache, and this way the next read WILL be marked on v$filestat

[]s

Chiappa