| 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
The alter index monitoring|nomonitoring usage syntax still appears in the
10g SQL manual though I did not see a monitoring parameter under alter
table.
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Terry Sutton
Sent: Wednesday, February 16, 2005 1:01 PM
To: oracle-l_at_freelists.org
Subject: Re: Index usage or last used
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
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 > __________________________________________________Do You Yahoo!?
--
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:39:55 CST
 |
 |