RE: v$object_usage: anyone have bad experience with it?
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <zhuchao@xxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 5 Feb 2005 13:16:59 +0100
see comments in line ...
Lex.
----------------------------------------------------------------
Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html
----------------------------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of zhu chao
Sent: Saturday, February 05, 2005 10:52
To: ORACLE-L
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.
LEX: Why do you care about storage, if you say you have a CPU problem?
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. One possible is unique index. Unique index is not used to speedup
SQL, but to enforce business logic.
LEX: Indeed -- and if they are associated with UNIQUE or PK constraints,
you'll find out, because you won't be able to drop them if the constraints
are enabled...
The other is for the FK related index. But we are running oracle
9.2.0.5 and I think it is no longer an issue.
LEX: I think this is a misunderstanding -- there still is different locking
behavior with and without indexes on your foreign keys. Just less difference
than before. This is explained very well in the Concepts manual.
Can someone share your experience/opinion on this?
LEX: I still don't understand how you hope to resolve the perceived CPU
problem by dropping indexes?
Thanks
--
Regards
Zhu Chao
www.cnoug.org
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
