Before You Begin

This tutorial shows you how to create a SQL tuning set and load it with a SQL workload. The tutorial takes approximately 10 minutes to complete and then the load operation takes one hour to complete.

Background

A SQL tuning set (STS) is a database object that you can use as input to tuning tools. It contains a SQL workload, which is a set of SQL statements that were executed over a period of time on a particular database. The workload also contains metadata describing the context in which the SQL statements were executed and how they performed. After creating a SQL tuning set on one database, you can transport it to another database, execute the same workload, and compare workload performance on the two databases.

What Do You Need?

• An Oracle database
• The SYSDBA administrative privilege

Create a SQL Tuning Set

1. Use SQL*Plus to log in to the database as a user who has the SYSDBA administrative privilege.
   

   $ sqlplus / as sysdba

2. Create SQL tuning set MY_WORKLOAD_STS.

   SQL> BEGIN
     2  DBMS_SQLTUNE.CREATE_SQLSET(
     3  sqlset_name => 'MY_WORKLOAD_STS');
     4  END;
     5  /

Load the SQL Tuning Set

1. Load the SQL tuning set with a workload by polling the cursor cache every 60 seconds for 3600 seconds (1 hour).

   SQL> BEGIN
     2  DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
     3  sqlset_name     => 'MY_WORKLOAD_STS',
     4  time_limit      => 3600,
     5  repeat_interval => 60);
     6  END;
     7  /

2. Wait one hour for the load operation to complete.

Want to Learn More?

• Managing SQL Tuning Sets