Database Reference
In-Depth Information
recommendations to improve their response time. If the Tuning Advisor notices that a nonaccepted SQL plan baseline
leads to better performance than an accepted one, it recommends a SQL profile that says nothing else than to accept
the SQL plan baseline. Obviously, if that SQL profile is accepted, then the SQL plan baseline is accepted as well.
Therefore, the SQL plan baselines are automatically accepted only if the SQL profiles generated by the SQL Tuning
Advisor are also automatically accepted.
It's essential to point out that the SQL profiles are automatically accepted only when the
accept_sql_profiles
parameter, which is specific to the SQL Tuning Advisor, is set to
TRUE
. By default it's set to
FALSE
. You can check its
value through the
dba_advisor_parameters
view (note that also the
user
and, as of version 12.1,
cdb
related views
exist) with a query like the following:
SQL> SELECT parameter_value
2 FROM dba_advisor_parameters
3 WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
4 AND parameter_name = 'ACCEPT_SQL_PROFILES';
PARAMETER_VALUE
---------------
FALSE
The
dbms_auto_sqltun
e package provides the
set_auto_tuning_task_parameter
procedure to change the value
of the
accept_sql_profiles
parameter. The following example shows how to set that parameter to
TRUE
to activate
the automatic acceptance of SQL profiles:
dbms_auto_sqltune.set_auto_tuning_task_parameter(parameter => 'ACCEPT_SQL_PROFILES',
value => 'TRUE');
As of version 12.1, there's also a new advisor called
SPM Evolve Advisor
. Its purpose is to execute an evolution for
the nonaccepted execution plans associated to SQL plan baselines. It runs during the maintenance window, just as
other advisors do. To display what the SPM Evolve Advisor did, you can use the
report_auto_evolve_task
function
in the
dbms_spm
package. If you call that function without parameters, it shows a report about the last execution. The
following example shows how to find out, through the
dba_advisor_executions
view (note that also
user
and, as of
version 12.1,
cdb
related views exist), when the last five executions took place, and how to display the report of one
specific execution:
SQL> SELECT *
2 FROM (
3 SELECT execution_name, execution_start
4 FROM dba_advisor_executions
5 WHERE task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
6 ORDER BY execution_start DESC
7 )
8 WHERE rownum <= 3;
EXECUTION_NAME EXECUTION_START
-------------- ----------------
EXEC_6294 23-APR-14
EXEC_6182 22-APR-14
EXEC_6082 21-APR-14
SQL> SELECT dbms_spm.report_auto_evolve_task(execution_name => 'EXEC_6294')
2 FROM dual;
