Step by step test Database Replay of Real Application Test

–Test version: 11.1.0.6


–1.Capture workload

Before capturing a database workload, carefully consider the following options:
*Restarting the Database
–to avoid capture partial transaction
*Defining the Workload Filters
–inclusion filters or exclusion filters, not both
*Setting Up the Capture Directory

–capturing a database workload using APIs, Adding and Removing Workload Filters
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
fname => ‘user_t’,
fattribute => ‘USER’,
fvalue => ‘T’);
END;
/

–this will filte out all sessions beloings to user T
–fattribute should be PROGRAM, MODULE, ACTION, SERVICE, INSTANCE_NUMBER, and USER
–wildcard can be used in fvalue, such as %

–to delete a filter
BEGIN
DBMS_WORKLOAD_CAPTURE.DELETE_FILTER (fname => ‘user_ichan’);
END;
/

–start_capture
SYS@ORA11G>create directory testdir as ‘D:\oracle\reorg’;
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => ‘mytest’,
dir => ‘TESTDIR’,
duration => 600);
END;
/

–it will stop automatically after 600 seconds
–if duration is not specified, call the following statement to stop it
BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE ();
END;
/

–export AWR data for workload capture
Exporting AWR data enables detailed analysis of the workload.
This data is also required if you plan to run the AWR Compare Period report
on a pair of workload captures or replays.
–get the capture id
SYS@ORA11G>select id from  DBA_WORKLOAD_CAPTURES;

ID
———-
1

–export awr of this id
BEGIN
DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (capture_id => 1);
END;
/


–2.Preprocessing workload

–This is very resource contention, should not run on production system
–copy the dirctory files to the replay db, and run the procedure
BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => ‘TESTDIR’);
END;
/


–3.Replaying a Database Workload

–step1. setup test system
Using rman restore/duplicate to test system
do the change you want, such as parameter change, hardware change
resetting system time, set the system time to capture start time

–step2. replaying
copy the preprocessed files to a db directory
resolving references to external system: db links, external tables, directories,URL, Email
remapping connections: the connection string used to connect the production system are captured
options:
synchronization
connect_time_scale
think_time_scale

–setting up replay clients
replay program: $ORACLE_HOME/bin/wrc, a multithreaded program
wrc [user/password[@server]] MODE=[value] [keyword=[value]]

–Calibrating Replay Clients
C:\Documents and Settings\Zengw>wrc t/t mode=calibrate replaydir=D:\oracle\reorg

Workload Replay Client: Release 11.1.0.6.0 – Production on Wed Dec 10 16:15:37 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Report for Workload in: D:\oracle\reorg
———————–

Recommendation:
Consider using at least 1 clients divided among 1 CPU(s).

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

Assumptions:
– 1 client process per 50 concurrent sessions
– 4 client process per CPU
– think time scale = 100
– connect time scale = 100
– synchronization = TRUE

–OEM is the primary tools to do replay
-initializing replay data
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (replay_name => ‘mytest_replay’,
replay_dir => ‘TESTDIR’);
END;
/

–setting workload replay options
BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (synchronization => TRUE);
END;
/

–start replay clients
C:\Documents and Settings\Zengw>wrc t/t mode=replay replaydir=D:\oracle\reorg

Workload Replay Client: Release 11.1.0.6.0 – Production on Wed Dec 10 16:23:07 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Wait for the replay to start (16:23:07)
–i get the below output after the below procedure is done
Replay started (16:23:48)
Replay finished (16:25:39)

–starting a workload replay in another window
BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY ();
END;
/

–Exporting AWR Data for Workload Replay
–get the replay id
SYS@ORA11G>select id from dba_workload_replays;

ID
———-
1

BEGIN
DBMS_WORKLOAD_REPLAY.EXPORT_AWR (replay_id => 1);
END;
/

–workload replay views
DBA_WORKLOAD_CAPTURES
DBA_WORKLOAD_FILTERS
DBA_WORKLOAD_REPLAYS
DBA_WORKLOAD_REPLAY_DIVERGENCE
DBA_WORKLOAD_CONNECTION_MAP


–4.Analyzing replay workload

–generating a workload capture report, OEM/procedure
set serveroutput on
DECLARE
cap_id         NUMBER;
cap_rpt        CLOB;
rpt_len                 NUMBER;
BEGIN
cap_id  := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir => ‘TESTDIR’);
cap_rpt := DBMS_WORKLOAD_CAPTURE.REPORT(capture_id => cap_id,format => DBMS_WORKLOAD_CAPTURE.TYPE_TEXT);
rpt_len :=DBMS_LOB.GETLENGTH(cap_rpt);
dbms_output.put_line(‘length:’||rpt_len);
dbms_output.put_line(DBMS_LOB.SUBSTR(cap_rpt,32767,1)); –if rpt_len < 32767
END;
/

