From oracle-l-bounce@freelists.org Wed Jul 28 12:22:07 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i6SHLqV10510 for ; Wed, 28 Jul 2004 12:22:02 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i6SHLg610469 for ; Wed, 28 Jul 2004 12:21:52 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2FBAB72D11D; Wed, 28 Jul 2004 11:59:56 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 14021-99; Wed, 28 Jul 2004 11:59:56 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9E92472D146; Wed, 28 Jul 2004 11:59:52 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 28 Jul 2004 11:58:20 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2A97F72C58F for ; Wed, 28 Jul 2004 11:58:20 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 14036-49 for ; Wed, 28 Jul 2004 11:58:19 -0500 (EST) Received: from waexfilter2.qg.com (WAEXFILTER2.qg.com [68.21.206.4]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B90AC72C269 for ; Wed, 28 Jul 2004 11:58:19 -0500 (EST) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Subject: RE: Feedback on query for monitoring index usage in 9i Date: Wed, 28 Jul 2004 12:25:24 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Feedback on query for monitoring index usage in 9i Thread-Index: AcR0xtZ+FjulUSemS+mG7gnmkLDEQgAAMJmw From: "Jesse, Rich" To: X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 6320 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Rich.Jesse@quadtechworld.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@freelists.org [ mailto:oracle-l-bounce@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@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@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 -----------------------------------------------------------------