From: "Pete Sharman" Newsgroups: comp.databases.oracle.server References: <9kricm$t383@imsp212.netvigator.com> <9krlip$67ebi$1@ID-85580.news.dfncis.de> Subject: Re: Auditing on using indexes Lines: 41 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 Message-ID: Date: Wed, 8 Aug 2001 15:13:19 -0700 NNTP-Posting-Host: 130.35.21.208 X-Trace: inet16.us.oracle.com 997309336 130.35.21.208 (Wed, 08 Aug 2001 15:22:16 PDT) NNTP-Posting-Date: Wed, 08 Aug 2001 15:22:16 PDT And there's the ALTER INDEX index_name MONITORING USAGE command available to you in 9i, if you're on that release. -- HTH. Additions and corrections welcome. Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA "Chuck Hamilton" wrote in message news:9krlip$67ebi$1@ID-85580.news.dfncis.de... > "Ken Shan" wrote in message > news:9kricm$t383@imsp212.netvigator.com... > : Hi there, > : > : There are many indexes created on our database, but how can I know which > : indexes are used by the SQLs or which indexes are no longer used ? Is there > : any ulitilities or tools ? thanks > : > : Ken > : > : > > The tuning pack includes a tool called "tuning expert" or something like > that which will tell you which indexes are being used. If you don't want to > buy that, you'll need to monitor the SQL area of the shared pool and capture > all sql that's using the table (don't forget views too), and run an explain > plan on each to see if the index is being used. > >