length:11623

Database Capture Report For ORA11G

DB Name         DB Id    Release     RAC Capture Name               Status
————
———– ———– — ————————– ———-
ORA11G        2333620726 11.1.0.6.0  NO  mytest                                            COMPLETED
Start time: 10-Dec-08 15:43:05 (SCN = 237383)
End time: 10-Dec-08 15:48:25
(SCN = 238531)
Duration: 5 minutes 20 seconds
Capture size: 1.99 KB
Directory object: TESTDIR
Directory path: D:\oracle\reorg
Directory shared in RAC: TRUE
Filters used: 1 EXCLUSION filter

Captured Workload Statistics                          DB: ORA11G  Snaps: 13-14
-> ‘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)                                    0.06        0.22
Average Active Sessions                           0.00
User calls captured                                  4        8.00
User calls captured with Errors                      0
Session logins                                       0        0.00
Transactions                                         0    0.00
————————————————————-
Top Events Captured                                   DB:ORA11G  Snaps: 13-14

No data exists for this section of the report.
————————————————————-
Top Service/Module Captured                           DB:ORA11G  Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top SQL Captured                                      DB:ORA11G  Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top Sessions Captured                                 DB:ORA11G  Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top Events containing Unreplayable Calls              DB:ORA11G  Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top Service/Module containing Unreplayable Calls      DB:ORA11G  Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top SQL containing Unreplayable Calls                 DB:ORA11G  Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top Sessions containing Unreplayable Calls            DB:ORA11G  Snaps: 13-14
No data exists for this section of the report.
————————————————————-
Top Events Filtered Out                               DB:ORA11G  Snaps: 13-14
Avg Active Event                        Event Class        % Event   Sessions
———————————– ————— ———- ———-
CPU + Wait for CPU
CPU                  37.70       0.07
db file scattered read              User I/O              1.64       0.00
db file sequential
read             User I/O              1.64       0.00
————————————————————-

Top Service/Module Filtered Out                       DB: ORA11G  Snaps: 13-14
Service        Module                   % Activity
Action               % Action
————– ———————— ———- —————— ———-
SYS$USERS
SQL*Plus                      39.34 UNNAMED                 39.34
sqlplus.exe                    1.64 UNNAMED
1.64
————————————————————-

Top SQL Filtered Out        DB: ORA11G  Snaps: 13-14
SQL ID     % Activity Event                          % Event
———————–
————– —————————— ——-
a6ta5bs4qwy6p          31.15 CPU + Wait for CPU
31.15
select count(*) from t,t2

2jpf0qbj9vxq3           3.28 CPU + Wait for CPU                3.28
** SQL Text Not Available **

fcwj05qyp0f6w           3.28 db file scattered read            1.64
** SQL Text Not Available **

db file sequential read           1.64

bs8bzd41vz3x0           1.64 CPU + Wait for CPU                1.64
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (name => ‘mytest’,
dir => ‘TESTDIR’, duration => 600); END;

g9u5j7y6cfxxq
1.64 CPU + Wait for CPU                1.64
** SQL Text Not Available **

————————————————————-
Top Sessions Filtered Out                             DB:
ORA11G  Snaps: 13-14
-> ‘# Samples Active’ shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity.
-> ‘XIDs’ shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event
-> For sessions running Parallel Queries, this section will NOT aggregate the PQ slave activity into the session issuing the PQ. Refer to the ‘Top Sessions running PQs’ section for such statistics.
Sid, Serial# % Activity
Event                             % Event
————— ———- —————————— ———-
User
Program                          # Samples Active     XIDs
——————– —————————— ——————
——–
88,    7      39.34 CPU + Wait for CPU                  36.07
T                    sqlplus.exe
22/320 [  7%]        2

db file scattered read               1.64

1/320 [  0%]        1

db file sequential read              1.64

1/320 [  0%]        1

61,   27       1.64 CPU + Wait for CPU                   1.64
SYS                  sqlplus.exe
1/320 [  0%]        0

————————————————————-
Top Events (Jobs and
Background Activity)             DB: ORA11G  Snaps: 13-14
Avg
Active
Event                               Event Class        % Event   Sessions
———————————– —————
———- ———-
db file sequential read             User I/O             14.75       0.03
CPU + Wait for CPU
CPU                   9.84       0.02
control file parallel write         System I/O            9.84       0.02
null event
Other                 6.56       0.01
control file sequential read        System I/O            4.92       0.01
————————————————————-
Top Service/Module (Jobs and Background Activity)     DB:ORA11G  Snaps: 13-14
Service        Module                   % Activity Action               % Action
————–
———————— ———- —————— ———-
SYS$BACKGROUND UNNAMED                       37.70 UNNAMED
37

