Automatic SQL Tuning In Oracle Database 11g

In Oracle 11g, we have SQL Tuning Advisor which is run automatically against high resource consuming SQL statements during the maintenance windows. Lets see how to do this.

1. DBMS_AUTO_TASK_ADMIN package is used to enable and disable this Automatic SQL Tuning Advisor.

To Enable the advisor
SQL> BEGIN
DBMS_AUTO_TASK_ADMIN.enable(
client_name => ‘sql tuning advisor’,
operation   => NULL,
window_name => NULL);
END;
/

To Disable the advisor
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => ‘sql tuning advisor’,
operation   => NULL,
window_name => NULL);
END;
/

Those SQLs which top high resource utilization as per the AWR statistics are the ones chosen for automatic SQL tuning.
Also it excludes the statements that are inherently less tunable, such as recently (within a month) tuned recursive statements, parallel queries, DML, DDL and SQL statements whose performance problems are caused by concurrency issues.

So by setting STATISTICS_LEVEL parameter to BASIC, automatic SQl tuning advisor is disabled, as the automatic statistics gathering by AWR is stopped.

2.Once enabled, we have few parameters that control the behavior of the SQL tuning advisor.

Parameters are ACCEPT_SQL_PROFILES,MAX_SQL_PROFILES_PER_EXEC and MAX_AUTO_SQL_PROFILES.

SELECT parameter_name, parameter_value
FROM   dba_advisor_parameters
WHERE  task_name = ‘SYS_AUTO_SQL_TUNING_TASK’
AND    parameter_name IN (‘ACCEPT_SQL_PROFILES’,
‘MAX_SQL_PROFILES_PER_EXEC’,
‘MAX_AUTO_SQL_PROFILES’);

PARAMETER_NAME                 PARAMETER_VALUE
—————————— ——————————
ACCEPT_SQL_PROFILES            FALSE
MAX_SQL_PROFILES_PER_EXEC      20
MAX_AUTO_SQL_PROFILES          10000

These are the default values.The SET_TUNING_TASK_PARAMETER procedure of the DBMS_SQLTUNE package is used to change the values.

3. Inorder to automatically accept the SQL profile, we need to set ACCEPT_SQL_PROFILES to true.

BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter(
task_name => ‘SYS_AUTO_SQL_TUNING_TASK’,
parameter => ‘ACCEPT_SQL_PROFILES’,
value     => ‘TRUE’);
END;
/

4.To get the report of the SQL tuning advisor, use REPORT_AUTO_TUNING_TASK function of the DBMS_SQLTUNE package.
Setting the BEGIN_EXEC and END_EXEC parameters to NULL produces a report from the most recent execution.

VARIABLE report CLOB;
BEGIN
:report := DBMS_SQLTUNE.report_auto_tuning_task(
begin_exec   => NULL,
end_exec     => NULL,
type         => DBMS_SQLTUNE.type_text,     — ‘TEXT’
level        => DBMS_SQLTUNE.level_typical, — ‘TYPICAL’
section      => DBMS_SQLTUNE.section_all,   — ‘ALL’
object_id    => NULL,
result_limit => NULL);
END;
/

The report consists of information like Tuning Task Name, Tuning Task Owner,General information, Summary, Tuning finding, Explain plans, Errors, Cumulative Elapsed Time of SQL, Number of Candidate SQLs etc.

Related Data Dictionary Views: DBA_SQL_PROFILES, dba_advisor_parameters

Article by Divya