Re: Historical Plan & Time

From: Jeremy Paul Schneider <jeremy.schneider_at_ardentperf.com>
Date: Thu, 14 Jun 2007 12:30:06 -0700
Message-ID: <18be0f260706141230o68437abcnd3b780b9272517a@mail.gmail.com>

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_at_lmco.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 14 2007 - 14:30:06 CDT