Sunday, January 4, 2009

11g Replay using DBMS_WORKLOAD_REPLAY

The other day I was playing around with the 11g Database Replay features and came across Arup Nanda's 11g-top-features articles on oracle site. Very very Nice! It goes in great depth detailing every step neatly with nice screen shots using Database Control. I went thru the steps and everything went clean!
Next, I decided to repeat all the steps using the DBMS_WORKLOAD_REPLAY packages and had to follow the documentation and a couple ML notes (mostly Note 445116.1 and Note 560977.1 ).
There were a few places that I stumbled upon. I created a small test scenario with steps, so here it goes.

Preparing user account and database objects
Create a USER account with the right privs that will have the privs to run the CAPTURE and REPLAY procedures.
CREATE USER ORACLE IDENTIFIED BY oracle;
GRANT CREATE SESSION, RESOURCE to ORACLE;
GRANT EXECUTE ON DBMS_WORKLOAD_CAPTURE TO oracle;
GRANT EXECUTE ON DBMS_WORKLOAD_REPLAY TO oracle;

GRANT CREATE SESSION TO oracle;
GRANT CREATE ANY DIRECTORY TO oracle;
GRANT SELECT_CATALOG_ROLE TO oracle;
GRANT BECOME USER TO oracle;
GRANT UNLIMITED TABLESPACE TO oracle;



Create a test table. To verify if DMLs generated due to trigger actions are also part of captured replay, I've created a child table and a trigger also.
create table ORACLE.test (
column_1 number,
column_2 varchar2(600)
);
create table ORACLE.test_child (
column_1 number,
column_2 varchar2(600)
);


create trigger ORACLE.tr_test after insert on ORACLE.test
for each row
begin
insert into ORACLE.test_child values (:new.column_1, :new.column_2);
end;
/



Creating a Restore Point

Since this is a test database, it will be flashed back to run the REPLAY. Create a restore point now.
drop restore point BEFORE_CAPTURE;
create restore point BEFORE_CAPTURE;


Adding a FILTER
Add a filter so that only transactions executed under ORACLE id are captured
exec DBMS_WORKLOAD_CAPTURE.DELETE_FILTER('Test_Replay');
begin
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (fname => 'Test_Replay',
fattribute =>'USER',
fvalue =>'ORACLE');
end;
/


Starting the CAPTURE process
The capture process could be started using DBMS_WORKLOAD_CAPTURE.START_CAPTURE. The capture statistics could be obtained by querying DBA_WORKLOAD_CAPTURES view. A value of "EXCLUDE" in the default_action indicates that only things that "pass" the filter should be captured (otherwords, only actions performed by the ORACLE id should be captured while it is running).

begin
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'Test_Capture',
dir => 'DBCAPTURE',
default_action => 'EXCLUDE');
end;
/

SELECT id,name,start_time,end_time,start_scn, end_scn, duration_secs, filters_used, capture_size FROM dba_workload_captures order by start_time desc;


You might hit the following errors while starting the CAPTURE process -

RGTEST > begin
2 DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => 'Test_Capture',
3 dir => 'DBCAPTURE',
4 default_action => 'EXCLUDE');
5 end;
6 /
begin
*
ERROR at line 1:
ORA-15505: cannot start workload capture because instance 1 encountered errors while accessing directory
"/home/oracle/dbcapture"
ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 799
ORA-06512: at line 2

=> Make sure that oracle owns (or has the write access) to that os directory. Also the directory should be fully empty.

RGTEST > SELECT id,name, start_time,end_time,start_scn, end_scn, duration_secs, filters_used, capture_size FROM dba_workload_captures order by start_time desc;

ID NAME START_TIME END_TIME START_SCN END_SCN DURATION_SECS FILTERS_USED
---------- ------------------------- --------------- --------------- ---------- ---------- ------------- ------------
CAPTURE_SIZE
------------
51 Test_Capture 03Jan2009 10:26 24971410 24971673 182 1
313174


Execute the WORKLOAD
To execute the workload, I generated a bunch of INSERT statements.
set termout off
spool ./trans.sql
set head off feed off pagesize 0 linesize 550
select 'insert into oracle.test values ('||rownum||','||''''||rpad('AZ',500,'ZA')||''');' from dba_objects where rownum < style="color: rgb(102, 102, 204);" face="georgia">These were executed as user "ORACLE".


