Repairing SQL Performance Regression with SQL Plan Management

You were hoping for an uneventful day at work when you notice that you have a critical workload query that is taking much longer to execute than usual, and you can see that it is beginning to affect your service levels. You suspect that it has a sub-optimal execution plan and you need to get it sorted out immediately. Is there a quick and easy way to do that?

Automatic SQL plan management can deal with this type of issue without DBA intervention, but what can you do if this feature is not available to you? Fortunately, you can take advantage of enhancements made in SQL plan management (SPM) from Oracle Database 18c onwards (I have a word to say about Oracle Database 12c Release 2 later on). The approach presented here must be initiated by a DBA, but it is nevertheless very easy to use.

The steps are as follows:

1. Capture the 'problem' SQL statement plan in a SQL plan baseline.
2. Run the SPM evolve task for this SQL plan baseline (using a particular set of parameters which I'll show you below).
3. Accept the recommended plan.

In many cases  - that's it! 

SPM evolution will locate and test execute previously-used SQL execution plans and figure out which ones are really the best. It does not rely on optimizer costings alone. It can be configured to search the automatic workload repository (AWR), the Oracle Database cursor cache and SQL tuning sets. In other words, if you have a better plan in a query repository or the cursor cache, SPM can find it and apply it to the problem query.

In broad terms, this is what the procedure looks like:

BEGIN 
   --
   -- Create a SQL plan baseline for the problem query plan
   -- (in this case assuming that it is in the cursor cache)
   -- 
   n := dbms_spm.load_plans_from_cursor_cache(
                  sql_id => '<problem_SQL_ID>', 
                  plan_hash_value=> <problem_plan_hash_value>, 
                  enabled => 'no');
   --
   -- Set up evolve
   --
   tname := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle=>handle); 

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => tname,
      parameter => 'ALTERNATE_PLAN_BASELINE', 
      value     => 'EXISTING');

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => tname,
      parameter => 'ALTERNATE_PLAN_SOURCE', 
      value     => 'CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY+SQL_TUNING_SET');

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => tname,
      parameter => 'ALTERNATE_PLAN_LIMIT', 
      value     => 'UNLIMITED');
   --
   -- Evolve
   --
   ename := DBMS_SPM.EXECUTE_EVOLVE_TASK(tname);
   --
   -- Optionally, choose to implement immediately
   --
   n := DBMS_SPM.IMPLEMENT_EVOLVE_TASK(tname);
END; 
/

There will be cases where a SQL statement was previously performing very well and was never picked up and stored in AWR (and perhaps are no longer in the cursor cache). In this case, SPM might not be able to find the previous good plan. You can mitigate this risk if you periodically capture your workloads in SQL tuning sets or if you enable the automatic SQL tuning set. The 'SQL_TUNING_SET' option shown above will include SQL tuning sets as the source of potential execution plans. This will improve the likelihood that you can repair queries that previously ran very quickly and did not show up in AWR. 

The method described above is available in Oracle Database 18c, but note that SPM internals were enhanced in this area for Oracle Database 19c. It is therefore possible that you will find that the technique will work best in this release. Oracle Database 12c Release 2 includes the parameter settings used above but the task (at the time of writing) will sometimes fail with ORA-01422 due to bug number 29539794.

SQL plan management applies some acceptance criteria and will only accept plans that perform better than the regressed plan by some margin. In the fully-worked example in GitHub you will see that the SPM evolve report is displayed (the primary script is spm.sql). The report clearly indicates whether or not the acceptance criteria is passed and if it is not, then the plan found in AWR will not be accepted.

The use of SQL tuning sets and AWR has licensing implications. Always  check the Database Licence Information User Manual.