Purpose
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
How to use SQL
TUNING ADVISOR
You can use STA through the PL/SQL API and query the various
advisory views in SQL*Plus to examine how to solve performance issues.
The example is based on the SCOTT account executing the
various tasks. To allow SCOTT user to both create task and execute it user
SCOTT needs to be granted proper access.
CONNECT / AS SYSDBA
GRANT ADVISOR TO SCOTT;
GRANT SELECT_CATALOG_ROLE TO SCOTT;
GRANT EXECUTE
ON DBMS_SQLTUNE TO SCOTT;
The example presented makes use of a table called SALES,
residing in the SCOTT schema. The table Is not analyzed.
I) First step is to
create the tuning task.
You can create tuning tasks from the following:
- SQL statement
selected by SQL identifier from the cursor cache
- SQL Tuning Set
containing multiple statements
- Text of a single
SQL statement
- SQL statement
selected by SQL identifier from the Automatic Workload Repository
DECLARE
my_task_name
VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext :=
'SELECT * ' ||
'FROM
sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 ';
my_task_name :=
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'Scott',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'TEST_sql_tuning_task',
description => 'Task to tune a query on a specified PRODUCT');
END;
/
User_name: User under which the CREATE_TUNING_TASK function
analyzes the SQL statement.
Scope: COMPREHENSIVE which means that the advisor also
performs SQL Profiling analysis
Time_limit: Time in seconds that the function can run.
The CREATE_TUNING_TASK function returns the task name that
you have provided
or generates a unique task name. You can use the task name
to specify this task
when using other APIs. To view the task names associated
with a specific owner,
you can run the following:
II) Execute the
tuning task.
Begin
dbms_sqltune.Execute_tuning_task (task_name =>
'TEST_sql_tuning_task');
end;
OR
Execute dbms_sqltune.Execute_tuning_task (task_name =>
'TEST_sql_tuning_task');
iii) View the
Recommendation
set long 65536
set longchunksize 65536
set linesize 100
select
dbms_sqltune.report_tuning_task('TEST_sql_tuning_task') from dual;
The output of above will be like this:
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name :
TEST_sql_tuning_task
Scope :
COMPREHENSIVE
Time Limit(seconds): 60
Completion Status :
COMPLETED
Started at :
02/08/2010 23:17:49
Completed at :
02/08/2010 23:18:19
-------------------------------------------------------------------------------
SQL ID :
9bxw71yp99fr6
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
SQL Text: SELECT * FROM sales WHERE prod_id = 10 AND cust_id
= 100
-------------------------------------------------------------------------------
FINDINGS SECTION (5 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Index
"Scott"."SALES_PROMO_BIX" was not analyzed.
Recommendation
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
Consider
collecting optimizer statistics for this index.
execute
dbms_stats.gather_index_stats(ownname => 'SH', indname =>
'SALES_PROMO_BIX', estimate_percent =>
SQL tuning information views, such as
DBA_SQLTUNE_STATISTICS, DBA_SQLTUNE_BINDS,
and DBA_SQLTUNE_PLANS views can also be queried to get this
information.
Note: It is possible for the SQL Tuning Advisor to return no
recommendations for
a particular SQL statement e.g. in cases where the plan is
already optimal or the
Automatic Tuning Optimization mode cannot find a better
plan.
