9 Capturing a Database Workload

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:

• Recovery Manager (RMAN) DUPLICATE command

• Snapshot standby

• Data Pump Import and Export

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

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:

• Restarting the Database

• Using Filters with Workload Capture

• Setting Up the Capture Directory

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

9.3 Workload Capture Restrictions

Certain types of user sessions and client requests may sometimes be captured in a workload, but they are not supported by Database Replay. Capturing these session and request types in a workload may result in errors during workload replay.

The following types of user sessions and client requests are not supported by Database Replay:

• Direct path load of data from external files using utilities such as SQL*Loader

• Non-PL/SQL based Advanced Queuing (AQ)

• Flashback queries

• Oracle Call Interface (OCI) based object navigations

• Non SQL-based object access

• Distributed transactions

  Any distributed transactions that are captured will be replayed as local transactions.

• XA transactions

  XA transactions are not captured or replayed. All local transactions are captured.

• JAVA_XA transactions

  If the workload uses the JAVA_XA package, JAVA_XA function and procedure calls are captured as normal PL/SQL workload. To avoid problems during workload replay, consider dropping the JAVA_XA package on the replay system to enable the replay to complete successfully.

• Database Resident Connection Pooling (DRCP)

• Workloads using OUT binds

• Multi-threaded Server (MTS) and shared server sessions with synchronization mode set to OBJECT_ID

• Migrated sessions

  The workload is captured for migrated sessions. However, user logins or session migration operations are not captured. Without a valid user login or session migration, the replay may cause errors because the workload may be replayed by a wrong user.

Typically, Database Replay refrains from capturing these types of non-supported user sessions and client requests. Even when they are captured, Database Replay will not replay them. Therefore, it is usually not necessary to manually filter out non-supported user sessions and client requests. In cases where they are captured and found to cause errors during replay, consider using workload capture filters to exclude them from the workload.

9.4 Enabling and Disabling the Workload Capture Feature

Database Replay supports capturing a database workload on a system running Oracle Database 10g Release 2 that can be used to test database upgrades to Oracle Database 11g and subsequent releases. 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.

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

9.5 Enterprise Manager Privileges and Roles

The Database Replay resource type privileges enable you to view or operate any Database Replay entities. Additionally, you need the target operator privilege for the target from which the workload was captured to access the entities associated with the workload. For a target that does not exist anymore, the Enterprise Manager user who owns the entities or the Enterprise Manager super user can still access the entities.

The two security roles discussed in the following sections make it easier to grant or revoke privileges related to Database Replay entities.

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

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.

   
   Description of the illustration dbr.gif
   
   

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

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".
   
   Description of the illustration dbr_capture_databases.gif
   
   

4. Click Add.

   The Add pop-up appears.

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

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

   • The capture database places the capture files in the intermediate storage location. After the capture, the files are copied to the storage location. For captures on an Oracle RAC database, Enterprise Manager only supports Oracle RAC configured with a shared file system.

   
   Description of the illustration dbr_capture_add_db.gif
   
   

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

8. Click Next.

   The Create Capture: Options page appears.

   
   Description of the illustration dbr_capture_options.gif
   
   

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.

   • 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
   
   

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.

    The Create Capture: Schedule page appears.

    
    Description of the illustration dbr_capture_schedule.gif
    
    

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.

    
    Description of the illustration dbr_capture_review.gif
    
    

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.

9.7 Capturing Workloads from Multiple Databases Concurrently

Concurrent capture refers to capturing the workload on multiple databases simultaneously.

To capture a concurrent database replay workload: 

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.

   The Create Capture: Plan Environment page appears.

3. Make sure 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.

   The Search and Select: Targets pop-up appears.

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

   • The Capture database places the Capture files in the intermediate storage location. After the Capture, the files are copied to the storage location.

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

8. Add another database for concurrent capture:

   1. Follow the instructions in steps 4 through 7.

      The Create Capture: Database page reappears, and displays the additional database for capture along with the first database you specified in steps 4 through 7.

   2. Provide a name and optional description for the concurrent capture, then click Next.

      The Create Capture: Options page appears.

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 recommended workload capture option.

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

   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, then click Next.

    The Create Capture: Schedule page appears.

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.

