RE: Index question

From: Kirtikumar Deshpande <kedeshpande_at_yahoo.com>
Date: Mon, 15 May 2006 17:57:08 -0700 (PDT)
Message-ID: <20060516005708.74467.qmail@web50104.mail.yahoo.com>

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_at_TDWaterhouse.com 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] 
> Sent: Monday, May 15, 2006 5:29 PM
> To: oracle-l_at_freelists.org
> 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

Received on Mon May 15 2006 - 19:57:08 CDT