SQL TUNING ADVISOR via the DBMS_SQLTUNE pkg

SQL TUNING ADVISOR via the DBMS_SQLTUNE pkg

This article provides an introduction to the use of the SQL TUNING ADVISOR (STA) via the DBMS_SQLTUNE package.

Scope

1. Create a Tuning TASK using DBMS_SQLTUNE - DBMS_SQLTUNE.CREATE_TUNING_TASK

2. Execute the Tuning TASK created -Execute dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');

3. Check the recommendation by tuning task...- select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task') from dual;

Details

The SQL Tuning Advisor automates the entire SQL tuning process replacing manual SQL tuning. It analyzes candidate SQL statements, and executes a complete analysis of the statements including: 

• Determining stale or missing statistics
• Determining better execution plans
• 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 also be controlled with procedures in the DBMS_SQLTUNE package. Output can be queried via the various advisory views in SQL*Plus.

To use these procedures 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 the DBMS_SQLTUNE package is a two-step process:

1. Create a SQL tuning task
2. Execute a SQL tuning task

Note: You can run SQL Tuning Advisor from the command line using $ORACLE_HOME/rdbms/admin/sqltrpt.sql

Example:

This example is based on the "SH" account executing the various tasks. To allow the "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.

• Create a SQL 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.

You can create the task using the following: 

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;

Parameter explanation:

• User_name: User under which the CREATE_TUNING_TASK function analyzes the SQL statement.
• Scope: COMPREHENSIVE. This means that the advisor also performs SQL Profiling analysis
• Time_limit: Time in seconds that the function can run.

The CREATE_TUNING_TASK function simply creates the task and returns either the task name that you have provided or generates a unique task name. This task name is the identifier by which you execute it (or perform other procedures upon it). 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'; 

• Execute the tuning task.:
• Execute dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');
• select status from dba_advisor_log where task_name='TEST_sql_tuning_task';
• set long 65536
• set longchunksize 65536
• set linesize 100
• select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task') from dual;
• Check the status of the task using following query:
• View the Recommendations

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 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.