Performance Tuning: How to find if a SQL changed it's plan based on AWR

How to Find if a SQL PLAN Changed

The Shared Pool keeps the current copy of the SQL Plan in it's cache. In order to track the SQL PLAN Change, we need to take assistance of the AWR history tables. When you are investigating a performance issue, it is better to have the AWR snapshot every 15 – 30  minutes. To change the AWR snapshot every 15 minutes, issue the following command.

 

·         To find out what is the current retention and snapshot interval

     select * from dba_hist_wr_control;

·         Change the setting using

execute dbms_workload_repository.modify_snapshot_settings ( interval => 10, retention => 14400);

The above command sets the snapshot interval to 10 minutes and retention to 14400 minutes.

To Track the SQL Plan Change.

        SELECT

            to_char(s.begin_interval_time,'mm/dd/yyyy hh24:mi:ss') c1, sql_id,

            s.snap_id,

            sql.PLAN_HASH_VALUE,

            sql.executions_delta   executions,

            decode(sql.executions_delta, 0, '0',

            sql.buffer_gets_delta/sql.executions_delta) BufferGets_Per_exec,

            sql.buffer_gets_Delta,

            decode(sql.executions_delta, 0, '0',

            sql.elapsed_time_delta/sql.executions_delta) Elap_Per_exec,

            sql.elapsed_time_delta

        from dba_hist_sqlstat sql, dba_hist_snapshot s

        where s.snap_id = sql.snap_id

        and s.instance_number = sql.instance_number

        and s.instance_number = 1

        and sql_id in (  '5s770nx4bfdjb')    --  ENTER your SQLID where you suspect the PLAN changed.

        --and executions_delta >0

        order by sql_id, s.begin_interval_time;

The above query will return records and you can observe the time when the plan really changed. You can also notice the buffer gets,elapsed time to see which query plan's hash value is an optimal one. (If you find multiple plan_hash_value for one particular SQL_ID, it means that the PLAN changed)

How to find SQL Execution PLAN using the AWR Repository

select plan_table_output from

table(dbms_xplan.display_awr('5s770nx4bfdjb',null,null,'typical+peeked_binds'));   -- ENTER the SQLID

IF the SQL Plan has changed, you will see multiple PLANS from the above query.

Currently in Oracle 10g, there is no easy way to get a plan from AWR repository and make the database use that PLAN.

How to find SQL Execution PLAN using the current CACHE

select plan_table_output from

table(dbms_xplan.display_cursor('5s770nx4bfdjb',null,'typical+peeked_binds'));