Automated SQL Tuning: Creating a SQL Tuning Set Object

Problem
You’re working on a performance issue that requires that you analyze a group of SQL statements. Before you process the SQL statements as a set, you need to create a SQL tuning set object.
Solution
Use the DBMS_SQLTUNE.CREATE_SQLSET procedure to create a SQL tuning set object—for example:

BEGIN

DBMS_SQLTUNE.CREATE_SQLSET(

sqlset_name => 'HIGH_IO',

description => 'High disk read tuning set');

END;

/

The prior code creates a tuning set with the name of HIGH_IO. At this point, you have created a named tuning set object. The tuning set does not contain any SQL statements.

How It Works
A SQL tuning set object must be created before populating a tuning set with SQL statements (see Recipes 11-9 through 11-11 for details on adding SQL statements to an STS). You can view any defined SQL tuning sets in the database by querying the DBA_SQLSET view:

SQL> select id, name, created, statement_count from dba_sqlset;

Here is some sample output:

ID                                   NAME   CREATED STATEMENT_COUNT

---------- ------------------------------ --------- ---------------

5                                 HIGH_IO 26-APR-11               0

If you need to drop a SQL tuning set object, then use the DBMS_SQLTUNE.DROP_SQLSET procedure to drop a tuning set. The following example drops a tuning set named MY_TUNING_SET:

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