juliandyke.com
1
© 2012 - Julian Dyke
Julian Dyke
Independent Consultant
Is RAT Worth Catching?
juliandyke.com
2
© 2012 - Julian Dyke
Introduction
- 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
juliandyke.com
3
© 2012 - Julian Dyke
Database Replay
Client
Client
Client
Oracle
Database
Storage
Workload
Capture
Production
Test
Workload
Preprocessing
Workload
Replay
Analysis & Reporting
Storage
Storage
Application
Tier
Oracle
Database
Storage
Storage
Storage
Replay
Client
Replay
Client
juliandyke.com
4
© 2012 - Julian Dyke
Prerequisites
- Workload capture is supported
on the following versions:
- 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
Version
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
juliandyke.com
5
© 2012 - Julian Dyke
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 MGMT_USER TO ratuser;
GRANT EXECUTE_CATALOG_ROLE TO ratuser
GRANT SELECT_CATALOG_ROLE TO ratuser;
GRANT CREATE ANY DIRECTORY TO ratuser;
GRANT DROP ANY DIRECTORY TO ratuser;
GRANT SELECT ON DBA_WORKLOAD_CAPTURES TO RATUSER;
GRANT UNLIMITED TABLESPACE TO ratuser;
GRANT SELECT ANY TABLE TO ratuser;
juliandyke.com
6
© 2012 - Julian Dyke
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
$ mkdir /home/oracle/capture1
$ chmod –R 777 /home/oracle/capture1
sqlplus ratuser/ratuser
SQL> CREATE OR REPLACE DIRECTORY
capture1
AS ‘/home/oracle/capture1’;
juliandyke.com
7
© 2012 - Julian Dyke
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
- Workload capture can be
disabled using:
- $ORACLE_HOME/rdbms/admin/wrrdsbl.sql
- 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
ALTER SYSTEM SET pre_11g_enable_capture=true
SID=‘*’;
ALTER SYSTEM SET pre_11g_enable_capture=false
SID=‘*’;
juliandyke.com
8
© 2012 - Julian Dyke
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:
- The file contains 0 bytes
and is deleted when the capture is disabled
juliandyke.com
9
© 2012 - Julian Dyke
RAT Trap - Restart
the Database
- 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
juliandyke.com
10
© 2012 - Julian Dyke
RAT Trap – Capture
Restrictions
- The following types of
client request are known restrictions for workload capture
- 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
juliandyke.com
11
© 2012 - Julian Dyke
Capture Filters
- 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
juliandyke.com
12
© 2012 - Julian Dyke
Workload Capture Directories
and Files
- In Oracle 11.2 and above
START_CAPTURE creates subdirectories in capture directory
- 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
juliandyke.com
13
© 2012 - Julian Dyke
Workload Capture Files
- 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
juliandyke.com
14
© 2012 - Julian Dyke
RAT Trap – Minimum
CPU time
- 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
juliandyke.com
15
© 2012 - Julian Dyke
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
juliandyke.com
16
© 2012 - Julian Dyke
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;
/
juliandyke.com
17
© 2012 - Julian Dyke
Generating a Capture
Report
- 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
juliandyke.com
18
© 2012 - Julian Dyke
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;
/
- 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
juliandyke.com
19
© 2012 - Julian Dyke
- 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
RAT Trap – AWR Snapshots
juliandyke.com
20
© 2012 - Julian Dyke
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
juliandyke.com
21
© 2012 - Julian Dyke
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
ALTER SYSTEM SET compatibility = ’11.2.0.3’
SCOPE = SPFILE;
ALTER SYSTEM SET optimizer_features_enable
= ’11.2.0.3’ SCOPE = SPFILE;
- Update COMPATIBILITY and
OPTIMIZER_FEATURES_ENABLE parameters
- Ensure replay database
parameters have equivalent values to capture database parameters
juliandyke.com
22
© 2012 - Julian Dyke
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;
juliandyke.com
23
© 2012 - Julian Dyke
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’;
juliandyke.com
24
© 2012 - Julian Dyke
Replay Preprocessing
- Pre-process the capture
for replay.
BEGIN
dbms_workload_replay.process_capture
(
capture_dir => ‘CAPTURE1’
);
END;
/
- 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
juliandyke.com
25
© 2012 - Julian Dyke
Replay Pre-processing
- 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
juliandyke.com
26
© 2012 - Julian Dyke
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 pre-processing
- To determine what percentage
of capture pre-processing is complete use:
SELECT dbms_workload_replay.process_capture_completion
FROM dual;
- Returns percentage of capture
files that have already been processed
- Percentage is updated every
60 seconds
juliandyke.com
27
© 2012 - Julian Dyke
Workload Analyzer
- 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
juliandyke.com
28
© 2012 - Julian Dyke
Workload Analyzer
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>
- <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
- 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
juliandyke.com
29
© 2012 - Julian Dyke
RAT Trap - System
Clock
- 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
juliandyke.com
30
© 2012 - Julian Dyke
RAT Trap – External
References
- 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
juliandyke.com
31
© 2012 - Julian Dyke
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
BEGIN
dbms_workload_replay.initialize_replay
(
replay_name => ‘CAPTURE1_REPLAY1’,
replay_dir => ‘CAPTURE1’
);
END;
/
- Known to populate the following
base tables:
- WRR$_REPLAYS
- WRR$_CONNECTION_MAP
juliandyke.com
32
© 2012 - Julian Dyke
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
juliandyke.com
33
© 2012 - Julian Dyke
RAT Trap – Remap
Connections
- 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
juliandyke.com
34
© 2012 - Julian Dyke
Prepare Replay
- Specify parameters for
workload replay using the PREPARE_REPLAY
procedure
- 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
- 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%
EXECUTE dbms_workload_replay.prepare_replay
(synchronization => TRUE);
juliandyke.com
35
© 2012 - Julian Dyke
Prepare Replay
- 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
36
© 2012 - Julian Dyke
RAT Trap - Synchronization
- 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
juliandyke.com
37
© 2012 - Julian Dyke
Synchronization
INSERT INTO t
1000000 ROWS
SELECT FROM t
1000000 ROWS
UPDATE t
1000000 ROWS
DELETE FROM t
1000000 ROWS
A
B
C
D
14:30
14:45
15:00
15:15
15:30
Capture – Table t
contains 0 rows at end of capture
Sessions
juliandyke.com
38
© 2012 - Julian Dyke
Synchronization
INSERT INTO t
1000000 ROWS
SELECT FROM t
0 ROWS
UPDATE t
0 ROWS
DELETE FROM t
0 ROWS
A
B
C
D
14:30
14:45
15:00
15:15
15:30
Replay without synchronization
In this example table t contains 1000000 rows at end of replay
Sessions
juliandyke.com
39
© 2012 - Julian Dyke
RAT Trap – Connect
and Think Times
- 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
juliandyke.com
40
© 2012 - Julian Dyke
Timing
A
B
C
D
BATCH
OLTP
OLTP
OLTP
14:30
14:45
15:00
15:15
15:30
Sessions
Capture – OLTP sessions
run at 14:30 and complete by 14:45
Batch runs at 15:00
juliandyke.com
41
© 2012 - Julian Dyke
Timing
A
B
C
D
BATCH
OLTP
OLTP
OLTP
14:30
14:45
15:00
15:15
15:30
Sessions
Replay with connect
time set to 0%
Batch and OLTP all start at 14:30 – may result in resource contention
juliandyke.com
42
© 2012 - Julian Dyke
RAT Trap – Scaling
- 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
juliandyke.com
43
© 2012 - Julian Dyke
Workload Replay Client
- 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
juliandyke.com
44
© 2012 - Julian Dyke
Workload Replay Client
- Calibration
- Run calibration to determine
how many replay clients are required to support the replay workload
wrc ratuser/ratuser MODE=calibrate
REPLAYDIR=‘/home/oracle/capture1’;
- 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)
juliandyke.com
45
© 2012 - Julian Dyke
Workload Replay Client
- Calibration
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
juliandyke.com
46
© 2012 - Julian Dyke
Workload Replay Client
- Replay
- To replay the workload
specify the REPLAY mode
wrc ratuser/ratuser MODE=replay REPLAYDIR=‘/home/oracle/capture1’
- 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
juliandyke.com
47
© 2012 - Julian Dyke
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;
- 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
juliandyke.com
48
© 2012 - Julian Dyke
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
juliandyke.com
49
© 2012 - Julian Dyke
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;
juliandyke.com
50
© 2012 - Julian Dyke
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);/
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
- 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
juliandyke.com
51
© 2012 - Julian Dyke
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;
/
juliandyke.com
52
© 2012 - Julian Dyke
Generating a Replay
Report
- 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
juliandyke.com
53
© 2012 - Julian Dyke
Divergence
- 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
juliandyke.com
54
© 2012 - Julian Dyke
Listing Divergences
- 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
juliandyke.com
55
© 2012 - Julian Dyke
RAT Trap – PLSQL
Blocks
- 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
juliandyke.com
56
© 2012 - Julian Dyke
Replay Analysis
- The following types of
reports are available to analyse workload capture and replay:
juliandyke.com
57
© 2012 - Julian Dyke
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
juliandyke.com
58
© 2012 - Julian Dyke
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
- The IMPORT_AWR function
returns the new DBID assigned to the imported AWR.
juliandyke.com
59
© 2012 - Julian Dyke
Compare Period Reports
- 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
juliandyke.com
60
© 2012 - Julian Dyke
Compare Period Reports
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
juliandyke.com
61
© 2012 - Julian Dyke
RAT Trap – DB Time
- 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
juliandyke.com
62
© 2012 - Julian Dyke
Compare SQLSET Reports
- 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
juliandyke.com
63
© 2012 - Julian Dyke
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
(
replay_id1 => 44,
replay_id2 => NULL,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML,
result => :v_clob
);
END;
/
PRINT v_clob
SPOOL OFF
Compare replay with
original capture
juliandyke.com
64
© 2012 - Julian Dyke
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
(
replay_id1 => 44,
replay_id2 => 42,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML,
result => :v_clob
);
END;
/
PRINT v_clob
SPOOL OFF
Compare replay with
another replay
juliandyke.com
65
© 2012 - Julian Dyke
Conclusions
- 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
juliandyke.com
66
© 2012 - Julian Dyke
Acknowledgements
- This presentation would
not have been possible without the help of:
- Andrew Ashworth (Ash)
- Pete Taylor