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

Hi,

You might also want to check from v$segment_statistics whether anyone is actually using your indes (logical reads, segment scans) if your statistics level allows segment-level statistics gathering.

Also you could sample from V$SQL_PLAN using object_owner and object_name columns to see whether any cursor in library cache actually uses the index in its execution. When joining v$sql_plan back to v$sql, you could sample how often this query is executed and how much resources does it take. Btw, you could even try the same query with NO_INDEX hint in 10g to see how much resources would the query take when particular index is not available.

The v$sql_plan method doesn't show use index usage for foreign key enforcement though.

Tanel.

> 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.

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