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 3rd
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 4th : 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.
