Skip Headers
Oracle® Database Real Application Testing User's Guide
11g Release 2 (11.2)

Part Number E16540-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

9 Capturing a Database Workload

This chapter describes how to capture a database workload on the production system. The first step in using Database Replay is to capture the production workload. For more information about how capturing a database workload fits within the Database Replay architecture, see "Workload Capture".

This chapter contains the following sections:

9.1 Prerequisites for Capturing a Database Workload

Before starting a workload capture, you should have a strategy in place to restore the database on the test system. Before a workload can be replayed, the logical state of the application data on the replay system should be similar to that of the capture system when replay begins. To accomplish this, consider using one of the following methods:

This will allow you to restore the database on the replay system to the application state as of the workload capture start time.

See Also:

9.2 Workload Capture Options

Proper planning before workload capture is required to ensure that the capture will be accurate and useful when replayed in another environment.

Before capturing a database workload, carefully consider the following options:

9.2.1 Restarting the Database

While this step is not required, Oracle recommends that the database be restarted before capturing the workload to ensure that ongoing and dependent transactions are allowed to be completed or rolled back before the capture begins. If the database is not restarted before the capture begins, transactions that are in progress or have yet to be committed will not be fully captured in the workload. Ongoing transactions will thus not be replayed properly, because only the part of the transaction whose calls were captured will be replayed. This may result in undesired replay divergence when the workload is replayed. Any subsequent transactions with dependencies on the incomplete transactions may also generate errors during replay. On a busy system, it is normal to see some replay divergence, but the replay can still be used to perform meaningful analysis of a system change if the diverged calls do not make up a significant portion of the replay in terms of DB time and other such key attributes.

Before restarting the database, determine an appropriate time to shut down the production database before the workload capture when it is the least disruptive. For example, you may want to capture a workload that begins at 8:00 a.m. However, to avoid service interruption during normal business hours, you may not want to restart the database during this time. In this case, you should consider starting the workload capture at an earlier time, so that the database can be restarted at a time that is less disruptive.

Once the database is restarted, it is important to start the workload capture before any user sessions reconnect and start issuing any workload. Otherwise, transactions performed by these user sessions will not be replayed properly in subsequent database replays, because only the part of the transaction whose calls were executed after the workload capture is started will be replayed. To avoid this problem, consider restarting the database in RESTRICTED mode using STARTUP RESTRICT, which will only allow the SYS user to login and start the workload capture. By default, once the workload capture begins, any database instance that are in RESTRICTED mode will automatically switch to UNRESTRICTED mode, and normal operations can continue while the workload is being captured.

Only one workload capture can be performed at any given time. If you have a Oracle Real Application Clusters (Oracle RAC) configuration, workload capture is performed for the entire database. To enable a clean state before starting to capture the workload, all the instances need to be restarted.

To restart all instances in a Oracle RAC configuration before workload capture:

  1. Shut down all the instances.

  2. Restart one of the instances.

  3. Start workload capture.

  4. Restart the rest of the instances.

See Also:

9.2.2 Using Filters with Workload Capture

By default, all user sessions are recorded during workload capture. You can use workload filters to specify which user sessions to include in or exclude from the workload during workload capture. There are two types of workload filters: inclusion filters and exclusion filters. You can use either inclusion filters or exclusion filters in a workload capture, but not both.

Inclusion filters enable you to specify user sessions that will be captured in the workload. This is useful if you want to capture only a subset of the database workload.

Exclusion filters enable you to specify user sessions that will not be captured in the workload. This is useful if you want to filter out session types that do not need to captured in the workload, such as those that monitor the infrastructure—like Oracle Enterprise Manager (EM) or Statspack—or other such processes that are already running on the test system. For example, if the system where the workload will be replayed is running EM, replaying captured EM sessions on the system will result in duplication of workload. In this case, you may want to use exclusion filters to filter out EM sessions.

9.2.3 Setting Up the Capture Directory

