2 SQL Performance Analyzer

Capturing a SQL Workload Using the Enterprise Manager

To capture a SQL workload into a SQL tuning set using Oracle Enterprise Manager:

1. On the Database Performance page, under Additional Monitoring Links, click SQL Tuning Sets.

   The SQL Tuning Sets page appears. Existing SQL tuning sets are displayed on this page.

2. Click Create.

   The Create SQL Tuning Set: Options page appears.

3. On the Create SQL Tuning Set: Options page:

   1. In the SQL Tuning Set Name field, enter a name for the SQL tuning set.

   2. Optionally, in the Description field, enter a description of the SQL tuning set.

   3. Click Next.

   The Create SQL Tuning Set: Load Methods page appears.

4. On the Create SQL Tuning Set: Load Methods page, select a load method to collect and load SQL statements into the SQL tuning set:

   • You can load active and running SQL statements from the cursor cache into the SQL tuning set incrementally over a specified period of time.

     To incrementally capture active SQL statements from the cursor cache, select Incrementally capture active SQL statements over a period of time from the cursor cache.

     In the Duration field, specify the duration within which the SQL statement will collected.

     In the Frequency field, specify the frequency over which the active SQL statements will be collected repeatedly from the cursor cache.

   • To load SQL statements from another data source, select Load SQL statements one time only.

     Select the desired data source:

     • Cursor cache

       You can load SQL statements from the cursor cache into the SQL tuning set. However, because only current and recent SQL statements are stored in the SQL cache, collecting these SQL statements only once may result in a SQL tuning set this is not representative of the entire workload on your database.

       To use SQL statements from the cursor cache, from the Data Source list, select Cursor Cache.

     • AWR snapshots

       You can load SQL statements captured in AWR snapshots. This is useful when you want to collect SQL statements for specific snapshot periods of interest.

       To use SQL statements captured in AWR snapshots, from the Data Source list, select AWR Snapshots.

       From the AWR Snapshots list, select the period containing the AWR snapshots you want to include. You can choose to include only AWR snapshots captured in the last day, week, or month. Alternatively, you can choose to include all AWR snapshots.

     • Preserved snapshot sets

       You can load SQL statements captured in preserved snapshot sets. This is useful when you want to collect SQL statements from previously preserved snapshots that are representative of a time period during known performance levels.

       To use SQL statements captured in preserved snapshot sets, from the Data Source list, select Preserved Snapshot Sets.

       In the Preserved Snapshot Sets field, enter the name of the preserved snapshot set containing the AWR snapshots you want to include, or click the Search icon to select it from the Search and Select: Preserved Snapshot Sets window.

     • User-defined workload

       You can load SQL statements into a SQL tuning set by importing SQL statements from a table or view. This is useful if the workload you want to use is not currently running on the database or captured in existing AWR snapshots or preserved snapshot sets. There are no restrictions on which schema the workload resides in, the name of the table, or the number of tables that you can define. The only requirement is that the table must have a sql_text column that stores the text of the SQL statements.

       To use SQL statements from a table or view, from the Data Source list, select User-Defined Workload. In the User-Defined Workload field, enter the name of the table or view you want to use, or click the Search icon to select it from the Search and Select: User Defined Workload window.

   Click Next.

   The Create SQL Tuning Set: Filter Options page appears.

5. After the load method is selected, you can apply filters to reduce the scope of the SQL statements found in the SQL tuning set:

   1. On the Create SQL Tuning Set: Filter Options page, specify the values of filter conditions that you want use in the search in the Value column, and an operator or a condition in the Operator column.

      Only the SQL statements that meet all of the specified filter conditions will be added into the SQL tuning set. Unspecified filter values will not be included as filter conditions in the search.

   2. To add filter conditions, under Filter Conditions, select the filter condition you want to add and click Add a Filter or Column.

      After the desired filter conditions have been added, specify their values in the Value column, and an operator or a condition in the Operator column.

   3. To remove any unused filter conditions, click the icon in the Remove column for the corresponding filter condition you want to remove.

   4. Click Next.

      The Create SQL Tuning Set: Schedule page appears.

6. After the filter options are specified for the SQL tuning set, you can schedule and submit a job to collect the SQL statements and load them into the SQL tuning set:

   1. On the Create SQL Tuning Set: Schedule page, under Job Parameters, enter a name in the Job Name field if you do not want to use the system-generated job name.

   2. In the Description field, enter a description of the job.

   3. Under Scheduling, select Immediately to run the job immediately after it has been submitted, or Later to run the job at a later time as specified using the Time Zone, Date, and Time fields.

   4. Click Next.

      The Create SQL Tuning Set: Review page appears.

7. Review the SQL tuning set options that you have selected.

   To view the SQL statements used by the job, expand Show SQL.

8. Click Submit.

   The SQL Tuning Sets page appears.

   If the job was scheduled to run immediately, a message is displayed to inform you that the job and the SQL tuning set was created successfully. If the job was scheduled to run at a later time, a message is displayed to inform you that the job was created successfully.

Capturing a SQL Workload Using APIs

To capture a SQL workload into a SQL tuning set using APIs:

1. Use the DBMS_SQLTUNE.CREATE_SQLSET procedure to create an empty SQL tuning set in the database.

   For example, the following procedure creates a SQL tuning set named STS_080125:

   BEGIN
     DBMS_SQLTUNE.CREATE_SQLSET(
       sqlset_name => 'STS_080125', 
       description  => 'SQL tuning set for 2008/01/25');
   END;
   /
   

2. Populate the SQL tuning set with selected SQL statements. The data sources for populating a SQL tuning set include the cursor cache, workload repository, or another SQL tuning set.

   In the following example, the CAPTURE_CURSOR_CACHE_SQLSET procedure is used to load STS_080125 with active SQL statements incrementally captured from the cursor cache every 5 minutes over a one-hour period.

   EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( -
       sqlset_name => 'STS_080125', -
       time_limit => 3600, -
       repeat_interval  => 300);
   

Exporting a SQL Workload Using Enterprise Manager

To transport a SQL workload using Enterprise Manager, you need to first export the SQL tuning set(s) containing the SQL workload from the production system where it was captured, then import the SQL tuning set(s) into another system where it can be analyzed by SQL Performance Analyzer.

To export a SQL tuning set:

1. On the Database Home page, under Related Links, click Advisor Central.

   The Advisor Central page appears.

2. Under Advisors, click SQL Tuning Advisor.

   The SQL Tuning Advisor Links page appears.

3. Click SQL Tuning Sets.

   The SQL Tuning Sets page appears. Existing SQL tuning sets are displayed on this page.

4. Select the SQL tuning set you want to export and click Export.

   The Export SQL Tuning Set page appears.

5. In the Directory Object field, select a directory where the export file will be created.

   For example, to use the Data Pump directory, select DATA_PUMP_DIR. The Directory Name field refreshes automatically to indicate the selected directory.

6. In the Export File field, enter a name for the dump file that will be exported.

   Alternatively, you can accept the name generated by the system.

7. In the Log File field, enter a name for the log file for the export operation.

   Alternatively, you can accept the name generated by the system.

8. Select a tablespace to temporarily store the data for the export operation.

   By default, SYSAUX is used.

9. In the Job Name field, enter a name for the job.

   Alternatively, you can accept the name generated by the system.

10. Under Schedule, select:

    • Immediately to run the job immediately after it has been submitted.

    • Later to run the job at a later time as specified by selecting or entering values in the Time Zone, Date, and Time fields.

11. Click OK.

    The SQL Tuning Sets page appears.

    A confirmation message is displayed to indicate that the job was successfully created.

12. Transport the export file to another system using the mechanism of choice (such as Data Pump or database link).

Transporting a SQL Workload Using APIs

To transport a SQL workload using APIs, you need to first move the SQL tuning set(s) containing the SQL workload from the production system where it was captured to a staging table, then export the SQL tuning set(s) from the staging table into another system where it can be analyzed by SQL Performance Analyzer.

To transport a SQL Tuning Set:

1. Use the CREATE_STGTAB_SQLSET procedure to create a staging table where the SQL tuning sets will be exported.

   The following example shows how to create a staging table named staging_table. Table names are case-sensitive.

   BEGIN
     DBMS_SQLTUNE.CREATE_STGTAB_SQLSET( table_name => 'staging_table' );
   END;
   /
   

2. Use the PACK_STGTAB_SQLSET procedure to export SQL tuning sets into the staging table.

   The following example shows how to export a SQL tuning set named STS_080125 to the staging table.

   BEGIN
     DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
         sqlset_name  => 'STS_080125',
         staging_table_name => 'staging_table');
   END;
   /
   

3. Move the staging table to the system where the SQL tuning sets will be imported using the mechanism of choice (such as datapump or database link).

4. On the system where the SQL Tuning Sets will be imported, use the UNPACK_STGTAB_SQLSET procedure to import SQL Tuning Sets from the staging table.

   The following example shows how to import SQL Tuning Sets contained in the staging table.

   BEGIN
     DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
         sqlset_name  => '%',
         replace  => TRUE,
         staging_table_name => 'staging_table');
   END;
   /