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