Dear Readers,

Trust you are doing well.

In my last post, I foucssed on overview of RAT testing process.

In this post, I’ll delve into the process of capturing RAT workloads. This post will provide insights into key considerations, best practices, implementation strategies, and essential MOS notes for RAT capture.

WHAT IS CAPTURE?

To initiate Database Replay, the initial step involves capturing a production workload. This process entails recording all requests originating from external clients directed towards the Oracle Database.

Upon enabling workload capture, the Oracle Database meticulously tracks and archives all external client requests. These records are stored as binary files, known as capture files, on the file system. The user can specify the storage location for these capture files. As the workload capture commences, every external database call is documented in the capture files, encompassing crucial details like SQL text, bind values, and transaction information. It’s worth noting that background activities and database scheduler jobs are excluded from this capture process. The resulting capture files are platform-independent and can be easily transported to another system for subsequent replay.

The Capture feature is implemented as a lightweight trace on the user process, recording session-specific information into a single .rec file per session via buffered writes. These capture files are then written into a user-defined capture directory in the format: wcr_.rec, accompanied by a few metadata files. Each captured session is individually documented in its respective file. For instance, if 100 sessions are captured, there will be 100 wcr_.rec files generated.

CONSIDERATIONS

Performance Overhead:

The impact on performance during capture is contingent on the workload and correlates with the volume of data transmitted from the client. In essence, the greater the data sent from the client to the server, the higher the associated overhead. Typically, this overhead is influenced by the quantity of SQL statements executed within the capture period.

Space requirement by capture:

The AWR report monitors the cumulative incoming network traffic labeled as “bytes received via SQL*Net from client.” Analyzing an AWR report that encompasses a comparable workload period preceding the current one can offer a reliable estimate for the ongoing workload capture.

Approx space required ==> 2 * Bytes received via SQL*Net from client (from AWR)

Best time to run capture:

It is advisable to select either the peak workload, a full business cycle, or a specific workload of interest.

Storage requirement to keep captured files:

You have the flexibility to employ any filesystem with robust write throughput. Additionally, opting for the ACFS filesystem is a viable choice. In the case of RAC databases, it is advisable to utilize a shared file system.

Mandatory patches:

Before proceeding with RAT capture, it is essential to verify the Mandatory Patches for Database Testing Functionality for both the current and earlier releases by consulting Document ID 560977.1.

Limitations:

• Database Capture excludes workload capture from dbms_jobs or scheduler jobs.
• Avoid initiating a capture if the Oracle database is currently experiencing CPU starvation.
• DB capture is not supported on Oracle Automatic Storage Management (Oracle ASM) file systems.
• Additionally, the following types of client requests are not supported.
  • Direct path load of data from external files using utilities such as SQL*Loader
  • Non-PL/SQL based Advanced Queuing (AQ)
  • Flashback queries
  • Oracle Call Interface (OCI) based object navigations
  • Non SQL-based object access
  • Distributed transactions (any distributed transactions that are captured will be replayed as local transactions)
  • Oracle Streams/Advanced Replication workload is not supported prior to 11.2.
  • Database session migration
  • Database Resident Connection Pooling ( DRCP )
  • XA transactions
  • Workloads having Object Out Bind

STEPS FOR RAT CAPTURE

Setting Up the Capture Directory :

In the case of a single-instance database, establish a directory for storing the captured workload.
For RAC databases, configure a directory on a shared file system that spans across all nodes to store the captured workload.

SQL> CREATE directory rat_capture AS '/SHAREDFILE/RAT_CAPTURE';
SQL> GRANT read,write ON directory rat_capture TO PUBLIC;

Adding Workload Filters – Setup Filters for capture: Filter unnecessary program

Identify and filter out unnecessary sessions that are not required for capture before initiating a capture session.

Example:

BEGIN
dbms_workload_capture.Add_filter (fname => 'emfilter1',
fattribute => 'PROGRAM',
fvalue => 'OMS');
END;
/

Start Capture:(Duration need to be specified in seconds)

BEGIN
dbms_workload_capture.Start_capture (name => 'RAT_CAPTURE', dir => 'RAT_CAPTURE', duration => '3600');
END;
/

Check Progress of Capture:

col NAME FOR a25
col directory FOR a15
col status FOR a20
SELECT id,
NAME,
directory,
status
FROM dba_workload_captures;

Finish the capture if you intend to stop it before the designated time:

exec dbms_workload_capture.finish_capture();

Verify capture status and identify capture ID :

SELECT id,
NAME,
status,
start_time,
end_time,
dir_path
FROM dba_workload_captures
WHERE id = (SELECT Max(id)
FROM dba_workload_captures);

Generate the Capture Report :

Replace the ID with Capture ID returned from the step above:

SET pagesize 0 long 30000000 longchunksize 2000 linesize 155                    SELECT dbms_workload_capture.Report (<Capture ID>, 'HTML') FROM dual; -- HTML Format
SELECT dbms_workload_capture.Report (<Capture ID>, 'TEXT') FROM dual; -- Text Format

Exporting AWR Data for Workload Capture :

BEGIN
dbms_workload_capture.Export_awr (capture_id => <Capture ID>);
END;
/

OR you can use awrextr.sql to generate AWR dump :

@?/rdbms/admin/awrextr

MOS REFERENCES

Mandatory Patches for Database Testing Functionality for Current and Earlier Releases (Doc ID 560977.1)
Real Application Testing: Database Capture FAQ (Doc ID 1920275.1)
Real Application Testing (RAT) Capture Start and End Times Recorded in DBA_WORKLOAD_CAPTURES or DBA_WORKLOAD_REPLAYS are Different from SYSDATE or SYSTIMESTAMP (Doc ID 2149332.1)

In Part 3, I will elaborate on Workload preprocessing procedure.

Let me know for any questions and any further information in comments or LinkedIn.

Regards
Adityanath.