Move SQL Tuning Sets between Oracle instances

SQL Tuning Sets (STS) capture the workload of an Oracle instance. Starting in Oracle 10g Release 2, these tuning sets can be transported from one instance to another to facilitate testing.

STS is a part of Oracle 10g's SQL Tuning Advisor feature. Each tuning set contains one or more SQL statements, plus the context information needed to interpret them correctly. A tuning set is used as input to the SQL Tuning Advisor, which examines the statements and makes recommendations for improving them.

The initial release of Oracle 10g required this analysis to be performed on the same machine that captured the SQL Tuning Set, because there was no way to move the tuning set to a different instance. This added overhead to the production system, and required giving developers more privileges in the production instance than they normally would have.

The ability to move SQL Tuning Sets has been added in Oracle 10g Release 2. By using a staging table and the procedures in the package DBMS_SQLTUNE, you can export SQL Tuning Sets from the instance in which they were created and import them into a test instance for analysis.

The actual procedure is documented in the Oracle Database Performance Tuning Guide. Briefly, the procedure consists of the following steps:

1. Create one or more SQL Tuning Sets. STS can be created in Oracle Enterprise Manager from existing AWR snapshots, preserved snapshot sets, or a defined period of historical SQL. They can also be created manually using procedures in DBMS_SQLTUNE.
2. Use the CREATE_STGTAB_SQLSET procedure in DBMS_SQLTUNE to create a staging table to hold the STS that will be transported.
3. Use the PACK_STGTAB_SQLSET procedure to load the staging table with existing tuning sets.
4. Move the staging table the same as you would any other table, such as via Datapump export and import.
5. On the destination system, use the UNPACK_STGTAB_SQLSET procedure to import the tuning sets into the system. They can then be analysed using DBMS_SQLTUNE or Enterprise Manager.