Re: v$object_usage: anyone have bad experience with it?

How long did you keep monitoring running ? On some versions/platforms,
the information is updated at probably 3 hours. You might want to check
v$object_usage
after a couple of days.

DON'T drop those UNIQUE Indexes. They are probably enforcing Uniqueness
on Inserts/Updates but not being used in Selects [bad design or bad
queries ?!]

Not sure what you mean by
"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."
Why is the Presence/Absence of Unique Indexes used for FK lookups "no
longer an issue" ?

Hemant

At 05:52 PM Saturday, zhu chao wrote:

>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.>>One possible is unique index. Unique index is not used to speedup SQL,>but to enforce business logic.>>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.>>Can someone share your experience/opinion on this?>Thanks>>-->Regards>Zhu Chao>www.cnoug.org>-->http://www.freelists.org/webpage/oracle-l