Re: Index usage or last used

From: Terry Sutton <terrysutton_at_usa.net>
Date: Wed, 16 Feb 2005 10:01:13 -0800
Message-ID: <016201c51451$e3f04cd0$6401a8c0@TerrySutton>

Aren't you confusing ALTER TABLE <table_name> MONITORING and ALTER INDEX <index_name> MONITORING USAGE? The former is related to dbms_stats, the latter not.

--Terry

Original Message ----- From: "Lex de Haan" <lex.de.haan_at_naturaljoin.nl> To: <smishra_97_at_yahoo.com>; <mgogala_at_allegientsystems.com> Cc: <oracle-l_at_freelists.org> Sent: Wednesday, February 16, 2005 5:04 AM Subject: 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.

Visit my website at http://www.naturaljoin.nl

-----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
>

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 16 2005 - 13:06:51 CST