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.

Database Jumping This was published in Database Jumping, check every Thursday for more stories

Leave a comment

You must read and type the 6 chars within 0..9 and A..F

* indicates mandatory fields.

Log in


Sign up | Forgot your password?

Blogs

RSS feed

  • Staff Aussies to pay more for Win 7

    If you are looking to make some money in these troubled times, perhaps importing copies of Windows 7 could be for you. Read more »

    -- posted by Staff

  • Staff Firefox: Greens want it, 3.5rc2 not up to par

    This week's roundup looks at the situation surrounding a campaign to change Outlook HTML renderer, a Greens MP wants to install Firefox but is restricted and all the photos from the iPhone 3GS launch. Read more »

    -- posted by Staff

  • Chris Duckett Microsoft misses the Outlook point

    Ask designers which mail program is the bane of their existence, and you'll find that Outlook tops the list. The reason why the most popular email reader is also the most painful is simple: it uses Word to render HTML emails. Read more »

    -- posted by Chris Duckett

What's on?