Automated SQL Tuning: Viewing Resource-Intensive SQL in the AWR And Populating the Sql Queries into Sql-Tuning-Set(STS)

Viewing Resource-Intensive SQL in the AWR

Problem
Before populating a SQL tuning set, you want to view high-load SQL statements in the AWR. You want to eventually use SQL contained in the AWR as input for populating a SQL tuning set.
Solution
The DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY function can be used to extract SQL stored in the AWR. This particular query selects queries in the AWR between snapshots 8200 and 8201 ordered by the top 10 in the disk reads usage category:

SELECT

sql_id

,substr(sql_text,1,20)

,disk_reads

,cpu_time

,elapsed_time

FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(8200,8201,

null, null, 'disk_reads',null, null, null, 10))

ORDER BY disk_reads DESC;

Here is a small snippet of the output:

SQL_ID         SUBSTR(SQL_TEXT,1,20 DISK_READS      CPU_TIME  ELAPSED_TIME

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

achffburdff9j   delete from "MVS"."     101145     814310000     991574249

5vku5ap6g6zh8  INSERT /*+ BYPASS_RE      98172      75350000      91527239

How It Works
Before you work with SQL tuning sets, it’s critical to understand you can use the DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY function to retrieve high resource-usage SQL from the AWR. The result sets retrieved by this PL/SQL function can be used as input for populating SQL tuning sets. See Table 11-5 for a description of the SELECT_WORKLOAD_REPOSITORY function parameters. You have a great deal of flexibility in how you use this function. A few examples will help illustrate this. Say you want to retrieve SQL from the AWR that was not parsed by the SYS user. Here is the SQL to do that:

SELECT sql_id, substr(sql_text,1,20)

,disk_reads, cpu_time, elapsed_time, parsing_schema_name

FROM table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(8200,8201,

'parsing_schema_name <> ''SYS''',

NULL, NULL,NULL,NULL, 1, NULL, 'ALL'));

The following example retrieves the top ten queries ranked by buffer gets for non-SYS users:

SELECT sql_id

,substr(sql_text,1,20)

,disk_reads

,cpu_time

,elapsed_time

,buffer_gets

,parsing_schema_name

FROM table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(

begin_snap => 21730

,end_snap => 22900

,basic_filter => 'parsing_schema_name <> ''SYS'''

,ranking_measure1 => 'buffer_gets'

,result_limit => 10

));

Populating SQL Tuning Set from High-Resource SQL in AWR

Problem
You want to create a SQL tuning set and populate it with the top I/O-consuming SQL statements found in the AWR.

Solution
Use the following steps to populate a SQL tuning set from high resource-consuming statements in the AWR:

1. Create a SQL tuning set object.
2. Determine begin and end AWR snapshot IDs.
3. Populate the SQL tuning set with high-resource SQL found in AWR.

The prior steps are detailed in the following subsections
Step 1: Create a SQL Tuning Set ObjectCreate a SQL tuning set. This next bit of code creates a tuning set named IO_STS:

BEGIN

dbms_sqltune.create_sqlset(

sqlset_name => 'IO_STS'

description => 'STS from AWR');

END;

/

Step 2: Determine Begin and End AWR Snapshot IDsIf you’re unsure of the available snapshots in your database, you can run an AWR report or select the SNAP_ID from DBA_HIST_SNAPSHOTS:

select snap_id, begin_interval_time

from dba_hist_snapshot order by 1;

Step 3: Populate the SQL Tuning Set with High-Resource SQL Found in AWRNow the SQL tuning set is populated with the top 15 SQL statements ordered by disk reads. The begin and end AWR snapshot IDs are 26800 and 26900 respectively:

DECLARE

base_cur dbms_sqltune.sqlset_cursor;

BEGIN

OPEN base_cur FOR

SELECT value(x)

FROM table(dbms_sqltune.select_workload_repository(

26800,26900, null, null,'disk_reads',

null, null, null, 15)) x;

--

dbms_sqltune.load_sqlset(

sqlset_name => 'IO_STS',

populate_cursor => base_cur);

END;

/

The prior code populates the top 15 SQL statements contained in the AWR ordered by disk reads. The DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY function is used to populate a PL/SQL cursor with AWR information based on a ranking criterion. Next the DBMS_SQLTUNE.LOAD_SQLSET procedure is used to populate the SQL tuning set using the cursor as input.

How It Works

The DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY function can be used in a variety of ways to populate a SQL tuning set using queries in the AWR. You can instruct it to load SQL statements by criteria such as disk reads, elapsed time, CPU time, buffer gets, and so on. See Table 11-5 for descriptions for parameters to this function. When designating the AWR as input, you can use either of the following:

• Begin and end AWR snapshot IDs
• An AWR baseline that you’ve previously created

You can view the details of the SQL tuning set (created in the “Solution” section) via this query:

SELECT

sqlset_name

,elapsed_time

,cpu_time

,buffer_gets

,disk_reads

,sql_text

FROM dba_sqlset_statements

WHERE sqlset_name = 'IO_STS';

Automated SQL Tuning: Viewing Resource-Intensive SQL in Memory and Populating a SQL Tuning Set from Resource- Consuming SQL in Memory

Viewing Resource-Intensive SQL in Memory

Problem
Before populating a SQL tuning set, you want to view high-load SQL statements in the cursor cache in memory. You want to eventually use SQL contained in memory as input for populating a SQL tuning set.

Solution
Use the DBMS_SQLTUNE.SELECT_CURSOR_CACHE function to view current high resource-consuming SQL statements in memory. This query selects SQL statements in memory that have required more than a million disk reads:

SELECT

sql_id

,substr(sql_text,1,20)

,disk_reads

,cpu_time

,elapsed_time

FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000000'))

ORDER BY sql_id;

Here is some sample output:

SQL_ID        SUBSTR(SQL_TEXT,1,20 DISK_READS   CPU_TIME ELAPSED_TIME

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

0s6gq1c890p4s  delete from "MVS"."    3325320 8756130000   1.0416E+10

b63h4skwvpshj BEGIN dbms_mview.ref    9496353 1.4864E+10   3.3006E+10

How It Works
Before you work with SQL tuning sets, it’s critical to understand you can use the DBMS_SQLTUNE.SELECT_CURSOR_CACHE function to retrieve high resource-usage SQL from memory. The result set retrieved by this PL/SQL function can be used as input for populating SQL tuning sets. See Table 11-6 for a description of the SELECT_CURSOR_CACHE function parameters.
You have a great deal of flexibility in how you use this function. Here’s an example that selects SQL in memory, but excludes statements parsed by the SYS user and also returns statements with an elapsed time greater than 100,000:

SELECT sql_id, substr(sql_text,1,20)

,disk_reads, cpu_time, elapsed_time

FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''

AND elapsed_time > 100000'))

ORDER BY sql_id;

In the prior query, the SYS keyword is enclosed by two single quotes (in other words, those aren’t double quotes around SYS). The SQL_TEXT column is truncated to 20 characters so that the output can be displayed on the page more easily. Here is some sample output:

SQL_ID        SUBSTR(SQL_TEXT,1,20 DISK_READS   CPU_TIME ELAPSED_TIME

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

byzwu34haqmh4  SELECT /* DS_SVC */          0     140000       159828

Once you have identified a SQL_ID for a resource-intensive SQL statement, you can view all of its execution details via this query:

SELECT *

FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''byzwu34haqmh4'''));

Note that the SQL_ID in the prior statement is enclosed by two single quotes (not double quotes).

 
This next example selects the top ten queries in memory in terms of CPU time for non-SYS users:

SELECT

sql_id

,substr(sql_text,1,20)

,disk_reads

,cpu_time

,elapsed_time

,buffer_gets

,parsing_schema_name

FROM table(

DBMS_SQLTUNE.SELECT_CURSOR_CACHE(

basic_filter => 'parsing_schema_name <> ''SYS'''

,ranking_measure1 => 'cpu_time'

,result_limit => 10

));

Populating a SQL Tuning Set from Resource- Consuming SQL in Memory

Problem
You want to populate a tuning set from high resource-consuming SQL statements that are currently in the memory.

Solution
Use the DBMS_SQLTUNE.SELECT_CURSOR_CACHE function to populate a SQL tuning set with statements currently in memory. This example creates a tuning set and populates it with high-load resource consuming statements not belonging to the SYS schema and having disk reads greater than 1,000,000:

-- Create the tuning set

EXEC DBMS_SQLTUNE.CREATE_SQLSET('HIGH_DISK_READS');

-- populate the tuning set from the cursor cache

DECLARE

cur DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN

OPEN cur FOR

SELECT VALUE(x)

FROM table(

DBMS_SQLTUNE.SELECT_CURSOR_CACHE(

'parsing_schema_name <> ''SYS'' AND disk_reads > 1000000',

NULL, NULL, NULL, NULL, 1, NULL,'ALL')) x;

--

DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'HIGH_DISK_READS',

populate_cursor => cur);

END;

/

In the prior code, notice that the SYS user is bookended by sets of two single quotes (not double quotes). The  SELECT_CURSOR_CACHE function loads the SQL statements into a PL/SQL cursor, and the LOAD_SQLSET procedure populates the SQL tuning set with the SQL statements.

How It Works
The DBMS_SQLTUNE.SELECT_CURSOR_CACHE function (see Table 11-6 for function parameter descriptions) allows you to extract from memory SQL statements and associated statistics into a SQL tuning set. The procedure allows you to filter SQL statements by various resource-consuming criteria, such as the following:
• ELAPSED_TIME
• CPU_TIME
• BUFFER_GETS
• DISK_READS
• DIRECT_WRITES
• ROWS_PROCESSED
This allows you a great deal of flexibility on how to filter and populate the SQL tuning set.

Automated SQL Tuning: Creating a SQL Tuning Set Object

Problem
You’re working on a performance issue that requires that you analyze a group of SQL statements. Before you process the SQL statements as a set, you need to create a SQL tuning set object.
Solution
Use the DBMS_SQLTUNE.CREATE_SQLSET procedure to create a SQL tuning set object—for example:

BEGIN

DBMS_SQLTUNE.CREATE_SQLSET(

sqlset_name => 'HIGH_IO',

description => 'High disk read tuning set');

END;

/

The prior code creates a tuning set with the name of HIGH_IO. At this point, you have created a named tuning set object. The tuning set does not contain any SQL statements.

How It Works
A SQL tuning set object must be created before populating a tuning set with SQL statements (see Recipes 11-9 through 11-11 for details on adding SQL statements to an STS). You can view any defined SQL tuning sets in the database by querying the DBA_SQLSET view:

SQL> select id, name, created, statement_count from dba_sqlset;

Here is some sample output:

ID                                   NAME   CREATED STATEMENT_COUNT

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

5                                 HIGH_IO 26-APR-11               0

If you need to drop a SQL tuning set object, then use the DBMS_SQLTUNE.DROP_SQLSET procedure to drop a tuning set. The following example drops a tuning set named MY_TUNING_SET:

SQL> EXEC DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'MY_TUNING_SET' );

Automated SQL Tuning: Generating a SQL Script to Implement Automatic Tuning Advice

Problem
You’ve reported on the automatic tuning advice. Now you want to generate a SQL script that can be used to implement tuning advice.
Solution
Use the DBMS_SQLTUNE.SCRIPT_TUNING_TASK function to generate the SQL statements to implement the advice of a tuning task. You need to provide as input the name of the automatic tuning task. In this example, the name of the task is SYS_AUTO_SQL_TUNING_TASK:

SET LINES 132 PAGESIZE 0 LONG 10000

SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('SYS_AUTO_SQL_TUNING_TASK') FROM dual;

Here is a small snippet of the output for this example:

execute dbms_stats.gather_index_stats(ownname => 'STAR2', indname => 'F_CONFIG_P

ROD_INST_FK1'

, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

create index NSESTAR.IDX$$_17F5F0004 on NSESTAR.D_DATES("FISCAL_YEAR","FISCAL_WE

EK_NUMBER_IN_YEAR","DATE_DTT");

How It Works
The SCRIPT_TUNING_TASK function generates the SQL to implement the advice recommended by the Automatic SQL Tuning job. If the tuning task doesn’t have any advice to give, then there won’t be any SQL statements generated in the output. SYS_AUTO_SQL_TUNING_TASK is the default name of the Automatic SQL Tuning task. If you’re unsure of the details regarding this task, then query the DBA_ADVISOR_LOG view

select task_name, execution_start from dba_advisor_log

where task_name='SYS_AUTO_SQL_TUNING_TASK'

order by 2;

Here’s some sample output for this example:

TASK_NAME                      EXECUTION

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

SYS_AUTO_SQL_TUNING_TASK       19-APR-11

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.