Overview:
SQL tuning is one of the challenging tasks faced by
Database Administrators. It is an interesting and creative, but at the same
time, daunting task. Manual tuning of SQL statements requires through knowledge
of how the statements are executed in the background and experience to
understand suitable access paths to yield better response times. Moreover, in a
typical application, there are just too many SQL statements to tune and advise
the developers.
Oracle Database 10g introduces many useful and easy-to-use
tuning tools and methodologies. In this article, we will examine one of these
new and improved features – SQL Tuning Advisor.
SQL Tuning Advisor is primarily designed to replace the manual tuning of
SQL statements or any third-party tools currently used by some DBAs. SQL Tuning
Advisor examines poorly executing SQL statements and evaluates resource
consumption in terms of CPU, I/O, and temporary space. The advisor receives one
or more SQL statements as input and provides advice on how to optimize their
execution plans, gives the rationale for the advice, the estimated performance
benefit, and the actual command to implement the advice.
In its normal mode, the query optimizer needs to make decisions about
execution plans in a very short time. As a result, it may not always be able to
obtain enough information to make the best decision. Oracle 10g allows the
optimizer to run in tuning mode where it can gather additional information and
make recommendations about how specific statements can be tuned further. This
process may take several minutes for a single statement so it is intended to be
used on high-load resource-intensive statements.
SQL
Tuning Advisor also identifies any objects with missing statistics referenced
in the query. Thus, the advisor performs four distinct types of tasks:
-
Checks if objects have valid,
usable statistics for proper optimization -
Attempts to rewrite queries for
better performance and suggests rewriting -
Checks the access path to see if
performance could be improved by adding additional structures such as indexes
and materialized views -
Creates SQL profiles and attaches
them to specific queries.
In tuning mode, the optimizer performs the following analysis:
- Statistics Analysis – The optimizer recommends the gathering of
statistics on objects with missing or stale statistics. Additional statistics
for these objects are stored in an SQL profile. - SQL Profiling – The optimizer may be able to improve performance
by gathering additional statistics and altering session specific parameters
such as the OPTIMIZER_MODE. If such improvements are possible, the information
is stored in an SQL profile. If accepted, this information can then be used by
the optimizer when running in normal mode. Unlike a stored outline, which fixes
the execution plan, an SQL profile may still be of benefit when the contents of
the table alter drastically. Even so, it is sensible to update profiles
periodically. The SQL profiling is not performed when the tuning optimizer is
run in limited mode. - Access Path Analysis – The optimizer investigates the effect of
new or modified indexes on the access path. Its index recommendations relate to
a specific statement, so where necessary it will also suggest the use of the
SQL Access Advisor to check the impact of these indexes on a representative SQL
workload. - SQL Structure Analysis – The optimizer suggests alternatives for
SQL statements that contain structures that may impact performance. The
implementation of these suggestions requires human intervention to check their
validity.
Using Enterprise Manager:
The below example was formulated to illustrate how
to use SQL Tuning Advisor in command-line mode, which is very useful for
scripting these tasks proactively. In most cases, however, you will need to
perform tuning in response to problems reported by an end user. Enterprise
Manager 10g comes in handy in those cases.
From the Database home page, click on the link
"Advisor Central" at the bottom of the screen, which launches the
page containing all of the advisors. Next, click on "SQL Tuning
Advisor" at the top of the screen as shown in Figure A.
Figure A: Advisor Central in Enterprise Manager
Now you have launched the SQL Tuning Advisor. Choose "Top
SQL" from the next page as shown in Figure B.
Figure B: SQL Tuning Advisors
When you choose Top SQL, it launches a page with a graph containing the
various wait classes, traced along a time dimension Dragging
the mouse to a location on the graph where the CPU wait is high, you would be
able to see relevant SQL statements and be able to identify areas with the
highest activity and maximum CPU consumption.
Now we focus on the PL/SQL API, as the Enterprise Manager interface is a
reasonably intuitive method. The statements to be analyzed can be retrieved
from the Automatic Workload Repository (AWR), the cursor cache, an SQL tuning
set or specified manually.
In order to access the SQL tuning advisor API, a user must be granted
the ADVISOR privilege:
CONN sys/password AS SYSDBA
GRANT ADVISOR TO scott;
CONN scott/tiger
The first step when using the SQL tuning advisor is to create a new
tuning task using the CREATE_TUNING_TASK function.
SET SERVEROUTPUT ON— Tuning task created for specific a statement from the AWR.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 764,
end_snap => 938,
sql_id => ’19v5guvsgcd1v’,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => ’19v5guvsgcd1v_AWR_tuning_task’,
description => ‘Tuning task for statement 19v5guvsgcd1v in AWR.’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/— Tuning task created for specific a statement from the cursor cache.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => ’19v5guvsgcd1v’,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => ’19v5guvsgcd1v_tuning_task’,
description => ‘Tuning task for statement 19v5guvsgcd1v.’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/— Tuning task created from an SQL tuning set.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => ‘test_sql_tuning_set’,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => ‘sqlset_tuning_task’,
description => ‘Tuning task for an SQL tuning set.’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/— Tuning task created for a manually specified statement.
DECLARE
l_sql VARCHAR2(500);
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql := ‘SELECT e.*, d.* ‘ ||
‘FROM emp e JOIN dept d ON e.deptno = d.deptno ‘ ||
‘WHERE NVL(empno, ”0”) = :empno’;l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => ‘scott’,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => ’emp_dept_tuning_task’,
description => ‘Tuning task for an EMP to DEPT join query.’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/
If the TASK_NAME parameter is specified, its value is returned as the
SQL tune task identifier. If omitted, a system generated name such as "TASK_1478"
is returned. If the SCOPE parameter is set to scope_limited the SQL profiling
analysis is omitted. The TIME_LIMIT parameter simply restricts the time the
optimizer can spend compiling the recommendations.
The following examples will reference the last tuning set, as it has no
external dependencies other than the SCOTT schema. The NVL in the SQL statement
was put in to provoke a reaction from the optimizer. In addition, we can delete
the statistics from one of the tables to provoke it even more:
EXEC DBMS_STATS.delete_table_stats('SCOTT','EMP');
With the tuning task defined, the next step is to execute it using the
EXECUTE_TUNING_TASK procedure:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task');
During the execution phase, you may wish to pause and restart the task,
cancel it or reset the task to allow it to be re-executed:
— Interrupt and resume a tuning task.
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => ’emp_dept_tuning_task’);
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => ’emp_dept_tuning_task’);— Cancel a tuning task.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => ’emp_dept_tuning_task’);— Reset a tuning task allowing it to be re-executed.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => ’emp_dept_tuning_task’);The status of the tuning task can be monitored using the DBA_ADVISOR_LOG view:
SELECT task_name, status FROM dba_advisor_log WHERE owner = ‘SCOTT’;
TASK_NAME STATUS
—————————— ———–
emp_dept_tuning_task COMPLETED1 row selected.
Once the tuning task has executed successfully the recommendations can
be displayed using the REPORT_TUNING_TASK function:
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task(’emp_dept_tuning_task’) AS recommendations FROM dual;
SET PAGESIZE 24
In this case, the output looks like this:
RECOMMENDATIONS
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : emp_dept_tuning_task
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/06/2004 09:29:13
Completed at : 05/06/2004 09:29:15——————————————————————————-
SQL ID : 0wrmfv2yvswx1
SQL Text: SELECT e.*, d.* FROM emp e JOIN dept d ON e.deptno = d.deptno
WHERE NVL(empno, ‘0’) = :empno——————————————————————————-
FINDINGS SECTION (2 findings)
——————————————————————————-1- Statistics Finding
———————
Table “SCOTT”.”EMP” and its indices were not analyzed.Recommendation
————–
Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => ‘SCOTT’, tabname =>
‘EMP’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, cascade => TRUE)Rationale
———
The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.2- Restructure SQL finding (see plan 1 in explain plans section)
—————————————————————-
The predicate NVL(“E”.”EMPNO”,0)=:B1 used at line ID 2 of the execution plan
contains an expression on indexed column “EMPNO”. This expression prevents
the optimizer from selecting indices on table “SCOTT”.”EMP”.Recommendation
————–
Rewrite the predicate into an equivalent form to take advantage of
indices. Alternatively, create a function-based index on the expression.Rationale
———
The optimizer is unable to use an index if the predicate is an inequality
condition or if there is an expression or an implicit data type conversion
on the indexed column.——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-1- Original
———–
Plan hash value: 1863486531—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 107 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 107 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 1 | 87 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
| 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
—————————————————————————————-Note
—–
– dynamic sampling used for this statement——————————————————————————-
1 row selected.
Once the tuning session is over the tuning task can be
dropped using the DROP_TUNING_TASK procedure:
BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => ’19v5guvsgcd1v_AWR_tuning_task’);
DBMS_SQLTUNE.drop_tuning_task (task_name => ’19v5guvsgcd1v_tuning_task’);
DBMS_SQLTUNE.drop_tuning_task (task_name => ‘sqlset_tuning_task’);
DBMS_SQLTUNE.drop_tuning_task (task_name => ’emp_dept_tuning_task’);
END;
/
Conclusion
SQL Tuning Advisor is a powerful tool that has the
"brains" to analyze performance metrics and offer recommendations based
on best practices and accepted methodologies professed by seasoned Oracle
professionals, all automatically. This functionality can tell the DBA not only
what happened and why, but most important, what to do next.
Sreeram Surapaneni
is an Oracle Certified DBA (7.3,8,8i,9i) working currently as DBA at Research
In Motion Ltd, Canada and can be reached at Sreeram
Surapaneni.
