Re: Historical Plan & Time

From: Shivaswamy Raghunath <shivaswamykr_at_gmail.com>
Date: Thu, 14 Jun 2007 16:04:37 -0400
Message-ID: <1e52ad820706141304l78ee8904hbcc6bc0e40361952@mail.gmail.com>

Jeremy,

Thanks. This is a good one.

In order to find out which plan for the given SQL_ID was in effect at what time, I added plan_hash_value to your query. I think this is exactly what I was looking for:

16:01:55 Shiva>select distinct plan_hash_value, min(begin_interval_time) first,

16:02:02   2  max(end_interval_time) last
16:02:02   3  from dba_hist_sqlstat natural join dba_hist_snapshot
16:02:02   4  where sql_id='druz74ks91v35'
16:02:02   5  group by sql_id, plan_hash_value
16:02:02   6  order by 3;
         Plan
         Hash
        Value FIRST                      LAST
------------- -------------------------- --------------------------
   1366996245 22-MAY-07 02.00.52.480 PM  22-MAY-07 03.00.08.009 PM
   4248165369 25-MAY-07 03.00.46.849 PM  25-MAY-07 04.01.01.358 PM
   3113788595 30-MAY-07 02.00.42.408 PM  30-MAY-07 03.00.57.097 PM
   3244881609 30-MAY-07 04.00.11.473 PM  30-MAY-07 05.00.25.653 PM
   3700884553 31-MAY-07 12.00.05.902 PM  31-MAY-07 01.00.19.467 PM
    795688398 31-MAY-07 09.00.13.286 PM  31-MAY-07 10.00.27.115 PM
   2960769051 31-MAY-07 07.00.45.399 PM  01-JUN-07 12.00.11.623 PM
    853937962 01-JUN-07 01.00.27.646 PM  03-JUN-07 07.00.41.343 PM

   2354157810 22-MAY-07 09.00.36.037 AM 05-JUN-07 07.00.42.765 AM    2424588842 10-JUN-07 05.00.44.530 PM 11-JUN-07 09.00.44.032 AM I have 10 different plan here and I know exactly which is in effect at datte and time! Amazing, is it not?

Thanks, again.

On 6/14/07, Jeremy Paul Schneider <jeremy.schneider_at_ardentperf.com> wrote:
>
> 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 - 15:04:37 CDT