is rat worth catching

7/27/2019 Is Rat Worth Catching

1/66

juliandyke.com1 2012 - Julian Dyke

Julian Dyke

Independent Consultant

Is RAT Worth Catching?


2/66


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


3/66


Database Replay

Client Client Client

OracleDatabase

Storage

WorkloadCapture

Production Test

WorkloadPreprocessing

WorkloadReplay

Analysis &Reporting

Storage Storage

Application Tier

OracleDatabase

StorageStorage Storage

ReplayClient

ReplayClient


4/66


Prerequisites Workload capture is supported on the following versions:

A one-off patch is required to implement workload capture in versions earlierthan 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


5/66


Capture User The workload can be captured by SYS

Alternatively a new user can be created to manage the workload capture. Forexample:

CREATE USER ratuserIDENTIFIED BY ratuser

DEFAULT TABLESPACE SYSAUXTEMPORARY TABLESPACE TEMPACCOUNT UNLOCK;

GRANT MGMT_USER TO ratuser;GRANT EXECUTE_CATALOG_ROLE TO ratuserGRANT 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;


6/66


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$ chmodR 777 /home/oracle/capture1

sqlplus ratuser/ratuser

SQL> CREATE OR REPLACE DIRECTORY capture1AS /home/oracle/capture1;


7/66juliandyke.com7 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=*;



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



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 backbefore 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 anyworkload replay

Changes to system clock affect AWR snapshots



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 asSQL*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 localtransactions



Capture Filters By default all user sessions are recorded during workload capture

Workload filters can be configured to include or exclude sessions from thecapture

Include filters specify user sessions that will be captured. Can be used tocapture a subset of the workload

Exclude filters specify user sessions that will not be captured. Can beused to exclude sessions such as

Enterprise Manager Agent,

STATSPACK,

BMC Patrol

Quest Spotlight

Precise I3

etc



Workload Capture Directories and Files 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



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 capturereport in the cap directory

wcr_cr.html - Capture report HTML format wcr_cr.text - Capture report Text format


14/66


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


15/66


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


16/66


Generating a Capture Report Determine the capture ID from the capture name e.g.:

SELECT id FROM dba_workload_capturesWHERE name = CAPTURE1;

ID1

Generate the capture report e.g.:

SET SERVEROUTPUT ON

DECLAREl_clob CLOB

BEGINl_clob := dbms_workload_capture.report

(capture_id=>1,format => DBMS_WORKLOAD_CAPTURE.TYPE_TEXT

);DBMS_OUTPUT.PUT_LINE (l_clob);

END;/


17/66


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 andscheduled jobs


18/66


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:

BEGINdbms_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


19/66


AWR snapshots are required for subsequent reporting

Ensure that the AWR snapshots are exported before they exceed the AWRretention period and are automatically deleted

RAT Trap AWR Snapshots


20/66


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 atthe start of the capture

Recommendation

Enable flashback logging and testthe replay before performing a

recorded test


21/66


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//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 capturedatabase parameters


22/66


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;


23/66


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;


24/66


Replay Preprocessing Pre-process the capture for replay.

For example:

BEGINdbms_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 workloadcapture

Can be run multiple times on same capture directory

Must be run in same database version as replay

Can run multiple replays following execution ofPROCESS_CAPTURE


25/66


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 thefollowing 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


26/66


Replay Preprocessing To determine how long pre-processing will take use:

SELECT dbms_workload_replay.process_capture_remaining_timeFROM dual;

Returns an estimate of remaining capture time in minutes

An accurate estimate of remaining processing time cannot be determinedduring 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_completionFROM dual;

Returns percentage of capture files that have already been processed

Percentage is updated every 60 seconds


27/66


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


28/66


Workload Analyzer Syntax is

javaclasspath \$ORACLE_HOME/jdbc/lib/ojdbc5.jar:\$ORACLE_HOME/rdbms/jlib/dbrparser.jar:\$ORACLE_HOME/rdbms/jlib/dbranalyzer.jar \oracle.dbreplay.workload.checker/CaptureChecker

- operating system path of the capture directory

- 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


29/66


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


30/66


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


31/66


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:

BEGINdbms_workload_replay.initialize_replay(

replay_name => CAPTURE1_REPLAY1,replay_dir=> CAPTURE1

);END;

/

Known to populate the following base tables:

WRR$_REPLAYS

WRR$_CONNECTION_MAP


32/66


Remap Connections During capture, connection strings used to connect to production system are

captured

Connection strings must be remapped to replay system

SELECTEXEC dbms_workload_replay.remap_connection(||a.conn_id||,(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=))(CONNECT_DATA=(SID=))));

FROM dba_workload_connection_map a, dba_workload_replays bWHERE a.replay_id = b.idAND b.status = INITIALIZEDORDER BY a.conn_id;

Note that the TNS address is enclosed by a pair of twosingle quotes, NOT a pair of double quotes


33/66


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 againstproduction database


34/66


Prepare Replay Specify parameters for workload replay using the PREPARE_REPLAY

procedure

For example:

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 workloadcapture to time when session connects. Can be used to increase ordecrease concurrency. Default is 100%

EXECUTE dbms_workload_replay.prepare_replay (synchronization => TRUE);


35/66


Prepare Replay PREPARE_REPLAY parameters (continued):

THINK_TIME_SCALE Scales elapsed time between successive usercalls from same session. Default is 100%. If 0 then no delays betweenuser calls

THINK_TIME_AUTO_CORRECT Automatically correct think timebetween calls when user calls take longer to complete during replay thancapture

