Flipping Plans ! Retrieving past execution plans from AWR
source link: https://mikesmithers.wordpress.com/2022/11/21/flipping-plans-retrieving-past-execution-plans-from-awr/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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 / |
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
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK