| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index usage or last used
I know this is not really the answer you were waiting for :-)
but the main purpose of putting indexes (or tables) in monitoring mode in 9i is
*not* to provide you with information, but rather to enable Oracle to perform
smarter statistics collection. See the documentation of the DBMS_STATS package.
By the way, in 10g segment monitoring is the instance-wide default; you cannot enable/disable it anymore for individual database objects.
hope this still helps a little bit,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On
Behalf Of Sanjay Mishra
Sent: Wednesday, February 16, 2005 13:54
To: mgogala_at_allegientsystems.com
Cc: oracle-l_at_freelists.org
Subject: Re: Index usage or last used
I was reading and found that v$segment_Statistics might contain an entry for index if the index is subject to any DML. So is it correct that even we got some update to the table, then it means index might also been updated and so the v$Segment_Statistics got an entry for the Index. If it is the case then it will be difficult to judge as what indexes are really been queried by select statement. Any expert comment. It look like that even if we put the Index in monitoring mode, still this V$ view is not accurate to make decisio as if the index is really been used by select queries
I am little confused here as I thought that I can assume that if this v$view has an entry for index, then it mean, the index is really been used sometime back
TIA
sanjay
--- Mladen Gogala <mgogala_at_allegientsystems.com>
wrote:
> Sanjay Mishra wrote:
>
> >Can we check as when Index was last used from the dictioanry tables.
> >Index are not in Monitoring mode and Statisitic Level is TYPICAL in
> >the Oracle 9i database
> >
> >TIA
> >Sanjay
> >
> >
> >
> >__________________________________
> >Do you Yahoo!?
> >Yahoo! Mail - Helps protect you from nasty viruses.
>
> >http://promotions.yahoo.com/new_mail
> >--
> >http://www.freelists.org/webpage/oracle-l
> >
> >
> V$SEGMENT_STATISTICS is populated nevertheless and it contains the
> values since the database startup.
> Also, V$SEGMENT_STATISTICS is very useful to determine candidates for
> a reorg. Here is the query that I use for that purpose:
>
> select owner,object_name,object_type,value
> from v$segment_statistics
> where statistic_name ='ITL waits' and value > 100 /
>
> --
> Mladen Gogala
> Oracle DBA
> Ext. 121
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 16 2005 - 08:07:42 CST
 |
 |