RE: When was analyze run last?

Ron,

Others have mentions the LAST_ANALYZED column of DBA_INDEXES or DBA_TABLES. Sorry but no such columns in 7.3.4.

You could look at the LAST_ANALYZED column of DBA_TAB_COLUMNS. It will tell you when the table was last analyzed and you might assume that the index was also analyzed then.

If you analyze tables with a FOR clause, the indexes may not have been analyzed. Also since it is possible to analyze an index without the tables, the index could have been analyzed more recently than the table. Both are unlikely but still a possibility.

Watch out for DBA_TAB_COLUMNS as it's name isn't exactly right. It also includes views. Below is something I use to check when the tables were last analyzed. You could add in DBA_INDEXES to get your answer.

select a.owner, a.table_name, a.last_analyzed from dba_tab_columns a, dba_tables b
where b.owner not like 'SY%'
and a.owner = b.owner
and a.table_name = b.table_name
group by a.owner, a.table_name, a.last_analyzed order by 3 desc;

There is the INDEX_STATS view. Unfortunately, it only holds information on one index at a time.

Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: Smith, Ron L. [SMTP:rlsmith_at_kmg.com]
>
> If there a way in 7.3.4 to tell the last time analyze was run on an index?
>
> Ron Smith
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Whittle Jerome Contr NCI
  INET: Jerome.Whittle_at_scott.af.mil

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).