Grokbase › Groups › Oracle › oracle-l › July 2004
FAQ
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_at_quadtechworld.com QuadTech, Sussex, WI USA
----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

Search Discussions

  • Jesse, Rich at Jul 28, 2004 at 12:22 pm ⇧
    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_at_freelists.org
    [ mailto:oracle-l-bounce_at_freelists.org]
    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_at_quadtechworld.com QuadTech, Sussex, WI USA

    ----------------------------------------------------------------

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Jo_holvoet_at_amis.com at Jul 29, 2004 at 3:15 am ⇧
    Rich,

    metalink Note:160712.1 does much the same thing (it just takes the clause
    "where io.owner# = userenv('SCHEMAID')" out of the definition of
    v$object_usage to create a new view that is no longer restricted to the
    current schema).

    mvg/regards

    Jo

    "Jesse, Rich"
    Sent by: oracle-l-bounce_at_freelists.org
    07/28/2004 19:25
    Please respond to oracle-l



    To: oracle-l_at_freelists.org
    cc:
    Subject: RE: Feedback on query for monitoring index usage in 9i

    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_at_freelists.org
    [ mailto:oracle-l-bounce_at_freelists.org]
    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_at_quadtechworld.com QuadTech, Sussex, WI USA

    ----------------------------------------------------------------

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Jesse, Rich at Jul 29, 2004 at 12:28 pm ⇧
    The part that's missing from that one is the ability to see all indexes =
    in order to determine if an index exists that isn't monitored (without =
    another join).

    Thanks for the feedback!
    Rich

    Rich Jesse System/Database Administrator
    Rich.Jesse_at_quadtechworld.com QuadTech, Sussex, WI USA

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org

    Sent: Thursday, July 29, 2004 3:19 AM
    To: oracle-l_at_freelists.org
    Cc: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
    Subject: RE: Feedback on query for monitoring index usage in 9i

    Rich,

    metalink Note:160712.1 does much the same thing (it just takes the =
    clause=20
    "where io.owner# =3D userenv('SCHEMAID')" out of the definition of=20
    v$object_usage to create a new view that is no longer restricted to the=20
    current schema).

    mvg/regards

    Jo

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 28, '04 at 12:14p
activeJul 29, '04 at 12:28p
posts4
users2
websiteoracle.com

2 users in discussion

Jesse, Rich: 3 posts Jo_holvoet_at_amis.com: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase