In this Document

Applies to:

Goal

To give a basic introduction to workload capture and replay

Solution

What is the workload capture/replay utility?

Prior to 11G it was extremely difficult to test what effects any upgrades may have on workloads without great expense. System implementors need confidence in any changes they may make to configuration before they go ahead and "go live" on any new implementations. Some keys changes which would typically require proper testing are:-

o Hardware/Software Upgrades
o Implementation of new features

Using Database Testing Capture and Replay,  it is easy to confidently predict the outcome of any changes on a given workload. Typically what would happen is that a typical workload (for instance a period of OLTP activity or a batch job) will be recorded with the 'capture' utility and then passed to a copy of the instance which has been upgraded and the workload replayed. Using existing features like AWR it is then easy to compare the effect of any changes which have been made and then put into place corrective actions should that be required.

The workload capture/replay utility works by us being able to record the workload in the RDBMS and then replaying that workload by having clients (WRC processes) consume the recorded information and replaying that to the RDBMS, so the replay is actually controlled from outside the database rather then inside it.

Before you begin

1) The Real Application Testing Option must be installed
2) The package dbms_workload_capture must be present and valid (created by dbmswrr.sql as part of the install)

Restrictions

The following workloads are captured but not supported. It is suggested that capture filters be used to minimise the possiblity of the replay failing:

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

The following additional restrictions also apply:

1) Workload is captured for migrated sessions. However, Database Replay does not support session migration  (See BUG 13524303). 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.
2) XA Transactions
3) Workload with Object Out Binds are not supported
4) Database Resident Connection Pooling ( DRCP )
5) MTS/Shared server is not supported with synchronization=object_id

Capturing a workload

The capture scripts should be run as sys.

Create a directory to hold the workload information

If one does not already exist then create a directory to hold the workload information (The directory must be empty):-

Start the database in restricted mode

You should now start the database in restricted mode for the capture to work correctly. This ensures that users are not in the middle of connecting or transactions when the capture begins. Once the capture is started then the database will be automatically placed in unrestricted mode.

If restricted mode is not operational it is possible you will receive the following error when trying to start the capture :-

...or..

Start the Capture

You can now start the capture using a name to identify the capture.

A few points to note:-

o The directory name should always be upper case.
o It is possible to start the capture without restricted mode using the argument no_restart_mode=TRUE but this is not advisable as it relies on transactional integrity

At this point the work now being done will be captured by the RDBMS

To stop the capture use:-

Using filters with workload capture

A filter is a set of attributes and values that define a set of actions which can be included or excluded from a capture session. The filter is defined using :

To then run the capture session you would call:-

If I wanted to use the filter to EXCLUDE work carried out by user SCOTT:-

If INCLUDE is used, by default all user requests to the database will be captured, except for the part of the workload defined by the filters. If EXCLUDE is used, by default no user request to the database will be captured, except for the part of the workload defined by the filters. Other FILTER attributes which can be used are:-

• -- SESSION_ID - type NUMBER
• -- USER - type STRING
• -- MODULE - type STRING
• -- ACTION - type STRING
• -- PROGRAM - type STRING
• -- SERVICE - type STRING

Filters NOT USED in a capture yet can be maintained by using DELETE filter , eg dbms_workload_capture.DELETE_FILTER('my_filter')

If the FILTER has been used then it can only be deleted as part of the capture by using DBMS_WORKLOAD_CAPTURE.DELETE_CAPTURE_INFO

Displaying information on a capture

To show the latest capture id:-

