Transcript Oracle 11gR2 for RAC Users - Session 2

Is RAT Worth Catching?
Julian Dyke
Independent Consultant
1
© 2012 - Julian Dyke
juliandyke.com
Introduction
2

Real Application Testing (RAT)
 Introduced in Oracle 11gR1
 Also referred to as Database Replay

This presentation describes RAT in Oracle 11gR2

Separately licenced option .

Processor license *
 Real Application Testing
$11,500

Some comparative processor license *
 Enterprise Edition
$47,500
 Real Application Clusters
$23,000
 Partitioning Option
$11,500

* Source – Oracle Technology Global Price List – 08Nov12
© 2012 - Julian Dyke
juliandyke.com
Database Replay
Production
Client
Client
Test
Client
Replay
Client
Replay
Client
Application Tier
Oracle
Database
Storage
Storage
Workload
Capture
3
Oracle
Database
Storage
Storage
Workload
Preprocessing
© 2012 - Julian Dyke
Storage
Workload
Replay
Storage
Analysis &
Reporting
juliandyke.com
Prerequisites

Workload capture is supported on the following versions:
Version
4
One-off Patch
9.2.0.8
9373986
10.2.0.2
9373986
10.2.0.3
9373986
10.2.0.4
10239989
10.2.0.5
9373986
11.2.0.1
9373986

A one-off patch is required to implement workload capture in versions earlier
than 11.2.0.2

Workload replay is supported in 11.2.0.1 and above
 Oracle 11.2.0.2 requires one-off patch 11870615
© 2012 - Julian Dyke
juliandyke.com
Capture User

The workload can be captured by SYS

Alternatively a new user can be created to manage the workload capture. For
example:
CREATE USER ratuser
IDENTIFIED BY ratuser
DEFAULT TABLESPACE SYSAUX
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
MGMT_USER TO ratuser;
EXECUTE_CATALOG_ROLE TO ratuser
SELECT_CATALOG_ROLE TO ratuser;
CREATE ANY DIRECTORY TO ratuser;
DROP ANY DIRECTORY TO ratuser;
SELECT ON DBA_WORKLOAD_CAPTURES TO RATUSER;
GRANT UNLIMITED TABLESPACE TO ratuser;
GRANT SELECT ANY TABLE TO ratuser;
5
© 2012 - Julian Dyke
juliandyke.com
Capture Directory

Captured workload is stored in binary files in a directory tree below the
capture directory

Capture will stop if capture directory is full

For RAC databases either:
 Use a shared file system for workload capture
 Use local file systems and subsequently merge contents

For example:
$ mkdir /home/oracle/capture1
$ chmod –R 777 /home/oracle/capture1
sqlplus ratuser/ratuser
SQL> CREATE OR REPLACE DIRECTORY capture1
AS ‘/home/oracle/capture1’;
6
© 2012 - Julian Dyke
juliandyke.com
Capture Parameters

In Oracle 10.2.0.5 and below, workload capture must be enabled using the
PRE_11G_ENABLE_CAPTURE parameter

In Oracle 10.2.0.5 and below:
 Workload capture can be enabled using :
 $ORACLE_HOME/rdbms/admin/wrrenbl.sql
ALTER SYSTEM SET pre_11g_enable_capture=true SID=‘*’;

Workload capture can be disabled using:
 $ORACLE_HOME/rdbms/admin/wrrdsbl.sql
ALTER SYSTEM SET pre_11g_enable_capture=false SID=‘*’;
7

Workload capture is enabled by default in Oracle 11.2.0.1 and above
 The PRE_11G_ENABLE_CAPTURE parameter is not supported

Workload capture can also be enabled using Enterprise Manager
© 2012 - Julian Dyke
juliandyke.com
Starting a Workload Capture

To start a workload capture use the START_CAPTURE procedure:
DBMS_WORKLOAD_CAPTURE.START_CAPTURE
(
name
=> ‘CAPTURE1’,
dir
=> ‘CAPTURE1’,
duration => 3600
);

Duration is specified in seconds
 1 hour = 3600 seconds
 24 hours = 86400 seconds

