RE: Index question

The view v$object_usage only shows index usage information for the executing 
user schema.


To see this information for all schemas you can create v$all_object_usage view 
using the
following sql: 

conn / as sysdba 

create or replace view V$ALL_OBJECT_USAGE
 (OWNER
 ,INDEX_NAME
 ,TABLE_NAME
 ,MONITORING
 ,USED
 ,START_MONITORING
 ,END_MONITORING
 )
as
select u.name
,      io.name
,      t.name
,      decode(bitand(i.flags, 65536), 0, 'NO', 'YES')
,      decode(bitand(ou.flags, 1), 0, 'NO', 'YES')
,      ou.start_monitoring
,      ou.end_monitoring
from 
    sys.user$ u
,   sys.obj$ io
,   sys.obj$ t
,   sys.ind$ i
,   sys.object_usage ou
where 
      i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#
  and u.user# = io.owner# 
/


HTH,

- Kirti 



--- JayMiller@xxxxxxxxxxxxxxxx wrote:

> In 9i and later you can
> alter index owner.index_name monitoring usage;
> 
> According to both Tom Kyte and Steve Adams there's very little overhead.
> 
> Then query v$object_usage to see whether the indexes are being used.
> 
> You should leave it on at least a month in case of end of month batch
> jobs.
> 
> 
> 
> Thanks,
> Jay Miller
> Sr. Oracle DBA
> x68355
>  
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 
> Sent: Monday, May 15, 2006 5:29 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Index question
> 
> Hi,
> How do we know  non used indexes?
> thanks
> -paul
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
--
http://www.freelists.org/webpage/oracle-l

Other related posts: