SQL Tuning Set

SQL tuning set (STS) is a database object that contains one or more SQL statements and the  associated execution statistics. You can populate a SQL tuning set from multiple sources, such as SQL recorded in the AWR and SQL in memory, or you can provide specific SQL statements. It’s critical that you be familiar with SQL tuning sets. This feature is used as an input to several of Oracle’s performance tuning and management tools, such as the SQL Tuning Advisor, SQL Plan Management, SQL Access Advisor, and SQL Performance Advisor. The key to understanding is that a SQL tuning set consists of the following:

• One or more SQL statements

• Associated metrics/statistics for each SQL statement

Creating a SQL tuning set:

BEGIN

DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'HIGH_IO', description => 'High disk read tuning set');

 END; /

To check the information you can use the below views:

select id, name, created, statement_count from dba_sqlset;

To check the statements/query associated with a sqltune set

SELECT sqlset_name, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text FROM dba_sqlset_statements;

Drop a sqlset:

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

Delete specific statements from the sqlset:

BEGIN

DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name => 'IO_STS',basic_filter => 'disk_reads < 2000000');

END;/

Delete complete sqlset:

exec DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name => 'IO_STS');

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.

4. Load the sqltune set with the SQL found in step 3^rd

Step 1: Create a SQL Tuning Set Object

Create 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 IDs

If 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 and 4^th : Populate the SQL Tuning Set with High-Resource SQL Found in AWR and load them in the sqlset

Now 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.

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

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY

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. Basic filter ==> SQL predicate to filter SQL statements from workload; and ranking_measure==> Order by clause on selected SQL statement(s), such as elapsed_time, cpu_time, buffer_gets, disk_reads .result_limit to limit the number of rows returned as output.

SELECT  sql_id ,substr(sql_text,1,20),disk_reads ,cpu_time,elapsed_time

FROM

 table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(

begin_snap => 21730 ,

end_snap => 22900

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

,ranking_measure1 => 'disk_reads' ,

result_limit => 10)

)

ORDER BY disk_reads DESC;

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

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

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.

The DBMS_SQLTUNE.SELECT_CURSOR_CACHE function 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.

use 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

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 SQL Tuning Set with All SQL in Memory

Solution

Use the DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET procedure to efficiently capture all of the SQL

currently stored in the cursor cache (in memory). This example creates a SQL tuning set named PROD_WORKLOAD and then populates by sampling memory for 3,600 seconds (waiting 20 seconds between each polling event):

BEGIN

-- Create the tuning set

DBMS_SQLTUNE.CREATE_SQLSET(

sqlset_name => 'PROD_WORKLOAD'

,description => 'Prod workload sample');

--

DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(

sqlset_name => 'PROD_WORKLOAD'

,time_limit => 3600

,repeat_interval => 20);

END;

/

The DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET procedure allows you to poll for queries and memory

and use any queries found to populate a SQL tuning set. This is a powerful technique that you can use when it’s required to capture a sample set of all SQL statements executing.

You have a great deal of flexibility on instructing DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET to capture SQL statements in memory (see Table 11-7 for details on all parameters). For example, you can instruct the procedure to capture a cumulative set of statistics for each SQL statement by specifying a

CAPTURE_MODE of DBMS_SQLTUNE.MODE_ACCUMULATE_STATS.

BEGIN

DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(

sqlset_name => 'PROD_WORKLOAD'

,time_limit => 60

,repeat_interval => 10

,capture_mode => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS);

END;

/

This is more resource-intensive than the default settings, but produces more accurate statistics for each SQL statement.

Transport SQL Tuning Set from Production to TEST Environment:

You’ve identified some resource-intensive SQL statements in a production environment. You want to transport these statements and associated statistics to a test environment, where you can tune the statements without impacting production.

Solution:

The following steps are used to copy a SQL tuning set from one database to another:

1. Create a staging table in source database.

2. Populate the staging table with STS data.

3. Copy the staging table to the destination database.

4. Unpack the staging table in the destination database.

Step 1: Create a Staging Table in the Source Database

Use the DBMS_SQLTUNE.CREATE_STGTAB_SQLSET procedure to create a table that will be used to contain the

SQL tuning set metadata. This example creates a table named STS_TABLE:

BEGIN

dbms_sqltune.create_stgtab_sqlset(

table_name => 'STS_TABLE'

,schema_name => 'MV_MAINT');

END;

/

Step 2: Populate Staging Table with STS Data

Now populate the staging table with STS metadata using DBMS_SQLTUNE.PACK_STGTAB_SQLSET:

BEGIN

dbms_sqltune.pack_stgtab_sqlset(

sqlset_name => 'IO_STS'

,sqlset_owner => 'SYS'

,staging_table_name => 'STS_TABLE'

,staging_schema_owner => 'MV_MAINT');

END;

/

If you’re unsure of the names of the STS you want to transport, run the following query to get the details:

SELECT name, owner, created, statement_count FROM dba_sqlset;

Step 3: Copy the Staging Table to the Destination Database

You can copy the table from one database to the other via Data Pump, the old exp/imp utilities, or by using a database link. This example creates a database link in the destination database and then copies the table from the source database:

create database link source_db connect to mv_maint identified by foo using 'source_db';

In the destination database, the table can be copied directly from the source with the CREATE TABLE AS SELECT statement:

SQL> create table STS_TABLE as select * from STS_TABLE@source_db;

Step 4: Unpack the Staging Table in the Destination Database

Use the DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET procedure to take the contents of the staging table and populate the data dictionary with the SQL tuning set metadata. This example unpacks all SQL tuning sets contained within the staging table:

BEGIN

DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(

sqlset_name => '%'

,replace => TRUE

,staging_table_name => 'STS_TABLE');

END;

/

How It Works

A SQL tuning set consists of one or more queries and corresponding execution statistics. You will occasionally have a need to copy a SQL tuning set from one database to another. For example, you might be having performance problems with a production database but want to capture and move the top resource-consuming statements to a test database where you can diagnose the SQL (within the STS) without impacting production.

Keep in mind that an STS can be used as input for any of the following tools:

·         SQL Tuning Advisor

·         SQL Access Advisor

·         SQL Plan Management

·         SQL Performance Analyzer

The prior tools are used extensively to troubleshoot and test SQL performance. Transporting a SQL tuning set from one environment to another allows you to use these tools in a testing or development environment.