Using the DBMS_SQLTUNE package to Run the Sql Tuning Advisor

清疚 2022-03-28 12:14 189阅读 0赞

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 and later  
Information in this document applies to any platform.

Purpose

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

Scope

DBAs and Support Analysts

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:

o SQL statement selected by SQL identifier from the cursor cache

o SQL Tuning Set containing multiple statements

o Text of a single SQL statement

o 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:

o User\_name: User under which the CREATE\_TUNING\_TASK function analyzes the SQL statement.

o Scope: COMPREHENSIVE. This means that the advisor also performs SQL Profiling analysis

o 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');

· Check the status of the task using following query:

·  select status from dba\_advisor\_log where task\_name='TEST\_sql\_tuning\_task';

· View the Recommendations

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