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.