Oracle Database 19c adds a new feature called automatic SQL plan management. This post covers:
This is an autonomous feature so it is only available on some platforms. See Automatic SQL Plan Management in table 1-6 of the license guide for full details.
If you are not using a supported platform, there are still some great SPM enhancements in Oracle Database 19c for you to make use of - see this post.
Automatic SQL plan management identifies SQL statements that are consuming significant system resources (by inspecting the AWR and SQL tuning sets). Historic performance information is used to establish whether there has been a likely performance regression. Perhaps application query Q1 has been completing in 1 minute for the past two years but today it takes 30 minutes. Alternate SQL execution plans are located automatically and test executed (using SPM evolution). The best plans are then enforced using SQL plan baselines without DBA intervention.
In short: SQL statement performance regressions are repaired automatically.
The automatic mode is enabled by setting the DBMS_SPM parameter ALTERNATE_PLAN_BASELINE to AUTO. This setting not available on all platforms.
Here is a summary of the flow:
I am aware that some DBAs use SQL plan management in a tactical fashion; controlling regression in a carefully chosen set of SQL statements. Some DBAs will capture SQL statements according to a predetermined schedule and perhaps run SPM evolution manually as-and-when required. Some DBAs disable SPM evolution altogether, choosing exactly when and how to evolve a particular SQL statement.
If you currently have a particular way of using SPM, then when you upgrade to Oracle Database 19c you will need to decide whether to choose:
BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' , parameter => 'ALTERNATE_PLAN_BASELINE', value => 'AUTO'); END; /
BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'ALTERNATE_PLAN_SOURCE', value => 'AUTO'); END; /
Use AUTO and continue to capture and evolve as you see fit. The automatic approach can work along-side existing strategies.
The Oracle Database 19c defaults are now the same as Oracle Database 12c Release 2 and Oracle Database 18c:
BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' , parameter => 'ALTERNATE_PLAN_BASELINE', value => 'EXISTING'); /* The Default */ END; / BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'ALTERNATE_PLAN_SOURCE', value => 'CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY'); /* The Default */ END; /
If you want to use the same settings in Oracle Database 19c as the Oracle Database 12c Release 1 defaults:
BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' , parameter => 'ALTERNATE_PLAN_BASELINE', value => ''); END; / BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'ALTERNATE_PLAN_SOURCE', value => ''); END; /
To view current parameter settings:
SELECT PARAMETER_NAME, PARAMETER_VALUE FROM DBA_ADVISOR_PARAMETERS WHERE TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK';If you want to accept evolved execution plans manually, I recommend that you continue to allow the automatic SPM evolve advisor task to execute (so that you can view SPM reports and findings). Then, to prevent alternative plans from being accepted automatically, use the following setting:
BEGIN DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' , parameter => 'ACCEPT_PLANS', value => FALSE); END; /
Note that the evolve advisor task, SYS_AUTO_SPM_EVOLVE_TASK, was introduced in Oracle Database 12c.
Automatic SQL plan management is a great new way to prevent query performance regressions and capture SQL plan baselines transparently, without any management overhead.
The parameters used above were first introduced in Oracle Database 12c Release 2. The internal implementation for identifying alternative plans continued to be enhanced in Oracle Database 18c and Oracle Database 19c.
See the SQL Tuning Guide for more details.
Regards,
Nigel
although automatic SQL Plan Baselines are the default in 19c, the parameter 'optimizer_capture_sql_plan_baselines' in 19c is set to false. Is this parameter obsolete now? I ask, because it is not listed in v$obsolete_parameter
thanks,
Nik
Regards,
Nigel
We are currently in the process of upgrading to 19c. Enterprise edition. 2 months into it.
Unless I am missing something in the article, how do we measure or identify the benefits in real implementations?
Reason is because if there is a compelling benefit then we incorporate it into standard DB deployment sheet/Standard parameter settings.
Again, nice 'look at this 19c feature' article. Appreciate it.
Prakash
Aha! Yes - this is a really good point and it is something I am working on. As you know, the evolve report includes information on how the performance of one plan compares with the performance of another. This gives us some indication of the benefit, but it doesn't account for how a query is used in the application workload (e.g. how often it is executed). I want to get that information from the live system AFTER a SQL plan baseline has been implemented.
The general idea is to retrieve query stats from AWR and SQL tuning sets and compare performance before and after SQL plan baseline creation. This information probably won't be available for all queries (I suppose a bad query showing up in AWR could fall below the radar once fixed), but I think there should be enough information to allow us to get a good idea of how effective the feature is overall.
When I've done this, I will update this post. Thanks for the question.
Regards,
Nigel
Would you mind to tell me what type of SQLs are recommended to use? (OLTP sql, reporting SQL, high cost SQL, etc. )
WS
Regards,
Nigel