From: "Richard Foote" Newsgroups: comp.databases.oracle.server References: <2958603.1054745576@dbforums.com> Subject: Re: INDEX USAGE Lines: 38 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Message-ID: Date: Fri, 6 Jun 2003 23:47:42 +1000 NNTP-Posting-Host: 203.54.191.205 X-Trace: newsfeeds.bigpond.com 1054906136 203.54.191.205 (Fri, 06 Jun 2003 23:28:56 EST) NNTP-Posting-Date: Fri, 06 Jun 2003 23:28:56 EST Organization: Telstra BigPond Internet Services (http://www.bigpond.com) Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!small1.nntp.aus1.giganews.com!border1.nntp.aus1.giganews.com!nntp.giganews.com!news1.optus.net.au!optus!snewsf0.syd.ops.aspac.uu.net!news.labyrinth.net.au!news.melbpc.org.au!news.melbpc.org.au!news.mel.connect.com.au!news.syd.connect.com.au!news.bri.connect.com.au!news.brisbane.pipenetworks.com!duster.adelaide.on.net!vicpull2.telstra.net!lon-transit.news.telstra.net!news.telstra.net!newsfeeds.bigpond.com!not-for-mail Xref: core-easynews comp.databases.oracle.server:188869 X-Received-Date: Fri, 06 Jun 2003 06:40:52 MST (news.easynews.com) "W.Breitling" wrote in message news:2958603.1054745576@dbforums.com... > > I don't have first hand experience with 9i in a production setting yet, > but the problem I see with using v$segment_statistics as well as using > v$bh (as touted in "Is the index still being used ?") in determining > used/unused indexes is that an index presence in those views does not > necessarily indicate that the index in question is actively being used > in sql plans. They could just be there because they need to be > maintained as a result of DML on the row(s) they index. So it could > still be just dead weight. > Hi You raise a good point and it's of course kinda true. However if you study the stats with your comments in mind and look at those indexes that have a very high ratio of logical reads to db block changes and physical writes, then it's likely to be used for more than maintenance reasons (although of course some logical reads could still result from DML operations). Those indexes with low ratios of logical reads to db block changes/ physical writes are less likely to be effectively used. Depending on the nature of changes and how checkpointing is implemented, there could be a number of changes per physical write so caution needs to be exercised when looking at the statistics. However I've found that those indexes that are frequently/infrequently used for non-maintenance reasons are highlighted by these statistics. Cheers Richard