9.8 Monitoring a 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:

• Monitor or stop an active workload capture

• View a completed workload capture

This section contains the following topics:

• Monitoring an Active Workload Capture

• Stopping an Active Workload Capture

• Viewing a Completed Workload Capture

9.9 Importing a Workload External to Enterprise Manager

You can import a workload captured through the PL/SQL interface or through a different Enterprise Manager instance into Enterprise Manager to manage it as you would for a workload originally created within Enterprise Manager. The workload can be in the process of being captured, or the Capture can be completed and the workload stored on a file system. You can also preprocess and replay the workload as you would ordinarily do for a workload created within Enterprise Manager.

This feature is available for Cloud Control Database plug-in 12.1.0.5 and later releases.

To import a database workload external to Enterprise Manager: 

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

   The Import Workload: Source page appears.

2. Select one of the three choices available to import a captured workload, then click Next:

   • Import a completed captured workload from a directory in the file system

     This option typically applies for a workload created using an API, in which you now want to import it to Enterprise Manager for subsequent processing. In this case, Enterprise Manager may not even be managing the Capture database.

   • Import a completed captured workload from a database target

     In this case, Enterprise Manager is probably already managing the Capture database. The Capture could have been done on this database, or it could have been loaded in as would be the case for the option above.

   • Attach to a Capture running in a database target

     This option is similar to the option above, except this a Capture that is still in progress, rather than one that has already completed.

   The Import Workload: Database page appears.

3. Click the search icon next to the Database Target field and select a database from the pop-up that appears.

   Note:

   The target version of the database loading the workload must be at least as high as the one you used to capture the workload. For instance, if you captured using Oracle Database 12x, the database you select to read the Capture must be at least version 12x.

   • The system now requests database and host credentials.

   • In the previous step, if you chose to import a completed captured workload from a directory in the file system, the system also requires a workload location.

   • A consolidated Replay has a different directory structure in which there are at least two capture directories. Consequently, if the workload directory contains a consolidated Replay, you need to enable the check box so that Enterprise Manager can be aware of the consolidated Replay during the import operation.

4. Provide the requisite input for the step above, then click Next.

   The Import Workload: Workload page appears.

   • If you chose "Import a completed captured workload from a directory in the file system” in step 2, this page provides a Load Workload button.

   • If you chose ”Import a completed captured workload from a database target” or ”Attach to a Capture running in a database target” in step 2, this page provides a Discover Workload button.

5. Click either Load Workload or Discover Workload, depending on which button is available in accordance with your selection in step 2.

   The system displays workloads, if found, in the Discovered Workloads table.

6. Either click Next to load the workload, or select one of the workloads, then click Next to continue importing the workload.

   The Import Workload: Replay page appears only under the following conditions:

   • You chose "Import a completed captured workload from a directory in the file system” in step 2.

   • The workload contains one or more Replays.

7. Optional: Select one or more Replays, if desired, provide a Replay Task Name, then click Next.

   The Import Workload: Review page appears.

8. If everything appears as you have intended, click Submit.

   The Database Replay page reappears and displays a message stating that the job was submitted successfully. The Status column for your loaded or imported workload in the Captured Workload table will show In Progress.

   Tip:

   You can check on the job's progress by clicking on the captured workload name that you just submitted in the Review step. A Capture Summary page appears, and you can click the Database Replay Import Job link to see the progress of the job execution steps.

9.10 Creating Subsets from an Existing Workload

For cases in which you have captured a large workload covering a long period of time, you may only want to replay a portion of it to accelerate testing. The Database Replay Workload Subsetting feature enables you to create new workloads by extracting portions of an existing captured workload.

Enterprise Manager provides a wizard to extract a subset of data from an existing workload that you can use for Replay on a test system. Each extracted subset is a legitimate workload that can be replayed on its own or with other workloads in a consolidated Replay.

To perform a Replay, you need to preprocess the workloads.

This feature is available for Cloud Control Database plug-in 12.1.0.5 and later releases.

To extract subsets from a workload: 

1. From the Captured Workloads tab of the Database Replay page, select a workload for which you want to extract a subset, then click Subset.

   The Subset Workload: Define page appears, showing an Active Sessions History chart for the workload.

