Historical regression of Oracle execution plan

Case----If I want to query a point in time: 2018-01-12 between 9:00--9:12, a RAC node, a SQL execution plan, how to handle it?

There are only four Display_awr parameters, namely: sql_id, Plan_hash_value, db_id, format, and no time and node inst_id options.

Display_cusor is likely to encounter a situation where the SQL execution plan was brushed out of memory.

Method:

1 first through Dbms_xplan. Display_awr view execution plan for SQL statements throughout the AWR

---Note that the premise here is that the relevant SQL information should be in the AWR

2 query Awr There are several execution plans in particular

SELECT * FROM table (Dbms_xplan.display_awr (db_id=> ',sql_id=> "))
---where plan_table_output like (' Plan hash value% ');

This step has been able to identify the problematic implementation plan through the implementation plan.

3 Based on Time view snap_id

Select Dbid,snap_id,instance_number,begin_interval_time,end_interval_time
From Dba_hist_snapshot where Begin_interval_time >=to_date (' 2018-01-12 09:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') Order BY Begin_interval_time

---The snap_id of the corresponding time

4 According to the results of 2, 3 to query the corresponding time-point SQL execution Plan:

Select A.* from (select distinct dbid,sql_id, plan_hash_value from Dba_hist_sqlstat
where sql_id = "and snap_id = xxx and Instance_number = 3) b,table (Dbms_xplan.display_awr (db_id=>",sql_id=> b.sq L_id,plan_hash_value=> b.plan_hash_value)) A;
----Of course, in the same snap_id may also query out multiple execution plans, if this is the case, select the problematic row

---View the operation of the same SQL in different snaps:

Select ss.snap_id, Ss.instance_number node, begin_interval_time, sql_id, Plan_hash_value, NVL (executions_delta,0) Execs, (Elapsed_time_delta/decode (NVL (executions_delta,0), 0,1,executions_delta))/1000000 Avg_etime (buffer_gets_ Delta/decode (NVL (buffer_gets_delta,0), 0,1,executions_delta)) Avg_lio
From Dba_hist_sqlstat S, Dba_hist_snapshot SS
where sql_id = ' '
and ss.snap_id = s.snap_id
and Ss.instance_number = S.instance_number
and Executions_delta > 0
Order by 1, 2, 3

Historical regression of Oracle execution plan