Flipping Plans ! Retrieving past execution plans from AWR

SQL is a special programming language.
It’s quite possible that I’ll be interrupted at this point by some Tech Bro arguing that SQL is not a programming language actually. The phrase “Turing Complete” may already be heading my way.
To save time, I’ll simply observe that I get paid folding money to write SQL so I don’t really care whether it lacks some the properties of Powerpoint (e.g. being Turing Complete).

As I was saying SQL is almost unique among modern programming languages in that it is a 4GL.
In other words it is declarative – you use it to describe the information you want to retrieve. It is then up to the database engine to figure out how best to fulfill your requirements.

Oracle likes to remind me of this every so often when, after months of efficient and trouble-free running, it suddenly gets bored and chooses a new and exciting execution plan which causes a query to take hours rather than minutes.

What follows is a short exploration of how we can tell this is happening and how to retrieve current and previous execution plans for a SQL statement from Oracle’s Active Workload Repository (AWR).

Before we go on, however…

WARNING – Licensing

Before you begin exploring AWR, please make sure that you have a license for the Oracle Diagnostics Pack ( or the Diagnostics and Tuning Pack in earlier Oracle versions).

The documentation for every Oracle version includes details of the licensing approach – here’s the one for 19c, which is the version I’ll be using for this post.

Environment and Setup

In an appropriately licensed production environment, it’s usual for AWR to have been configured appropriately. However, I’m using an Oracle Developer Day Virtual Box Image running 19c Enterprise Edition, so I’ll need to do some tweaking.
First of all (connected as SYS as SYSDBA on the PDB), I need to check the AWR parameters :

show parameter awr

NAME                        TYPE    VALUE 
--------------------------- ------- ----- 
awr_pdb_autoflush_enabled   boolean FALSE 
awr_pdb_max_parallel_slaves integer 10    
awr_snapshot_time_offset    integer 0     

I can see that I need to enable the autoflush :

alter system set awr_pdb_autoflush_enabled = true;

System SET altered

At present, the interval between AWR snapshots is a bit lengthy :

select * from cdb_hist_wr_control;

We’re going to shorten this interval to 10 minutes. Also, were going to record details of as many statements as possible in the snapshot :

begin
    dbms_workload_repository.modify_snapshot_settings(
        interval => 10,
        topnsql => 'MAXIMUM');
end;
/

Even with AWR configured like this, it’s not guaranteed to capture every single statement run in a snapshot so I need to create a sizeable table to make sure my queries make the cut.

DBA_OBJECTS contains around 79K rows so I’ll use that as a basis for my table :

create table chunky as select * from dba_objects;

begin
    for i in 1..100 loop
        insert into chunky
        select * from dba_objects;
        -- commit after each iteration as we're a bit tight 
        -- on resources
        commit;
    end loop;
end;
/

create index chunky_owner on chunky(owner);

exec dbms_stats.gather_table_stats(user, 'CHUNKY'); 

CHUNKY contains 7.9 million rows.

Now that’s done, we should be able to start the “slideshow” …

Simulating a Plan Flip

I’m going to start with a new snapshot…

exec dbms_workload_repository.create_snapshot;

Now to run a query against the table. In order to make it easier to find, I’ll pretend that I’m working in a Turing Complete medium…

select /* slide 1 */ *
from chunky
where owner = 'HR';

Now that I’ve executed the query, I should be able to find it’s SQL_ID :

select sql_id, sql_text
from v$sql 
where sql_text like 'select%/*%slide 1%*/%'
and sql_text not like '%v$sql%'
/

SQL_ID        SQL_TEXT                                                                                  
------------- ------------------------------------------------------
9nwrantgwhcta select /* slide 1 */ * from chunky where owner = 'HR'                                     

Before we run the query again, let’s ensure that the optimizer uses a different plan for the next execution by removing the index :

alter index chunky_owner invisible;

To simulate a separate batch run, the next run will be in a new snapshot :

exec dbms_workload_repository.create_snapshot;

select /* slide 1 */ *
from chunky
where owner = 'HR';

exec dbms_workload_repository.create_snapshot;

The two runs should now be in separate AWR snapshots.

Identifying an Execution Plan Change

It’s the morning after the overnight batch run and I’m trying to figure out why my job has taken much longer than normal.

Thanks to this extremely useful query from Kerry Osborne, I can see that the execution plan has changed from when the query was first captured in Snapshot 29 to the latest execution in Snapshot 32 :

select
    snap.snap_id,
    snap.instance_number,
    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 stat, dba_hist_snapshot snap
where sql_id = '9nwrantgwhcta'
and snap.snap_id = stat.snap_id
and snap.instance_number = stat.instance_number
and executions_delta > 0
order by 3
/

Flippin’ Optimizer !

Finding the Actual Plans in the AWR

The component lines of these execution plans can be found in DBA_HIST_SQL_PLAN :

select *
from dba_hist_sql_plan
where sql_id = '9nwrantgwhcta' 
/

Alternatively, if you’d like to the plans nicely formatted, you can use DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY.
A couple of points to note here :

• the DISPLAY_AWR function is depracated from 12c onwards in favour of this procedure
• if, as in this case, we’re viewing the contents of a Pluggable Database Repository then we need to specify this in the AWR_LOCATION parameter.

Other than that, we simply need to provide the SQL_ID and PLAN_HASH_VALUE.

For the current plan (in SNAP_ID 32 above) :

select *
from table( 
    dbms_xplan.display_workload_repository( 
        sql_id => '9nwrantgwhcta', 
        plan_hash_value => 1105471336,
        awr_location => 'AWR_PDB'));

We can see the old plan ( from SNAP_ID 29) by running :

select *
from table( 
    dbms_xplan.display_workload_repository( 
        sql_id => '9nwrantgwhcta', 
        plan_hash_value => 317341109,
        awr_location => 'AWR_PDB'));

Links

As well as the aforementioned article by Kerry Osborne, I also found the following quite useful :

Connor McDonald’s Superhero alter ego provides an overview of how AWR works here

This article was helpful in configuring AWR

And I’m indebted to Uwe Hesse for mentioning the MAXIMUM parameter for TOPNSQL, which had me puzzled for a time.