| Oracle® Database Performance Tuning Guide 11g Release 1 (11.1) Part Number B28274-01 |
|
|
View PDF |
| Oracle® Database Performance Tuning Guide 11g Release 1 (11.1) Part Number B28274-01 |
|
|
View PDF |
Before system changes are made, such as hardware and software upgrades, extensive testing is usually performed in a test environment to validate the changes. However, despite the testing, the new system often experiences unexpected behavior when it enters production because the testing was not performed using a realistic workload. The inability to simulate a realistic workload during testing is one of the biggest challenges when validating system changes.
Database Replay enables realistic testing of system changes by essentially recreating the production workload environment on a test system. Using Database Replay, you can capture a workload on the production system and replay it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This enables you to fully assess the impact of the change, including undesired results, new contention points, or plan regressions. Extensive analysis and reporting is provided to help identify any potential problems, such as new errors encountered and performance divergence.
Capturing the production workload eliminates the need to develop simulation workloads or script, resulting in significant cost reduction and time savings. By using Database Replay, realistic testing of complex applications that previously took months using load simulation tools can now be completed in days. This enables you to rapidly test changes and adopt new technologies with a higher degree of confidence and at lower risk.
Database Replay performs workload capture of external client workload at the database level and has negligible performance overhead. You can use Database Replay to test any significant system changes, including:
Database and operating system upgrades
Configuration changes, such as conversion of a database from a single instance to an Oracle RAC environment
Storage, network, and interconnect changes
Operating system and hardware migrations
This chapter describes how to use the Database Replay feature of Oracle Database and contains the following sections:
By capturing a workload on the production system and replaying it on a test system, Database Replay enables you to realistically test system changes by essentially recreating the production workload environment on a test system.
Figure 22-1 illustrates the Database Replay workflow.
Using Database Replay requires four main steps, as shown in Figure 22-1:
The first step in using Database Replay is to capture the production workload. Capturing a workload involves recording all requests made by external clients to Oracle Database. When workload capture is enabled, all external client requests directed to Oracle Database are tracked and stored in binary files, called capture files, on the file system. These capture files are platform independent and can be transported to another system. You can specify the start time and duration for the workload capture, as well as the location to store the capture files. Once workload capture begins, all external database calls are written to the capture files. The capture files contain all relevant information about the client request, such as SQL text, bind values, and transaction information. Background activities and database scheduler jobs are not captured.
For information about how to capture a workload on the production system, see "Capturing a Database Workload".
Once the workload has been captured, the information in the capture files need to be preprocessed. Preprocessing transforms the captured data into replay files and creates all necessary metadata needed for replaying the workload. This must be done once for every captured workload before they can be replayed. After the captured workload is preprocessed, it can be replayed repeatedly on a replay system running the same version of Oracle Database. Typically, the capture files should be copied to another system for preprocessing. As workload preprocessing can be time consuming and resource intensive, it is recommended that this step be performed on the test system where the workload will be replayed.
For information about how to preprocess a captured workload, see "Preprocessing a Database Workload".
After a captured workload has been preprocessed, it can be replayed on a test system. During the workload replay phase, Oracle Database performs the actions recorded during the workload capture phase on the test system by recreating all captured external client requests with the same timing, concurrency, and transaction dependencies of the production system.
Database Replay uses a client program called the replay client to recreate all external client requests recorded during workload capture. Depending on the captured workload, you may need one or more replay clients to properly replay the workload. A calibration tool is provided to help determine the number of replay clients needed for a particular workload. Because the entire workload is replayed, including DML and SQL queries, the data in the replay system should be logically similar to the data in the capture system to minimize data divergence and enable reliable analysis of the replay.
For information about how to replay a preprocessed workload on the test system, see "Replaying a Database Workload".
Once the workload is replayed, in-depth reporting is provided for you to perform detailed analysis of both workload capture and replay.
The report summary provides basic information about the workload capture and replay, such as errors encountered during replay and data divergence in rows returned by DML or SQL queries. A comparison of several statistics—such as DB time, average active sessions, and user calls—between the workload capture and the workload replay is also provided. For advanced analysis, Automatic Workload Repository (AWR) reports are available to enable detailed comparison of performance statistics between the workload capture and the workload replay. The information available in these reports is very detailed, and some differences between the workload capture and replay can be expected.
For application-level validation, you should consider developing a script to assess the overall success of the replay. For example, if 10,000 orders are processed during workload capture, you should validate that similar number of orders are also processed during replay.
For information about how to analyze data and performance divergence using Database Replay reports, see "Analyzing Replayed Workload".
This section describes how to capture a database workload on the production system. 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.
This section contains the following topics:
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 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.
See Also:
Oracle Database Backup and Recovery User's Guide for information about duplicating a database using RMAN
Oracle Data Guard Concepts and Administration for information about managing snapshot standby databases
Oracle Database Utilities for information about using Data Pump
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:
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 data divergence when the workload is replayed. Any subsequent transactions with dependencies on the incomplete transactions may also generate errors during replay.
Before restarting the database, determine an appropriate time to shut down the production database prior to the workload capture time period 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 at 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_RESTRICTED, 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.
See Also:
Oracle Database Administrator's Guide for information about restricting access to an instance at startupOnly one workload capture can be performed at any given time. For Oracle Real Application Clusters (RAC), 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. You can do this by:
Shutting down all the instances.
Restarting one of the instances.
Starting workload capture.
Restarting the rest of the instances.
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. 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. For example, if the system where the workload will be replayed is running Oracle Enterprise Manager (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. You can use either inclusion filters or exclusion filters in a workload capture, but not both.
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.
For Oracle RAC, consider using a shared file system. Alternatively, you can set up capture directory paths that resolve to separate physical directories on each instance, but you will need to collect the capture files created in each of these directories into a single directory before preprocessing the workload capture.
The following types of client requests are not captured in a workload:
Direct path load of data from external files using utilities such as SQL*Loader
Shared server requests (Oracle MTS)
Oracle Streams
Advanced replication streams
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)
Remote DESCRIBE and COMMIT operations
This section describes how to capture a database workload using Enterprise Manager.
To capture a database workload using Enterprise Manager:
On the Software and Support page, under Real Application Testing, click Database Replay.
The Database Replay page appears.
In the Go to Task column, click the icon that corresponds to the Capture Workload task.
The Capture Workload: Plan Environment page appears.
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.
Select the workload capture options.
Under Database Restart Options, select whether the database will be restarted before workload capture.
It is strongly 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 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 type, and attribute value in the corresponding fields. For more information, see "Defining the Workload Filters".
After selecting the desired workload capture options, click Next. The Capture Workload: Parameters page appears.
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 new 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 prior to shutting down the database.
Transactional
A transactional shutdown will first complete all active transactions and then disconnect the connected user prior to shutting down the database.
Abort
An abort shutdown will shut down the database instantaneously by aborting all active transactions.
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.
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.
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.
Under Job Credentials, enter the host and database login credentials:
Under Host Credentials, enter the username and password for the host machine.
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 capture the workload.
Click Next. The Capture Workload: Review page appears.
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.
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 an Active Workload Capture".
Tip:
After capturing a workload on the production system, you need to preprocess the captured workload, as described in "Preprocessing a Database Workload".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 or delete a completed workload capture
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:
This section describes how to monitor an active workload capture using Enterprise Manager.
To monitor an active workload capture:
On the Software and Support page, under Real Application Testing, click Database Replay.
The Database Replay page appears.
Under Active Capture and Replay, select the workload capture you want to monitor and click View.
The View Workload Capture page appears.
Under Summary, information about the workload capture is displayed.
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, 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.
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.
To return to the Database Replay page, click OK.
This section describes how to stop an active workload capture using Enterprise Manager.
To stop an active workload capture:
On the Software and Support page, under Real Application Testing, click Database Replay.
The Database Replay page appears.
Under Active Capture and Replay, select the workload capture you want to stop and click Stop.
The Confirmation page appears.
To confirm that you want to stop the workload capture, click Yes.
The Export AWR Data page appears.
To export the Automatic Workload Repository (AWR) data, click Yes.
Exporting AWR data enables detailed analysis of the workload. This data is also required if you plan to run 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. For information about the AWR, see "Overview of the Automatic Workload Repository".
This section describes how to manage a completed workload capture using Enterprise Manager.
To manage a completed workload capture:
On the Software and Support page, under Real Application Testing, click Database Replay.
The Database Replay page appears.
Click View Workload Capture History.
The View Workload Capture History page appears.
To delete a workload capture, select the workload capture and click Delete.
To export AWR data for a workload capture, select the workload capture and click Export AWR Data.
Exporting AWR data enables detailed analysis of the workload. This data is also required if you plan to run the AWR Compare Period report on a pair of workload captures or replays.
To view details about a workload capture, select the workload capture and click View.
The View Workload Capture page appears.
Under Summary, information about the workload capture is displayed.
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. For information about ASH, see "Active Session History".
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.
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.
To return to the Database Replay page, click OK.
This section describes how to capture a database workload using APIs. Capturing a database workload using the DBMS_WORKLOAD_CAPTURE package involves:
This section describes how to add and remove workload filters. For information about using workload filters, see "Defining the Workload Filters".
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.
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_RESTRICTED prior to 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 => 'dec06_peak',
dir => 'dec06',
duration => 600);
END;
/
In this example, a workload named dec06_peak will be captured for 600 seconds and stored in the operating system defined by the database directory object named dec06.
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 optional 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.
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 "Preprocessing a Database Workload".Exporting AWR data enables detailed analysis of the workload. This data is also required if you plan to run 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. 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.
This section summarizes the views that you can display to monitor workload capture. You need DBA privileges to access these views.
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.
See Also:
Oracle Database Reference for information on these viewsAfter a workload is captured and setup of the test system is complete, the captured data must be preprocessed. Preprocessing a captured workload transforms the captured data into replay files and creates all necessary metadata. This must be done once for every captured workload before they can be replayed. After the captured workload is preprocessed, it can be replayed repeatedly on a replay system.
To preprocess a captured workload, you will first need to move all captured data files from the directory where they are stored on the capture system to a directory on the instance where the preprocessing will be performed. Preprocessing is resource intensive and should be performed on a system that is:
Separate from the production system
Running the same version of Oracle Database as the replay system
For Oracle RAC, select one database instance of the replay system for the preprocessing. This instance must have access to the captured data files that require preprocessing, which can be stored on a local or shared file system. If the capture directory path on the capture system resolves to separate physical directories in each instance, you will need to move all the capture files created in each of these directories into a single directory on which preprocessing will be performed.
Typically, you will preprocess the captured workload on the replay system. If you plan to preprocess the captured workload on a system that is separate from the replay system, you will also need to move all preprocessed data files from the directory where they are stored on the preprocessing system to a directory on the replay system after preprocessing is complete.
The primary tool for preprocessing workload captures is Oracle Enterprise Manager. If for some reason Oracle Enterprise Manager is unavailable, you can preprocess workload captures using the APIs.
This section contains the following topics:
Tip:
Before you can preprocess a captured workload, you need to:Capture the workload on the production system, as described in "Capturing a Database Workload"
This section describes how to preprocess a captured workload using Enterprise Manager.
To preprocess a captured workload using Enterprise Manager:
On the Software and Support page, under Real Application Testing, click Database Replay.
The Database Replay page appears.
In the Go to Task column, click the icon that corresponds to the Preprocess Captured Workload task.
The Preprocess Captured Workload page appears.
In the Directory Object list, select a directory that contains the captured workload that you want to preprocess.
After a directory is selected, the Preprocess Captured Workload page will be refreshed to display the Capture Summary section, which contains information about the captured workload in the selected directory.
To view additional details about the captured workload, expand Capture Details. The expanded Capture Details section displays the workload profile and details for the captured workload.
Click Preprocess Workload.
The Preprocess Captured Workload: Database Version page appears.
Ensure that the current database version displayed matches the database version on the intended replay system and click Next.
Preprocessing must be performed on a system that is running the same version of Oracle Database as the replay system.
The Preprocess Captured Workload: Schedule page appears.
Define the parameters for the preprocessing job.
Under Job Parameters, enter a name and a description for the job.
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 Host Credentials, enter the user name and password information for the database host that will used for the preprocessing.
After defining the job parameters, click Next. The Preprocess Captured Workload: Review page appears.
Review the selected options for the preprocessing job. To preprocess the captured workload, click Submit. To make changes, click Back. To cancel preprocessing without saving changes, click Cancel.
Tip:
After preprocessing a captured workload, you can replay it on the test system, as described in "Replaying a Database Workload".This section describes how to preprocess a captured workload using the DBMS_WORKLOAD_REPLAY package.
To preprocess a captured workload, use the PROCESS_CAPTURE procedure:
BEGIN DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'dec06'); END; /
In this example, the captured workload stored in the dec06 directory will be preprocessed.
The PROCESS_CAPTURE procedure in this example uses the capture_dir required parameter, which specifies the directory that contains the captured workload to be preprocessed.
Tip:
After preprocessing a captured workload, you can replay it on the test system, as described in "Replaying a Database Workload".This section describes how to replay a database workload on the test system. After a captured workload is preprocessed, it can be replayed repeatedly on a replay system that is running the same version of Oracle Database. The primary tool for replaying database workloads is Oracle Enterprise Manager. However, you can also replay database workloads using APIs.
This section contains the following topics:
Tip:
Before you can replay a workload, you must first:Capture the workload on the production system, as described in "Capturing a Database Workload"
Preprocess the captured workload, as described in "Preprocessing a Database Workload"
Typically, the replay system where the preprocessed workload will be replayed should be a test system that is separate from the production system. Before a test system can be used for replay, it must be prepared properly as described in the following sections:
Before a workload can be replayed, the application data state should be logically equivalent to that of the capture system at the start time of workload capture. This minimizes data divergence during replay. The method for restoring the database depends on the backup method that was used before capturing the workload. For example, if RMAN was used to back up the capture system, you can use RMAN DUPLICATE capabilities to create the test database. For more information, see "Prerequisites for Capturing a Database Workload".
After the database is created with the appropriate application data on the test system, perform the system change you want to test, such as a database or operating system upgrade. The primary purpose of Database Replay is to test the effect of system changes on a captured workload. Therefore, the system changes you make should define the test you are conducting with the captured workload.
It is recommended that the system time on the replay system host be changed to a value that approximately matches the capture start time just before replay is started. Otherwise, an invalid data set may result when replaying time-sensitive workloads. For example, a captured workload that contains SQL statements using the SYSDATE and SYSTIMESTAMP functions may cause data divergence when replayed on a system that has a different system time. Resetting the system time will also minimize job scheduling inconsistencies between capture and replay.
Proper planning of the workload replay ensures that the replay will be accurate. The section describes the required steps for replaying a database workload:
The captured workload must have been preprocessed and copied to the replay system. A directory object for the directory to which the preprocessed workload is copied must exist in the replay system.
A captured workload may contain references to external systems, such as database links or external tables. Typically, you should disable or reconfigure these external interactions to avoid impacting other production systems during replay. External references that need to be resolved before replay a workload include:
Database links
It is typically not desirable for the replay system to interact with other databases. Therefore, you should disable or reconfigure all database links to point to an appropriate database that contains the data needed for replay.
External tables
All external files specified using directory objects referenced by external tables need to be available to the database during replay. The content of these files should be the same as during capture, and the filenames and directory objects used to define the external tables should also be valid.
Directory objects
You should disable or reconfigure any references to directories on the production system by appropriately redefining the directory objects present in the replay system after restoring the database.
URLs
URLs need to be configured so that Web services accessed during the workload capture will point to the proper URLs during replay.
E-mails
To avoid resending E-mail notifications during replay, the mail server on the replay system should be configured to ignore requests for outgoing E-mails.
To avoid impacting other production systems during replay, Oracle strongly recommends running the replay within an isolated private network that does not have access to the production environment hosts.
During workload capture, connection strings used to connect to the production system are captured. In order for the replay to succeed, you need to remap these connection strings to the replay system. The replay clients can then connect to the replay system using the remapped connections.
For Oracle RAC databases, you can map all connection strings to a load balancing connection string. This is especially useful if the number of nodes on the replay system is different from the capture system. Alternatively, if you wish to direct workload to specific instances, you can use services or explicitly specify the instance identifier in the remapped connection strings.
After the database is restored and connections are remapped, you can set the following replay options as appropriate:
The synchronization parameter controls whether the COMMIT order in the captured workload will be preserved during replay. By default, this option is enabled to preserve the COMMIT order in the captured workload during replay. All transactions will be executed only after all dependent transactions have been committed.
You can disable this option, but the replay will likely yield significant data divergence. However, this may be desirable if the workload consists primarily of independent transactions, and divergence during unsynchronized replay is acceptable
The connect_time_scale parameter enables you to scale the elapsed time between the time when the workload capture began and when sessions connect. You can use this option to manipulate the session connect time during replay with a given percentage value. The default value is 100, which will attempt to connect all sessions as captured. Setting this parameter to 0 will attempt to connect all sessions immediately.
User think time is the elapsed time while the user waits between issuing calls. To control replay speed, use the think_time_scale parameter to scale user think time during replay. If user calls are being executed slower during replay than during capture, you can make the database replay attempt to catch up by setting the think_time_auto_correct parameter to TRUE. This will make the replay client shorten the think time between calls, so that the overall elapsed time of the replay will more closely match the captured elapsed time.
The replay client is a multithreaded program (an executable named wrc located in the $ORACLE_HOME/bin directory) where each thread submits a workload from a captured session. Before replay begins, the database will wait for replay clients to connect. At this point, you need to set up and start the replay clients, which will connect to the replay system and send requests based on what has been captured in the workload.
Before starting replay clients, ensure that the:
Replay client software is installed on the hosts where it will run
Replay clients have access to the replay directory
Replay directory contains the preprocessed workload capture
Replay user has the correct user ID, password, and privileges (the replay user needs the DBA role and cannot be the SYS user)
After these prerequisites are met, you can proceed to set up and start the replay clients using the wrc executable. The wrc executable uses the following syntax:
wrc [user/password[@server]] MODE=[value] [keyword=[value]]
The parameters user and password specify the username and password used to connect to the host where the wrc executable is installed. The parameter server specifies the server where the wrc executable is installed. The parameter mode specifies the mode in which to run the wrc executable. Possible values include replay (the default), calibrate, and list_hosts. The parameter keyword specifies the options to use for the execution and is dependent on the mode selected. To display the possible keywords and their corresponding values, run the wrc executable without any arguments.
The following sections describe the modes that you can select when running the wrc executable:
Since one replay client can initiate multiple sessions with the database, it is not necessary to start a replay client for each session that was captured. The number of replay clients that need to be started depends on the number of workload streams, the number of hosts, and the number of replay clients for each host.
To estimate the number of replay clients and hosts that are required to replay a particular workload, run the wrc executable in calibrate mode.
In calibration mode, the wrc executable accepts the following keywords:
replaydir specifies the directory that contains the preprocessed workload capture you want to replay. If unspecified, it defaults to the current directory.
process_per_cpu specifies the maximum number of client processes that can run per CPU. The default value is 4.
threads_per_process specifies the maximum number of thread that can run within a client process. The default value is 50.
The following example shows how to run the wrc executable in calibrate mode:
%> wrc mode=calibrate replaydir=./replay
In this example, the wrc executable is executed to estimate the number of replay clients and hosts that are required to replay the workload capture stored in a subdirectory named replay under the current directory. In the following sample output, the recommendation is to use at least 21 replay clients divided among 6 CPUs:
Workload Replay Client: Release 11.1.0.6.0 - Production on Thu Jul 12 14:06:33 2007 Copyright (c) 1982, 2007, Oracle. All rights reserved. Report for Workload in: /oracle/replay/ ----------------------- Recommendation: Consider using at least 21 clients divided among 6 CPU(s). Workload Characteristics: - max concurrency: 1004 sessions - total number of sessions: 1013 Assumptions: - 1 client process per 50 concurrent sessions - 4 client process per CPU - think time scale = 100 - connect time scale = 100 - synchronization = TRUE
After determining the number of replay clients that are needed to replay the workload, you need to start the replay clients by running the wrc executable in replay mode on the hosts where they are installed. Once started, each replay client will initiate one or more sessions with the database to drive the workload replay.
In replay mode, the wrc executable accepts the following keywords:
userid and password specify the user ID and password of a replay user for the replay client. If unspecified, these values default to the system user.
server specifies the connection string that is used to connect to the replay system. If unspecified, the value defaults to an empty string.
replaydir specifies the directory that contains the preprocessed workload capture you want to replay. If unspecified, it defaults to the current directory.
workdir specifies the directory where the client logs will be written. This parameter is only used in conjunction with the debug parameter for debugging purposes.
debug specifies whether debug data will be created. Possible values include:
files
Debug data will be written to files in the working directory
stdout
Debug data will be written to stdout
both
Debug data will be written to both files in the working directory and to stdout
none
No debug data will be written (the default value)
connection_override specifies whether to override the connection mappings stored in the DBA_WORKLOAD_CONNECTION_MAP view. If set to TRUE, connection remappings stored in the DBA_WORKLOAD_CONNECTION_MAP view will be ignored and the connection string specified using the server parameter will be used. If set to FALSE, all replay threads will connect using the connection remappings stored in the DBA_WORKLOAD_CONNECTION_MAP view. This is the default setting.
The following example shows how to run the wrc executable in replay mode:
%> wrc system/oracle@test mode=replay replaydir=./replay
In this example, the wrc executable starts the replay client to replay the workload capture stored in a subdirectory named replay under the current directory.
After all replay clients have connected, the database will automatically distribute workload capture streams among all available replay clients. At this point, workload replay can begin. After the replay finishes, all replay clients will disconnect automatically.
You can display the hosts that participated in a workload capture and workload replay by running the wrc executable in list_hosts mode.
In list_hosts mode, the wrc executable accepts the keyword replaydir, which specifies the directory that contains the preprocessed workload capture you want to replay. If unspecified, it defaults to the current directory.
The following example shows how to run the wrc executable in list_hosts mode:
%> wrc mode=list_hosts replaydir=./replay
In this example, the wrc executable is executed to list all hosts that participated in capturing or replaying the workload capture stored in a subdirectory named replay under the current directory. In the following sample output, the hosts that participated in the workload capture and three subsequent replays are shown:
Workload Replay Client: Release 11.1.0.6.0 - Production on Thu Jul 12 13:44:48 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Hosts found:
Capture:
prod1
prod2
Replay 1:
test1
Replay 2:
test1
test2
Replay 3:
testwin
This section describes how to replay a database workload using Enterprise Manager.
To replay a database workload using Enterprise Manager:
On the Software and Support page, under Real Application Testing, click Database Replay.
The Database Replay page appears.
In the Go to Task column, click the icon that corresponds to the Replay Workload task.
The Replay Workload page appears.
In the Directory Object list, select a directory that contains the preprocessed workload that you want to replay.
After a directory is selected, the Replay Workload page will be refreshed to display the Capture Summary and the Replay History sections. For more information, see "Setting Up the Replay Directory".
The Capture Summary section displays information about the preprocessed workload capture in the selected directory. To view additional details about the workload capture, expand Capture Details. The expanded Capture Details section displays the workload profile and workload filters used during the workload capture.
Click Set Up Replay.
The Replay Workload: Prerequisites page appears.
Verify that all prerequisites are met before proceeding.
If you are replaying the workload on a test system, ensure that the test system is properly prepared for replay. For more information, see "Setting Up the Test System".
Once all prerequisites are completed, click Continue.
The Replay Workload: References to External Systems page appears.
Verify potential references to all external systems and modify any invalid references.
Use the links available on the Replay Workload: References to External Systems page to verify the database links, directory objects, and Oracle Streams that may be referenced during the workload capture process. There may be other references to external systems that are not included in these categories. For more information, see "Resolving References to External Systems".
Once all references to external systems have been verified and modified as necessary, click Continue.
The Replay Workload: Choose Initial Options page appears.
In the Replay Name field, you may enter a name for the replay, or simply use the name generated by the system.
Under Initial Options, select whether to use default replay options or replay options from a previous replay (if one is available). If more than one previous replay exist, select the replay you want to use from the Replay Name list.
Click Next. The Replay Workload: Customize Options page appears.
Remap captured connection strings to connection strings that point to the replay system.
Click the Connection Mappings tab. There are several methods you can use to remap captured connection strings. You can choose to:
Use a single connect descriptor for all client connections by selecting this option and entering the connect descriptor you want to use. The connect descriptor should point to the replay system.
To test the connection, click Test Connection. If the connect descriptor is valid, an Information message is displayed to inform you that the connection was successful.
Use a single TNS net service name for all client connections by selecting this option and entering the net service name you want to use. All replay clients must be able to resolve the net service name, which can be done using a local tnsnames.ora file.
Use a separate connect descriptor or net service name for each client connect descriptor captured in the workload by selecting this option and, for each capture system value, entering a corresponding replay system value that will be used by the replay client.
For more information, see "Remapping Connections".
Specify the replay options using the replay parameters.
To modify the replay behavior, click the Replay Parameters tab and enter the desired values for each replay parameter. Using the default values is recommended. For information about setting the replay parameters, see "Specifying Replay Options".
After setting the replay parameters, click Next. The Replay Workload: Prepare Replay Clients page appears.
Ensure that replay clients are prepared for replay.
Before proceeding, the replay clients need to be prepared. For more information, see "Setting Up Replay Clients".
After all replay clients are ready to start, click Next. The Replay Workload: Wait for Client Connections page appears.
Start the replay clients.
For information about starting replay clients, see "Setting Up Replay Clients". As replay clients are started, the replay client connections will be displayed under Client Connections. When all replay clients have connected, click Next.
The Replay Workload: Review page appears.
Review the options and parameters that have been defined for the workload replay.
To begin replay, click Submit. If no replay clients are connected, this button will be disabled. To make changes, click Back. To cancel replay without saving changes, click Cancel.
Once the replay is started, the View Workload Replay page appears. For information about monitoring an active workload replay, see "Monitoring an Active Workload Replay".
This section describes how to monitor workload replay using Enterprise Manager. The primary tool for monitoring workload replay is Oracle Enterprise Manager. Using Enterprise Manager, you can:
Monitor or stop an active workload replay
View a completed workload replay
If for some reason Oracle Enterprise Manager is unavailable, you can monitor workload replay using views, as described in "Monitoring Workload Replay Using Views".
This section contains the following topics:
This section describes how to monitor an active workload replay using Enterprise Manager.
To monitor an active workload replay:
On the Software and Support page, under Real Application Testing, click Database Replay.
The Database Replay page appears.
Under Active Capture and Replay, select the workload replay you want to monitor and click View.
The View Workload Replay page appears.
Under Summary, information about the workload replay is displayed.
To view the workload profile, click the Workload Profile tab.
Under Elapsed Time Comparison, the chart shows how much time it has taken to replay the same workload compared to the elapsed time during the workload capture. If the Replay bar is shorter than the Capture bar, the replay system is processing the workload faster than the capture system.
Under Divergence, any error and data discrepancies between the replay system and the capture system are displayed. This information can be used as a measure of the replay quality.
To view a detailed comparison of the workload during capture and replay, expand Detailed Comparison. This section displays the following information:
Duration
The duration that was captured in a workload is compared to the amount of time it is taking to replay the workload. In the Capture column, the duration of the time period that was captured is shown. In the Replay column, the amount of time it is taking to replay the workload is shown. The Percentage of Capture column shows the percentage of the captured duration that it is taking to replay the workload. If the value is under 100 percent, the replay system is processing the workload faster than the capture system. If the value is over 100 percent, the replay system is processing the workload slower than the capture system.
Database time
The database time that is consumed in the time period that was captured is compared to the amount of database time that is being consumed while replaying the workload.
Average active sessions
The number of average active sessions captured in the workload is compared to the number of average active session that are being replayed.
User calls
The number of user calls captured in the workload is compared to the number of user calls that are being replayed.
To view the workload capture report, click View Workload Replay Report.
To view the connection strings used in the capture and the replay systems, click the Connection Mappings tab.
To view replay parameters used by the workload replay, click the Replay Parameters tab.
To stop the workload replay, click Stop Replay.
To return to the Database Replay page, click OK.
This section describes how to view a completed workload replay using Enterprise Manager.
To view a completed workload replay:
On the Software and Support page, under Real Application Testing, click Database Replay.
The Database Replay page appears.
In the Go to Task column, click the icon that corresponds to the Replay Workload task.
The Replay Workload page appears.
In the Directory Object list, select a directory that contains the preprocessed workload that was used for the replay.
After a directory is selected, the Replay Workload page will be refreshed to display the Capture Summary and the Replay History sections.
The Replay History section displays previous replays of the workload capture. To view details about a previous replay, select the replay and click View.
The View Workload Replay page appears.
Under Summary, information about the workload replay is displayed.
To view the workload profile, click the Workload Profile tab.
Under Elapsed Time Comparison, the chart shows how much time it has taken to replay the same workload compared to the elapsed time during the workload capture. If the Replay bar is shorter than the Capture bar, the replay system is processing the workload faster than the capture system.
Under Divergence, any error and data discrepancies between the replay system and the capture system are displayed. This information can be used as a measure of the replay quality.
To view a detailed comparison of the workload during capture and replay, expand Detailed Comparison. This section displays the following information:
Duration
The duration that was captured in a workload is compared to the amount of time it took to replay the workload. In the Capture column, the duration of the time period that was captured is shown. In the Replay column, the amount of time it took to replay the workload is shown. The Percentage of Capture column shows the percentage of the captured duration that it took to replay the workload. If the value is under 100 percent, the replay system processed the workload faster than the capture system. If the value is over 100 percent, the replay system processed the workload slower than the capture system.
Database time
The database time that is consumed in the time period that was captured is compared to the amount of database time that is consumed when replaying the workload.
Average active sessions
The number of average active sessions captured in the workload is compared to the number of average active session that are replayed.
User calls
The number of user calls captured in the workload is compared to the number of user calls that are replayed.
To view the workload capture report, click View Workload Replay Report.
To view the connection strings used in the capture and the replay systems, click the Connection Mappings tab.
To view replay parameters used by the workload replay, click the Replay Parameters tab.
To run a report, click the Report tab.
There are several types of reports you can run for a completed workload replay:
Workload Replay
The Workload Replay report contains information that can be used to measure data and performance divergence between the capture system and the replay system. To run this report, under Workload Replay Report, click Run Report. For information about using the Workload Replay report, see "Using a Workload Replay Report".
AWR Compare Period
The AWR Compare Period report can be used to compare the AWR data in one workload capture or replay with another. Before running this report, AWR data for the captured or replayed workload must have been previously exported. To run this report, under AWR Compare Period Report, select the first and second workload captures or replays you want to compare and click Run Report. If AWR data is not previously exported from the captured or replayed workload, you will be prompted to import the AWR data before continuing. For more information about the AWR Compare Period report, see "Generating Automatic Workload Repository Compare Periods Reports".
AWR
The AWR report shows the AWR data contained in a workload that was captured or replayed. Before running this report, AWR data must have been previously exported from the captured or replayed workload. To run this report, under AWR Report, select the workload capture or replay for which you want to generate an AWR report and click Run Report. If AWR data is not previously exported from the captured or replayed workload, you will be prompted to import the AWR data before continuing. For more information about the AWR report, see "Generating Automatic Workload Repository Reports".
ASH
The ASH report contains active session history (ASH) information for a specified duration of a workload that was captured or replayed. Before running this report, AWR data must have been previously exported from the captured or replayed workload. To run this report, under ASH Report, select the workload capture or replay for which you want to generate an ASH report. Specify the duration using the Start Date, Start Time, End Date, and End Time fields. You can also apply filters using the Filter field. Once the duration and filters are specified, click Run Report. If AWR data is not previously exported from the captured or replayed workload, you will be prompted to import the AWR data before continuing. For more information about the ASH report, see "Generating Active Session History Reports".
The Report window opens while the report is being generated. Once the report is generated, you can save the report by clicking Save to File.
To return to the Database Replay page, click OK.
This section describes how to replay a database workload using the DBMS_WORKLOAD_REPLAY package. Replaying a database workload using the DBMS_WORKLOAD_REPLAY package is a multi-step process that involves:
After the workload capture is preprocessed and the test system is properly prepared, the replay data can be initialized. Initializing replay data loads the necessary metadata into tables required by workload replay. For example, captured connection strings are loaded into a table where they can be remapped for replay. For information about preprocessing a workload capture, see "Preprocessing a Database Workload". For information about preparing the test system, see "Setting Up the Test System".
To initialize replay data, use the INITIALIZE_REPLAY procedure:
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => 'dec06_102',
replay_dir => 'dec06');
END;
/
In this example, the INITIALIZE_REPLAY procedure loads preprocessed workload data from the dec06 directory into the database.
The INITIALIZE_REPLAY procedure in this example uses the following parameters:
The replay_name required parameter specifies a replay name that can be used with other APIs to retrieve settings and filters of previous replays.
The replay_dir required parameter specifies the directory that contains the workload capture that needs to be replayed.
After the replay data is initialized, connection strings used in the workload capture need to be remapped so that user sessions can connect to the appropriate databases and perform external interactions as captured during replay. To view connection mappings, use the DBA_WORKLOAD_CONNECTION_MAP view. For information about connection remapping, see "Remapping Connections".
To remap connections, use the REMAP_CONNECTION procedure:
BEGIN
DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (connection_id => 101,
replay_connection => 'dlsun244:3434/bjava21');
END;
/
In this example, the connection that corresponds to the connection ID 101 will use the new connection string defined by the replay_connection parameter.
The REMAP_CONNECTION procedure in this example uses the following parameters:
The connection_id required parameter is generated when initializing replay data and corresponds to a connection from the workload capture.
The replay_connection optional parameter specifies the new connection string that will be used during workload replay.
After the replay data is initialized and the connections are appropriately remapped, you need to prepare the database for workload replay. For information about workload replay preparation, see "Steps for Replaying a Database Workload".
To prepare workload replay on the replay system, use the PREPARE_REPLAY procedure:
BEGIN DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => TRUE); END; /
In this example, the PREPARE_REPLAY procedure prepares a replay that has been previously initialized. The COMMIT order in the workoad capture will be preserved.
The PREPARE_REPLAY procedure uses the following parameters:
The synchronization required parameter determines if synchronization will be used during workload replay. If this parameter is set to TRUE, the COMMIT order in the captured workload will be preserved during replay and all replay actions will be executed only after all dependent COMMIT actions have completed. The default value is TRUE.
The connect_time_scale optional parameter scales the elapsed time from when the workload capture started to when the session connects with the specified value and is interpreted as a % value. Use this parameter to increase or decrease the number of concurrent users during replay. The default value is 100.
The think_time_scale optional parameter scales the elapsed time between two successive user calls from the same session and is interpreted as a % value. Setting this parameter to 0 will send user calls to the database as fast as possible during replay. The default value is 100.
The think_time_auto_correct optional parameter corrects the think time (based on the think_time_scale parameter) between calls when user calls take longer to complete during replay than during capture and can be set to either TRUE or FALSE. The default value is TRUE.
For more information about setting these parameters, see "Specifying Replay Options".
Before starting a workload replay, you must first:
Preprocess the captured workload, as described in "Preprocessing a Captured Workload Using APIs"
Initialize the replay data, as described in "Initializing Replay Data"
Set up the replay options, as described in "Setting Workload Replay Options"
Start the replay clients, as described in "Setting Up Replay Clients"
To start a workload replay, use the START_REPLAY procedure:
BEGIN DBMS_WORKLOAD_REPLAY.START_REPLAY (); END; /
To stop a workload replay, use the CANCEL_REPLAY procedure:
BEGIN DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY (); END; /
Exporting AWR data enables detailed analysis of the workload. This data is also required if you plan to run 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_REPLAY.EXPORT_AWR (replay_id => 1); END; /
In this example, the AWR snapshots that correspond to the workload replay with a replay ID of 1 are exported. The EXPORT_AWR procedure uses the replay_id required parameter, which specifies the ID of the replay whose AWR snapshots will be exported. This procedure will work only if the corresponding workload replay was performed in the current database and the AWR snapshots that correspond to the original replay time period are still available.
This section summarizes the views that you can display to monitor workload replay. You need DBA privileges to access these views.
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, for both workload captures and workload replays, defined in the current database.
The DBA_WORKLOAD_REPLAYS view lists all the workload replays that have been replayed in the current database.
The DBA_WORKLOAD_REPLAY_DIVERGENCE view enables you to monitor workload replay divergence.
The DBA_WORKLOAD_CONNECTION_MAP view lists the connection mapping information for workload replay.
The V$WORKLOAD_REPLAY_THREAD view lists information about all sessions from the replay clients.
See Also:
Oracle Database Reference for information on these viewsThis section describes how to generate and analyze workload capture and workload replay reports. The primary tool for generating Database Replay reports is Oracle Enterprise Manager. If for some reason Oracle Enterprise Manager is unavailable, you can generate Database Replay reports using APIs.
This section contains the following topics:
Generating a Workload Capture Report Using Enterprise Manager
Generating a Workload Replay Report Using Enterprise Manager
The workload capture report contains captured workload statistics, information about the top session activities that were captured, and any workload filters used during the capture process.
To generate a workload capture report using Enterprise Manager:
On the Software and Support page, under Real Application Testing, click Database Replay.
The Database Replay page appears.
Click View Workload Capture History.
The View Workload Capture History page appears.
Select the workload capture for which you want to run a workload capture report and click View.
The View Workload Capture page appears.
To view the workload capture report, click View Workload Capture Report.
The Report window opens while the report is being generated.
Once the report is generated, you can save the report by clicking Save to File.
For information about how to use a workload capture report, see "Using a Workload Capture Report".
The workload capture report contains captured workload statistics, information about the top session activities that were captured, and any workload filters used during the capture process.
To generate a report on the latest workload capture, use the DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO procedure and the DBMS_WORKLOAD_CAPTURE.REPORT function:
DECLARE
cap_id NUMBER;
cap_rpt CLOB;
BEGIN
cap_id := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir => 'dec06');
cap_rpt := DBMS_WORKLOAD_CAPTURE.REPORT(capture_id => cap_id,
format => DBMS_WORKLOAD_CAPTURE.TYPE_TEXT);
END;
/
In this example, the GET_CAPTURE_INFO procedure retrieves all information regarding the workload capture in the dec06 directory and returns the appropriate cap_id for the workload capture. The REPORT function generates a text report using the cap_id that was returned by the GET_CAPTURE_INFO procedure.
The GET_CAPTURE_INFO procedure uses the dir required parameter, which specifies the name of the workload capture directory object.
The REPORT function uses the following parameters:
The capture_id required parameter relates to the directory that contains the workload capture for which the report will be generated. The directory should be a valid directory in the host system containing the workload capture. The value of this parameter should match the cap_id returned by the GET_CAPTURE_INFO procedure.
The format parameter required parameter specifies the report format. Valid values include DBMS_WORKLOAD_CAPTURE.TYPE_TEXT and DBMS_WORKLOAD_REPLAY.TYPE_HTML.
For information about how to use a workload capture report, see "Using a Workload Capture Report".
The workload capture report contains various types of information that can be used to assess the validity of the workload capture. Using the information provided in this report, you can determine if the captured workload:
Represents the actual workload you want to replay
Does not contain any workload you want to exclude
Can be replayed
The information contained in the workload capture report are divided into the following categories:
Details about the workload capture (such as the name of the workload capture, defined filters, date, time, and SCN of capture)
Overall statistics about the workload capture (such as the total DB time captured, and the number of logins and transactions captured) and the corresponding percentages with respect to total system activity
Profile of the captured workload
Profile of the workload that was not captured due to version limitations
Profile of the uncaptured workload that were excluded using defined filters
Profile of the uncaptured workload that consists of background process or scheduled jobs
The workload replay report contains information that can be used to measure performance differences between the capture system and the replay system.
To generate a workload replay report using Enterprise Manager:
On the Software and Support page, under Real Application Testing, click Database Replay.
The Database Replay page appears.
In the Go to Task column, click the icon that corresponds to the Replay Workload task.
The Replay Workload page appears.
In the Directory Object list, select a directory that contains the preprocessed workload that was used for the replay for which you want to generate a workload replay report.
After a directory is selected, the Replay Workload page will be refreshed to display the Capture Summary and the Replay History sections.
Under Replay History, select the replay for which you want to generate a workload replay report and click View.
The View Workload Replay page appears.
Click View Workload Replay Report.
For information about using the Workload Replay report, see "Using a Workload Replay Report".
The workload replay report contains information that can be used to measure data and performance divergence between the capture system and the replay system.
To generate a report on the latest workload replay for a workload capture, use the DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO procedure and the DBMS_WORKLOAD_REPLAY.REPORT function:
To generate a workload replay report, use the REPORT function:
DECLARE
cap_id NUMBER;
rep_id NUMBER;
rep_rpt CLOB;
BEGIN
cap_id := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(dir => 'dec06');
/* Get the latest replay for that capture */
SELECT max(id)
INTO rep_id
FROM dba_workload_replays
WHERE capture_id = cap_id;
rep_rpt := DBMS_WORKLOAD_REPLAY.REPORT(replay_id => rep_id,
format => DBMS_WORKLOAD_REPLAY.TYPE_TEXT);
END;
/
In this example, the GET_REPLAY_INFO procedure retrieves all information regarding the workload capture in the dec06 directory and the history of all the workload replay attempts from this directory. The procedure first imports a row into DBA_WORKLOAD_CAPTURES, which contains information about the workload capture. It then imports a row for every replay attempt retrieved from the replay directory into the DBA_WORKLOAD_REPLAYS view. The SELECT statement returns the appropriate rep_id for the latest replay of the workload. The REPORT function generates a text report using the rep_id that was returned by the SELECT statement.
The GET_CAPTURE_INFO procedure uses the dir required parameter, which specifies the name of the workload replay directory object.
The REPORT function uses the following parameters:
The replay_id required parameter relates to the directory that contains the workload replay for which the report will be generated. The directory should be a valid directory in the host system containing the workload replay. The value of this parameter should match the rep_id returned by the GET_CAPTURE_INFO procedure.
The format parameter required parameter specifies the report format. Valid values include DBMS_WORKLOAD_REPLAY.TYPE_TEXT, DBMS_WORKLOAD_REPLAY.TYPE_HTML, and DBMS_WORKLOAD_REPLAY.TYPE_XML.
For information about using the Workload Replay report, see "Using a Workload Replay Report".
After the workload is replayed on a test system, there may be some divergence in what is replayed compared to what was captured. There are numerous factors that can cause replay divergence, which can be analyzed using the workload replay report. The information contained in the workload replay report consists of performance and data divergence.
Performance divergence may result when new algorithms are introduced in the replay system that affect the overall performance of the database. For example, if the workload is replayed on a newer version of Oracle Database, a new algorithm may cause specific requests to run faster, and the divergence will appear as a faster execution. In this case, this is a desirable divergence.
Data divergence occurs when the results of DML or SQL queries do not match results that were originally captured in the workload. For example, a SQL statement may return fewer rows during replay than those returned during capture.
Error divergence occurs when the same errors are not encountered during replay and capture.
The information contained in the workload replay report are divided into the following categories:
Details about the workload replay and the workload capture, such as job name, status, database information, duration and time of each process, and the directory object and path
Replay options selected for the workload replay and the number of replay clients that were started
Overall statistics about the workload replay and the workload capture (such as the total DB time captured and replayed, and the number of logins and transactions captured and replay) and the corresponding percentages with respect to total system activity
Profile of the replayed workload
Replay divergence
Error divergence
DML and SQL query data divergence
