Introduction
In 11g, Oracle introduced a new feature called SQL baselines whereby Oracle could capture all of the SQL plans used over time. The idea was that an individual SQL statement may have a number of plans over its lifetime. Some more efficient than others. Therefore, if Oracle found a baseline plan was more efficient than the current plan for a given SQL statement, it could use this baseline instead.
The capture and use of the baselines was controlled by two initialization parameters.
- optimizer_capture_sql_plan_baselines
- optimizer_use_sql_plan_baselines
As with many new features in Oracle, by default, it was turned off (optimizer_capture_sql_plan_baselines=false) and therefore Oracle did not capture the baseline plans. However, as soon as the capture of plans was set, then the use of those plans was automatic (optimizer_use_sql_plan_baselines=true) so long as they had been accepted..
The accepted or evolution of the baselines in 11g was a manual task, under 12c, the evolving of these plans in now automatic using a new daily maintenance job SYS_AUTO_SPM_EVOLVE_TASK.
Step-By-Step Guide
1. Display the SPM Evolve Task:
- sqlplus / as sysdba
- SQL> COLUMN client_name FORMAT A35
- SQL> COLUMN task_name FORMAT a30
- SQL> SELECT client_name, task_name
- SQL> FROM dba_autotask_task
- SQL> where task_name='AUTO_SQL_TUNING_PROG'
2. Display the parameters set for the SPM Evolve Task:
- sqlplus / as sysdba
- SQL> SELECT parameter_name, parameter_value
- SQL> FROM dba_advisor_parameters
- SQL> WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
- SQL> AND parameter_value != 'UNUSED'
- SQL> ORDER BY parameter_name;
3. Turn off Automatic Evolving of Baselines:
- sqlplus / as sysdba
- SQL> BEGIN
- SQL> DBMS_SPM.set_evolve_task_parameter(
- SQL> task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
- SQL> parameter => 'ACCEPT_PLANS',
- SQL> value => 'FALSE');
- SQL> END;
- SQL> /
4. Turn on the Automatic Evolving of Baselines:
- sqlplus / as sysdba
- SQL> BEGIN
- SQL> DBMS_SPM.set_evolve_task_parameter(
- SQL> task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
- SQL> parameter => 'ACCEPT_PLANS',
- SQL> value => 'FALSE');
- SQL> END;
- SQL> /