SCALE_UP_MULTIPLIER Defines number of times workload is scaledup during replay

Each captured session will be replayed number of times specified bythis 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 inparallel with workload replay

STS_CAP_INTERVAL specifies duration of SQL tuning set capture from

cursor cache in seconds


36/66


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


37/66


Synchronization

INSERT INTO t1000000 ROWS

SELECT FROM t1000000 ROWS

UPDATE t1000000 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

Sessio

ns


38/66


Synchronization

INSERT INTO t1000000 ROWS

SELECT FROM t0 ROWS

UPDATE t0 ROWS

DELETE FROM t

0 ROWS

A

B

C

D

14:30 14:45 15:00 15:15 15:30

Replay without synchronizationIn this example table t contains 1000000 rows at end of replay

Sessio

ns


39/66


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


40/66


Timing

A

B

C

D

BATCH

OLTP

OLTP

OLTP

14:30 14:45 15:00 15:15 15:30

Sessio

ns

Capture OLTP sessions run at 14:30 and complete by 14:45Batch runs at 15:00


41/66


Timing

A

B

C

D

BATCH

OLTP

OLTP

OLTP

14:30 14:45 15:00 15:15 15:30

Sessio

ns

Replay with connect time set to 0%Batch and OLTP all start at 14:30 may result in resource contention


42/66


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


43/66


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


44/66


Workload Replay Client - Calibration Run calibration to determine how many replay clients are required to support

the replay workload

For example:

wrc ratuser/ratuserMODE=calibrate REPLAYDIR=/home/oracle/capture1;

Advanced parameters are:

PROCESS_PER_CPU - Maximum number of client process than can berun per CPU (Default: 4)

THREADS_PER_PROCESS - Maximum number of threads than can be runwithin a client process (Default: 50)


45/66


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


46/66


Workload Replay Client - Replay To replay the workload specify the REPLAY mode

For example:

wrc ratuser/ratuserMODE=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 connectionsspecified 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 todatabase serially (one after another). Recommended for clients using

bequeath protocol. If FALSE (default) replay threads will connect todatabase concurrently

DSCN_OFF If TRUE then ignore all dependencies due to blockcontention during capture when synchronizing replay. If FALSE (default)honour all captured dependencies


47/66


Starting a Workload Replay After the replay clients have been started, the replay must be started on the

database

BEGINdbms_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


48/66


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 isreported in DBA_WORKLOAD_REPLAYS

SELECT replay_dir_numberFROM dba_workload_replaysWHERE id = 31;

REPLAY_DIR_NUMBER930632346

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


49/66


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;


50/66


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 AWRretention 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


51/66


Generating a Replay Report Identify the most recent replay ID

SELECT MAX(id) AS id FROM dba_workload_replaysWHERE status = 'COMPLETED';

ID2

Generate the replay report

SET SERVEROUTPUT ON

DECLAREl_clob CLOB

BEGINl_clob := dbms_workload_replay.report(

replay_id=>1,format => DBMS_WORKLOAD_REPLAY.TYPE_TEXT

);DBMS_OUTPUT.PUT_LINE (l_clob);

END;/


52/66


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 thereplay 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


53/66


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 systemaffect overall performance. Changes may include

Software Hardware

Parameters

Data Divergence occurs when results of DML or SQL queries do notmatch

For example a SELECT statement may return fewer rows duringreplay than during capture


54/66


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 theGET_DIVERGING_STATEMENT procedure in DBMS_WORKLOAD_REPLAY


55/66


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 bereported

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


56/66


Replay Analysis The following types of reports are available to analyse workload capture and

replay:

Capture Report

Replay Report

Compare Period Report

Compare SQLSET report


57/66


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 versiondoes 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_capturesWHERE name = CAPTURE31;

ID115


58/66


Importing a Capture AWR Import the AWR using the IMPORT_AWR procedure. For example:

SET SERVEROUTPUT ON

DECLAREl_dbid NUMBER;

BEGINl_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 importedAWR.


59/66


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 becompared using this report


60/66


Compare Period Reports For example:

SET PAGESIZE 0SET TRIMSPOOL ONSET LINESIZE 500SET FEEDBACK OFFSET LONG 1000000SET SERVEROUTPUT ON

VAR v_clob CLOB

BEGINdbms_workload_replay.compare_period_report(

replay_id1 => 31,replay_id2 => NULL,

format => DBMS_WORKLOAD_REPLAY.TYPE_HTML,result => :v_clob

);

END;/

PRINT v_clob


61/66


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 beforemaking a decision based on Database Replay outcomes


62/66


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 theworkload capture

a SQL tuning set from a workload replay with the SQL tuning set fromanother workload replay from the same workload capture


63/66


Compare SQLSET ReportSET PAGESIZE 0SET TRIMSPOOL ON

SET LINESIZE 500SET FEEDBACK OFFSET LONG 1000000

VAR v_clob CLOB

DECLAREl_result VARCHAR2(200);

BEGINl_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_clobSPOOL OFF

Compare replay withoriginal capture


64/66


Compare SQLSET ReportSET PAGESIZE 0SET TRIMSPOOL ON

SET LINESIZE 500SET FEEDBACK OFFSET LONG 1000000

VAR v_clob CLOB

DECLAREl_result VARCHAR2(200);

BEGINl_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_clobSPOOL OFF

Compare replay withanother replay


65/66


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 modificationsThink time modifications

Workload scaling


66/66

Acknowledgements This presentation would not have been possible without the

help of:

Andrew Ashworth (Ash)

Pete Taylor

Thank you

is rat worth catching

Documents