Re: v$object_usage: anyone have bad experience with it?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <zhuchao@xxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 5 Feb 2005 16:24:54 -0000
The index monitoring feature simply flags
an index as used when the optimizer generates
an execution plan that includes that index.
This means that any use of the index that is
not instigated by the optimization process
is simply 'forgotten'.
It also means that when the optimizer produces
a 'bad plan', you may have a misleading report
identifying a 'used, therefore useful' index.
For a good strategy on index monitoring,
you might like to read the following article,
which has some useful comments on the
issue:
http://www.dizwell.com/html/useful_indexes.html
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Jan 21st 2005
----- Original Message -----
From: "zhu chao" <zhuchao@xxxxxxxxx>
To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Sent: Saturday, February 05, 2005 9:52 AM
Subject: v$object_usage: anyone have bad experience with it?
Hi, all,
We have a database which maxed its CPU capacity. and I did some SQL
tuning /index creation. I happened to think some index should be
useless and I enabled index monitoring for some index and they do
showed used='NO' in v$object_usage.
Later I enabled index monitoring in all the indexes for this user, and I
see:
SQL> select used,count(*) from v$object_usage group by used;
USE COUNT(*)
--- ----------
NO 160
YES 108
and these indexes used 36G space:
1 select uniqueness, sum(bytes) from user_segments a,
user_indexes b where segment_name in
2 (select index_name from v$object_usage where used='NO')
3 and a.segment_name=b.index_name
4* group by uniqueness
SQL> /
UNIQUENES SUM(BYTES)
--------- ----------------
NONUNIQUE 36,488,478,720
UNIQUE 272,760,832
I plan to drop those indexes. But I am not sure whether there is
bugs/issues with the v$object_usage that it does not report some used
index, or under some circumstance, even SQL don't use the index , we
have to keep these indexes.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
