Capturing a Database Workload Using Enterprise Manager

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.

Description of the illustration dbr.gif

From the Database Replay page, click the Captured Workloads tab, then click Create in the toolbar.

The Create Capture: Plan Environment page appears.

Description of the illustration dbr_capture_plan_env.gif

Verify that you have met both prerequisites described on this page, then enable both checkboxes and click Next.

The Create Capture: Database page appears.

Description of the illustration dbr_capture_databases.gif

Click Add.

The Add pop-up appears.

Provide a capture name, provide an optional description, then click the Target Database search icon.

The Search and Select: Targets pop-up appears.

Description of the illustration dbr_capture_select_db.gif

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.

Description of the illustration dbr_capture_add_db.gif

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.

Click Next.

The Create Capture: Options page appears.

Description of the illustration dbr_capture_options.gif

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.

Description of the illustration dbr_capture_storage.gif

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.

Provide Host Credentials, click Browse to select a Storage Location, select the location and click OK, then click Next.

The Create Capture: Schedule page appears.

Description of the illustration dbr_capture_schedule.gif

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.

Description of the illustration dbr_capture_review.gif

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."

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.