Re: INDEX USAGE

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Wed, 4 Jun 2003 23:02:02 +1000
Message-ID: <WOlDa.49591$1s1.650238@newsfeeds.bigpond.com>

"Paul" <paul821_at_yahoo.com> wrote in message news:DseDa.11886$sG3.3838538_at_news4.srv.hcvlny.cv.net... Can anyone tell me how to find out how frequently an index is used? I have many indexes in my SIEBEL system that I don't believe are being used, but I don't know how to tell for sure.

Hi Paul,

You haven't mention db version but a really really nice feature of 9i Rel 2 are the segment statistics views (eg. v$segment_statistics) which details a whole bunch of useful statistics at the *segment* level:

logical reads
buffer busy waits
db block changes
physical reads
physical writes
physical reads direct
physical writes direct
global cache cr blocks served
global cache current blocks served
ITL waits
row lock waits

This provides far more useful info than whether an index has been used or not but how much actual activity is occurring on the index (or other segment types for that matter).

A level 10 statspack report will also provides a number of "top 5" segment level stats.

Cheers

Richard Received on Wed Jun 04 2003 - 08:02:02 CDT