Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index Usage

RE: Index Usage

From: Polarski, Bernard <Bernard.Polarski_at_atosorigin.com>
Date: Wed, 11 Oct 2006 09:49:19 +0200
Message-ID: <25D4919915CCF742A88EE3366D6D913D0FB9F99C@mailserver1>


Aren't all view 'dba_hist%' part of the AWR license?  

-----Original Message-----
From: Deepak Sharma [mailto:sharmakdeep_oracle_at_yahoo.com] Sent: Wednesday, 11 October, 2006 5:56 AM To: kennaim_at_gmail.com; oracle-l_at_freelists.org 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 monitoring).

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;

HTH,
Deepak

> 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 http://mail.yahoo.com
--
http://www.freelists.org/webpage/oracle-l





--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 11 2006 - 02:49:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US