RGTEST > select count(*) from oracle.test;

COUNT(*)
----------
1000

1 row selected.

RGTEST > select count(*) from oracle.test_child;

COUNT(*)
----------
1000

1 row selected.



Stopping the CAPTURE process
The capture process could also be stopped using DBMS_WORKLOAD_CAPTURE.START_CAPTURE
RGTEST > exec DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE();

PL/SQL procedure successfully completed.



Export the AWR for capture statistics
Export the AWR if you need to compare any stats etc after the replay has been done. You'd need the capture_id (queried earlier) for this.
RGTEST > begin
2 DBMS_WORKLOAD_CAPTURE.EXPORT_AWR(51);
3 end;
4 /

PL/SQL procedure successfully completed.


Following files should be created in the directory referenced by the DBCAPTURE directory object -
-rw-r----- 1 oracle dba 14413824 Jan 3 10:35 wcr_ca.dmp
-rw-r--r-- 1 oracle dba 28458
Jan 3 10:35 wcr_ca.log
-rw-r--r-- 1 oracle dba 17246
Jan 3 10:30 wcr_cr.text
-rw-r--r-- 1 oracle dba 35502
Jan 3 10:30 wcr_cr.html
-rw-r--r-- 1 oracle dba 208
Jan 3 10:29 wcr_fcapture.wmd
-rw-r--r-- 1 oracle dba 2975
Jan 3 10:29 wcr_4m42t64002s47.rec
-rw-r--r-- 1 oracle dba 69
Jan 3 10:28 wcr_4m42tsw002sa4.rec
-rw-r--r-- 1 oracle dba 303553
Jan 3 10:28 wcr_4m42tnh002s92.rec
..
..

Looking at export log (wcr_ca.log) indicates that all the WRH tables get exported
. . exported "SYS"."WRH$_SYSMETRIC_HISTORY" 13.46 KB 100 rows
. . exported "SYS"."WRH$_SQL_PLAN" 6.355 MB 26787 rows
. . exported "SYS"."WRH$_SYSMETRIC_SUMMARY" 32.19 KB 296 rows
. . exported "SYS"."WRH$_ENQUEUE_STAT" 18.02 KB 174 rows
. . exported "SYS"."WRH$_WAITCLASSMETRIC_HISTORY" 9.429 KB 0 rows


We may also run a report on capture using DBMS_WORKLOAD_CAPTURE.REPORT.
A snip of it is shown below --
RGTEST > select dbms_workload_capture.report(51,'TEXT') from dual;

Database Capture Report For RGTEST

DB Name DB Id Release RAC Capture Name Status
------------ ----------- ----------- --- -------------------------- ----------
RGTEST 2815669595 11.1.0.6.0 NO Test_Capture COMPLETED


Start time: 03-Jan-09 10:26:25 (SCN = 24971410)
End time: 03-Jan-09 10:29:27 (SCN = 24971673)
Duration: 3 minutes 2 seconds
Capture size: 305.83 KB
Directory object: DBCAPTURE
Directory path: /home/oracle/dbcapture
Directory shared in RAC: TRUE
Filters used: 1 INCLUSION filter

Captured Workload Statistics DB: RGTEST Snaps: 4390-4391
-> 'Value' represents the corresponding statistic aggregated
across the entire captured database workload.
-> '% Total' is the percentage of 'Value' over the corresponding
system-wide aggregated total.

Statistic Name Value % Total
---------------------------------------- ------------- ---------
DB time (secs) 2.07 64.69
Average Active Sessions 0.01
User calls captured 2054 72.71
User calls captured with Errors 0
Session logins 5 35.71
Transactions 3 4.35
-------------------------------------------------------------

Top Events Captured DB: RGTEST Snaps: 4390-4391

Avg Active
Event Event Class % Event Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 50.00 0.01
-------------------------------------------------------------



Pre-processing the WORKLOAD
Before REPLAY can be done, the workload information needs to be "pre-processed". Essentially, this creates a .pp file (wcr_login.pp) in the capture directory.

RGTEST > BEGIN
2 DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE
3 (capture_dir => 'DBCAPTURE');
4 END;
5 /

PL/SQL procedure successfully completed.



Preparing for a REPLAY
In a practical scenario, the CAPTURE will run in production and the REPLAY will be tested in a QA/TST type environment (to check potential impact of any database/application changes) . However in our test scenario, we'll simply flashback the TEST database to the restore point that was created earlier.
RGTEST > connect / as sysdba
Connected.

RGTEST > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

RGTEST > startup mount
ORACLE instance started.

Total System Global Area 954155008 bytes
Fixed Size 1303916 bytes
Variable Size 692062868 bytes
Database Buffers 255852544 bytes
Redo Buffers 4935680 bytes
Database mounted.
RGTEST > flashback database to restore point BEFORE_CAPTURE;

Flashback complete.

RGTEST > alter database open resetlogs;

Database altered.

RGTEST > select count(*) from oracle.test;

COUNT(*)
----------
0

1 row selected.

RGTEST > select count(*) from oracle.test_child;

COUNT(*)
----------
0

1 row selected.


In my test, I also drop the TRIGGER to see if the DML changes generated from the trigger action were captured and will be replayed.
RGTEST > drop trigger ORACLE.TR_TEST;

Trigger dropped.

Executing a REPLAY of the workload captured earlier
The REPLAY needs to be "initialized" and "prepared" before replay clients can be started.
RGTEST > begin
2 DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
3 replay_name=> 'TEST_REPLAY',
4 replay_dir=>'DBCAPTURE');
5 end;
6 /


PL/SQL procedure successfully completed.

RGTEST > exec DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY();

PL/SQL procedure successfully completed.


From another windows session, we can start the replay clients (wrc).
[oracle@dbasandbox /home/oracle/ravi/DB_REPLAY] $ wrc system/ mode=replay replaydir=/home/oracle/dbcapture
Workload Replay Client: Release 11.1.0.6.0 - Production on Sat Jan 3 15:59:57 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Wait for the replay to start (15:59:57)

Start the REPLAY using START_REPLAY procedure of DBMS_WORKLOAD_REPLAY package.
RGTEST > BEGIN
2 DBMS_WORKLOAD_REPLAY.START_REPLAY ();
3 end;
4 /

PL/SQL procedure successfully completed.

The window where you started the replay client now shows that replay has been started.
[oracle@dbasandbox /home/oracle/ravi/DB_REPLAY] $ wrc system/ mode=replay replaydir=/home/oracle/dbcapture
Workload Replay Client: Release 11.1.0.6.0 - Production on Sat Jan 3 15:59:57 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.

Wait for the replay to start (15:59:57)

Replay started (16:01:11)
Replay finished (16:06:26)



Database view DBA_WORKLOAD_REPLAY may be queried for getting the replay_id (for exporting AWR stats etc) and other stats.
RGTEST > select id,name, start_time,end_time,duration_secs FROM DBA_WORKLOAD_REPLAYS;

ID NAME START_TIM END_TIME DURATION_SECS
---------- ------------------------- --------- --------- -------------
13 TEST_REPLAY 03-JAN-09 03-JAN-09 193

3 rows selected.


A running REPLAY may be stopped (if its running for quite a long time and you need to cancel it) by executing the CANCEL_REPLAY procedure. In our case, it returned an error since it already completed earlier.
RGTEST > exec DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY ();
BEGIN DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY (); END;

*
ERROR at line 1:
ORA-20223: No workload replay is in progress!
ORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY", line 1578
ORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY", line 1633
ORA-06512: at line 1


Querying the 2 tables indicate only the primary table gets populated with data. The child table that was earlier populated by the triggering action does not get populated in our replay (note: we dropped the trigger earlier to check if the DMLs generated by the triggering action get captured).
RGTEST > select count(*) from oracle.test;
COUNT(*)
----------
100

1 row selected.

RGTEST > select count(*) from oracle.test_child;
COUNT(*)
----------
0

1 row selected.



Export the AWR for replay statistics
Like earlier, AWR stats can be exported for replay statistics also. We need a replay_id to do this however (queried earlier).
RGTEST > exec DBMS_WORKLOAD_REPLAY.EXPORT_AWR (replay_id => 13);
PL/SQL procedure successfully completed.


This completes the REPLAY exercise. I need to play a bit more with the excellent 11g feature and will post anything new I learn here.