This tutorial shows you how to use the Database Replay feature to perform real-world testing by capturing the actual database workload on the production system and replaying it on the test system.
Approximately 1 hour
This tutorial covers the following topics:
|
Overview |
|
Prerequisites |
|
Setting Up Your Environment |
|
Using Database Replay |
|
Viewing Database Replay Reports |
|
Making a Database Change and Replaying the Workload |
|
Summary |
Place the cursor over this icon to load and view all the screenshots for this tutorial.
(Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.
What is Database Replay?
The Database Replay feature enables users to perform real-world testing by capturing the actual database workload on the production system and replaying it on the test system. The replay on the test system can be done with production characteristics including timing and concurrency. It also provides analysis and reporting to highlight potential problems (for example, errors encountered and divergence in performance) and recommend ways to remedy the problems.
Before you perform this tutorial, you should:
| 1. | Install Oracle Database 11g. |
|
| 2. | Download and unzip the dbreplay.zip file into your working directory and navigate into your working directory. |
|
Prior to using Database Replay, perform the following steps to set up your environment:
| 1. |
Open a terminal window. Change to the dbreplay directory and execute the first_time.csh script to set up the files. ./first_time.csh
If you execute first_time.csh a second time you won't receive the error.
|
|
| 2. |
Execute the wcr_demo_init.csh script to set up the environment. ./wcr_demo_init.csh
|
|
| 3. | Add the dbreplay directory to your PATH variable and add the $ORACLE_HOME/lib directory to the LD_LIBRARY_PATH variable. From your terminal window, execute the following commands: export PATH=.:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
|
|
In this section you will capture your production workload and use Database Replay to replay the workload. Enterprise Manager provides an interface to manage each step in the Database Replay process.
| A | Creating a Directory for the Capture |
| B. | Capturing the Production Workload |
| C. | Processing the Captured Workload |
| D. | Replaying the Workload |
Creating a Directory for the Capture
In the next subtopic, you will perform a capture. Prior to using Enterprise Manager Database Control to perform the capture, you must create a directory in the operating system. Perform the following step to create a directory for your capture:
| 1. | Create a directory named capture_dir in your working directory. Take note of the full path to your directory as you will need to specify this later.
|
To use Database Replay, you must first capture the production system workload that you want to use during the replay. Perform the following steps to capture the production workload:
| 1. |
Using your browser, invoke Enterprise Manager Database Console by enter the following URL: https://<hostname>:1158/em (Note: Specify localhost or your specific hostname in place of <hostname>). Log in as the SYS user with the SYSDBA password for your database.
|
| 2. |
Select the Software and Support tab.
|
| 3. |
In the Real Application Testing section, select Database Replay.
|
| 4. |
For the Capture Workload task, click the Go to Task icon.
|
| 5. |
Click the Acknowledge check box for each prerequisite and click Next.
|
| 6. | On the Capture Workload: Options page, select Do not restart the database prior to capture and click Next.
|
| 7. | On the Capture Workload: Parameters page, accept the default name and click Create Directory Object.
|
| 8. | On the Create Directory Object page, enter CAPDIR for the name (make sure it is in UPPERCASE). Specify the directory name for the directory you created in the previous substep in the Path field. Click Test File System.
|
| 9. | On the Host Login page, enter the operating system username and password. Click Login.
|
| 10. | A confirmation page is displayed. Click Return.
|
| 11. | You are returned to the Create Directory Object page . Click OK.
|
| 12. | You are returned to the Capture Workload: Parameters page and an update message is displayed. Click Next to continue with the capture setup.
|
| 13. | The Capture Workload: Schedule page is displayed. Accept the default Job Name. In the Job Schedule section, accept the default of Immediately in the Start subsection and Not Specified in the End subsection. (In your own environment you should select the capture period based on the nature of your applications and their peak periods.) At the bottom of the page, enter the host credentials and click Next.
|
| 14. | Note: In the next step you will start a workload for this capture task. Make sure to run the workload as soon as Enterprise Manager confirms that the capture has started. Similarly stop the workload capture as soon as the workload completes. This is recommended to avoid capturing a lot of idle time. In any event, DB Time (and not Elapsed Time) should be used for assessing database replays. Review the capture information. Click Submit to start the capture.
The View Workload Capture page is displayed confirming that the capture has commenced.
|
| 15. | Return to your terminal window immediately and change to your dbreplay directory. Execute the wcr_demo_workload.csh script.
Monitor the script. When it has completed, return to Enterprise Manager Database Control.
|
| 16. | In Enterprise Manager Database Control, click Stop Capture.
|
| 17. | Confirm that you want to stop the capture by clicking Yes.
|
| 18. | Click Yes to export the Automatic Workload Repository data.
|
| 19. | The workload summary page is displayed. Review the statistics. Click OK to return to the Workload Capture and Replay page. It may take the export take 1-2 minutes to complete.
|
| 20. | Return to your terminal window. Execute the following command to list the capture files in the directory structure that has been created inside your capture directory: find capture_dir -type f -print
|
Processing the Captured Workload
In the last subtopic you captured the workload. Prior to using the captured workload for replay, it must be processed. During the processing, the captured data is transformed into replay files and required metadata is created. Perform the following steps to process the captured workload:
| 1. | Return to the Database Replay page in Enterprise Manager Database Control. For the Preprocess Captured Workload task, click the Go to Task icon. Note: Preprocessing of the captured workload only needs to be done once on the version that the workload is replayed.
|
| 2. |
Select the directory object you specified for the capture in the previous subtopic in the Capture Directory drop-down menu.
|
| 3. | Summary information is displayed. Click Preprocess Workload.
|
| 4. | The Preprocess Captured Workload: Database Version page is displayed. Review the information displayed. Click Next.
|
| 5. | The Preprocess Captured Workload: Schedule page is displayed. Accept the default Job Name and Start time of Immediately. Enter the host username and password. Click Next.
|
| 6. | The Preprocess Workload: Review page is displayed. Review the information displayed. Click Submit.
|
| 7. | A confirmation message is displayed. Click View Job.
|
| 8. | The Job Run page is displayed. Use your browser's reload button to refresh the page until the job status is "Succeeded." Click the Database tab.
|
Enterprise Manager is used to replay the workload. In addition, the workload replay is accomplished by using replay clients connected to the replay database. Perform the following steps to replay the workload:
| 1. |
Return to your terminal window. Change to your dbreplay directory. Execute the wcr_demo_init.csh script to restore your database to its original state.
|
| 2. | Return to Enterprise Manager Database Control. Select the Software and Support tab.
|
| 3. | Under Real Application Testing, select Database Replay.
|
| 4. |
For the Replay Workload task, click the Go to Task icon.
|
| 5. |
In the Replay Directory drop-down menu, choose the directory object that contains your capture (CAPDIR).
|
| 6. | The page is refreshed with information about your capture. Click Set Up Replay.
|
| 7. | The Replay Workload: Prerequisites page is displayed. For the purposes of this tutorial, no changes are required. Click Continue.
|
| 8. | The Replay Workload: References to External Systems page is displayed. Click Continue again.
|
| 9. | The Replay Workload: Choose Initial Options page is displayed. Enter a name in the Replay Name field or accept the default. Click Next.
|
| 10. | The Replay Workload: Customize Options page is displayed. In this tutorial you do not need to make any changes because you are executing the replay on the same database instance. Click Next.
|
| 11. | The Replay Workload: Prepare Replay Clients page is displayed. You are alerted to the fact that you need to connect the replay clients. You will connect the client in a couple of steps. Click Next.
|
| 12. | The Replay Workload: Wait for Client Connections page is displayed.
|
| 11. | Return to your terminal window and execute the following command to start the replay client (wrc). The user specified must have the BECOME USER privilege. wrc REPLAYDIR=<capture directory path> USERID=system PASSWORD=<system password>
Note: It is generally recommended that the replay client (wrc) should be run on a system other than the database server. In such as case it would connect to the database server using Oracle Network Services. The reason for this is that the replay client places a load on the database server that can cause some level of performance divergence.
|
| 12. | Return to Enterprise Manager Database Control. A connection was detected. Click Next on the Replay Workload: Wait for Client Connections page.
|
| 13. | The Review page is displayed. Click Submit.
|
| 14. | The View Workload Replay page is displayed. You can watch the progression of the replay.
|
| 15. | After the workload replay is finished, the status is changed to "COMPLETED." You should assess database replay to capture by checking DB Time, Top wait events, errors, data divergence. Scroll to the bottom of the screen to investigate divergence and view more detailed comparison metrics.
As expected there is no data and error divergence.
The Detailed Comparison shows that the replay duration was shorter than the capture duration. This is because the capture duration includes all the time from the beginning of the capture to the end of the capture whereas the replay duration only includes the length of the workload (not the idle time just before the workload commenced and just after it finished). As expected the remaining statistics show little divergence.
|
| 16. | Return to your terminal window and note that the "Replay finished" message is displayed and control is returned to the operating system.
|
You can view a number of Database Replay reports by performing the following steps:
| 1. | Switch back to Enterprise Manager and from the View Workload Replay window, click the Report tab. Note: This tutorial illustrates the Database Replay workflow and functionality. Due to the short length and synthetic nature of the workload, the DB replay results will show some performance divergence. This will be less apparent for longer workload captures and replays.
|
| 2. | Under Workload Replay Report, click Run Report. Note: If the report does not display, make sure that your pop-up windows are not blocked in your browser.
|
| 3. | The Workload Replay Report provides a high level summary of the replay. Review the information on the report. When done, click OK to dismiss the report.
|
| 4. | Under Compare Period Report, click Run Replay Compare Period Report.
|
| 5. | This report is new in Oracle Database 11g Release 2. It provides a more detailed summary of the replay that can be used as a starting point for diagnostic analysis. Review the information on the report. When done, click OK to dismiss the report.
The Top SQL by Change in DB Time section is empty in this case as there was little divergence between the capture and the replay.
|
| 6. | You can also view an AWR Compare Period Report. Click Run AWR Compare Period Report.
|
| 7. | This report provides detailed statistics for low level diagnostic analysis. Review the report. When done, click OK.
|
| 8. | Click OK.
|
Now you will make a change to the database and perform the replay again. This time, the replay should consume more database time because the updates in the workload will have additional index management associated with them. The difference should be small but noticeable. Perform the following steps:
| 1. | Return to your terminal window. Change to your dbreplay directory. Execute the wcr_demo_init.csh script to restore your database to its original state.
|
| 2. | Execute the wcr_demo_dbchange.csh script to create some indexes on your tables.
|
| 3. | For the Replay Workload task, click the Go to Task icon.
|
| 4. | In the Replay Directory drop-down menu, choose the directory object that contains your capture (CAPDIR). The page is refreshed with information about your capture. Click Set Up Replay.
|
| 5. | The Replay Workload: Prerequisites page is displayed. For the purposes of this tutorial, no changes are required. Click Continue.
|
| 6. | The Replay Workload: References to External Systems page is displayed. Click Continue again.
|
| 7. | The Replay Workload: Choose Initial Options page is displayed. Enter a name in the Replay Name field or accept the default. Click Next.
|
| 8. | The Replay Workload: Customize Options page is displayed. In this tutorial you do not need to make any changes because you are executing the replay on the same database instance. Click Next.
|
| 9. | The Replay Workload: Prepare Replay Clients page is displayed. You are alerted to the fact that you need to connect the replay clients. You will connect the client in a couple of steps. Click Next.
|
| 10. | The Replay Workload: Wait for Client Connections page is displayed.
|
| 11. | Return to your terminal window and execute the following command to start the replay client. The user specified must have the BECOME USER privilege. wrc REPLAYDIR=<capture directory path> USERID=system PASSWORD=<system password>
|
| 12. | Return to Enterprise Manager Database Control. A connection was detected. Click Next on the Replay Workload: Wait for Client Connections page.
|
| 13. | The Review page is displayed. Click Submit.
|
| 14. | After the workload replay is finished, the status is changed to "COMPLETED." Check the Detailed Comparison section at the bottom of the page.
As predicted the additional index maintenance work caused a moderate increase in DB Time for this replay.
|
In this tutorial, you learned how to:
|
Capture your production workload | |
|
Process the captured workload | |
|
Replay the captured workload | |
Place the cursor over this icon to hide all screenshots.