“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
OTNDatabase 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
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'; |
Remember in case on Oracle RAC directory must be on shared disk otherwise , you will get following error
| SQL> l
1 BEGIN
2 DBMS_WORKLOAD_CAPTURE.start_capture (name =>'capture_testing',dir => 'DB
3 END;
4* SQL> /
BEGIN
*
ERROR at line 1:
ORA-15505: cannot start workload capture because instance 2 encountered errors
while accessing directory "/u04/oraout/test/db-replay-capture"
ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 799
ORA-06512: at line 2
|
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'); |
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) |
Analysis and Reporting
Generate AWR , ADDM and DB reply report and compare with data gathered on production for same timeperiod 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;
/ |
Reference
Chapter 22 Database Replay
Posts
