A SQL Tuning Set (STS) in Oracle is a database object that includes one or more SQL statements along with their execution statistics and context. It provides a means to store SQL statements for future use, such as input to the SQL Tuning Advisor or SQL Access Advisor, capturing workloads to move them from one database to another, or just as a general repository of SQL statements for performance investigations. The SQL statements can be loaded into a SQL Tuning Set from different SQL sources, such as the Automatic Workload Repository, the cursor cache, or custom SQL provided by the user.
A SQL Tuning Set contains:
- The set of SQL statements
- Associated execution context, such as user schema, application module name and action, and the list of bind values
- Associated basic execution statistics, like elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, etc.
STSs are created, manipulated and managed using the DBMS_SQLTUNE package
How to create the SQL Tuning Set
You can create an STS using the CREATE_SQLSET procedure of the DBMS_SQLTUNE package.
BEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'sql_tuning_set_1', description => 'tuning workload'); END; /
where sql_tuning_set_1 is the name of the STS in the database and ‘tuning workload’ is the description assigned to the STS.
How to perform loading in the SQL Tuning set
SQL statements can be loaded into an STS from different sources like the cursor cache or AWR using the LOAD_SQLSET procedure of the DBMS_SQLTUNE package.
Let’s see an example of loading from AWR from the Baseline
DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN baseline_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
'IO baseline', -- Base line Name
NULL,-- basic_filter
NULL,-- object_filter
'elapsed_time',-- ranking_measure1
NULL,-- ranking_measure2
NULL, -- ranking_measure3
NULL,-- result_percentage
30 -- result_limit
)) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name => 'sql_tuning_set_1',
populate_cursor => baseline_cursor);
END;
/
Another example would be the AWR snapshot
DECLARE baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR; BEGIN OPEN baseline_cursor FOR SELECT VALUE(p) FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( 1116, -- begin_snap 1119, -- end_snap NULL, -- basic_filter NULL, -- object_filter elapsed_time, -- ranking_measure1 NULL, -- ranking_measure2 NULL, -- ranking_measure3 NULL, -- result_percentage 30) -- result_limit )) p; DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name => 'sql_tuning_set_1', populate_cursor => baseline_cursor); END; /
Displaying the contents of a SQL Tuning Set
The SELECT_SQLSET table function reads the contents of the STS. After an STS has been created and populated, you can browse the SQL in the STS using different filtering criteria. The SELECT_SQLSET procedure is provided for this purpose.
SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(
'sql_tuning_set_1',
'(disk_reads/buffer_gets) >= 0.75'));
Dropping a SQL Tuning Set
The DROP_SQLSET procedure is used to drop an STS that is no longer needed. For example:
BEGIN
DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'sql_tuning_set_1' );
END;
/
Remember, managing the performance of SQL statements involves more than just collecting them. It’s about understanding their execution plans, identifying performance issues, and then tuning them to improve performance. The use of STSs and tools like the SQL Tuning Advisor can be very helpful in this process.
I hope you find this article on SQL Tuning Set in Oracle Useful. Please do provide the feedback
Related Articles
create sql baseline in oracle 19c : check out how to create sql baseline in Oracle 19c using cursor cache, AWR, STS . How to check if the sql baseline got created
Autotrace in oracle :Autotrace in oracle , What is autotrace,how to setup it,Understanding Autotrace Output, Autotrace options, statistics displayed by autotrace
STATISTICS_LEVEL in Oracle : check in this post STATISTICS_LEVEL in Oracle,How to get the activation level setting for each value,How to set this parameter