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
How to create the SQL Tuning Set
You can create an STS using the
CREATE_SQLSET procedure of the
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
Let’s see an example of loading from AWR from 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 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(
'(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:
DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => 'sql_tuning_set_1' );
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.
Leave a Reply