Move SQL adjustment tool set between Oracle instances

SQL Tuning Set (STS) is an integral part of Oracle's 10 Gb SQL Tuning Advisor feature. Each adjustment tool set contains one or more SQL statements and the context information required to correctly interpret them. SQL Tuning Advisor uses an adjustment tool set as the input, checks the statements and provides optimization suggestions for them.

Since there is no way to adjust the toolset between different instances, the initial version of Oracle 10 Gb requires that this analysis be performed on the same machine with the SQL Tuning Set. This increases the management overhead of the production system and requires higher privileges for developers in production instances than in normal cases.

Oracle 10g Version 2 (release 2) added the mobile SQL Tuning Set function. When using a transit table and the DBMS_SQLTUNE class, you can output them from the instance that created the SQL Tuning Set and input them to a test instance for analysis.

The article Oracle database performance adjustment Guide details the conversion process. To put it simply, this process includes the following steps:

Create one or more SQL Tuning sets. STS can be created in Oracle Enterprise Manager through an existing AWR snapshot, a saved snapshot set, or a defined SQL historical period. They can also be manually created using the process in DBMS_SQLTUNE.

Use the PAC_STGTAB_SQLSET process in DBMS_SQLTUNE to create a transit table and save the STS to be transmitted.

Use the PAC_STGTAB_SQLSET process to load a transit table containing an existing adjustment tool set.

Move the intermediate table like moving other tables (such as output and input through Datapump.

In the target system, use the UNPACK_STGTAB_SQLSET process to import the adjustment tool to the system. Then you can use DBMS_SQLTUNE or Enterprise Manager to analyze them.

Bob Watkins (OCP, mcba, MCSE, and MCT) is a computer professional with 25 years of experience and has worked as a technical trainer, consultant, and database administrator.

Note: For more exciting articles, please follow the help houseProgramming TutorialTopic.