Table of contents
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:
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
- Using the workload capture and replay in 11G [ID 445116.1]
- Real Application Testing (It’s not RAC !) /*+ Part 1 */ (French)
MUTHUSAMY Subramanian says:
Hello Yannick,
I am facing this error while starting dbms_workload_replay.process_capture (‘DBCAPTURE’) step
Please help. Thank you
BEGIN DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(‘DBREPLAY’) ; END;
*
ERROR at line 1:
ORA-15532: workload replay encountered unexpected error: capture does not contain any user calls
ORA-06512: at “SYS.DBMS_WORKLOAD_REPLAY_I”, line 3452
ORA-06512: at “SYS.DBMS_WORKLOAD_REPLAY_I”, line 3363
ORA-06512: at “SYS.DBMS_WORKLOAD_REPLAY_I”, line 2691
ORA-06512: at “SYS.DBMS_WORKLOAD_REPLAY_I”, line 10390
ORA-06512: at “SYS.DBMS_WORKLOAD_REPLAY_I”, line 10475
ORA-06512: at “SYS.DBMS_WORKLOAD_REPLAY_I”, line 3085
ORA-06512: at “SYS.DBMS_WORKLOAD_REPLAY_I”, line 3346
ORA-06512: at “SYS.DBMS_WORKLOAD_REPLAY_I”, line 3432
ORA-06512: at “SYS.DBMS_WORKLOAD_REPLAY”, line 12
ORA-06512: at line 1
Thank you
Regards
Muthu/UK
Yannick Jaquier says:
Hello Muthu,
Looks like you had no user activity while capturing the workload…
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
Yannick Jaquier says:
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…
chakravarthi says:
Clean and crisp and neat, good work
Yannick Jaquier says:
Thanks for stopping by !