Moving the SQL Tuning toolset between Oracle instances

The SQL Tuning toolset (SQL Tuning set,sts) is an integral part of the Oracle 10g SQL Tuning Advisor feature. Each tuning toolset contains one or several SQL statements, as well as the contextual information needed to properly interpret them. SQL Tuning Advisor uses an adjustment toolset as input, examines the statements in it, and presents optimization suggestions for them.

Because there is no way to move the tuning toolset between different instances, the initial version of Oracle 10g requires that this analysis be performed on the same machine with SQL tuning set. This increases the management overhead of the production system and requires a higher privilege than normal for the developer in the production instance.

The ability to move SQL tuning set is added to the Oracle 10g second Edition (Release 2). Using the procedures in a staging table and Dbms_sqltune class, you can output them from an instance of creating a SQL tuning set, and then enter them into a test instance for analysis.

This conversion process is described in detail in the Oracle Database Performance Tuning Guide. In simple terms, the process includes the following steps:

• Set up one or several SQL tuning sets. STS can be created in Oracle Enterprise Manager through an existing awr snapshot, saved snapshot set, or a defined SQL history period. They can also be created manually using the procedures in Dbms_sqltune.
• Use the Pac_stgtab_sqlset procedure in Dbms_sqltune to create a staging table that holds the STS that will be transferred.
• Use the Pac_stgtab_sqlset procedure to load a staging table that contains an existing tuning toolset.
• Move the staging table like the other tables (such as through datapump output and input).
• In the target system, use the Unpack_stgtab_sqlset procedure to enter the tuning toolset into the system. They can then be parsed using Dbms_sqltune or Enterprise Manager.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience and has been involved in technical trainers, consultants and database administrators.