RE: Index question
- From: Kirtikumar Deshpande <kedeshpande@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 15 May 2006 17:57:08 -0700 (PDT)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
