Subject: How to use the Sql Tuning Advisor.
Doc ID: Note:262687.1 Type: BULLETIN
Last Revision Date: 23-MAY-2005 Status: PUBLISHED

PURPOSE
-------

To show an introduction on how to use the SQL TUNING ADVISOR feature.
The STA consists of functionality built into the Oracle OPTIMIZER to assist
in making tuning Of sql queries easy.

SCOPE & APPLICATION
-------------------

DBAs and Support Analysts

Using the SQL TUNING ADVISOR (STA)
-------------------------------------

Introduction:
--------------

This is a new feature introduced in Oracle 10G . This automates
the entire SQL tuning process. The automatic process replaces manual SQL tuning.

'SQL Tuning Adviser' analyzes candidate SQL statements, and execute a complete
analysis of the statement including:
- Determining stale or missing statistics
- Determining better execution plan
- Detecting better access paths and objects required to satisfy them (indexes, materialized views)
- Restructuring SQL

While the primary interface for the SQL Tuning Advisor is the Oracle Enterprise Manager Database Control,
the advisor can be administered with procedures in the DBMS_SQLTUNE package. To use the APIs the user must
have been granted the DBA role and the ADVISOR privilege. If using the SQL Tuning Advisor in Oracle Enterprise
Manager, the user must have been granted the select_catalog_role role.

Running SQL Tuning Advisor using DBMS_SQLTUNE package is a two-step process:
1. Create a SQL tuning task
2. Execute a SQL tuning task


Example:
--------

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 SH account executing the various tasks. To
allow SH user to both create task and execute it user SH needs to be granted proper access:
CONNECT / AS SYSDBA
GRANT ADVISOR TO SH;
GRANT SELECT_CATALOG_ROLE TO SH;
GRANT EXECUTE ON DBMS_SQLTUNE TO SH;

The example presented makes use of a table called SALES, residing in the
SH 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 => 'SH',
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:

select task_name from dba_advisor_log where owner='SH';

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) You can check the status of the tas using following query:

select status from dba_advisor_log where task_name='TEST_sql_tuning_task';

iV) View the Recomendation


set long 1000
set longchunksize 1000
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/04/2004 23:17:49
Completed at : 02/04/2004 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 "SH"."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.


RELATED DOCUMENTS
-----------------

Oracle10g Database Performance Guide Release 1 (10.1)
Oracle10g Database Reference Release 1 (10.1)
PL/SQL Packages and Types Reference Release 1 (10.1)

출처 : metalink