2. Select a starting and ending time for the subset you want to extract from the workload:

   1. Click Add above the Subsets table at the bottom of the page.

      The Create Subset pop-up appears.

   2. Select either snapshot or calendar times, provide start and end times, then click OK.

      Note:

      Snapshot time is the preferred choice, because not all performance data may be available for the calendar time you select.

      Your selected time period now appears in the Active Sessions chart as a greyed-out segment.

   3. Optional: Define one or more additional subsets with different time periods than those you selected in the step above.

   4. Optional: In the Advanced Parameters section, indicate whether you want to include incomplete calls after the subset workload ends. The default is to include incomplete calls when the subset workload begins.

      • These parameters enable you to include the calls outside of your defined boundaries. For example, when you specify a starting and ending time as the boundaries for a transaction, the transaction may have started before your indicated start time, and may also have continued after your indicated end time.

3. Click Next.

   The Subset Workload: Database page appears.

4. Click the search icon next to the Database Target field and select a database for subsetting from the Search and Select: Targets pop-up that appears.

   The system now requests database and host credentials.

5. Provide the requisite input for the step above, then click Next.

   The Subset Workload: Location page appears.

   • If the source host and staged database host are the same, the location is pre-populated, so you do not need to provide the location for the source workload files.

   • If the source host and staged database host are not the same, do the following:

     1. Choose whether you want to access the workload files from the host name shown in the Host field, or whether you want to copy the files from the source host to the Destination Host shown.

        Access Directly means that the database to be used to subset the workload can access the original workload directly using the specified file system location. This is typically the case when the original workload is stored at a network shared location.

        Access Copy means that the two hosts are not in the shared network path. You need to provide the source host credentials so that Enterprise Manager can copy the original workload from its current location to the specified location on the subset database host.

     2. Depending on your choice above, either provide the directory location containing the workload files, or provide the location for the destination host.

6. In the Subset field, specify the storage location for each subset, then click Next.

   The Subset Workload: Schedule page appears.

7. Indicate when you want to start the subset job, then click Next.

   The Subset Workload: Review page appears.

8. If everything appears as you have intended, click Submit.

   The Database Replay page reappears and displays a message stating that the job was submitted successfully. The Status column for your subset in the Replay Tasks table will show In Progress.

   Tip:

   You can check on the job's progress by clicking on the subset name that you just submitted in the Review step. A Capture Summary page appears, and you can click the Database Replay Subset Job link to see the progress of the job execution steps.

9.11 Copying or Moving a Workload to a New Location

You can use the copy function for two purposes:

• Duplicate the Capture files from the source to another host and location.

• Move the Capture files to a new host and location, and delete the source files from the original location.

This feature is available for Cloud Control Database plug-in 12.1.0.5 and later releases.

To copy a workload to a new location: 

1. From the Captured Workloads tab of the Database Replay page, select a workload you want to copy to a new location, then click Copy.

   The Copy Workload page appears, and displays the current source location of the workload directory you selected.

2. Provide or change credentials for the storage host, if necessary. The system automatically picks up the previously defined credentials for the current storage host.

3. Leave the After Copy radio button enabled, which is "Keep original workload in the source location."

4. Select the Storage Host for the new location of the workload directory.

5. Provide credentials for the new storage host.

6. Select the directory for the new Destination Location for the workload.

7. Schedule the job, then click Submit.

   The Database Replay page reappears and displays a message stating that the job was submitted and that the storage location has been updated successfully.

   Tip:

   You can check on the job's progress by going to the Job Activity page, searching for the job name that appeared in the submit message, then clicking its link to access the Job Run page.

9.12 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:

• Defining Workload Capture Filters

• Starting a Workload Capture

• Stopping a Workload Capture

• Exporting AWR Data for Workload Capture

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

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

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

BEGIN
  DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE (); 
END;
/

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

9.13 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 a Workload Capture Using Enterprise Manager".

To access these views, you need DBA privileges:

• The DBA_WORKLOAD_CAPTURES view lists all the workload captures that have been captured in the current database.

• The DBA_WORKLOAD_FILTERS view lists all workload filters used for workload captures defined in the current database.