Oracle 11g Feature Database Replay


Home

Oracle Database 11g Top New Features for DBA

08-AUG-07 Oracle 11g New Feature Database Replay

“Simulating production load is not possible” , you might have heard these word.

In one project, where last 2 year  management want to migrate from UNIX system to Linux system ( RAC ) , but they still testing because they are not sure where this Linux Boxes where bale to handle load or not. They have put lot of efforts and time in load testing and functional testing etc, but still not le gain confidence.

After using these feature of 11g , they will gain confidence and will able to migrate to Linux with full confidence and will know how there system will behave after migration/upgrade.

 

As per  datasheet given on OTN

Database Replay workload capture of external clients is performed at the database server level. Therefore, Database Replay can be used to assess the impact of any system changes below the database tier level such as below:

 

  •  Database upgrades, patches, parameter, schema changes, etc.
  • Configuration changes such as conversion from a single instance to RAC etc.
  • Storage, network, interconnect changes
  • Operating system, hardware migrations, patches, upgrades, parameter changes

 

 

DB replay does this by capturing a workload on the production system with negligible performance overhead( My observation is 2-5% more CPU usage ) and replaying it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This makes possible complete assessment of the impact of the change including undesired results; new contentions points or performance regressions. Extensive analysis and reporting ( AWR , ADDM report and DB replay report) is provided to help identify any potential problems, such as new errors encountered and performance divergences. The ability to accurately capture the production workload results in significant cost and timesaving since it completely eliminates the need to develop simulation workloads or scripts. As a result, realistic testing of even complex applications using load simulation tools/scripts that previously took several months now can be accomplished at most in a few days with Database Replay and with minimal effort. Thus using Database Replay, businesses can incur much lower costs and yet have a high degree of confidence in the overall success of the system change and significantly reduce production deployment

 

Steps for Database Replay

 

  1. Workload Capture

 

Database are tracked and stored in binary files, called capture files, on the file system. These files contain all relevant information about the call needed for replay such as SQL text, bind values, wall clock time, SCN, etc.

 

1)     Backup production Database #

2)     Add/remove filter ( if any you want )
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.
For example , we don't want to capture load for SCOTT user 

    

BEGIN
  DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
                           fname => 'user_scott',
                           fattribute => 'USER',
                           fvalue => 'SCOTT');
END;

Here filter name is "user_scott" ( user define name)

3)     Create directory make sure enough space is there

    

 

CREATE OR REPLACE DIRECTORY db_replay_dir
AS '/u04/oraout/test/db-replay-capture';

 

 

4)     Capture workload   

BEGIN
      DBMS_WORKLOAD_CAPTURE.start_capture (
       name => capture_testing',dir=>'DB_REPLAY_DIR',
     
duration => NULL );
END
; 

Duration => NULL mean , it will capture load till we stop with below mentioned manual SQL command. Duration is optional input to specify the duration (in seconds) , default is NULL

5)     Finish capture

   

BEGIN
    DBMS_WORKLOAD_CAPTURE.finish_capture;
END;  

 

 

# Take backup of production before Load capture, so we can restore database on test environment and will run replay on same SCN level of database to minimize data divergence

 

Note as per Oracle datasheet

 

The workload that has been captured on Oracle Database release 10.2.0.4 and higher can also be replayed on Oracle Database 11g release.So , I think , It simply mean NEW patch set 10.2.0.4 will support capture processes.  Is it mean Current patch set (10.2.0.3) not support load capture ??????

 

2.      Workload Processing

Once the workload has been captured, the information in the capture files has to be processed preferably on the test system because it is very resource intensive job. This processing transforms the captured data and creates all necessary metadata needed for replaying the workload.                  

exec DBMS_WORKLOAD_REPLAY.process_capture('DB_REPLAY_DIR');

  

 

  1. Workload Replay

1)     Restore database backup taken step one to test system and start Database

2)     Initialize

BEGIN
DBMS_WORKLOAD_REPLAY.initialize_replay (
  replay_name => 'TEST_REPLAY',
  replay_dir => 'DB_REPLAY_DIR');
END;

 

3)     Prepare

 

   exec DBMS_WORKLOAD_REPLAY.prepare_replay(synchronization => TRUE)

 

4)     Start clients

 

$ wrc mode=calibrate replaydir=/u03/oradata/test/db-replay-capture

Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:41 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Report for Workload in: /u03/oradata/test/db-replay-capture
-----------------------

Recommendation:
Consider using at least 1 clients divided among 1 CPU(s).

Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 7

Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE




$ wrc system/pass mode=replay replaydir=/u03/oradata/test/db-replay-capture

Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:52 2007
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Wait for the replay to start (00:31:52)

  

5)     Start Replay

    BEGIN
        DBMS_WORKLOAD_REPLAY.start_replay;
END;
/

 
 


$ wrc system/pass mode=replay replaydir=/u03/oradata/test/db-replay-capture

Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:52 2007
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Wait for the replay to start (00:31:52)
Replay started (00:33:32)
Replay finished (00:42:52)



  1. Analysis and Reporting

Generate AWR , ADDM and DB reply report and compare with data gathered on production for same time period when load was captured on Production database. For Database Replay Report  run following command 

                

 

   SQL>  COLUMN name FORMAT A20
                SQL> SELECT id, name FROM dba_workload_replays;

                            ID NAME
                    ---------- --------------------
                             1 TEST_REPLAY

 

DECLARE
   v_report  CLOB;
BEGIN
    v_report := DBMS_WORKLOAD_replay.report(
                     replay_id => 1,
                  format=>DBMS_WORKLOAD_CAPTURE.TYPE_HTML
                    );
    dbms_output.put_line(l_report);
END;
/

 


For sample report [ Click Here]



 

Reference 

Chapter 22 Database Replay