Re: v$object_usage: anyone have bad experience with it?
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: zhuchao@xxxxxxxxx, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 05 Feb 2005 20:44:14 +0800
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
>--
>//www.freelists.org/webpage/oracle-l
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
--
//www.freelists.org/webpage/oracle-l
Other related posts:
