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

1. 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.
2. 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 ?!]
3. 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

Hemant K Chitale
http://web.singnet.com.sg/~hkchital                

--
http://www.freelists.org/webpage/oracle-l