RE: Index Usage

  • From: "Ken Naim" <kennaim@xxxxxxxxx>
  • To: "'Deepak Sharma'" <sharmakdeep_oracle@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 11 Oct 2006 00:24:25 -0500

I thought index monitoring provided more info than it does, but it should


-----Original Message-----
From: Deepak Sharma [mailto:sharmakdeep_oracle@xxxxxxxxx] 
Sent: Tuesday, October 10, 2006 10:56 PM
To: kennaim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Index Usage

I am not sure about the "time" when the index was last
accessed, but SYS.OBJECT_USAGE should tell you if an
index was accessed or not (need to turn on index

You can use this following query to access the ASH
tables for index usage for last 7 days (I'm still
testing this) :

select p.object_owner "Index Owner",
           p.object_name "Index Name",
           sum(t.disk_reads_total) "Disk Reads",
           sum(t.rows_processed_total) "Rows Processed"
from dba_hist_sql_plan p,
         dba_hist_sqlstat t,
         dba_hist_snapshot s
where p.sql_id = t.sql_id
  and t.snap_id = s.snap_id
  and p.object_type like '%INDEX%'
  and p.object_owner = &Your_owner
  and p.object_name = &Your_index
  and s.begin_interval_time > sysdate - 7
group by p.object_owner, p.object_name
order by 3 desc;


--- Ken Naim <kennaim@xxxxxxxxx> wrote:

> I remember reading somewhere that in 10g you can see
> the last time an index
> was accessed, however after googling for it I am
> unable to find the name of
> the view that contains it. Anyone know where I
> should look or know it off
> the top of their head?

Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 


Other related posts: