Move SQL Tuning Sets between Oracle instances

by Bob Watkins | Feb 27, 2007 8:00:00 AM

Tags: Programming languages, Databases, Bob Watkins, SQL, SQL Tuning Set, Oracle Corp., DBMS_SQLTUNE, Oracle Tips Newsletter

Takeaway: 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.

SQL Tuning Sets (STS) are 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 analyzed using DBMS_SQLTUNE or Enterprise Manager.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.

Print/View all Posts Comments on this article

SQL Tuning Sets Need Tuning Pack LicenseRSchofield@...  | 02/28/07

Absolutely rightbwatkins  | 02/28/07