1 Verify Real Application Testing option is installed at both 'Capture' and 'Replay' instances.
Option 1: Check inventory
$ORACLE_HOME/OPatch/opatch lsinventory -details|grep "Oracle Real Application Testing"
Option 2: Unix archiver (ar) command
If ar command available on OS:
cd $ORACLE_HOME/rdbms/lib
ar -t libknlopt.a | grep -c kecwr.o
This ar command will return 0 if RAT is disabled or number > 0 if enabled.
Option 3: Data Dictionary
select value from v$option where parameter = 'Real Application Testing';
SELECT returns TRUE if Real Application Testing is installed, otherwise FALSE. Note that the information is not in dba_registry.
2 (Capture) Identify the ORACLE_SID of the capture database.
3 (Capture) Confirm the dbms_workload_capture package exists and is valid.
SQL > select object_name, object_type, status from dba_objects where object_name like '%WORKLOAD_CAPTURE';
OBJECT_NAME OBJECT_TYPE STATUS
--------------------------------------------------------------
DBMS_WORKLOAD_CAPTURE PACKAGE VALID
DBMS_WORKLOAD_CAPTURE SYNONYM VALID
DBMS_WORKLOAD_CAPTURE PACKAGE BODY VALID
4 (Replay). Identify the ORACLE_SID of the replay database.
On the Capture instance, capture a workload
1 Create a new directory object to store the workload information.
Create an empty directory for the collected workload information
$ mkdir /
SQL > create directory replay as '/
2 Restart the capture database in restricted mode.
SQL > shutdown immediate;
SQL > startup restrict;
Although restarting the database is not required, it is recommended that the database be restarted before capturing a workload to ensure that ongoing and dependent transactions are allowed to be completed or be rolled back before the capture starts. Once the database is restarted, it is important to start the workload capture before any user sessions reconnect and start issuing any workload. Otherwise, transactions performed by these user sessions will not be replayed properly in subsequent database replays, because only the part of the transaction whose calls were executed after the workload capture is started will be replayed. To avoid this problem, consider restarting the database in RESTRICTED mode using STARTUP RESTRICT, which will only allow any AS SYSDBA user (or any user given restricted session privilege) to login and start the workload capture. By default, once the workload capture begins, any database instance that is in RESTRICTED mode will automatically switch to UNRESTRICTED mode, and normal operations can continue while the workload is being captured. ALTER SYSTEM ENABLE RESTRICTED SESSION on all active instances may also be used.
3 Start the workload capture.
SQL > exec dbms_workload_capture.start_capture(name => 'capture', dir => 'REPLAY');
$ ls /
drwxrwxr-x 12 cagray g900 4096 Apr 11 12:17 inst1 (subdirectories aa -> aj)
-rw-rw-r-- 1 cagray g900 0 Apr 11 12:17 wcr_cap_00001.start
-rw-rw-r-- 1 cagray g900 93 Apr 11 12:17 wcr_scapture.wmd <- start capture 4 Generate the workload. For testing run simple SQL scripts. For this example only a single UPDATE on SCOTT.EMP. 5 After workload is complete, end the workload capture. SQL > exec dbms_workload_capture.finish_capture();
$ ls -l /
drwxrwxr-x 12 cagray g900 4096 Apr 11 12:17 inst1
-rw-rw-r-- 1 cagray g900 31308 Apr 11 12:24 wcr_cr.html
-rw-rw-r-- 1 cagray g900 14218 Apr 11 12:24 wcr_cr.text
-rw-rw-r-- 1 cagray g900 151 Apr 11 12:24 wcr_fcapture.wmd <- finish capture -rw-rw-r-- 1 cagray g900 93 Apr 11 12:17 wcr_scapture.wmd $ ls -l inst1/aa -rw-rw-r-- 1 cagray g900 1708 Apr 11 12:24 wcr_4rhj134002w34.rec 6 Identify the workload capture ID. SQL > select id, name, status, start_time, end_time, connects, user_calls, dir_path from dba_workload_captures where id = (select max(id) from dba_workload_captures);
ID 1
NAME capture
STATUS COMPLETED
START_TIM 11-04-2010
END_TIME 11-04-2010
CONNECTS 0
USER_CALLS 9
DIR_PATH /scratch/cagray/REPLAY
Note that if dbms_workload_capture.finish_capture() does not complete with success (i.e. dba_workload_captures.status != 'COMPLETED'), then an ORA-20222: "WORKLOAD CAPTURE IN "
7 Review the workload capture report with API.
SQL > set pagesize 0 long 30000000 longchunksize 2000
SQL > select dbms_workload_capture.report (capture_id => 1,'TEXT') from dual;
See sample output at end of article for example. The report format options are TEXT, HTML, XML.
8 Export capture workload AWR data into the workload directory.
SQL> exec dbms_workload_capture.export_awr (capture_id => 1);
$ ls -l /
drwxrwxr-x 12 cagray g900 4096 Apr 11 12:17 inst1
-rw-rw---- 1 cagray g900 4366336 Apr 11 12:54 wcr_ca.dmp <- AWR data -rw-rw-rw- 1 cagray g900 12696 Apr 11 12:54 wcr_ca.log -rw-rw-r-- 1 cagray g900 31308 Apr 11 12:24 wcr_cr.html -rw-rw-r-- 1 cagray g900 14218 Apr 11 12:24 wcr_cr.text -rw-rw-r-- 1 cagray g900 151 Apr 11 12:24 wcr_fcapture.wmd -rw-rw-r-- 1 cagray g900 93 Apr 11 12:17 wcr_scapture.wmd 9 Run the AWR report for the capture workload period. SQL > select id,AWR_BEGIN_SNAP,AWR_END_SNAP from dba_workload_captures;
ID AWR_BEGIN_SNAP AWR_END_SNAP
---- ---------------------------- --------------------------
1 4 5
On the Replay instance, preprocess and replay a captured workload
1 Confirm the dbms_workload_replay package exists and is valid.
SQL > select object_name, object_type, status from dba_objects where object_name like '%WORKLOAD_REPLAY';
OBJECT_NAME OBJECT_TYPE STATUS
--------------------------------------------------------------
DBMS_WORKLOAD_REPLAY PACKAGE VALID
DBMS_WORKLOAD_REPLAY SYNONYM VALID
DBMS_WORKLOAD_REPLAY PACKAGE BODY VALID
2 Create an empty directory for the collected workload information on the replay instance.
$ mkdir /
SQL > create directory replay as '/
3.3 Copy the files created by the capture instance to the replay directory.
$ cp /
4 Preprocess the captured workload using API.
SQL> exec DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'REPLAY');
$ ls -l /
drwxrwxr-x 12 cagray g900 4096 Apr 11 12:17 inst1
-rw-rw---- 1 cagray g900 4366336 Apr 11 12:54 wcr_ca.dmp
-rw-rw-rw- 1 cagray g900 12696 Apr 11 12:54 wcr_ca.log
-rw-rw-r-- 1 cagray g900 3586 Apr 11 13:16 wcr_calibrate.xml <- preprocess -rw-rw---- 1 cagray g900 12288 Apr 11 13:16 wcr_conn_data.extb <- preprocess -rw-rw-r-- 1 cagray g900 31308 Apr 11 12:24 wcr_cr.html -rw-rw-r-- 1 cagray g900 14218 Apr 11 12:24 wcr_cr.text -rw-rw-r-- 1 cagray g900 151 Apr 11 12:24 wcr_fcapture.wmd -rw-rw-r-- 1 cagray g900 104 Apr 11 13:16 wcr_login.pp <- preprocess -rw-rw-r-- 1 cagray g900 35 Apr 11 13:16 wcr_process.wmd <- preprocess -rw-rw-r-- 1 cagray g900 93 Apr 11 12:17 wcr_scapture.wmd -rw-rw---- 1 cagray g900 12288 Apr 11 13:16 wcr_scn_order.extb <- preprocess -rw-rw---- 1 cagray g900 12288 Apr 11 13:16 wcr_seq_data.extb <- preprocess 5 Initialize workload replay. SQL> exec DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(replay_name => 'replay', replay_dir => 'REPLAY');
6 Put the replay database in replay state.
SQL> exec DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY();
7 Start the replay client.
Open another terminal window and execute the following workload replay client (wrc) command:
wrc userid=system password=
You should see the following output on the screen:
Workload Replay Client: Release 11.1.0.7.0 - Production on Sun Apr 11 13:29:54 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (13:29:54)
Note that if you attempt to start replay before replay client has been connected you will get the following:
SQL> exec DBMS_WORKLOAD_REPLAY.START_REPLAY;
BEGIN DBMS_WORKLOAD_REPLAY.START_REPLAY; END;
*
ERROR at line 1:
ORA-20223: No replay clients have connected yet! Please issue START_REPLAY()
after one or more WRC replay clients have been started!
ORA-06512: at "SYS.DBMS_WORKLOAD_REPLAY", line 1926
ORA-06512: at line 1
8 Execute workload replay.
SQL> exec DBMS_WORKLOAD_REPLAY.START_REPLAY;
$ ls /
-rw-rw---- 1 cagray g900 4366336 Apr 11 12:54 wcr_ca.dmp
-rw-rw-rw- 1 cagray g900 12696 Apr 11 12:54 wcr_ca.log
-rw-rw-r-- 1 cagray g900 3586 Apr 11 13:16 wcr_calibrate.xml
-rw-rw---- 1 cagray g900 12288 Apr 11 13:16 wcr_conn_data.extb
-rw-rw-r-- 1 cagray g900 31308 Apr 11 12:24 wcr_cr.html
-rw-rw-r-- 1 cagray g900 14218 Apr 11 12:24 wcr_cr.text
-rw-rw-r-- 1 cagray g900 151 Apr 11 12:24 wcr_fcapture.wmd
-rw-rw-r-- 1 cagray g900 104 Apr 11 13:16 wcr_login.pp
-rw-rw-r-- 1 cagray g900 35 Apr 11 13:16 wcr_process.wmd
-rw-rw-r-- 1 cagray g900 0 Apr 11 13:32 wcr_rep_00001.start <- start replay -rw-rw-r-- 1 cagray g900 342 Apr 11 13:32 wcr_replay.wmd <- start replay -rw-rw-r-- 1 cagray g900 93 Apr 11 12:17 wcr_scapture.wmd -rw-rw---- 1 cagray g900 12288 Apr 11 13:16 wcr_scn_order.extb -rw-rw---- 1 cagray g900 12288 Apr 11 13:16 wcr_seq_data.extb When complete, the workload replay client (wcr) window will have the following: Wait for the replay to start (13:29:54) Replay started (13:32:17) Replay finished (13:35:58) To check the status of replay, use the following SELECT. The value for dba_workload_replays.status will be 'IN PROGRESS' while processing replay and 'COMPLETED' when done. SQL > select id, name, status from dba_workload_replays;
9 Generate the replay report.
SQL> set pagesize 0 long 30000000 longchunksize 2000
SQL> select dbms_workload_replay.report(replay_id => 1,format => 'TEXT') from dual;
See sample output at end of article for example. The report format options are TEXT, HTML, XML.
10 Import Capture AWR data into the Replay database to compare both capture and replay AWR data.
SQL > select dbms_workload_capture.import_awr(capture_id => 1, staging_schema => 'AWRRPT') from dual;
SQL > -- get the capture data details
SQL > select id, awr_begin_snap, awr_end_snap from dba_workload_captures;
SQL > -- get the replay data details
SQL> select id, awr_begin_snap, awr_end_snap from dba_workload_replays;
Use dbms_workload_repository.awr_diff_report_text or dbms_workload_repository.awr_diff_report_html to generate a comparison report.
Appendix A: Sample dbms_workload_capture.report.
Note: Only statement issued in workload session: UPDATE scott.emp set empno = empno+100;
Database Capture Report For C11107
DB Name DB Id Release RAC Capture Name Status
------------ ----------- ----------- --- -------------------------- ----------
C11107 1671618030 11.1.0.7.0 NO capture COMPLETED
Start time: 11-Apr-10 12:17:53 (SCN = 401832)
End time: 11-Apr-10 12:24:01 (SCN = 402239)
Duration: 6 minutes 8 seconds
Capture size: 1.61 KB
Directory object: REPLAY
Directory path: /scratch/cagray/replay
Directory shared in RAC: TRUE
Filters used: 0 EXCLUSION filters
Captured Workload Statistics DB: C11107 Snaps: 4-5
-> '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.02 20.00
Average Active Sessions 0.00
User calls captured 9 56.25
User calls captured with Errors 0
Session logins 0 0.00
Transactions 1 12.50
-------------------------------------------------------------
Top Events Captured DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
Top Service/Module Captured DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Captured DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions Captured DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
Top Events containing Unreplayable Calls DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
Top Service/Module containing Unreplayable Calls DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL containing Unreplayable Calls DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions containing Unreplayable Calls DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
Top Events Filtered Out DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
Top Service/Module Filtered Out DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Filtered Out DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions Filtered Out DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
Top Events (Jobs and Background Activity) DB: C11107 Snaps: 4-5
Avg Active
Event Event Class % Event Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 50.00 0.00
null event Other 50.00 0.00
-------------------------------------------------------------
Top Service/Module (Jobs and Background Activity) DB: C11107 Snaps: 4-5
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$BACKGROUND KTSJ 50.00 KTSJ Slave 50.00
UNNAMED 50.00 UNNAMED 50.00
-------------------------------------------------------------
Top SQL (Jobs and Background Activity) DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions (Jobs and Background Activity) DB: C11107 Snaps: 4-5
-> '# 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
-------------------- ------------------------------ ------------------ --------
95, 8 50.00 null event 50.00
SYS oracle@dadvmo0162 (W000) 1/368 [ 0%] 0
112, 1 50.00 CPU + Wait for CPU 50.00
SYS oracle@dadvmo0162 (DBW0) 1/368 [ 0%] 0
-------------------------------------------------------------
Workload Filters DB: C11107 Snaps: 4-5
No data exists for this section of the report.
-------------------------------------------------------------
End of Report
Appendix B: Sample dbms_workload_replay.report.
DB Replay Report for replay
------------------------------------------------------------------------------
---------------------
-------------------------------------------------------------------------
| DB Name | DB Id | Release | RAC | Replay Name | Replay Status |
-------------------------------------------------------------------------
| C11107 | 1671618030 | 11.1.0.7.0 | NO | replay | COMPLETED |
-------------------------------------------------------------------------
Replay Information
----------------------------------------------------------------------
| Information | Replay | Capture |
----------------------------------------------------------------------
| Name | replay | capture |
----------------------------------------------------------------------
| Status | COMPLETED | COMPLETED |
----------------------------------------------------------------------
| Database Name | C11107 | C11107 |
----------------------------------------------------------------------
| Database Version | 11.1.0.7.0 | 11.1.0.7.0 |
----------------------------------------------------------------------
| Start Time | 11-APR-10 13:32:15 | 11-APR-10 12:17:53 |
----------------------------------------------------------------------
| End Time | 11-APR-10 13:35:23 | 11-APR-10 12:24:01 |
----------------------------------------------------------------------
| Duration | 3 minutes 8 seconds | 6 minutes 8 seconds |
----------------------------------------------------------------------
| Directory Object | REPLAY | REPLAY |
----------------------------------------------------------------------
| Directory Path | /scratch/cagray/replay | /scratch/cagray/replay |
----------------------------------------------------------------------
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 | 2.416 seconds | 0.016 seconds |
-------------------------------------------------------------
| Average Active Sessions | .01 | 0 |
-------------------------------------------------------------
| User calls | 9 | 9 |
-------------------------------------------------------------
| Network Time | 0.046 seconds | . |
-------------------------------------------------------------
| Think Time | 183.407 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. |
--------------------------------------------------
Replay Clients Alerts
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
End of Report.