If you wish to see a report of the capture (let's assume the above was id 1) then do:-

The output will be something like:-

You can also see the files in the directory (ie /home/%usernm%/my_workload_directory) :

Running an AWR report for the period of the capture

As part of the capture, the RDBMS will also initiate an AWR report for the duration of the capture period. This can be seen by running the following for a given capture id :-

So, we can now run @?/rdbms/admin/awrrpt for the given begin and end snapids to get the report (See Document 276103.1)

Database Replay

Preparing for the replay

An instance identical to the instance used for the capture should exist - by identical, it should be logically the same. If the replay is purely read only (selects etc) then the structure of the database should be that same and if the replay includes update/delete then the copy must be able to support this logically. The files created by the capture should also be transferred to the new server , preferably in their own directory , and be readable by the oracle software owner. You should also consider setting the system time on the replay machine to be the same as that prior to the capture being run on the original instance to ensure that time sensitive operations (ie SQL relying on SYSDATE) replay accurately First of all you need to ensure that there are sufficient replay clients running in order to replay the workload. To determine how many clients you need, once you have got as far as processing the capture directory(DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE) you can run:-

Which will then tell you how many clients it recommends you start:-

From the OS command line you can start the client(s) using:-

You would then see something like:-

The process appears as a session in the instance , ie:-

And from a session perspective , will appear in v$session and sit on event 'WRR: replay client notify' while it waits for work. Ensure the directory exists:-

First of all , we need to process the CAPTURE in the directory :-

This will create additional files which contain metadata required for the replay in the directory:-

We now need to put the database in a state of readiness for replaying the workload:-

This loads the connection maps so that we can remap them to the new instance if required (See Connection Maps). We can then put the database in a replay state:-

Where :-

• synchronization = (TRUE/FALSE) - Ensure the replay observes the commit sequence of the capture. ie any work is run only after dependent commits in the replay are completed.
• connect_time_scale = Scale the time between each connection (as a percentage - default 100) ie suppose the capture was started at 12:00 and the first connection happened at 12:10, the 2nd at 12:20 With the parameter set at 50 then when the replay occurs the first connection will happen at 12:05 as that is 50% of the time taken during the capture and the 2nd will start at 12:10 (10 minutes after the start ,not 20) Likewise, if the parameter is set to 200 then the first connection would start 20 minutes after the replay starts Using this parameter it is possible to increase and decrease the concurrency and test throughput
• think_time_scale= Scale the time between user calls within a session (as a percentage - default 100) ie suppose user SCOTT connects at 12:10 does nothing for 10 minutes, runs some SQL which takes 6 minutes , does nothing for 4 minutes and then runs something new. With the parameter set at 50 then the time between the initial connection and the first call will be 5 minutes, the time between the first SQL finishing and us running the second will be 3 minutes.
• think_time_auto_correct= (TRUE/FALSE) - Auto correct the think time if the replay is slower/faster then the capture. In other words, if we took a minute longer to run a SQL statement in the replay then we will adjust the time of the next call such that it is done at the same time point in the replay as the capture. ie if we ran some SQL at 12:10 which took 5 minutes during the capture and then ran something at 12:20 (5 minutes after the completion) then auto correction will, if that SQL took 8 minutes during the replay adjust the time so the next call occurs 2 minutes after the completion so that it effectively starts at "12:20"

Using all the above , typically we would just run:-

..which would just prepare to replay the SQL in exactly the same way it was recorded

Executing the replay

Once all the above is completed then the replay can begin:

What can be observed during this time is the work will be picked up by the wrc clients. You will see this in the client session:-

Once this has completed you can view dba_workload_replays:-

You can also view a report of the replay:-

DB Replay Report for test_replay_1
---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------
| DB Name  | DB Id     | Release    | RAC | Replay Name   | Replay Status |
---------------------------------------------------------------------------
| JCREPLAY | 147275354 | 11.1.0.5.0 | NO  | test_replay_1 | COMPLETED     |
---------------------------------------------------------------------------

Replay Information
--------------------------------------------------------------------------------------------------
|   Information    | Replay                               | Capture                 |
--------------------------------------------------------------------------------------------------
| Name             | test_replay_1                        | test_capture_2                 |
--------------------------------------------------------------------------------------------------
| Status           | COMPLETED                            | COMPLETED                 |
--------------------------------------------------------------------------------------------------
| Database Name    | JCREPLAY                             | V11B5                 |
--------------------------------------------------------------------------------------------------
| Database Version | 11.1.0.5.0                           | 11.1.0.5.0                 |
--------------------------------------------------------------------------------------------------
| Start Time       | 25-JUL-07 16:19:27                   | 25-JUL-07 14:09:49                 |
--------------------------------------------------------------------------------------------------
| End Time         | 25-JUL-07 16:20:31                   | 25-JUL-07 14:12:11                 |
--------------------------------------------------------------------------------------------------
| Duration         | 1 minute 4 seconds                   | 2 minutes 22 seconds                 |
--------------------------------------------------------------------------------------------------
| Directory Object | MY_WORKLOAD_DIR                      | MY_WORKLOAD_DIR                 |
--------------------------------------------------------------------------------------------------
| Directory Path   | /home/%usernm%/my_workload_directory | /home/%usernm%/my_workload_directory |
--------------------------------------------------------------------------------------------------

Replay Options
---------------------------------------------------------
|       Option Name       | Value                       |
---------------------------------------------------------
| Synchronization         | TRUE                        |
---------------------------------------------------------
| Connect Time            | 100%                        |
---------------------------------------------------------
| Think Time              | 100%                        |
---------------------------------------------------------
| Think Time Auto Correct | TRUE                        |
---------------------------------------------------------
| Number of WRC Clients   | 1 (1 Completed, 0 Running ) |
---------------------------------------------------------

Replay Statistics
------------------------------------------------
|        Statistic        | Replay   | Capture |
------------------------------------------------
| DB Time                 | 932061   | 1006294 |
------------------------------------------------
| Average Active Sessions | .01      | .01     |
------------------------------------------------
| User calls              | 46       | 50      |
------------------------------------------------
| Network Time            | 2724     | .       |
------------------------------------------------
| Think Time              | 17159449 | .       |
------------------------------------------------
| Elapsed Time Difference | 148907   | .       |
------------------------------------------------
| New Errors              | 0        | .       |
------------------------------------------------
| Mutated Errors          | 0        | .       |
------------------------------------------------
---------------------------------------------------------------------------------------------------
Workload Profile Top Events (+) Show (-) Hide
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
Top Service/Module/Action (+) Show (-) Hide
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
Top SQL with Top Events (+) Show (-) Hide
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
Top Sessions with Top Events (+) Show (-) Hide
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
Replay Divergence Session Failures By Application (+) Show (-) Hide
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
Error Divergence By Application (+) Show (-) Hide
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
By SQL (+) Show (-) Hide
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
By Session (+) Show (-) Hide
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
DML Data Divergence By Application (+) Show (-) Hide
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
By SQL (+) Show (-) Hide
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
SELECT Data Divergence By Application (+) Show (-) Hide
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------

End of Report.

You should use the report to ascertain that the replay ran without error first of all - any errors may well constitute an invalid replay so these should be address where possible and the replay resubmitted.

Connection Maps

Obviously, when connections are being made during capture, those are only pertinent to the instance where the capture occured. Any connection strings etc need to be remapped during the replay to ensure they point to the correct instance. Current connections can be viewed by selecting against DBA_WORKLOAD_CONNECTION_MAP To change a connection you can use:-

Where connection_id is the connection_id of the connection you wish to change and replay_connection is the new connection string.

References