Database Replay by example

Preamble

Have you ever fought with a customer that doesn’t want to support you, for testing application, when upgrading a database ? Have you ever wanted to predict how your application will behave if changing a software and/or an hardware piece ? Database Replay included in Real Application Testing (RAT) is the answer to all those production questions and helps you to maintain your Service Level Agreement (SLA) with customers…

Database replay consists of capturing a workload (from your production database ideally) and execute it on another database (your QA/Dev one for example). This is done by storing the executed workload in flat files that can be copied to your target system.

Unfortunately it is not free and comes as an option of Enterprise Edition. RAT is as expensive as Partitioning or Advanced Compression. RAT option is made of database replay and SQL Performance Analyzer (SPA) that we have already seen.

This blog post has been done on Red Hat Enterprise Linux Server release 5.5 (Tikanga) with Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production.

For testing purpose I have created the below table in my own Oracle account (YJAQUIER):

DROP TABLE test1;
 
CREATE TABLE test1(id NUMBER, descr VARCHAR(50)) TABLESPACE st_data;

Then I fill my test table using below PL/SQL code. I executed it four times to have around 20 millions rows:

DECLARE
  i NUMBER;
  nbrows NUMBER;
BEGIN
  SELECT NVL(MAX(id),0) INTO i FROM test1;
  i:=i+1;
  nbrows:=i+5000000;
  LOOP
    EXIT WHEN i>nbrows;
    INSERT INTO test1 VALUES(i, RPAD('A',49,'A'));
    i:=i+1;
  END LOOP;
  COMMIT;
END;
/

For capture process I do not create any index to generate workload and for replay operation I create an index on id column to speed up update process. This will simulate a data model change, that is common operation in production environment.

Capture

As SYS user create the database capture directory:

SQL> CREATE OR REPLACE DIRECTORY dbcapture AS '/home/orapris/yannick/dbcapture';
 
DIRECTORY created.

And add a filter to capture only what will be executed by YJAQUIER account (inclusion filters). Please note it is case sensitive i.e. Oracle username must be in capital letters:

SQL> EXEC dbms_workload_capture.add_filter('YJAQUIER user','USER','YJAQUIER');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> col TYPE format a10
SQL> col status format a10
SQL> col name format a20
SQL> col attribute format a10
SQL> col VALUE format a30
SQL> SET lines 150
SQL> SELECT * FROM dba_workload_filters;
 
TYPE               ID STATUS     NAME                 ATTRIBUTE  VALUE
---------- ---------- ---------- -------------------- ---------- ------------------------------
CAPTURE             0 NEW        YJAQUIER USER        USER       YJAQUIER

Remark:
If for any reason you want to change a filter you can delete it with DBMS_WORKLOAD_CAPTURE.DELETE_FILTER and then recreate it. Please note that if the filter has already been used in a completed capture process (status is equal to USED) then you cannot delete it anymore using this function and you have to use DBMS_WORKLOAD_CAPTURE.DELETE_CAPTURE_INFO.

Then start the capture process, as SYS, with below statement (no duration and in EXCLUDE mode):

SQL> EXEC DBMS_WORKLOAD_CAPTURE.START_CAPTURE(name => 'YJAQUIER capture', dir => 'DBCAPTURE', default_action => 'EXCLUDE');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET lines 150
SQL> col name FOR a20
SQL> SELECT name,DIRECTORY,status,filters_used FROM DBA_WORKLOAD_CAPTURES;
 
NAME                 DIRECTORY                      STATUS                                   FILTERS_USED
-------------------- ------------------------------ ---------------------------------------- ------------
YJAQUIER capture     DBCAPTURE                      IN PROGRESS                                         1

Then, as YJAQUIER account in another SQL*Plus session, I execute below PL/SQL code to generate workload. I intentionally update table putting same value to avoid to be able to restore table in its initial state for replay (you could have done it using flashback table technology). I execute as well a simple update on test1 table. Here no need to restart the database in restricted mode, as suggested, as I’m only one using my account and I will start workload process after capture has been started:

SET serveroutput ON SIZE 999999
DECLARE
i NUMBER;
random_id NUMBER;
maxid NUMBER;
stmt VARCHAR2(100);
BEGIN
  SELECT NVL(MAX(id),0) INTO maxid FROM test1;
  FOR i IN 1..10 LOOP
    random_id:=ROUND(DBMS_RANDOM.VALUE(1,maxid));
    DBMS_OUTPUT.PUT_LINE('UPDATE test1 SET id=' || random_id || ' WHERE id=' || random_id || ';');
    UPDATE test1 SET id=random_id WHERE id=random_id;
  END LOOP;
  COMMIT;
END;
/
 
UPDATE test1 SET id=12357280 WHERE id=12357280;
UPDATE test1 SET id=17883237 WHERE id=17883237;
UPDATE test1 SET id=9755142 WHERE id=9755142;
UPDATE test1 SET id=10406059 WHERE id=10406059;
UPDATE test1 SET id=8549215 WHERE id=8549215;
UPDATE test1 SET id=12281604 WHERE id=12281604;
UPDATE test1 SET id=13974471 WHERE id=13974471;
UPDATE test1 SET id=11680990 WHERE id=11680990;
UPDATE test1 SET id=7415851 WHERE id=7415851;
UPDATE test1 SET id=4863599 WHERE id=4863599;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> UPDATE test1 SET id=15702806 WHERE id=15702806;
 
1 ROW updated.
 
SQL> COMMIT;
 
COMMIT complete.

Once the update process is over, back to our SYS session, I end the capture process:

SQL> EXEC DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(timeout => 0, reason => 'Load test over');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET lines 150
SQL> col name FOR a20
SQL> SELECT name,DIRECTORY,status,filters_used FROM dba_workload_captures;
 
NAME                 DIRECTORY                      STATUS                                   FILTERS_USED
-------------------- ------------------------------ ---------------------------------------- ------------
YJAQUIER capture     DBCAPTURE                      COMPLETED                                           1

Let’s see what has been generated in DBCAPTURE directory:

[orapris@server1 dbcapture]$ pwd
/home/orapris/yannick/dbcapture
[orapris@server1 dbcapture]$ ll
total 8
drwxr-x--- 2 orapris dba 4096 Jan 24 13:05 cap
drwxr-x--- 3 orapris dba 4096 Jan 24 13:01 capfiles
[orapris@server1 dbcapture]$ ll *
cap:
total 96
-rw-r----- 1 orapris dba 56850 Jan 24 13:05 wcr_cr.html
-rw-r----- 1 orapris dba 26572 Jan 24 13:05 wcr_cr.text
-rw-r----- 1 orapris dba   257 Jan 24 13:04 wcr_fcapture.wmd
-rw-r----- 1 orapris dba   139 Jan 24 13:01 wcr_scapture.wmd
 
capfiles:
total 4
drwxr-x--- 12 orapris dba 4096 Jan 24 13:01 inst1

Strangely on my Linux box I cannot use vi on file but cat command is working fine… Anyway transfer the html file on your desktop/laptop and it should give you something like this file.

You can even generate it in Html or Text mode with DBMS_WORKLOAD_CAPTURE.REPORT procedure:

SQL> SET lines 150
SQL> SET pagesize 1000
SQL> SET LONG 999999
SQL> SET longchunksize 150
SQL> SELECT dbms_workload_capture.report(0,'HTML') FROM dual;

Replay

Fist process workload with below procedure:

SQL> EXEC DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE('DBCAPTURE');
 
PL/SQL PROCEDURE successfully completed.

Second initialize the replay:

SQL> EXEC DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(replay_name => 'YJAQUIER replay', replay_dir => 'DBCAPTURE');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET lines 150
SQL> col name FOR a20
SQL> SELECT name,DIRECTORY,status FROM dba_workload_replays;
 
NAME                 DIRECTORY                      STATUS
-------------------- ------------------------------ ----------------------------------------
YJAQUIER replay      DBCAPTURE                      INITIALIZED

Finally prepare it:

SQL> EXEC DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY();
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET lines 150
SQL> col name FOR a20
SQL> SELECT name,DIRECTORY,status FROM dba_workload_replays;
 
NAME                 DIRECTORY                      STATUS
-------------------- ------------------------------ ----------------------------------------
YJAQUIER replay      DBCAPTURE                      PREPARE

I noticed this generates additional file in capture directory:

[orapris@server1 dbcapture]$ pwd
/home/orapris/yannick/dbcapture
[orapris@server1 dbcapture]$ ll
total 16
drwxr-x--- 2 orapris dba 4096 Jan 24 15:56 cap
drwxr-x--- 3 orapris dba 4096 Jan 24 15:32 capfiles
drwxr-x--- 2 orapris dba 4096 Jan 24 16:47 pp11.2.0.1.0

To simulate a data model change I create an index on test1.id column and I gather table statistics:

SQL> CREATE INDEX test1_idx_id ON test1(id) TABLESPACE st_data;
 
INDEX created.
 
SQL> EXEC dbms_stats.gather_table_stats('yjaquier','test1');
 
PL/SQL PROCEDURE successfully completed.