PL/SQL procedure successfully completed.

–generating a workload replay report , OEM/procedure

set serveroutput on
DECLARE
cap_id         NUMBER;
rep_id         NUMBER;
rep_rpt        CLOB;
rpt_len                 NUMBER;
BEGIN
cap_id := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(dir => ‘TESTDIR’);
/* Get the latest replay for that capture */
SELECT max(id)
INTO   rep_id
FROM   dba_workload_replays
WHERE  capture_id = cap_id;

rep_rpt := DBMS_WORKLOAD_REPLAY.REPORT(replay_id => rep_id,
format => DBMS_WORKLOAD_REPLAY.TYPE_TEXT);
rpt_len :=DBMS_LOB.GETLENGTH(rep_rpt);
dbms_output.put_line(‘length:’||rpt_len);
dbms_output.put_line(DBMS_LOB.SUBSTR(rep_rpt,32767,1)); –if rpt_len < 32767
END;
/
–format can be DBMS_WORKLOAD_REPLAY.TYPE_TEXT, DBMS_WORKLOAD_REPLAY.TYPE_HTML, and DBMS_WORKLOAD_REPLAY.TYPE_XML.
length:6603
DB Replay Report for mytest_replay
————————————————————————–
—————–
———————————————————-
| DB Name | DB Id      | Release    | RAC | Replay Name   | Replay Status
|
————————————————————————–
| ORA11G  | 2333620726 | 11.1.0.6.0 | NO  |
mytest_replay | COMPLETED     |
————————————————————————–
Replay Information
—————————————————————-
|   Information    | Replay             | Capture
|
—————————————————————-
| Name             | mytest_replay      | mytest
|
—————————————————————-
| Status           | COMPLETED          | COMPLETED
|
—————————————————————-
| Database Name    | ORA11G             | ORA11G
|
—————————————————————-
| Database Version | 11.1.0.6.0         | 11.1.0.6.0
|
—————————————————————-
| Start Time       | 10-DEC-08 16:23:38 | 10-DEC-08 15:43:05
|
—————————————————————-
| End Time         | 10-DEC-08 16:24:02 | 10-DEC-08 15:48:25
|
—————————————————————-
| Duration         | 24 seconds         | 5 minutes 20 seconds
|
—————————————————————-
| Directory Object | TESTDIR            | TESTDIR
|
—————————————————————-
| Directory Path   | D:\oracle\reorg    | D:\oracle\reorg
|
—————————————————————-

Replay Options
———————————————————
|       Option Name       | Value
|
———————————————————
| Synchronization         | TRUE
|
———————————————————
| Connect Time            | 100%
|
———————————————————
| Think Time              | 100%
|
———————————————————
| Think Time Auto Correct | TRUE
|
———————————————————
| Number of WRC Clients   | 1 (1 Completed, 0 Running )
|
———————————————————

Replay Statistics
———————————————————–
|        Statistic        | Replay        | Capture
|
———————————————————–
| DB Time                 | 4.388 seconds | 0.061 seconds
|
———————————————————–
| Average Active Sessions |           .18 |             0
|
———————————————————–
| User calls              |             4 |             4
|
———————————————————–
| Network Time            | 0.000 seconds | .
|
———————————————————–
| Think Time              | 0.000 seconds | .
|
———————————————————–

Replay Divergence Summary
——————————————————————-
|                Divergence Type                | Count
| % Total |
——————————————————————-
| Session Failures During Replay                |
0 |    0.00 |
——————————————————————-
| Errors No Longer Seen During Replay           |
0 |    0.00 |
——————————————————————-
| New Errors Seen During Replay                 |
0 |    0.00 |
——————————————————————-
| Errors Mutated During Replay                  |
0 |    0.00 |
——————————————————————-
| DMLs with Different Number of Rows Modified   |
0 |    0.00 |
——————————————————————-
| SELECTs with Different Number of Rows Fetched |
0 |    0.00
|
——————————————————————-

