Re: Historical Plan & Time

Thanks, Allen, Niall.

At least in this land of pleny, I have happened not to see that as a
problem, mostly. With multi year  multi billion orders flowing in company's
way, the mood is  to add CPUs and meomry rather than imporve code:(

I happened to agree with the car tag I saw this morning 'Life is Good'.  :)

We have got that covered, Niall. Thanks.

On 6/15/07, Niall Litchfield <niall.litchfield@xxxxxxxxx> wrote:


It's good stuff indeed, though you need to have paid the extra money to
Oracle in order to run this query, except in se/se1 where it impossible to
legaly run this query.  :(



On 6/14/07, Shivaswamy Raghunath <shivaswamykr@xxxxxxxxx> wrote:
>
> 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@xxxxxxxxxxxxxx>
> 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@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
>
>
>


--
Niall Litchfield
Oracle DBA
http://www.orawin.info

Other related posts: