Collecting SQL Plan Baselines

Ever thought about collecting baselines for all queries on your database? Let's explore the benefits and the methods of doing so.

Before we begin -

What is an SQL Plan Baseline?

A baseline for a specific query (sql_text) stores one or more execution plans for this query. You can load execution plans into a baseline from the shared SQL area, AWR and other sources (see the documentation for package dbms_spm for more details). And you can persist those baselines in SMB (short for "SQL Plan Management Base"), that is, in your database dictionary or unpack ("export") them into a regular table. This table can be exported/moved using Data Pump etc later on.

The baselines in SMB can be enabled and/or fixed. Optimizer will first consider only enabled fixed plans. If no enabled fixed plans exists, then all enabled (but not fixed) plans are considered.

Regardless of how many plans for a statement are enabled, Oracle can still try to find a better plan than what is stored in the baseline. If it finds such a plan, then you will have the option of testing and accepting the new plan. One baseline can have many accepted plans - in such case, the optimizer will choose between all accepted plans.

Is this feature available on Standard Edition?

Since 18c, partially, yes. The main limitation is that SE only allows to store a single SQL plan baseline per SQL statement. This was announced on blogs.oracle.com.

Single plan baseline also means no opportunity for plan evolvement and other fancy features which would require more than a single plan per statement. So, this blog post will only mention what is relevant for SE from here on.

1. Use Case: Upgrades

Have you ever successfully upgraded the database without even a warning, just to be awoken the next morning by the customer stating that their database is running very slow or even seems like it is "hang"?

There are many possible reasons for such experiences and the main one probably being insufficient or no testing at all. Nonetheless, at times, the reason happens to be that the execution plan changed for worse due to newer version of CBO (Cost Based Optimizer).

Sure, you may succeed within a minute by using something like /*+ OPTIMIZER_FEATURES_ENABLE=19.1.0 */ and resume your morning coffee. If not, you likely need to figure out what the execution plan was on the previous version.

There are many options on how to obtain the old plan, even without baselines. Those options include ASH, AWR, our APPM and other third party tools. You may also restore previous version of database and run query there, hoping to produce a better plan.

Anyway, none of this options is as fast as simply doing this:

declare 
   l_count number := 0;
begin
   l_count := dbms_spm.alter_sql_plan_baseline(
      sql_handle => 'SQL_b85e793d85b59754',
      plan_name => 'SQL_PLAN_bhrmt7q2vb5undd079936',
      attribute_name => 'enabled',
      attribute_value => 'YES');
   dbms_output.put_line('enabled [' || l_count || '] baseline plans');
end;
/

So, see, with baselines, your coffee won't even get cold by the time you're done fixing the execution plan.

Regarding sql_handle and plan_name parameters... You can find those values in dba_sql_plan_baselines view by searching for your statement's sql_text:

select sql_handle, plan_name
   from dba_sql_plan_baselines
   where sql_text like 'select ... %';

sql_handle identifies your SQL. Maybe we can also call this a baseline. Each can have one or more execution plans, identified by plan_name. And each execution plan can be enabled (though in SE, only one plan_name per sql_hande is allowed). You can see the exact plan like this:

select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(
   sql_handle => 'SQL_b85e793d85b59754',
   plan_name => 'SQL_PLAN_bhrmt7q2vb5undd079936'));

2. Use Case: OLTP Applications

Let's imagine an OLTP application, which is deployed to many customers. So, schema and queries are the same at every customer, but each customer has their own data.

Suppose the vendor provides collected baselines. Here are the use cases:

When application is first deployed, customer doesn't yet have any data or they have incomplete data. And so, they don't have the correct statistics. Initial execution plans may be off because of that.

So, on EE we can have baselines which provide a starting point. If database comes up with a better plan than what is in a baseline, we can evaluate/accept that.

On SE, we can have a regular table, containing all baselines. If users experience unexpected delays because of suboptimal execution plan for specific query, we can simply load (and mark as fixed) the vendor supplied, field tested, execution plan.