————————————————————-
————————————–
Workload Profile Top Events
————————————————–
| No data
exists for this section of the report. |
————————————————–
Top
Service/Module/Action
————————————————–
| No data exists for this section of the report.
|
————————————————–
Top SQL with Top Events
————————————————–
| No
data exists for this section of the report. |
————————————————–
Top Sessions with Top
Events
————————————————–
| No data exists for this section of the report.
|
————————————————–
Replay Divergence Session Failures By
Application
————————————————–
| No data exists for this section of the report.
|
————————————————–
Error Divergence By
Application
————————————————–
| No data exists for this section of the report.
|
————————————————–
By SQL
————————————————–
| No data exists for
this section of the report. |
————————————————–
By
Session
————————————————–
| No data exists for this section of the report.
|
————————————————–
DML Data Divergence By
Application
————————————————–
| No data exists for this section of the report.
|
————————————————–
By SQL
————————————————–
| No data exists for
this section of the report. |
————————————————–
SELECT Data Divergence By
Application
————————————————–
| No data exists for this section of the report.
|
————————————————–

End of Report.

PL/SQL procedure successfully completed.

–cause I am doing the capture and replay on same db, there is no much divergence

–appendix: sql statements tested
T@ORA11G>create table t as select * from dba_objects;

Table created.

T@ORA11G>select count(*) from dba_objects;

COUNT(*)
———-
12091

T@ORA11G>create index t_oid on t(object_id);

Index created.

T@ORA11G>create index t_oname on t(object_name);

Index created.

T@ORA11G>update t set object_id=99999999-object_id;

12091 rows updated.

T@ORA11G>commit;

Commit complete.

T@ORA11G>select min(object_id) from t;

MIN(OBJECT_ID)
————–
99987491

T@ORA11G>create table t2 as select * from dba_objects;

Table created.

T@ORA11G>select count(*) from t,t2 where t.object_id=t2.object_id;

COUNT(*)
———-
0

T@ORA11G>select count(*) from t,t2 where t.object_name=t2.object_name;

COUNT(*)
———-
18629

T@ORA11G>create index t2_oname on t2(object_name);

Index created.

T@ORA11G>select count(*) from t,t2 where t.object_name=t2.object_name;

COUNT(*)
———-
18629

T@ORA11G>update t set object_id=99999999-object_id;

12091 rows updated.

T@ORA11G>select count(*) from t,t2 where t.object_id=t2.object_id;

COUNT(*)
———-
12091

T@ORA11G>commit;

Commit complete.

T@ORA11G>select count(*) from t,t2;

COUNT(*)
———-
146228554

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

10 Responses to Step by step test Database Replay of Real Application Test

  1. neworacledba says:

    database replay is a great feature in 11g…..oracle has come up with great replication products like streams…….i don’t understand the reason behind this feature

  2. oracle_beginner says:

    great !! article

  3. Dominical says:

    Hi Alex,

    Good article, thanks for putting it in the blog. I found very few step-by-step Real application testing article.
    I have a question for you. I don’t understand where you do the first export of AWR.
    –export awr of this id
    BEGIN
    DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (capture_id => 1);
    END;

    The first time, you run export_awr in the “capture db instance”?
    And the second time, you export_awr in the “replay db instance”?

    I don’t understand why we need to export_awr ..
    and then do I need to bring the first “expor_awr” to the replay db instance ?

    Thanks in advance,

    Dominica

    • Alex Zeng says:

      hi Dominica,
      They are 2 different procedures from different packages.
      DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (capture_id => 1);
      DBMS_WORKLOAD_REPLAY.EXPORT_AWR (replay_id => 1);

      As I stated in the article, AWR is used to analysis performance and compare.

      Exporting AWR data enables detailed analysis of the workload.
      This data is also required if you plan to run the AWR Compare Period report
      on a pair of workload captures or replays.

      You don’t need to bring the first export awr to replay instance. You just run report on capture instance and compare it with the report on replay instance.

      Hope this helpful!
      Alex

      • dominical says:

        Hi Alex,

        If I want to run the AWR compare period report, how to “import” back the “capture database”‘s EXPORT_AWR (dbms_workload_capture.export_awr(capture_id =>5);
        where did those “export” went ? how to “take” it over to the “replay db server”?
        Sorry about my confusion.

        Thanks,

        Dominica

  4. Dominical says:

    Hi Alex,

    Thanks for your reply, I got what you mean.
    But now, I want the “AWR Compare Period report
    on a pair of workload captures or replays.” like you suggest.
    I don’t use OEM, is there API to call for the “compare period report”?

    Thanks in advance,

    Dominica L

  5. Alex says:

    Hi Alex – what would be the different steps if you’re capturing on RAC and replaying it to RAC?

  6. Ravi kumar says:

    Excellent post! It is so useful even in 2014, many years after it is posted.

    May I add one point:

    For AWR eports, if you want to get the id from the capture name –

    SELECT id, name FROM dba_workload_captures where name=’mytest’;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: