Automated SQL Tuning: Displaying Automatic SQL Tuning Advice

Problem
You’re aware that Oracle automatically runs a daily job that generates SQL tuning advice. You want to view the advice.
Solution
If you’re using Oracle Database 11g Release 2 or higher, here’s the quickest way to display automatically generated SQL tuning advice:

SQL> SET LINESIZE 80 PAGESIZE 0 LONG 100000

SQL> SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

Note: Starting with Oracle Database 11g Release 2, the DBMS_AUTO_SQLTUNE package should be used (instead of DBMS_SQLTUNE) for administrating automatic SQL tuning features. If you are using an older release of Oracle, use DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK to view automated SQL tuning advice.

Depending on the activity in your database, there may be a great deal of output. Here’s a small sample of output from a very active database:

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name                         : SYS_AUTO_SQL_TUNING_TASK

Tuning Task Owner                        : SYS

Workload Type                            : Automatic High-Load SQL Workload

Execution Count                          : 30

Current Execution                        : EXEC_3483

Execution Type                           : TUNE SQL

Scope                                    : COMPREHENSIVE

.....

Completion Status                        : COMPLETED

Started at                               : 04/10/2011 06:00:01

Completed at                             : 04/10/2011 06:02:41

Number of Candidate SQLs                 : 103

Cumulative Elapsed Time of SQL (s)       : 49124

-------------------------------------------------------------------------------

SUMMARY SECTION

-------------------------------------------------------------------------------

Global SQL Tuning Result Statistics

-------------------------------------------------------------------------------

Number of SQLs Analyzed                            : 103

Number of SQLs in the Report                       : 8

Number of SQLs with Findings                       : 8

Number of SQLs with Alternative Plan Findings      : 1

Number of SQLs with SQL profiles recommended       : 1

-------------------------------------------------------------------------------

SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID

-------------------------------------------------------------------------------

object ID         SQL ID statistics profile(benefit) index(benefit) restructure

---------- ------------- ---------- ---------------- -------------- -----------

9130       crx9h7tmwwv67                      51.44%

AUTOMATICALLY E-MAILING SQL OUTPUT

On Linux/Unix systems, it’s quite easy to automate the e-mailing of output from a SQL script. First
encapsulate the SQL in a shell script, and then use a utility such as cron to automatically generate and email
the output. Here’s a sample shell script:

#!/bin/bash

if [ $# -ne 1 ]; then

echo "Usage: $0 SID"

exit 1

fi

# source oracle OS variables

. /var/opt/oracle/oraset $1

#

BOX=`uname -a | awk '{print$2}'`

OUTFILE=$HOME/bin/log/sqladvice.txt

#

sqlplus -s <<EOF

mv_maint/foo

SPO $OUTFILE

SET LINESIZE 80 PAGESIZE 0 LONG 100000

SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

EOF

cat $OUTFILE | mailx -s "SQL Advice: $1 $BOX" larry@oracle.com

exit 0

Here’s the corresponding cron entry that runs the report on a daily basis:

#-----------------------------------------------------------------

# SQL Advice report from SQL auto tuning

16 11 * * * /orahome/oracle/bin/sqladvice.bsh <DatabaseName> 1>/orahome/oracle/bin/log/sqladvice.log 2>&1

#-----------------------------------------------------------------

How It Works
The “Solution” section describes a simple method to display in-depth tuning advice for high-load queries in your database. Depending on the activity and load on your database, the report may contain no suggestions or may provide a great deal of advice. The Automatic SQL Tuning job uses the highworkload SQL statements identified in the AWR as the target SQL statements to report on. The advice report consists of one or more of the following general subsections:

• General information
• Summary
• Details
• Findings
• Explain plans
• Alternate plans
• Errors

The general information section contains high-level information regarding the start and end time, number of SQL statements considered, cumulative elapsed time of the SQL statements, and so on. The summary section contains information regarding the SQL statements analyzed—for example:

Global SQL Tuning Result Statistics

-------------------------------------------------------------------------------

Number of SQLs Analyzed                         : 26

Number of SQLs in the Report                    : 5

Number of SQLs with Findings                    : 5

Number of SQLs with Alternative Plan Findings   : 1

Number of SQLs with SQL profiles recommended    : 5

Number of SQLs with Index Findings              : 2

-------------------------------------------------------------------------------

SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID

-------------------------------------------------------------------------------

object ID         SQL ID statistics profile(benefit) index(benefit) restructure

---------- ------------- ---------- ---------------- -------------- -----------

1160       31q9w59vpt86t                      98.27%         99.90%

1167       3u8xd0vf2pnhr                      98.64%

The detail section contains information describing specific SQL statements, such as the owner and SQL text. Here is a small sample:

DETAILS SECTION

------------------------------------------------------------------------------

Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID

-------------------------------------------------------------------------------

Object ID         : 1160

Schema Name       : CHN_READ

SQL ID            : 31q9w59vpt86t

SQL Text          : SELECT "A2"."UMID","A2"."ORACLE_UNIQUE_ID","A2"."PUBLIC_KEY","A2"

                    ."SERIAL_NUMBER",:1||"A1"."USER_NAME","A1"."USER_NAME",NVL("A2"."

                    CREATE_TIME_DTT",:2),NVL("A2"."UPDATE_TIME_DTT",:3) FROM

                    "COMPUTER_SYSTEM" "A2","USERS" "A1" WHERE

The findings section contains recommendations such as accepting a SQL profile or creating an index—for example:

FINDINGS SECTION

-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)

-------------------------------------------------------

A potentially better execution plan was found for this statement.

    Recommendation (estimated benefit: 98.27%)

    -----------------------------------------

    - Consider accepting the recommended SQL profile to use

    parallel execution for this statement.

    execute dbms_sqltune.accept_sql_profile(task_name =>

    'SYS_AUTO_SQL_TUNING_TASK', object_id => 1160, task_owner =>

    'SYS', replace => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

.................

2- Index Finding (see explain plans section below)

-------------------------------------------------

The execution plan of this statement can be improved by creating

one or more indices.

    Recommendation (estimated benefit: 99.9%)

    ----------------------------------------

    - Consider running the Access Advisor to improve the physical schema design

      or creating the recommended index.

      create index CHAINSAW.IDX$$_90890002 on

      CHAINSAW.COMPUTER_SYSTEM("UPDATE_TIME_DTT");

Where appropriate, the original execution plan for a query is displayed along with a suggested fix and new execution plan. This allows you to see the before and after plan differences. This is very useful when determining if the findings (such as adding an index) would improve performance.

Lastly, there is an error section of the report. For most scenarios, there typically will not be an error section in the report.

The “Solution” section showed how to execute the REPORT_AUTO_TUNING_TASK function from a SQL statement. This function can also be called from an anonymous block of PL/SQL. Here’s an example:

VARIABLE tune_report CLOB;

BEGIN

:tune_report := DBMS_AUTO_SQLTUNE.report_auto_tuning_task(

begin_exec => NULL

,end_exec => NULL

,type => DBMS_AUTO_SQLTUNE.type_text

,level => DBMS_AUTO_SQLTUNE.level_typical

,section => DBMS_AUTO_SQLTUNE.section_all

,object_id => NULL

,result_limit => NULL);

END;

/

--

SET LONG 1000000

PRINT :tune_report

The parameters for the REPORT_AUTO_TUNING_TASK function are described in detail in Table 11-2. These parameters allow you a great deal of flexibility to customize the advice output.