Re: Index usage or last used
- From: "Terry Sutton" <terrysutton@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Feb 2005 10:01:13 -0800
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@xxxxxxxxxxxxxx>
To: <smishra_97@xxxxxxxxx>; <mgogala@xxxxxxxxxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On
Behalf Of Sanjay Mishra
Sent: Wednesday, February 16, 2005 13:54
To: mgogala@xxxxxxxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxx>
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
> >--
> >//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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