While capture is enabled a temporary file is created in the capture directory.
For example:
 wcr_cap_0003n.start
The file contains 0 bytes and is deleted when the capture is disabled


8
© 2012 - Julian Dyke
juliandyke.com
RAT Trap - Restart the Database
9

Oracle recommends that the database is restarted immediately before any
workload capture
 Ensures that any on-going transactions are completed or rolled back
before the capture begins
 Reduces amount of divergence

Start workload capture immediately after database restart
 Restart database in RESTRICTED mode using STARTUP RESTRICT
 Login as SYS and start workload capture
 Instance will automatically switch to UNRESTRICTED mode

Experience suggests that the database should be also restarted before any
workload replay
 Changes to system clock affect AWR snapshots
© 2012 - Julian Dyke
juliandyke.com
RAT Trap – Capture Restrictions

The following types of client request are known restrictions for workload
capture







10
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
© 2012 - Julian Dyke
juliandyke.com
Capture Filters
11

By default all user sessions are recorded during workload capture

Workload filters can be configured to include or exclude sessions from the
capture

Include filters specify user sessions that will be captured. Can be used to
capture a subset of the workload

Exclude filters specify user sessions that will not be captured. Can be
used to exclude sessions such as
 Enterprise Manager Agent,
 STATSPACK,
 BMC Patrol
 Quest Spotlight
 Precise I3
 etc
© 2012 - Julian Dyke
juliandyke.com
Workload Capture Directories and Files
12

In Oracle 11.2 and above START_CAPTURE creates subdirectories in capture
directory
 cap
 capfiles
 inst1
 aa to aj

Each session stores data in a record file e.g. wcr_czq45h0000005.rec
 Compressed file
 XML-like format with elements and attributes
 All SQL calls including
 statement text
 bind variables
 execution time
 rows returned
 error code
 Flushed periodically
© 2012 - Julian Dyke
juliandyke.com
Workload Capture Files
13

START_CAPTURE generates the following workload metadata (WMD) files
automatically in the cap directory
 wcr_scapture.wmd - Start capture details
 wcr_fcapture.wmd - Finish capture details

In Oracle 11.2 and above START_CAPTURE automatically generates a capture
report in the cap directory
 wcr_cr.html
- Capture report – HTML format
 wcr_cr.text
- Capture report – Text format
© 2012 - Julian Dyke
juliandyke.com
RAT Trap – Minimum CPU time


14
A replay requires a minimum of 5 minutes captured CPU time
 CPU time NOT Elapsed Time
May be a problem for workloads that are:
 I/O-bound
 Network intensive
 Application-bound

Workload capture period must be long enough to capture five minutes CPU

Can use SLEEP functions to artificially capture CPU
 Does not work with PL/SQL DBMS_LOCK.SLEEP function
 Works with JServer Java sleep class
 Works with External C class
© 2012 - Julian Dyke
juliandyke.com
RAT Trap – Finishing Capture

Capture files are written using buffered I/O

Advantages are:
 Lower overhead
 Lower impact on workload

Disadvantages are:
 Buffers are not flushed automatically
 SQL statements can be “lost”

When capture ends or FINISH_CAPTURE is executed:
 Timeout period is specified
 Sessions will be informed during next database call

If sessions make a database call within timeout period
 Capture file buffer will be flushed
Otherwise contents will be lost

15
© 2012 - Julian Dyke
juliandyke.com
Generating a Capture Report

Determine the capture ID from the capture name e.g.:
SELECT id FROM dba_workload_captures
WHERE name = ‘CAPTURE1’;
ID
1

Generate the capture report e.g.:
SET SERVEROUTPUT ON
DECLARE
l_clob CLOB
BEGIN
l_clob := dbms_workload_capture.report
(
capture_id=>1,
format => DBMS_WORKLOAD_CAPTURE.TYPE_TEXT
);
DBMS_OUTPUT.PUT_LINE (l_clob);
END;
/
16
© 2012 - Julian Dyke
juliandyke.com
Generating a Capture Report
17

The capture report format can be:
 DBMS_WORKLOAD_CAPTURE.TYPE_TEXT
 DBMS_WORKLOAD_CAPTURE.TYPE_HTML

The capture report includes the following:
 Details of workload capture including name, filters, date, time and SCN
 Overall statistics including total DB time, number of logins, number of
transactions
 Profile of captured workload
 Profile of workload not captured due to version limitations
 Profile of uncaptured workload that was excluded by defined filters
 Profile of uncaptured workload consisting of background process and
scheduled jobs
© 2012 - Julian Dyke
juliandyke.com
Exporting the AWR

An export of the AWR is required for subsequent analysis on the replay
system

To export the AWR use the EXPORT_AWR procedure. For example:
BEGIN
dbms_workload_capture.export_awr (capture_id=>1);
END;
/

18
This creates the following files in the capture directory:
 wcr_ca.dmp – Data Pump export
 wcr_ca.log – Data Pump log file
 wcr_cap_uc_graph.extb – User calls graph
© 2012 - Julian Dyke
juliandyke.com
RAT Trap – AWR Snapshots
19

AWR snapshots are required for subsequent reporting

Ensure that the AWR snapshots are exported before they exceed the AWR
retention period and are automatically deleted
© 2012 - Julian Dyke
juliandyke.com
Restore Strategy

A strategy is required to restore a pre-capture copy of the database to the
replay system

One of the following methods can be used:
 RMAN backup and restore
 RMAN DUPLICATE command
 Snapshot standby
 Data Pump Import and Export
 SAN replication

The database should be restored on the replay system to the point in time at
the start of the capture
Recommendation
Enable flashback logging and test
the replay before performing a
recorded test
20
© 2012 - Julian Dyke
juliandyke.com
Replay Preparation

Restore pre-capture database to new 10.2.0.4 home
ALTER DATABASE OPEN RESETLOGS;

Modify archive log destination
ALTER SYSTEM SET log_archive_dest_1 =
‘LOCATION=/11/oradata/<DUP_DB>/arch MANDATORY REOPEN=300’;

Run standard 11.2.0.3 upgrade

Update COMPATIBILITY and OPTIMIZER_FEATURES_ENABLE parameters
ALTER SYSTEM SET compatibility = ’11.2.0.3’ SCOPE = SPFILE;
ALTER SYSTEM SET optimizer_features_enable = ’11.2.0.3’ SCOPE = SPFILE;

21
Ensure replay database parameters have equivalent values to capture
database parameters
© 2012 - Julian Dyke
juliandyke.com
Replay User

Replay can be performed by SYS user

Alternatively create a dedicated replay user e.g. RATUSER.
$ sqlplus / as sysdba
SQL> GRANT DBA TO ratuser;
22
© 2012 - Julian Dyke
juliandyke.com
Replay Directory

The contents of the capture directory should be copied across to the replay
server.

It will be necessary to recreate the Oracle directory object:
$ sqlplus ratuser/ratuser
SQL> CREATE OR REPLACE DIRECTORY capture1
AS ‘/home/oracle/capture1’;
23
© 2012 - Julian Dyke
juliandyke.com
Replay Preprocessing

Pre-process the capture for replay.

For example:
BEGIN
dbms_workload_replay.process_capture
(
capture_dir => ‘CAPTURE1’
);
END;
/


24
Analyses the workload capture found in the CAPTURE_DIR
Creates workload replay metadata files required to replay the given workload
capture
 Can be run multiple times on same capture directory
 Must be run in same database version as replay
 Can run multiple replays following execution of PROCESS_CAPTURE
© 2012 - Julian Dyke
juliandyke.com
Replay Pre-processing

25
In Oracle 11.2.0.3 the PROCESS_CAPTURE procedure creates a new
subdirectory called pp11.2.0.3.0 in the capture directory containing the
following files:
 wcr_calibrate.html
 wcr_commits.extb
 wcr_conn_data.extb
 wcr_data.extb
 wcr_dep_graph.extb
 wcr_login.pp
 wcr_process.wmd
 wcr_references.extb
 wcr_scn_order.extb
 wcr_seq_data.extb
