Automatic SQL Tuning

As we are aware, manual SQL tuning process is very complex and time consuming, Oracle 10g introduces automatic SQL tuning to ease out the tuning process. To use this feature, we need to use SQL tuning advisor.

Here, I will be giving a brief introduction of Automatic SQL Tuning and the simplest way of using SQL Tuning Advisor.

The query optimizer can run in two modes:

1. Normal mode: In this mode, the optimizer generates the access plan using already available statistics in a very short time and it may not get time to look at all the statistics available.

2. Tuning mode: In this mode, the optimizer generates recommendations after doing extra analysis for the query. This process can be time and resource consuming, so should be used for long ops queries only.

Automatic SQL tuning advisor performs following analysis:

· Statistics Analysis: The optimizer recommends the gathering of statistics wherever it has become stale.

· SQL Profiling: The optimizer generates a SQL profile with additional statistics specific to the query which can be saved and used later. This analysis generates a recommendation to use the SQL profile. A SQL profile, once accepted, is stored in the data dictionary and used to generate access plan for the query when the optimizer is running in normal mode.

· Access Path Analysis: In this analysis, the advisor recommends new indexes which may enhance the performance of the query.

· SQL Structure Analysis: The advisor suggests some changes in the SQL query itself to improve the performance. The developer should analyze the recommendations to check whether those are fit for the query. e.g. The advisor may suggest to replace UNION by UNION ALL. If the query will never give duplicate records, then UNION ALL will be faster.

SQL tuning advisor can work on various inputs such as ADDM, high load SQL statements, cursor cache, SQL tuning sets or manually specified statement. Here only cursor cache and manually specified statement methods are covered.

Using SQL Tuning Advisor

For query in cursor cache:

1. Create a SQL tuning task

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'dn7gnkw06r2yu',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'dn7gnkw06r2yu_tuning_task',
description => 'Tuning task for statement dn7gnkw06r2yu.');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

2. Execute a SQL tuning task

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>'dn7gnkw06r2yu_tuning_task' );
END;
/

The status of the task can be checke by following query

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'dn7gnkw06r2yu_tuning_task';

The execution progress of the SQL Tuning Advisor can be checked in the V$ADVISOR_PROGRESS view.

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE user_name = 'HR' AND task_name = 'dn7gnkw06r2yu_tuning_task';

3. Display the results of a SQL tuning task

SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('dn7gnkw06r2yu_tuning_task') FROM DUAL;

4. Implement recommendations as appropriate

For manually supplied query:

1. Create a SQL tuning task

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT /*+ ORDERED */ * ' ||
'FROM employees e, locations l, departments d ' ||
'WHERE e.department_id = d.department_id AND ' ||
'l.location_id = d.location_id AND ' ||
'e.employee_id < :bnd'; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task',
description => 'Task to tune a query on a specified employee');
END;
/

2. Execute a SQL tuning task

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>'my_sql_tuning_task' );
END;
/

The status of the task can be checke by following query

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';

The execution progress of the SQL Tuning Advisor can be checked in the V$ADVISOR_PROGRESS view.

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE user_name = 'HR' AND task_name = 'my_sql_tuning_task';

3. Display the results of a SQL tuning task

SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task')
FROM DUAL;

4. Implement recommendations as appropriate