Re: Historical Plan & Time

  • From: "Jeremy Paul Schneider" <jeremy.schneider@xxxxxxxxxxxxxx>
  • To: john.hallas@xxxxxxxx
  • Date: Thu, 14 Jun 2007 12:30:06 -0700

You can also query the AWR:

SQL> select min(snap_id), max(snap_id) from dba_hist_sqlstat where
------------ ------------
       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 PM           09-JUN-07 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


*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


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,

Jeremy Schneider
Chicago, IL

Other related posts: