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

Hi, Hemant,

     The index monitoring now has been enable for about two days. I plan to file change request and drop those unused indexes one by one. This is going to take a long time, but in case we really see something getting bad, we can easily find out what caused the problem.

     I also plan to keep those unique index, even if they are not used in SQL execution plan. Thanks for your confirm. Your second reply also remind me that FK related indexes are all unique, so I just keep all the unique index and it will solve both the FK issue and unique constraint issue.

THanks very much.      

On Sat, 05 Feb 2005 20:44:14 +0800, Hemant K Chitale <hkchital_at_singnet.com.sg> wrote:
>
> 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
>
>

-- 
Regards
Zhu Chao
www.cnoug.org
--
http://www.freelists.org/webpage/oracle-l