10.7 Capturing a Database Workload Using Enterprise Manager

This section describes how to capture a database workload using Enterprise Manager. The primary tool for capturing database workloads is Oracle Enterprise Manager.

Tip:

If Oracle Enterprise Manager is unavailable, you can capture database workloads using APIs, as described in "Capturing a Database Workload Using APIs".

To capture a database workload using Enterprise Manager:

  1. From the Enterprise menu of the Enterprise Manager Cloud Control console, select Quality Management, then Database Replay.

    If the Database Login page appears, log in as a user with administrator privileges.

    The Database Replay page appears.

  2. From the Database Replay page, click the Captured Workloads tab, then click Create in the toolbar.
  3. Verify that you have met both prerequisites described on this page, then enable both checkboxes and click Next.

    The Create Capture: Database page appears.

    Tip:

    For information about the prerequisites, see "Prerequisites for Capturing a Database Workload".

  4. Click Add.

    The Add pop-up appears.

  5. Provide a capture name, provide an optional description, then click the Target Database search icon.
  6. Select a Target Type, optionally provide a configuration search, choose a target database from the list, then click Select.

    The Add pop-up reappears with added sections for Database Credential and Database Host Credential.

  7. Provide database credentials, database host credentials, a Database Capture Intermediate Storage Location, then click OK.
    • After the Capture, the files are copied to the storage location, unless you use the intermediate storage location as the final storage location.

      Note:

      For captures on an Oracle RAC database, Enterprise Manager only supports Oracle RAC configured with a shared file system.

    The selected target database now appears in the list of databases in the Select Production Databases table.

  8. Click Next.
  9. Select the workload capture options:
    • Under the SQL Performance Analyzer section, select whether to capture SQL statements into a SQL tuning set during workload capture.

      While Database Replay provides an analysis of how a change affects your entire system, you can use a SQL tuning set in conjunction with the SQL Performance Analyzer to gain a more SQL-centric analysis of how the change affects SQL statements and execution plans.

      By capturing a SQL tuning set during workload capture and another SQL tuning set during workload replay, you can use the SQL Performance Analyzer to compare these SQL tuning sets to each other without having to re-execute the SQL statements. This enables you to obtain a SQL Performance Analyzer report and compare the SQL performance, before and after changes, while running Database Replay.

      Note:

      Capturing SQL statements into a SQL Tuning Set is the default, and is the recommended workload capture option. Capturing SQL statements into a SQL Tuning Set is not available for Oracle RAC.

      Tip:

      For information about comparing SQL tuning sets using SQL Performance Analyzer reports, see "Generating SQL Performance Analyzer Reports Using APIs".

    • Under the Workload Filters section, select whether to use exclusion filters by selecting Exclusion in the Filter Mode list, or inclusion filters by selecting Inclusion in the Filter Mode list.

      To add filters, click Add and enter the filter name, session attribute, and value in the corresponding fields.

      Tip:

      For more information, see "Using Filters with Workload Capture".

    After selecting the desired workload capture options, click Next.

    The Create Capture: Storage page appears.

  10. Click the Storage Host icon, choose a target from the list, then click Select.

    The Storage page now requests Host Credentials and a Storage Location.

  11. Provide Host Credentials, click Browse to select a Storage Location, select the location and click OK, then click Next.
  12. Schedule the starting time and duration for the capture, schedule the exporting of AWR data, then click Next.
    • The default capture duration is 5 minutes. Change the capture duration to capture representative activity over a time period of interest that needs to be tested.

    The Create Capture: Review page appears.

  13. If all of the parameters appear as you have intended, click Submit to start the capture job.
    • The "Capture SQL statements into a SQL Tuning Set during workload capture" option is enabled by default. Uncheck this option if you do not want to compare SQL tuning sets at the end of the Replay.

    The Database Replay page reappears, displays a message that the capture was created successfully, and displays the status of the capture in the Captures list, such as "Scheduled."

  14. For detailed information about the capture, double-click the name of the capture.

    The Capture Summary page appears, and displays several attributes, including the average active sessions, a workload comparison, and related concurrent captures, if any.

Tip:

After capturing a workload on the production system, you need to preprocess the captured workload, as described in Preprocessing a Database Workload .