Now all is prepared and we can start the database replay client (wrc executable in $ORACLE_HOME/bin directory). The number of database replay client is linked to workload you captured. Fortunately, you can know in advance how many replay clients you may need using the calibrate mode of wrc workload replay client (DBMS_WORKLOAD_REPLAY.CALIBRATE). In our case one client is enough, obviously I would say as there was only one workload session:

[orapris@server1 dbcapture]$ wrc replaydir=/home/orapris/yannick/dbcapture mode=calibrate
 
Workload Replay Client: Release 11.2.0.1.0 - Production on Tue Jan 24 16:48:09 2012
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
 
Report for Workload in: /home/orapris/yannick/dbcapture
-----------------------
 
Recommendation:
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
 
Assumptions:
- 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

In one shell session initiate your client(s) with:

[orapris@server1 ~]$ wrc yjaquier replaydir=/home/orapris/yannick/dbcapture
 
Workload Replay Client: Release 11.2.0.1.0 - Production on Tue Jan 24 17:02:00 2012
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
 
Password:
Wait for the replay to start (17:02:03)

Start the replay, as SYS in a SQL*Plus session, with:

SQL> EXEC DBMS_WORKLOAD_REPLAY.START_REPLAY();
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SET lines 150
SQL> col name FOR a20
SQL> SELECT name,DIRECTORY,status FROM dba_workload_replays;
 
NAME                 DIRECTORY                      STATUS
-------------------- ------------------------------ ----------------------------------------
YJAQUIER replay      DBCAPTURE                      IN PROGRESS

Back to your Unix session you see:

[orapris@server1 ~]$ wrc yjaquier replaydir=/home/orapris/yannick/dbcapture
 
Workload Replay Client: Release 11.2.0.1.0 - Production on Tue Jan 24 17:02:00 2012
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
 
Password:
Wait for the replay to start (17:02:03)
Replay started (17:11:12)

After capture period is over you see:

[orapris@server1 ~]$ wrc yjaquier replaydir=/home/orapris/yannick/dbcapture
 
Workload Replay Client: Release 11.2.0.1.0 - Production on Tue Jan 24 17:02:00 2012
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
 
Password:
Wait for the replay to start (17:02:03)
Replay started (17:11:12)
Replay finished (17:31:12)

Which can be confirmed in SQL with:

SQL> SELECT name,DIRECTORY,status FROM dba_workload_replays;
 
NAME                 DIRECTORY                      STATUS
-------------------- ------------------------------ ----------------------------------------
YJAQUIER replay      DBCAPTURE                      COMPLETED

Remark:
Note that replay time is more or less equal to capture time and if like me you did various checks in parallel while capturing, replay time is 20 minutes even if in reality all SQLs are executed in 2-3 minutes…

Database replay result and cleaning

You can use DBMS_WORKLOAD_REPLAY.REPORT function to generate either a text or html replay report like this file.

Note that for final report Grid Control provide an interesting report like:

rat1
rat1

We clearly see that we had no divergence (DBA_WORKLOAD_REPLAY_DIVERGENCE) in our replay process (as expected with this simple example) and that database time replay is less than 2% of capture database time so clearly it has improved (the update on test1 table with an index is almost instantaneous despite the 20 millions rows).

I have to say that at the end I’m a bit disappointed as I was expecting much more details in replay report. You only have divergence, elapsed time, database time and few other high level information. Nothing to show you SQL that have the most diverge unless you go in AWR reports. But then if main source of information is AWR reports then what’s the added value of database replay ? Well at least it offers a nice functionality to capture a real life workload without being oblige to construct an exhaustive list of SQL statements to be tested…

Once all is done you can clean the capture process with (it will also clean the used filters):

SQL> SET lines 150
SQL> SELECT id,name FROM dba_workload_captures;
 
        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         0 YJAQUIER capture
 
SQL> EXEC DBMS_WORKLOAD_CAPTURE.DELETE_CAPTURE_INFO(0);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT id,name FROM dba_workload_replays;
 
        ID NAME
---------- --------------------
         1 YJAQUIER replay
 
SQL> EXEC DBMS_WORKLOAD_REPLAY.DELETE_REPLAY_INFO(1);
 
PL/SQL PROCEDURE successfully completed.

References

About Post Author

4 thoughts on “Database Replay by example

  1. Abhijit Jadhav says:

    Yannick, enjoyed your post. Google directed me to your post when I was checking alternatives for “Database Replay” 🙂 As you mentioned this product is not free and licensing cost are prohibitive for my organization. Are there any alternatives that you are aware of

    • Abhijit,

      Thanks for comment.
      Database Replay “only” automate what you can 100% do manually. I mean go to your application owner and ask him to test his application on a test upgraded database. He should be able to tell you if working or not.
      You might also want to consider SQL Plan Management

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>