© 2012 - Julian Dyke
juliandyke.com
Replay Preprocessing

To determine how long pre-processing will take use:
SELECT dbms_workload_replay.process_capture_remaining_time
FROM dual;

Returns an estimate of remaining capture time in minutes

An accurate estimate of remaining processing time cannot be determined
during the first minute
 Function returns NULL if invoked within first minute of capture preprocessing

To determine what percentage of capture pre-processing is complete use:
SELECT dbms_workload_replay.process_capture_completion
FROM dual;


26
Returns percentage of capture files that have already been processed
Percentage is updated every 60 seconds
© 2012 - Julian Dyke
juliandyke.com
Workload Analyzer
27

Workload Analyzer
 Available in 11.2.0.2 and above
 Java program that analyses a workload capture directory
 Identifies parts of captured workload that may not replay accurately e.g.
 Insufficient data
 Errors occurring during workload capture
 Usage features not supported by Database Replay

Results are stored in capture directory in the following files:
 wcr_cap_analysis.html
 wcr_cap_analysis.xml

Executed automatically by Enterprise Manager during capture pre-processing
 Must be invoked manually otherwise

Requires :
 $ORACLE_HOME/jdbc/lib/dbranalyzer.jar
 $ORACLE_HOME/jdbc/lib/dbrparser.jar
 Java 1.5 or above
© 2012 - Julian Dyke
juliandyke.com
Workload Analyzer

Syntax is
java –classpath \
$ORACLE_HOME/jdbc/lib/ojdbc5.jar:\
$ORACLE_HOME/rdbms/jlib/dbrparser.jar:\
$ORACLE_HOME/rdbms/jlib/dbranalyzer.jar \
oracle.dbreplay.workload.checker/CaptureChecker
<capture_directory> <connection_string>



In Oracle 11.2.0.3 this utility only works with JDBC thin client
 JDBC thick client (OCI) fails
Tested successfully with Java 1.6 and ojdbc6.jar

Analyzer will prompt for username/password of database user e.g. ratuser

28
<capture_directory> - operating system path of the capture directory
<connection_string> - connection string of Oracle database (11.1 and above)
 e.g. jdbc:oracle:thin:@myhost.mycompany.com:1521:orcl
© 2012 - Julian Dyke
juliandyke.com
RAT Trap - System Clock
29

Reset the system clock on the replay system to the start time of the capture

Ensure NTP daemon is disabled on replay server

Restart all database instances after resetting the system clock

Consider generating a new ID for the database to ensure AWR snapshots can
still be created automatically after the system clock is reset
© 2012 - Julian Dyke
juliandyke.com
RAT Trap – External References
30

Reconfigure references to external systems to avoid impact on production
systems during replay

References that should be resolved include:
 Database Links
 External Tables
 Directory Objects
 URLs
 E-mail servers
© 2012 - Julian Dyke
juliandyke.com
Initialize Replay

After workload capture has been pre-processed, replay data can be initialized
 Loads necessary metadata required by workload replay into tables
 captured connection strings are loaded into a table where they can be
remapped for replay

For example:
BEGIN
dbms_workload_replay.initialize_replay
(
replay_name => ‘CAPTURE1_REPLAY1’,
replay_dir => ‘CAPTURE1’
);
END;
/

31
Known to populate the following base tables:
 WRR$_REPLAYS
 WRR$_CONNECTION_MAP
© 2012 - Julian Dyke
juliandyke.com
Remap Connections

During capture, connection strings used to connect to production system are
captured
 Connection strings must be remapped to replay system
SELECT
‘EXEC dbms_workload_replay.remap_connection
(‘||a.conn_id||’,’’(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(HOST=<replay_server>)(PORT=<port_number>))
(CONNECT_DATA=(SID=<sid>)))’’);’
FROM dba_workload_connection_map a, dba_workload_replays b
WHERE a.replay_id = b.id
AND b.status = ‘INITIALIZED’
ORDER BY a.conn_id;
Note that the TNS address is enclosed by a pair of two
single quotes, NOT a pair of double quotes
32
© 2012 - Julian Dyke
juliandyke.com
RAT Trap – Remap Connections
33

Ensure that connections are remapped from the capture database to the
replay database

If connections are not remapped, workload may be replayed against
production database
© 2012 - Julian Dyke
juliandyke.com
Prepare Replay

Specify parameters for workload replay using the PREPARE_REPLAY
procedure

For example:
EXECUTE dbms_workload_replay.prepare_replay (synchronization => TRUE);

PREPARE_REPLAY parameters include:
 SYNCHRONIZATION – Determines how replay will be synchronized.
Options are:
 SCN – COMMIT order in captured workload is preserved during replay
 OBJECT_ID – COMMIT order is maintained for relevant objects only.
Relevant objects are objects referenced within current action
 OFF – replay is unsynchronized

34
CONNECT_TIME_SCALE – Scales elapsed time from start of workload
capture to time when session connects. Can be used to increase or
decrease concurrency. Default is 100%
© 2012 - Julian Dyke
juliandyke.com
Prepare Replay

35
PREPARE_REPLAY parameters (continued):
 THINK_TIME_SCALE – Scales elapsed time between successive user
calls from same session. Default is 100%. If 0 then no delays between
user calls

THINK_TIME_AUTO_CORRECT – Automatically correct think time
between calls when user calls take longer to complete during replay than
capture

SCALE_UP_MULTIPLIER – Defines number of times workload is scaled
up during replay
 Each captured session will be replayed number of times specified by
this parameter
 Only one session in each set will execute both queries and updates
 Remaining sessions in set will only execute queries

CAPTURE_STS – specifies whether or not to capture a SQL tuning set in
parallel with workload replay

STS_CAP_INTERVAL – specifies duration of SQL tuning set capture from
cursor cache in seconds
juliandyke.com
© 2012 - Julian Dyke
RAT Trap - Synchronization
36

Most workloads require full synchronization

Failure to synchronize will increase amount of divergence
 Potentially invalidates results

Object synchronization may be useful for a limited range of workloads
© 2012 - Julian Dyke
juliandyke.com
Synchronization
14:30
Sessions
A
B
C
14:45
15:00
15:15
15:30
INSERT INTO t
1000000 ROWS
SELECT FROM t
1000000 ROWS
UPDATE t
1000000 ROWS
DELETE FROM t
1000000 ROWS
D
Capture – Table t contains 0 rows at end of capture
37
© 2012 - Julian Dyke
juliandyke.com
Synchronization
14:30
14:45
15:00
Sessions
C
D
15:30
INSERT INTO t
1000000 ROWS
A
B
15:15
SELECT FROM t
0 ROWS
UPDATE t
0 ROWS
DELETE FROM t
0 ROWS
Replay without synchronization
In this example table t contains 1000000 rows at end of replay
38
© 2012 - Julian Dyke
juliandyke.com
RAT Trap – Connect and Think Times
39

Changing connect times and/or think times
 Can introduce or alleviate contention
 May not be representative of workload

No granularity
 Connect / think times must be changed for entire workload
 For example cannot distinguish between OLTP and batch within workload

Beware of
 Time dependent workload e.g. feeds from other systems
 External schedulers e.g. Control M
 Application server bottlenecks e.g. Java
© 2012 - Julian Dyke
juliandyke.com
Timing
14:30
14:45
15:00
Sessions
A
15:15
15:30
BATCH
B
OLTP
C
OLTP
D
OLTP
Capture – OLTP sessions run at 14:30 and complete by 14:45
Batch runs at 15:00
40
© 2012 - Julian Dyke
juliandyke.com
Timing
Sessions
14:30
14:45
A
BATCH
B
OLTP
C
OLTP
D
OLTP
15:00
15:15
15:30
Replay with connect time set to 0%
Batch and OLTP all start at 14:30 – may result in resource contention
41
© 2012 - Julian Dyke
juliandyke.com
RAT Trap – Scaling
42

RAT offers limited scaling of workloads

For a specific session
 SELECT statements can be scaled to multiple sessions
 DML statements are only executed in one session

Therefore scaling can only be considered appropriate for read-only workloads

For all other workloads,
 Further analysis will be required
 Scaling may not be appropriate
© 2012 - Julian Dyke
juliandyke.com
Workload Replay Client
43

Replay client is a multi-threaded program called wrc located in
$ORACLE_HOME/bin
 Included in Oracle 11.2.0.2 and above client

Each thread submits a workload from a captured session
 Database will wait for replay clients to connect before replay begins

Replay clients should run on separate hardware to database

Replay clients must have access to replay directory
 Replay directory should contain pre-processed workload capture
 Replay directory should be in different file system to database files

Replay client must specify username/password
 user must have DBA role
 user cannot be SYS
© 2012 - Julian Dyke
juliandyke.com
Workload Replay Client - Calibration

Run calibration to determine how many replay clients are required to support
the replay workload

For example:
wrc ratuser/ratuser MODE=calibrate REPLAYDIR=‘/home/oracle/capture1’;

44
Advanced parameters are:
 PROCESS_PER_CPU - Maximum number of client process than can be
run per CPU (Default: 4)
 THREADS_PER_PROCESS - Maximum number of threads than can be run
within a client process (Default: 50)
© 2012 - Julian Dyke
juliandyke.com
Workload Replay Client - Calibration

Sample output:
Workload Replay Client: Release 11.2.0.3.0 - Production on Thu May 24 20:01:35 2012
Report for Workload in: /home/oracle/rat/capture31
Recommendation:
Consider using at least 2 clients divided among 1 CPU(s)
You will need at least 135 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.
Workload Characteristics:
- max concurrency: 71 sessions
- total number of sessions: 15760
Assumptions:
- 1 client process per 50 concurrent session
- 4 client process per CPU
- 256 KB of memory cache per concurrent session
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE
45
© 2012 - Julian Dyke
juliandyke.com
Workload Replay Client - Replay

To replay the workload specify the REPLAY mode

For example:
wrc ratuser/ratuser MODE=replay REPLAYDIR=‘/home/oracle/capture1’

46
Options include:
 WORKDIR – directory for trace files
 DEBUG – ON or OFF (Default : OFF)
 CONNECTION_OVERRIDE – If TRUE the ignore replay connections
specified in DBA_WORKLOAD_CONNECTION_MAP. If FALSE (default)
use replay connections in DBA_WORKLOAD_CONNECTION_MAP
 SERIALIZE_CONNECTS – if TRUE all replay threads will connect to
database serially (one after another). Recommended for clients using
bequeath protocol. If FALSE (default) replay threads will connect to
database concurrently
 DSCN_OFF – If TRUE then ignore all dependencies due to block
contention during capture when synchronizing replay. If FALSE (default)
honour all captured dependencies
© 2012 - Julian Dyke
juliandyke.com
Starting a Workload Replay

After the replay clients have been started, the replay must be started on the
database
BEGIN
dbms_workload_replay.start_replay;
END;
47

The START_REPLAY procedure does not take any arguments

When executed within SQL*Plus,
 session returns to the prompt after replay has been started
 replay continues to execute in background
© 2012 - Julian Dyke
juliandyke.com
Workload Replay Directories


The replay creates a new replay directory within the capture directory
For example if the capture directory is /home/oracle/rat/capture31:


/home/oracle/rat/capture31/rep930632346
The replay directory name includes the REPLAY_DIR_NUMBER which is
reported in DBA_WORKLOAD_REPLAYS
SELECT replay_dir_number
FROM dba_workload_replays
WHERE id = 31;
REPLAY_DIR_NUMBER
930632346

The workload replay creates several files within the replay directory:

wcr_replay.wmd
- Replay workload metadata
wcr_rep_uc_graph_930632346.extb
- Replay user calls graph
wcr_rr_930632346.xml - Replay report – XML format


48
© 2012 - Julian Dyke
juliandyke.com
Managing a Workload Replay

To pause a workload replay use:
EXECUTE dbms_workload_replay.pause_replay;

To resume a paused workload replay use:
EXECUTE dbms_workload_replay.resume_replay;

To check if a replay is paused use:
SELECT dbms_workload_replay.is_replay_paused FROM dual;

To cancel a workload replay use:
EXECUTE dbms_workload_replay.cancel_replay;
49
© 2012 - Julian Dyke
juliandyke.com
Exporting AWR Data

AWR data
 can be exported to provide detailed workload analysis
 is also required for AWR Compare Period report

To export AWR use the EXPORT_AWR procedure e.g:
EXECUTE dbms_workload_replay.export_awr (replay_id=>107);/

Replay AWR data is exported into the replay directory, for example:
 /home/oracle/rat/capture31/rep930632346

The export files include the REPLAY_DIR_NUMBER:
 wcr_ra_930632346.dmp
 wcr_ra_930632346.log
AWR snapshots are required for subsequent reporting
Ensure that the AWR snapshots are exported before they exceed the AWR
retention period and are automatically deleted
50
© 2012 - Julian Dyke
juliandyke.com
Generating a Replay Report

Identify the most recent replay ID
SELECT MAX(id) AS id FROM dba_workload_replays
WHERE status = 'COMPLETED';
ID
2

Generate the replay report
SET SERVEROUTPUT ON
DECLARE
l_clob CLOB
BEGIN
l_clob := dbms_workload_replay.report
(
replay_id=>1,
format => DBMS_WORKLOAD_REPLAY.TYPE_TEXT
);
DBMS_OUTPUT.PUT_LINE (l_clob);
END;
/
51
© 2012 - Julian Dyke
juliandyke.com
Generating a Replay Report
52

The replay report format can be:
 DBMS_WORKLOAD_REPLAY.TYPE_TEXT
 DBMS_WORKLOAD_REPLAY.TYPE_HTML
 DBMS_WORKLOAD_REPLAY.TYPE_XML

The AWR snapshots must still exist in the replay database to generate the
replay report

The replay report includes the following:
 Details of workload replay including name, filters, date and time
 Replay options and number of replay clients started
 Overall statistics about the replay and capture including total DB time,
number of logins, number of transactions
 Profile of replayed workload
 Replay divergence
 Error divergence
 DML and SQL query data divergence
© 2012 - Julian Dyke
juliandyke.com
Divergence
53

When a workload is replayed there can be divergence between the capture
and the replay

There are two types of divergence

Performance Divergence – occurs when changes on the replay system
affect overall performance. Changes may include
 Software
 Hardware
 Parameters

Data Divergence – occurs when results of DML or SQL queries do not
match
 For example a SELECT statement may return fewer rows during
replay than during capture
© 2012 - Julian Dyke
juliandyke.com
Listing Divergences
54

Divergences are reported in DBA_WORKLOAD_REPLAY_DIVERGENCE

Each divergence is identified by
 REPLAY_ID
 STREAM_ID
 CALL_COUNTER

Information about a divergence is reported by the
GET_DIVERGING_STATEMENT procedure in DBMS_WORKLOAD_REPLAY
© 2012 - Julian Dyke
juliandyke.com
RAT Trap – PLSQL Blocks
55

Anonymous PL/SQL blocks are captured
 PL/SQL calls within those blocks are not captured
 SQL statement calls within those blocks are not captured

Only errors returned by PL/SQL block exceptions are recorded
 If recursive exceptions are caught and handled internally these will not be
reported

If PL/SQL logic is different, this may not be captured

Replay may be affected by
 Changes in supplied PL/SQL packages
 Changes in application PL/SQL packages
© 2012 - Julian Dyke
juliandyke.com
Replay Analysis

56
The following types of reports are available to analyse workload capture and
replay:

Capture Report

Replay Report

Compare Period Report

Compare SQLSET report
© 2012 - Julian Dyke
juliandyke.com
Importing a Capture AWR

Before running the compare reports, it is necessary to import the capture
AWR

The workload analyzer attempts to import the capture AWR, but this version
does not appear to be usable

The capture AWR should be imported into a new schema.
 In this example the new schema is called CAPTURE31
 RESOURCE role appears to be necessary and sufficient
CREATE USER capture31 IDENTIFIED BY capture31;
GRANT RESOURCE TO capture31;

Identify the ID of the capture workload
SELECT id FROM dba_workload_captures
WHERE name = ‘CAPTURE31’;
ID
115
57
© 2012 - Julian Dyke
juliandyke.com
Importing a Capture AWR

Import the AWR using the IMPORT_AWR procedure. For example:
SET SERVEROUTPUT ON
DECLARE
l_dbid NUMBER;
BEGIN
l_dbid := dbms_workload_capture.import_awr
(
capture_id => 115,
staging_schema => 'CAPTURE31‘
);
dbms_output.put_line (‘DBID = '||l_dbid);
END;
/
DBID = 2128266044

58
The IMPORT_AWR function returns the new DBID assigned to the imported
AWR.
© 2012 - Julian Dyke
juliandyke.com
Compare Period Reports
59

Compare Period reports allow comparison of
 Workload replay with workload capture
 Workload replay with another workload replay from same capture

Only workload replays containing at least 5 minutes of database time can be
compared using this report
© 2012 - Julian Dyke
juliandyke.com
Compare Period Reports

For example:
SET PAGESIZE 0
SET TRIMSPOOL ON
SET LINESIZE 500
SET FEEDBACK OFF
SET LONG 1000000
SET SERVEROUTPUT ON
VAR v_clob CLOB
BEGIN
dbms_workload_replay.compare_period_report
(
replay_id1 => 31,
replay_id2 => NULL,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML,
result => :v_clob
);
END;
/
PRINT v_clob
60
© 2012 - Julian Dyke
juliandyke.com
RAT Trap – DB Time
61

DB time is the best and possibly only metric to compare captures with
replays
 Other metrics can be used to compare replays with each other

DB time may be affected by
 Changes in hardware e.g.
 SSD ,
 10GbE networks
 faster CPU
 more memory
 Changes in Oracle version
 Additional functionality
 Longer code paths
 Background workload
 Divergence

Make sure you understand all the differences between environments before
making a decision based on Database Replay outcomes
© 2012 - Julian Dyke
juliandyke.com
Compare SQLSET Reports
62

SQL Performance Analyzer (SPA) reports can be generated using the
DBMS_WORKLOAD_REPLAY package

The SPA can be used to compare
 a SQL tuning set from a workload replay with the SQL tuning set from the
workload capture
 a SQL tuning set from a workload replay with the SQL tuning set from
another workload replay from the same workload capture
© 2012 - Julian Dyke
juliandyke.com
Compare SQLSET Report
SET PAGESIZE 0
SET TRIMSPOOL ON
SET LINESIZE 500
SET FEEDBACK OFF
SET LONG 1000000
VAR v_clob CLOB
DECLARE
l_result VARCHAR2(200);
BEGIN
l_result := dbms_workload_replay.compare_sqlset_report
(
Compare replay with
replay_id1 => 44,
original capture
replay_id2 => NULL,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML,
result => :v_clob
);
END;
/
PRINT v_clob
SPOOL OFF
63
© 2012 - Julian Dyke
juliandyke.com
Compare SQLSET Report
SET PAGESIZE 0
SET TRIMSPOOL ON
SET LINESIZE 500
SET FEEDBACK OFF
SET LONG 1000000
VAR v_clob CLOB
DECLARE
l_result VARCHAR2(200);
BEGIN
l_result := dbms_workload_replay.compare_sqlset_report
(
Compare replay with
replay_id1 => 44,
another replay
replay_id2 => 42,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML,
result => :v_clob
);
END;
/
PRINT v_clob
SPOOL OFF
64
© 2012 - Julian Dyke
juliandyke.com
Conclusions

65
RAT can be very good at capturing and replaying workloads

Requires a lot of user discipline

Follow the capture and replay recommendations

Unsupported workload features can increase divergence

For best results avoid using “enhancements” such as
 Object synchronization
 Connect time modifications
 Think time modifications
 Workload scaling
© 2012 - Julian Dyke
juliandyke.com
Acknowledgements

This presentation would not have been possible without the
help of:



66
Andrew Ashworth (Ash)
Pete Taylor
Thank you
© 2012 - Julian Dyke
juliandyke.com