A quick note on why the title says "OLTP" applications; those are online transaction processing apps, which usually have live users who expect nearly real time response. They get frustrated if they click something and it doesn't respond within the time they deem necessary. More importantly, such workloads tend to use somewhat simpler queries, which generally should execute always the same way. It's not about providing the absolutely best plan possible, it's simply about providing good enough plans that users can rely on.

In contrast to OLTP, say, data warehouses, where it's generally expected to run background jobs which move or transform vast amounts of data and run highly complex queries which produce complex execution plans. Baselines can still be beneficial in such scenarios - just be aware that setting a fixed baseline in SE for such a complex query might do no good when, for example, one of the many referenced tables drastically changes data.

If you are a software vendor, I'd suggest that you think about deploying baselines as part of your software installation.

Collecting Baselines

One, quite simple, way of collecting the baselines is spfile parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true. You can fine tune which plans to capture by using DBMS_SPM.CONFIGURE (e.g. to capture only queries with specific parsing schema). This will automatically add baselines for repeatable queries and will only happen at query parse time.

Another way of manually collecting baselines (which works on SE) is by using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE, like this:

set serveroutput on;
declare
   l_plan_count number := 0;
begin
   for l_plan in (select distinct sql_id, plan_hash_value 
                     from v$sql 
                     where parsing_schema_name='MY_APP')
   loop
         l_plan_count := l_plan_count + dbms_spm.load_plans_from_cursor_cache (
            sql_id => l_plan.sql_id,
            plan_hash_value => l_plan.plan_hash_value,
            fixed => 'NO',
            enabled => 'NO');                     
   end loop;
   dbms_output.put_line('Loaded [' || l_plan_count || '] plans.');
end;
/

Staging Baselines (= Exporting)

Once you load baselines into SMB, they reside in the data dictionary and, if enabled, will be considered by the optimizer. You can export them from dictionary to a regular table by:

declare
   l_plan_count number := 0;
begin
   dbms_spm.create_stgtab_baseline (
      table_owner => 'DEMO_OWNER',
      table_name => 'DEMO_STGTAB');

   l_plan_count := l_plan_count + dbms_spm.pack_stgtab_baseline (
      table_owner => 'DEMO_OWNER',
      table_name => 'DEMO_STGTAB');
end;

You can also verify if baselines were exported by querying the created table:

select count(*) from demo_owner.demo_stgtab;

If you prefer, you can remove loaded plans from dictionary. You can remove only those that were not enabled and/or only those belonging to specific parsing schema, etc:

declare
   l_plan_count number := 0;
begin
   for l_baseline in (select sql_handle
                        from dba_sql_plan_baselines
                        where enabled='NO'
                           and parsing_schema_name='MY_APP')
   loop
      l_plan_count := l_plan_count + 
         dbms_spm.drop_sql_plan_baseline(sql_handle => l_baseline.sql_handle);
   end loop;
   dbms_output.put_line('Dropped [' || l_plan_count || '] baselines');
end;
/

Now, you can even move those baselines to another database by using Data Pump to export DEMO_STGTAB into, say, baselines.dmp. Then import this in another database and from there into SMB of another database.

Specifically for Standard Edition, I recommend keeping only the baselines that you actually need in SMB and removing all others, before staging and exporting all of them. Firstly, this will allow you to collect new plans and secondly, there is no restriction in SE on how many plans can be exported in a table - here's my example:

select sql_handle, obj_name, max(optimizer_cost) as cost
   from spm_test.spm_export_tab
   group by sql_handle, obj_name
   order by max(optimizer_cost) desc

SQL_HANDLE                     OBJ_NAME                             COST
------------------------------ ------------------------------ ----------
SQL_9832fdfb8497d4c2           SQL_PLAN_9hcrxzf29gp6202feb565      19089
SQL_9832fdfb8497d4c2           SQL_PLAN_9hcrxzf29gp62cea8bf8c        759

References

You can find out more about Managing SQL Plan Baselines in:

• official documentation

• dbms_spm pl/sql package for SPM management

• dba_sql_plan_baselines view which displays all available baselines in SMB