Wednesday, May 2, 2012

Oracle Database Replay in Oracle database 11g

Database Replay and SQL performance analyzer are the two tools of Oracle new Real Application Testing (RAT) feature. If there is a change in database in terms of new code promotion, software or OS then these two tools of RAT can help to predict how the production server is going to cope with that change

Oracle Supplied Packages for Database Replay


Oracle Supplied Packages for SQL Performance Analyzer


In this article we are going to discuss "Database Replay"

Database Replay:

Database Replay allows you to capture database workload on the production server and replay it on test server by using the above mentioned database replay oracle supplied packages.Once the testing is done, reports can be produced to analyze and implement changes, if any recommend by database replay.

Situations to use Database Replay:

1. Storage changes
2. Moving from single instance to RAC
3. OS and database upgrades/ migrations

WorkLoad captured by Database Replay:

1. SQL queries
2. PL/SQL blocks
3. Session Login & logoffs and switches
4. OCI Calls and DDL/DML statements etc

Phases of Database Replay:

1. Workload Capture: this is when you record the production database workload.
2. Workload Preprocessing: this is to convert the captured workload into replay files.
3. Workload Replay:after the step above,  apply the changes on the test system.
4. Analysis and Reporting: when the step above successfully finishes, you generate report on the errors and performance influence.

Things to consider before capturing the workload:

1.Take production database backup that can be used to create a testing env. for database replay testing
2.Shut-down and restart the database to ensure all pending transactions are finished before the replay
3.Create directory object for storing captured load by connecting as sysdba user as shown below

create or replace directory db_replay_dir as '/u01/app/oracle/db_replay';

4.Decide whether some of the user sessions should not be captured. e.g. DBA sessions, Oracle Enterprise Manager sessions etc. as shown below

   dbms_workload_capture.add_filter ( fname => 'user_filter', fattribute => 'USER', fvalue  => 'reporting' );

fname specifies the filter name. This can be any name
fattribute specifies the filter attributes such as program, module,  action, service, instance_number, and user.
fvalue specifies the user name for which the workload has to be captured

below is the SQL to find out the existing filters in the database


In case if you want to remove the above filter then use the below mentioned script

           dbms_workload_capture.delete_filter (fname =>  'user_filter'); 

Workload Capture:

Start the workload capture by using the START_CAPTURE procedure as shown below

   dbms_workload_capture.start_capture (name  => '2012May',  dir  => 'DB_REPLAY_DIR',duration => NULL); 

name stands for the name of the workload capture.
dir specifies the directory object pointing to the workload capture directory created in step 2.
duration specifies the number of seconds for which the workload will be captured.

If you want to specify the duration for the workload capture process then use the AWR (automatic workload repository) or ASH tools to select the appropriate period based on the workload history of the production database

To stop the capture process before ending of duration period, issue the following command:

   dbms_workload_capture.finish_capture ;

The workload capture will continue indefinitely, until you stop it with the FINISH_CAPTURE procedure or time specified by the duration parameter is reached.
You can also use the DBA_WORKLOAD_CAPTURES view to see all the workload captures performed by the database.

SQL> SELECT id, name FROM dba_workload_captures;

        ID NAME
---------- ------------------------------
         1 2012May

Checkout the capture directory to see if there are any files generated 

$ cd /u01/app/oracle/db_replay
$ ls
wcr_cr.html  wcr_cr.text  wcr_fcapture.wmd  wcr_scapture.wmd

we can get the ID of the capture run by passing directory name as below



You can generate a report about the workload capture you have made:

v_capture_id  number;
v_capture_rpt clob;
v_capture_rpt := DBMS_WORKLOAD_CAPTURE.REPORT( CAPTURE_ID => v_capture_id , FORMAT => DBMS_WORKLOAD_CAPTURE.TYPE_TEXT); -- format could also be TYPE_HTML
-- display contents of v_capture_rpt

Alternatively, you can use the following statements:



Preprocessing Workload Capture Data:

To be able to replay a Workload Capture, you must preprocess its data. Although it is possible to preprocess in the production database, practically it is done in a test database. Preprocessing includes the following steps:

1. Restore the test database from the backup you made in the production database. The target is to make the same application become in the same state as it has been in the production database.

2. Create a directory object in the test database to hold the workload capture data files.

create or replace directory db_replay_dir as '/u01/app/oracle/db_replay';

3. Move the workload data files from the production database to the created directory in the test system.

4. Use the PROCESS_CAPTURE procedure to process the workload data:


Replaying the Workload:

Typically, at this stage, you perform the changes you want to undertake on the system. Then you  start the replay process. Replaying Workload is done by performing of the following steps:

1. It is recommended to set the time of the test system to the time when the workload was captured on the production system. This is to avoid any invalid time-based data or job-scheduling issues.

2. Take steps to resolve, if any, external references including: database links, external tables, directory objects, and URLs.

3. Initialize the Replay Data: this process means metadata will be read from Workload Capture files and loaded into tables. Workload replay process will read from those tables when it operates. Initializing the replay data is done by invoking the procedure INITIALIZE_REPLAY


  DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'test_capture_1',  replay_dir  => 'DB_REPLAY_DIR');
  DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE);

Before we can start the replay, we need to calibrate and start a replay client using the "wrc" utility. The calibration step tells us the number of replay clients and hosts necessary to faithfully replay the workload.

$ wrc mode=calibrate replaydir=/u01/app/oracle/db_replay

Workload Replay Client: Release - Production on Wed May 2 15:27:37 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Report for Workload in: /u01/app/oracle/db_replay

Consider using at least 1 clients divided among 1 CPU(s)
You will need at least 3 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.

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

- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- 256 KB of memory cache per concurrent session
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE

The calibration step suggest a single client on a single CPU is enough, so we only need to start a single replay client, which is shown below.

$ wrc system/password mode=replay replaydir=/u01/app/oracle/db_replay

Workload Replay Client: Release - Production on Wed May 2 15:37:00 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Wait for the replay to start (15:37:00)

The replay client pauses waiting for replay to start. We initiate replay with the following command.


The output from the replay client includes the start and finish time of the replay operation.

$ wrc system/DBAKanetix921 mode=replay replaydir=/u01/app/oracle/db_replay

Workload Replay Client: Release - Production on Wed May 2 15:39:02 2012

Copyright (c) 1982, 2009, Oracle and/or it

Wait for the replay to start (15:39:02)
Replay started (15:39:40)
Replay finished (15:44:01)

Query the DBA_WORKLOAD_REPLAYS and generate the HTML report as below

SQL> SELECT id, name FROM dba_workload_replays;
        ID NAME---------- ------------------------------         1 test_capture_1

DECLARE  my_report  CLOB;BEGIN  my_report := => 1,format => DBMS_WORKLOAD_REPLAY.TYPE_HTML);END;

reference: Oracle documentation,

No comments:

Post a Comment