You can also query the AWR: SQL> select min(snap_id), max(snap_id) from dba_hist_sqlstat where sql_id='1nh4y7yurm73g'; MIN(SNAP_ID) MAX(SNAP_ID) ------------ ------------ 8301 8414 SQL> col first format a35 SQL> col last format a35 SQL> select min(begin_interval_time) first, max(end_interval_time) last 2 from dba_hist_sqlstat natural join dba_hist_snapshot 3 where sql_id='1nh4y7yurm73g'; FIRST LAST ----------------------------------- ----------------------------------- 04-JUN-07 05.00.26.802 PM 09-JUN-07 11.00.08.921 AM On 6/11/07, Hallas, John (EXP N-ARM) <john.hallas@xxxxxxxx> wrote:
Can you not write the dbms_xplan.display to an interim table (create table xx as select * from ...) and then join that with the last_load_time from v$sql based on the sql_id in both tables John ------------------------------ *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Shivaswamy Raghunath *Sent:* 11 June 2007 14:05 *To:* Oracle-L *Subject:* Historical Plan & Time Hello. I have found that, the following sql is very helpful in finding out the historical (What is in AWR) plan of a given SQL_ID. select * from table (sys.dbms_xplan.display_awr('&Your_SQL_ID')) ; But so far I could not figure out, how I can figure out WHEN this plan was in effect. Is there a way, I can corelate this plan to Sysdate or in other words, find out when this plan was in effect? Thanks for your insight, Shiva
-- Jeremy Schneider Chicago, IL http://www.ardentperf.com/category/technical