11g Workload Replay and Analysis on the Testing System Oracle 11g New Features Tips by Donald BurlesonJune 29, 2015

Oracle 11g New Features Tips

The last adjustment to be made before running the replay is about the replay itself. Here is where it can be specified as to whether the commit sequence should be respected during the replay or not.

M The default for the synchronization parameter is TRUE which means             dependent commits must be completed before the next work can be             performed. 

Additionally, it is possible to adjust connect time and think time to a percentage value of the original workload capture time. For example, if the first session connected 12 minutes after the capture started and the second session connected 18 minutes after the capture started, this could be scaled down to six and nine minutes by setting connect_time_scale to 50. It is similar with think time which can be used to adjust the time between two calls of the same session. Adjusting connect time and think time can be useful if the testing system is significantly stronger or weaker than the original system.

By leaving think_time_auto_correct to its default TRUE, leave the adjusting to the server.

Lutz @ orcl11gtest as sysdba SQL> exec DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY -
                       (synchronization =>TRUE,
                        connect_time_scale => 50,
                        think_time_scale =>50,
                        think_time_auto_correct => FALSE); 

If the choice is to run the replay exactly as it was recorded, do this:

Lutz @ orcl11gtest as sysdba SQL> DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY() 

Now the system is waiting for the actual start of the appropriate number of replay clients. Here is how to start a replay client:

oracle@rhas4test ~]$ wrc replaydir=/home/oracle/replay_dir 
Workload Replay Client: Release 11.1.0.6.0 Production on Mon Feb 11 12:42:35 2015
Copyright (c) 1982, 2015, Oracle.  All rights reserved.
Wait for the replay to start (12:42:35)

Note: The replay client logs onto the testing system by default as user SYSTEM.

This is what is found in v$session on the testing instance:

Lutz @ orcl11gtest as sysdba SQL> SELECT username, program, event, status
                                   FROM v$session
                                     WHERE event like '%WCR%'; 

USERNAME   PROGRAM                          EVENT STATUS
---------- -------------------------------- ---------------------
SYSTEM     wrc@rhas4.mydomain (TNS V1-V3)   WCR: replay client notify ACTIVE

It is possible to start multiple replay clients on the same machine. It is also possible to start replay clients on multiple machines in a clustered environment to replay the workload. This is how to actually start the replay:

Lutz @ orcl11gtest as sysdba SQL> DBMS_WORKLOAD_REPLAY.START_REPLAY

The replay clients will show this:
Replay started (13:23:27)

A number of data dictionary views can be used to monitor the replay:

Lutz @ orcl11gtest as sysdba SQL> SELECT id,
                                         name,
                                         dbversion,
                                         status, 
                                         elapsed_time_diff,
                                         awr_begin_snap,
                                         awr_end_snap
                                    FROM dba_workload_replays; 

ID NAME      DBVERSION    STATUS  ELAPSED_TIME_DIFF AWR_BEGIN_SNAP AWR_END_SNAP
-- ------    ---------    -------  ---------------- -------------- -----------
1 test_replay_1  11.1.0.6.0 COMPLETED    16423      89           90

A report can also be shown in text or html format from the package:

Lutz @ orcl11gtest as sysdba SQL> SELECT dbms_workload_replay.report(1,'TEXT')
                                   FROM dual;

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

| DB Name  | DB Id     | Release    | RAC | Replay Name   | Replay Status |
---------------------------------------------------------------------------
| ORCL11GTEST | 2825011450 | 11.1.0.6.0 | NO  | test_replay_1 | COMPLETED     |
--------------------------------------------------------------------------- 

Replay Information

-------------------------------------------------------------------------------|  
Information    | Replay                               | Capture                 |
-------------------------------------------------------------------------------|
Name             | my_test_replay_1                        |
test_capture_2                 |
-------------------------------------------------------------------------------
| Status           | COMPLETED                            | COMPLETED                
|
-------------------------------------------------------------------------------
| Database Name    | ORCL11GTEST                             | ORCL11G                
|
-------------------------------------------------------------------------------|
Database Version | 11.1.0.6.0                           | 11.1.0.6.0                
|
-------------------------------------------------------------------------------|
Start Time       | 11-FEB-08 14:19:27                   | 11-FEB-08
14:19:27                |
-------------------------------------------------------------------------------
| End Time         | 11-FEB-08 14:22:41                   | 11-FEB-08
14:25:35                 |
-------------------------------------------------------------------------------
| Duration         | 3 minute 14 seconds                   |5 minutes 8
seconds                 |
-------------------------------------------------------------------------------
| Directory Object | CAPTURE_DIR                      | WORKLOAD_DIR                 |
-------------------------------------------------------------------------------
| Directory Path   | /home/oracle/capture_dir | /home/oracle/my_workload_dir |
-------------------------------------------------------------------------------

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

Replay Statistics
------------------------------------------------
|        Statistic        | Replay   | Capture |
------------------------------------------------
| DB Time                 | 96206   | 112629 |
------------------------------------------------
| Average Active Sessions | .01      | .01     |
------------------------------------------------
| User calls              | 15       | 15      |
------------------------------------------------
| Network Time            | 2523     | .       |
------------------------------------------------
| Think Time              | 19158449 | .       |
------------------------------------------------
| Elapsed Time Difference | 16423   | .       |
------------------------------------------------
| 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

Lutz @ orcl11gtest as sysdba SQL> desc dba_workload_replay_divergence

 Name                                    Null?    Type
 --------------------------------------- -------- ---------------------------
REPLAY_ID                                NOT NULL NUMBER
TIMESTAMP                                         TIMESTAMP(6) WITH TIME ZONE
DIVERGENCE_TYPE                          NOT NULL NUMBER
IS_QUERY_DATA_DIVERGENCE                          VARCHAR2(1)
IS_DML_DATA_DIVERGENCE                            VARCHAR2(1)
IS_ERROR_DIVERGENCE                               VARCHAR2(1)
IS_THREAD_FAILURE                                 VARCHAR2(1)
EXPECTED_ROW_COUNT                                NUMBER
OBSERVED_ROW_COUNT                                NUMBER
EXPECTED_ERROR#                                   NUMBER
OBSERVED_ERROR#                                   NUMBER
STREAM_ID                                         NOT NULL NUMBER
CALL_COUNTER                                      NOT NULL NUMBER
SQL_ID                                            VARCHAR2(13)
SESSION_ID                               NOT NULL NUMBER
SESSION_SERIAL#                          NOT NULL NUMBER
SERVICE                                           VARCHAR2(64)
MODULE                                            VARCHAR2(48)
ACTION                                            VARCHAR2(32)

In order to analyze the divergences between the capture run and the replay of the workload, it would be best to use the comparison report for the two snapshot ranges in the AWR and use ADDM to get recommendations for improvement. This will be now shown with the graphical interface.