RE: Feedback on query for monitoring index usage in 9i

From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 28 Jul 2004 12:25:24 -0500

<sigh>  Try the HTML-ized query:

SELECT u.name "OWNER",
    io.name "INDEX_NAME",
    t.name "TABLE_NAME",
    DECODE(bitand(i.flags, 65536), 0, 'NO', 'YES') "MONITORING",
    DECODE(bitand(NVL(ou.flags,0), 1), 0, 'NO', 'YES') "USED",
    ou.start_monitoring "START_MONITORING",
    ou.end_monitoring "END_MONITORING"
FROM sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u
WHERE t.obj# = i.bo#
    AND io.owner# = u.user#
    AND io.obj# = i.obj#
    AND u.name NOT IN ('SYS','SYSTEM')
    AND i.obj# = ou.obj#(+);

Rich

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[ mailto:oracle-l-bounce@xxxxxxxxxxxxx]
Sent: Wednesday, July 28, 2004 12:18 PM
To: ORACLE-L (E-mail)
Subject: Feedback on query for monitoring index usage in 9i


Hey all,

Now that we've upgraded to 9.2.0.5.0, I'm looking to see which indexes =
are not being used over time, thanks to the new feature.  Trying to =
monitor this is a pain using the V$OBJECT_USAGE view (which itself is an =
oddity -- where's the V_$?) because it'll only show indexes in the =
current schema that are already being monitored.  I'd like to see =
indexes for the whole DB so I can see which ones are not being monitored =
as well.  So I came up with this SQueaL, based on V$OBJECT_USAGE:

SELECT u.name "OWNER",
        io.name "INDEX_NAME",
        t.name "TABLE_NAME",
        DECODE(bitand(i.flags, 65536), 0, 'NO', 'YES') "MONITORING",
        DECODE(bitand(NVL(ou.flags,0), 1), 0, 'NO', 'YES') "USED",
        ou.start_monitoring "START_MONITORING",
        ou.end_monitoring "END_MONITORING"
FROM sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ =
u
WHERE t.obj# =3D i.bo#
        AND io.owner# =3D u.user#
        AND io.obj# =3D i.obj#
        AND u.name NOT IN ('SYS','SYSTEM')
        AND i.obj# =3D ou.obj#(+);

The output looks to be correct on our test DBs, but I'd like to get some =
feedback from y'all as to any possible gotchas.

Thanks!
Rich

Rich Jesse                        System/Database Administrator
rich.jesse@xxxxxxxxxxxxxxxxx      QuadTech, Sussex, WI USA 


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

RE: Feedback on query for monitoring index usage in 9i

Other related posts: