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