Determine the location and set up a directory where the captured workload will be stored. Before starting the workload capture, ensure that the directory is empty and has ample disk space to store the workload. If the directory runs out of disk space during a workload capture, the capture will stop. To estimate the amount of disk space that is required, you can run a test capture on your workload for a short duration (such as a few minutes) to extrapolate how much space you will need for a full capture. To avoid potential performance issues, you should also ensure that the target replay directory is mounted on a separate file system.

For Oracle RAC, consider using a shared file system. Alternatively, you can set up one capture directory path that resolves to separate physical directories on each instance, but you will need to consolidate the files created in each of these directories into a single directory. The entire content of the local capture directories on each instance (not only the capture files) must be copied to the shared directory before it can be used for preprocessing or data masking. For example, assume that you are:

  • Running a Oracle RAC environment in Linux with two database instances named host1 and host2

  • Using a capture directory object named CAPDIR that resolves to /$ORACLE_HOME/rdbms/capture on both instances

  • Using a shared directory that resides in /nfs/rac_capture

You will need to login into each host and run the following command:

cp -r /$ORACLE_HOME/rdbms/capture/* /nfs/rac_capture

After this is done for both instances, the /nfs/rac_capture shared directory is ready to preprocessed or masked.

9.3 Workload Capture Restrictions

The following types of client requests are not captured in a workload:

9.4 Enabling and Disabling the Workload Capture Feature

Oracle Database 10g Release 2 supports using Database Replay to capture a database workload that can be used to test database upgrades to Oracle Database 11g and subsequent releases. To use this feature, it must be enabled on the capture system running Oracle Database 10g Release 2 before a workload can be captured. By default, the workload capture feature is not enabled in Oracle Database 10g Release 2 (10.2). You can enable or disable this feature by specifying the PRE_11G_ENABLE_CAPTURE initialization parameter.

Note:

It is only necessary to enable the workload capture feature if you are capturing a database workload on a system running Oracle Database 10g Release 2.

If you are capturing a database workload on a system running Oracle Database 11g Release 1 or a later release, it is not necessary to enable the workload capture feature because it is enabled by default. Furthermore, the PRE_11G_ENABLE_CAPTURE initialization parameter is only valid with Oracle Database 10g Release 2 (10.2) and cannot be used with subsequent releases.

To enable the workload capture feature on a system running Oracle Database 10g Release 2, run the wrrenbl.sql script at the SQL prompt:

@$ORACLE_HOME/rdbms/admin/wrrenbl.sql

The wrrenbl.sql script calls the ALTER SYSTEM SQL statement to set the PRE_11G_ENABLE_CAPTURE initialization parameter to TRUE. If a server parameter file (spfile) is being used, the PRE_11G_ENABLE_CAPTURE initialization parameter will be modified for the currently running instance and recorded in the spfile, so that the new setting will persist when the database is restarted. If a spfile is not being used, the PRE_11G_ENABLE_CAPTURE initialization parameter will only be modified for the currently running instance, and the new setting will not persist when the database is restarted. To make the setting persistent without using a spfile, you will need to manually specify the parameter in the initialization parameter file (init.ora).

To disable workload capture, run the wrrdsbl.sql script at the SQL prompt:

@$ORACLE_HOME/rdbms/admin/wrrdsbl.sql

The wrrdsbl.sql script calls the ALTER SYSTEM SQL statement to set the PRE_11G_ENABLE_CAPTURE initialization parameter to FALSE. If a server parameter file (spfile) is being used, the PRE_11G_ENABLE_CAPTURE initialization parameter will be modified for the currently running instance and also recorded in the spfile, so that the new setting will persist when the database is restarted. If a spfile is not being used, the PRE_11G_ENABLE_CAPTURE initialization parameter will only be modified for the currently running instance, and the new setting will not persist when the database is restarted. To make the setting persistent without using a spfile, you will need to manually specify the parameter in the initialization parameter file (init.ora).

Note:

The PRE_11G_ENABLE_CAPTURE initialization parameter can only be used with Oracle Database 10g Release 2 (10.2). This parameter is not valid in subsequent releases. After upgrading the database, you will need to remove the parameter from the server parameter file (spfile) or the initialization parameter file (init.ora); otherwise, the database will fail to start up.

See Also:

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

If for some reason 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. On the Software and Support page, under Real Application Testing, click Database Replay.

    The Database Replay page appears.

    Description of dbr.gif follows
    Description of the illustration dbr.gif

  2. In the Go to Task column, click the icon that corresponds to the Capture Workload task.

    The Capture Workload: Plan Environment page appears.

    Description of dbr_capture_plan_env.gif follows
    Description of the illustration dbr_capture_plan_env.gif

  3. Verify that all prerequisites are met before proceeding.

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

    For each verified prerequisite, check the box in the Acknowledge column. Once all prerequisites are verified, click Next.

    The Capture Workload: Options page appears.

    Description of dbr_capture_options.gif follows
    Description of the illustration dbr_capture_options.gif

  4. Select the workload capture options:

    • Under Database Restart Options, select whether the database will be restarted before workload capture.

      It is recommended that the database be restarted before capturing a workload to enable a clean state for workload capture. Otherwise, potential problems may arise when replaying the workload. For more information, see "Restarting the Database".

    • Under SQL Performance Analyzer, select whether to capture SQL statements into a SQL tuning set during workload capture.

      While Database Replay provide analysis of how a change affects your entire system, you can use a SQL tuning set in conjunction with 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 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 change, while running Database Replay.

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

      Note:

      Capturing SQL statements into a SQL tuning set is the default and recommended workload capture option.
    • Under Workload Filters, 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 Another Row and enter the filter name, session attribute, and value in the corresponding fields. For more information, see "Using Filters with Workload Capture".

    After selecting the desired workload capture options, click Next.

    The Capture Workload: Parameters page appears.

    Description of dbr_capture_params.gif follows
    Description of the illustration dbr_capture_params.gif

  5. Define the parameters for the workload capture:

    • Under Workload Capture Parameters, in the Capture Name field, enter a name for the workload capture. In the Directory Object list, select the directory where the captured workload will be stored. You must select a directory that does not already contain a workload capture. For more information, see "Setting Up the Capture Directory".

      To create a directory object, click Create Directory Object. The Create Directory Object page appears. In the Name field, enter a name for the directory object. In the Path field, enter the path to the directory object. To test if the directory exists in the file system, click Test File System. If the directory does not exist, it will need to be created first.

    • Under Database Shutdown Parameters, select the type of database shutdown method to perform. This option only appears if the database will be restarted before workload capture. The types of available database shutdown methods include:

      • Immediate

        An immediate shutdown will roll back all active transactions and disconnect all connected users before shutting down the database.

      • Transactional

        A transactional shutdown will first complete all active transactions and then disconnect the connected user before shutting down the database.

      • Abort

        An abort shutdown will shut down the database instantaneously by aborting all active transactions.

      • Force the database to shutdown

        A force shutdown will shut down the database if any cluster-managed database services are operational. This option only appears if you are running Oracle RAC.

    • Under Database Startup Parameters, select if the database will restart using the current default server parameter file (spfile) or a specific parameter file (pfile). To select a pfile, enter the fully qualified name for the pfile. This option only appears if the database will be restarted before workload capture.

    After defining the parameters for the workload capture, click Next.

    The Capture Workload: Schedule page appears.

    Description of dbr_capture_schedule.gif follows
    Description of the illustration dbr_capture_schedule.gif

  6. Under Job Parameters, define the parameters for the job:

    • In the Job Name field, enter a name for the job name or accept the system generated name.

    • In the Description field, enter an optional description of the job.

  7. Under Job Schedule, specify a start time and duration for the workload capture:

    • Under Start, select whether the job will run immediately by selecting Immediately, or at a later time by selecting Later and specifying the desired time using the Date and Time fields.

    • Under Capture Duration, specify how long the job will run by selecting Duration and specifying the desired duration using the Hours and Minutes fields. To not specify a capture duration, select Not Specified. If a capture duration is unspecified, the job must be stopped manually.

  8. Under Job Credentials, enter the host and database login credentials:

    • Under Host Credentials, enter the username and password for the host system.

    • Under Database Credentials, enter the username and password for the database that will used for the workload capture. The user needs the DBA privilege in order to restart the database. This section only appears if the database will be restarted before workload capture.

    Click Next.

    The Capture Workload: Review page appears.

    Description of dbr_capture_review.gif follows
    Description of the illustration dbr_capture_review.gif

  9. Review the job settings for the workload capture that have been defined.

    To run the job, click Submit. To make changes, click Back. To cancel the workload capture without saving changes, click Cancel.

  10. Depending on the job settings that have been defined:

    • If the job is scheduled to start immediately and the database will be restarted, the Confirmation: Restart Database page appears.

      To restart the database, click Yes.

      The Information: Restart Database page appears while the database is being restarted. Once the database is restarted, the workload capture begins automatically. Click Refresh.

      The View Workload Capture page appears.

    • If the job is scheduled to start immediately but the database will not be restarted, the workload capture begins automatically and the View Workload Capture page appears.

    • If the job is scheduled to start at a later time, the Database Replay page appears with a confirmation that the job has been successfully created.

    Once workload capture begins, you can monitor the capture process using the View Workload Capture page, as described in "Monitoring Workload Capture Using Enterprise Manager".

Tip:

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

9.6 Monitoring Workload Capture Using Enterprise Manager

This section describes how to monitor workload capture using Enterprise Manager. The primary tool for monitoring workload capture is Oracle Enterprise Manager. Using Enterprise Manager, you can:

If for some reason Oracle Enterprise Manager is unavailable, you can monitor workload capture using views, as described in "Monitoring Workload Capture Using Views".

This section contains the following topics:

9.6.1 Monitoring an Active Workload Capture

This section describes how to monitor an active workload capture using Enterprise Manager.

To monitor an active workload capture:

  1. On the Software and Support page, under Real Application Testing, click Database Replay.

    The Database Replay page appears.

  2. Under Active Capture and Replay, select the workload capture you want to monitor and click View.

    The View Workload Capture page appears.

    Description of dbr_capture_view.gif follows
    Description of the illustration dbr_capture_view.gif

  3. Under Summary, information about the workload capture is displayed.

  4. To view the workload profile, click the Workload Profile tab.

    Under Average Active Sessions, the Active Sessions chart provides a graphic display of the captured session activity compared to the uncaptured session activity (such as background activities or filtered sessions).

    Under Comparison, various statistics for the workload capture are displayed, including database time, average active sessions, user calls, transactions, session logins, and application errors. The statistics for the total session activity are displayed in the Total column, and the statistics for the captured session activity are displayed in the Capture column. The Percentage of Total column displays the percentage of total session activity that are being captured in the workload.

    To view the workload capture report, click View Workload Capture Report.

  5. To view workload filters used by the workload capture, click the Workload Filters tab.

    Details about the workload filters used by the workload capture are displayed, including the workload filter name, type, session attribute, and value.

  6. To return to the Database Replay page, click OK.

9.6.2 Stopping an Active Workload Capture

This section describes how to stop an active workload capture using Enterprise Manager.

To stop an active workload capture:

  1. On the Software and Support page, under Real Application Testing, click Database Replay.

    The Database Replay page appears.

  2. Under Active Capture and Replay, select the workload capture you want to stop and click Stop.

    The Confirmation page appears.

  3. To confirm that you want to stop the workload capture, click Yes.

    Once the workload capture is stopped, the Export AWR Data page appears.

  4. To export the Automatic Workload Repository (AWR) data, click Yes.

    The Export AWR Data page appears; click Yes.

    Exporting AWR data enables detailed analysis of the workload. This data is also required if you plan to run the Replay Compare Period report or the AWR Compare Period report on a pair of workload captures or replays.

    If you choose not to export AWR data, click No. You can still export AWR data from a completed workload capture at a later time from the View Workload Capture History page.

    The View Workload Capture page appears.

See Also:

9.6.3 Managing a Completed Workload Capture

This section describes how to manage a completed workload capture using Enterprise Manager.

To manage a completed workload capture:

  1. On the Software and Support page, under Real Application Testing, click Database Replay.

    The Database Replay page appears.

  2. Click View Workload Capture History.

    The View Workload Capture History page appears.

    Description of dbr_capture_history.gif follows
    Description of the illustration dbr_capture_history.gif

  3. To delete a workload capture, select the workload capture and click Delete.

    This will not remove the capture files from the capture directory.

  4. To export AWR data for a workload capture, select the workload capture and click Export AWR Data.

    The Export AWR Data page appears; click Yes.

    Exporting AWR data enables detailed analysis of the workload. This data is also required if you plan to run the Replay Compare Period report or the AWR Compare Period report on a pair of workload captures or replays.

  5. To view details about a workload capture, select the workload capture and click View.

    The View Workload Capture page appears.

  6. Under Summary, information about the workload capture is displayed.

  7. To view the workload profile, click the Workload Profile tab.

    Under Average Active Sessions, the Active Sessions chart provides a graphic display of the captured session activity compared to the uncaptured session activity (such as background activities or filtered sessions). This chart will be shown only when there is Active Session History (ASH) data available for the capture period.

    Under Comparison, various statistics for the workload capture are displayed, including database time, average active sessions, user calls, transactions, connects, and application errors. The statistics for the total session activity are displayed in the Total column, and the statistics for the captured session activity are displayed in the Capture column. The Percentage of Total column displays the percentage of total session activity that are being captured in the workload.

    To view the workload capture report, click View Workload Capture Report.

  8. To view workload filters used by the workload capture, click the Workload Filters tab.

    Details about the workload filters used by the workload capture are displayed, including the workload filter name, type, session attribute, and value.

  9. To return to the Database Replay page, click OK.

See Also:

9.7 Capturing a Database Workload Using APIs

This section describes how to capture a database workload using APIs. You can also use Oracle Enterprise Manager to capture database workloads, as described in "Capturing a Database Workload Using Enterprise Manager".

Capturing a database workload using the DBMS_WORKLOAD_CAPTURE package involves:

See Also:

9.7.1 Defining Workload Capture Filters

This section describes how to add and remove workload capture filters. For information about using workload filters with workload capture, see "Using Filters with Workload Capture".

To add filters to a workload capture, use the ADD_FILTER procedure:

BEGIN
  DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
                           fname => 'user_ichan',
                           fattribute => 'USER',
                           fvalue => 'ICHAN');
END;
/

In this example, the ADD_FILTER procedure adds a filter named user_ichan, which can be used to filter out all sessions belonging to the user name ICHAN.

The ADD_FILTER procedure in this example uses the following parameters:

  • The fname required parameter specifies the name of the filter that will be added.

  • The fattribute required parameter specifies the attribute on which the filter will be applied. Valid values include PROGRAM, MODULE, ACTION, SERVICE, INSTANCE_NUMBER, and USER.

  • The fvalue required parameter specifies the value for the corresponding attribute on which the filter will be applied. It is possible to use wildcards such as % with some of the attributes, such as modules and actions.

To remove filters from a workload capture, use the DELETE_FILTER procedure:

BEGIN
  DBMS_WORKLOAD_CAPTURE.DELETE_FILTER (fname => 'user_ichan');
END;
/

In this example, the DELETE_FILTER procedure removes the filter named user_ichan from the workload capture.

The DELETE_FILTER procedure in this example uses the fname required parameter, which specifies the name of the filter to be removed. The DELETE_FILTER procedure will not remove filters that belong to completed captures; it only applies to filters of captures that have yet to start.

9.7.2 Starting a Workload Capture

Before starting a workload capture, you must first complete the prerequisites for capturing a database workload, as described in "Prerequisites for Capturing a Database Workload". You should also review the workload capture options, as described in "Workload Capture Options".

It is important to have a well-defined starting point for the workload so that the replay system can be restored to that point before initiating a replay of the captured workload. To have a well-defined starting point for the workload capture, it is preferable not to have any active user sessions when starting a workload capture. If active sessions perform ongoing transactions, those transactions will not be replayed properly in subsequent database replays, since only that part of the transaction whose calls were executed after the workload capture is started will be replayed. To avoid this problem, consider restarting the database in restricted mode using STARTUP RESTRICT before starting the workload capture. Once the workload capture begins, the database will automatically switch to unrestricted mode and normal operations can continue while the workload is being captured. For more information about restarting the database before capturing a workload, see "Restarting the Database".

To start the workload capture, use the START_CAPTURE procedure:

BEGIN
  DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'dec10_peak', 
                           dir => 'dec10',
                           duration => 600,
                           capture_sts => TRUE,
                           sts_cap_interval => 300);
END;
/

In this example, a workload named dec10_peak will be captured for 600 seconds and stored in the operating system defined by the database directory object named dec10. A SQL tuning set will also be captured in parallel with the workload capture.

The START_CAPTURE procedure in this example uses the following parameters:

  • The name required parameter specifies the name of the workload that will be captured.

  • The dir required parameter specifies a directory object pointing to the directory where the captured workload will be stored.

  • The duration parameter specifies the number of seconds before the workload capture will end. If a value is not specified, the workload capture will continue until the FINISH_CAPTURE procedure is called.

  • The capture_sts parameter specifies whether to capture a SQL tuning set in parallel with the workload capture. If this parameter is set to TRUE, you can capture a SQL tuning set during workload capture, then capture another SQL tuning set during workload replay, and use SQL Performance Analyzer to compare the SQL tuning sets 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 the change—while running Database Replay. You can also export the resulting SQL tuning set with its AWR data using the EXPORT_AWR procedure, as described in "Exporting AWR Data for Workload Capture".

    This feature is not supported for Oracle RAC. Workload capture filters that are defined using DBMS_WORKLOAD_CAPTURE do not apply to the SQL tuning set capture. The default value for this parameter is FALSE.

  • The sts_cap_interval parameter specifies the duration of the SQL tuning set capture from the cursor cache in seconds. The default value is 300. Setting the value of this parameter below the default value may cause additional overhead with some workloads and is not recommended.

9.7.3 Stopping a Workload Capture

To stop the workload capture, use the FINISH_CAPTURE procedure:

BEGIN
  DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE (); 
END;
/

In this example, the FINISH_CAPTURE procedure finalizes the workload capture and returns the database to a normal state.

Tip:

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

9.7.4 Exporting AWR Data for Workload Capture

Exporting AWR data enables detailed analysis of the workload. This data is also required if you plan to run the Replay Compare Period report or the AWR Compare Period report on a pair of workload captures or replays.

To export AWR data, use the EXPORT_AWR procedure:

BEGIN
  DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (capture_id => 2);
END;
/

In this example, the AWR snapshots that correspond to the workload capture with a capture ID of 2 are exported, along with any SQL tuning set that may have been captured during workload capture.

The EXPORT_AWR procedure uses the capture_id required parameter, which specifies the ID of the capture whose AWR snapshots will be exported. This procedure will work only if the corresponding workload capture was performed in the current database and the AWR snapshots that correspond to the original capture time period are still available.

9.8 Monitoring Workload Capture Using Views

This section summarizes the views that you can display to monitor workload capture. You can also use Oracle Enterprise Manager to monitor workload capture, as described in "Monitoring Workload Capture Using Enterprise Manager".

To access these views, you need DBA privileges:

See Also: