12C sql baseline - loading sql plan from AWR
When a comparatively slow performance of particular query/process is observed, one of the first things to do is to check for any plan changes. Below are the steps taken when a plan change was identified and rectified by loading sql plan from AWR via SQL Tuning Set.( STS). Check whether a plan change is impacting the performance for a given sql_id in question, 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 = '<<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; Below is a scenario where Oracle is using a bad plan, with higher execution time and cost. Wit...