Moving the SQL Tuning toolset between Oracle instances

Source: Internet
Author: User
Tags requires oracle database

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.

        Note : More wonderful articles please pay attention to the triple programming Tutorial column.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.