Is RAT Worth Catching Julian Dyke Independent Consultant

Is RAT Worth Catching? Julian Dyke Independent Consultant © 2012 - Julian Dyke juliandyke. com

Introduction 2 u Real Application Testing (RAT) u Introduced in Oracle 11 g. R 1 u Also referred to as Database Replay u This presentation describes RAT in Oracle 11 g. R 2 u Separately licenced option. u Processor license * u Real Application Testing $11, 500 u Some comparative processor license * u Enterprise Edition $47, 500 u Real Application Clusters $23, 000 u Partitioning Option $11, 500 u * Source – Oracle Technology Global Price List – 08 Nov 12 © 2012 - Julian Dyke juliandyke. com

Database Replay Production Client Test Client Replay Client Application Tier Oracle Database Storage Workload Capture 3 Oracle Database Storage Workload Preprocessing © 2012 - Julian Dyke Storage Workload Replay Storage Analysis & Reporting juliandyke. com

Prerequisites u Workload capture is supported on the following versions: Version 4 One-off Patch 9. 2. 0. 8 9373986 10. 2. 0. 3 9373986 10. 2. 0. 4 10239989 10. 2. 0. 5 9373986 11. 2. 0. 1 9373986 u A one-off patch is required to implement workload capture in versions earlier than 11. 2. 0. 2 u Workload replay is supported in 11. 2. 0. 1 and above u Oracle 11. 2. 0. 2 requires one-off patch 11870615 © 2012 - Julian Dyke juliandyke. com

Capture User u The workload can be captured by SYS u 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; 5 © 2012 - Julian Dyke juliandyke. com

Capture Directory u Captured workload is stored in binary files in a directory tree below the capture directory u Capture will stop if capture directory is full u For RAC databases either: u Use a shared file system for workload capture u Use local file systems and subsequently merge contents u For example: $ mkdir /home/oracle/capture 1 $ chmod –R 777 /home/oracle/capture 1 sqlplus ratuser/ratuser SQL> CREATE OR REPLACE DIRECTORY capture 1 AS ‘/home/oracle/capture 1’; 6 © 2012 - Julian Dyke juliandyke. com

Capture Parameters u In Oracle 10. 2. 0. 5 and below, workload capture must be enabled using the PRE_11 G_ENABLE_CAPTURE parameter u In Oracle 10. 2. 0. 5 and below: u Workload capture can be enabled using : u $ORACLE_HOME/rdbms/admin/wrrenbl. sql ALTER SYSTEM SET pre_11 g_enable_capture=true SID=‘*’; u Workload capture can be disabled using: u $ORACLE_HOME/rdbms/admin/wrrdsbl. sql ALTER SYSTEM SET pre_11 g_enable_capture=false SID=‘*’; 7 u Workload capture is enabled by default in Oracle 11. 2. 0. 1 and above u The PRE_11 G_ENABLE_CAPTURE parameter is not supported u Workload capture can also be enabled using Enterprise Manager © 2012 - Julian Dyke juliandyke. com

Starting a Workload Capture u To start a workload capture use the START_CAPTURE procedure: DBMS_WORKLOAD_CAPTURE. START_CAPTURE ( name => ‘CAPTURE 1’, dir => ‘CAPTURE 1’, duration => 3600 ); u Duration is specified in seconds u 1 hour = 3600 seconds u 24 hours = 86400 seconds u While capture is enabled a temporary file is created in the capture directory. For example: u wcr_cap_0003 n. start The file contains 0 bytes and is deleted when the capture is disabled u u 8 © 2012 - Julian Dyke juliandyke. com

RAT Trap - Restart the Database 9 u Oracle recommends that the database is restarted immediately before any workload capture u Ensures that any on-going transactions are completed or rolled back before the capture begins u Reduces amount of divergence u Start workload capture immediately after database restart u Restart database in RESTRICTED mode using STARTUP RESTRICT u Login as SYS and start workload capture u Instance will automatically switch to UNRESTRICTED mode u Experience suggests that the database should be also restarted before any workload replay u Changes to system clock affect AWR snapshots © 2012 - Julian Dyke juliandyke. com

RAT Trap – Capture Restrictions u The following types of client request are known restrictions for workload capture u u u u 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 u By default all user sessions are recorded during workload capture u Workload filters can be configured to include or exclude sessions from the capture u Include filters specify user sessions that will be captured. Can be used to capture a subset of the workload u Exclude filters specify user sessions that will not be captured. Can be used to exclude sessions such as u Enterprise Manager Agent, u STATSPACK, u BMC Patrol u Quest Spotlight u Precise I 3 u etc © 2012 - Julian Dyke juliandyke. com

Workload Capture Directories and Files 12 u In Oracle 11. 2 and above START_CAPTURE creates subdirectories in capture directory u capfiles u inst 1 u aa to aj u Each session stores data in a record file e. g. wcr_czq 45 h 0000005. rec u Compressed file u XML-like format with elements and attributes u All SQL calls including u statement text u bind variables u execution time u rows returned u error code u Flushed periodically © 2012 - Julian Dyke juliandyke. com

Workload Capture Files 13 u START_CAPTURE generates the following workload metadata (WMD) files automatically in the cap directory u wcr_scapture. wmd - Start capture details u wcr_fcapture. wmd - Finish capture details u In Oracle 11. 2 and above START_CAPTURE automatically generates a capture report in the cap directory u wcr_cr. html - Capture report – HTML format u wcr_cr. text - Capture report – Text format © 2012 - Julian Dyke juliandyke. com

RAT Trap – Minimum CPU time u u 14 A replay requires a minimum of 5 minutes captured CPU time u CPU time NOT Elapsed Time May be a problem for workloads that are: u I/O-bound u Network intensive u Application-bound u Workload capture period must be long enough to capture five minutes CPU u Can use SLEEP functions to artificially capture CPU u Does not work with PL/SQL DBMS_LOCK. SLEEP function u Works with JServer Java sleep class u Works with External C class © 2012 - Julian Dyke juliandyke. com

RAT Trap – Finishing Capture u Capture files are written using buffered I/O u Advantages are: u Lower overhead u Lower impact on workload u Disadvantages are: u Buffers are not flushed automatically u SQL statements can be “lost” u When capture ends or FINISH_CAPTURE is executed: u Timeout period is specified u Sessions will be informed during next database call u If sessions make a database call within timeout period u Capture file buffer will be flushed Otherwise contents will be lost u 15 © 2012 - Julian Dyke juliandyke. com

Generating a Capture Report u Determine the capture ID from the capture name e. g. : SELECT id FROM dba_workload_captures WHERE name = ‘CAPTURE 1’; ID 1 u 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 u The capture report format can be: u DBMS_WORKLOAD_CAPTURE. TYPE_TEXT u DBMS_WORKLOAD_CAPTURE. TYPE_HTML u The capture report includes the following: u Details of workload capture including name, filters, date, time and SCN u Overall statistics including total DB time, number of logins, number of transactions u Profile of captured workload u Profile of workload not captured due to version limitations u Profile of uncaptured workload that was excluded by defined filters u Profile of uncaptured workload consisting of background process and scheduled jobs © 2012 - Julian Dyke juliandyke. com

Exporting the AWR u An export of the AWR is required for subsequent analysis on the replay system u To export the AWR use the EXPORT_AWR procedure. For example: BEGIN dbms_workload_capture. export_awr (capture_id=>1); END; / u 18 This creates the following files in the capture directory: u wcr_ca. dmp – Data Pump export u wcr_ca. log – Data Pump log file u wcr_cap_uc_graph. extb – User calls graph © 2012 - Julian Dyke juliandyke. com

RAT Trap – AWR Snapshots 19 u AWR snapshots are required for subsequent reporting u 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 u A strategy is required to restore a pre-capture copy of the database to the replay system u One of the following methods can be used: u RMAN backup and restore u RMAN DUPLICATE command u Snapshot standby u Data Pump Import and Export u SAN replication u 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 u Restore pre-capture database to new 10. 2. 0. 4 home ALTER DATABASE OPEN RESETLOGS; u Modify archive log destination ALTER SYSTEM SET log_archive_dest_1 = ‘LOCATION=/11/oradata/<DUP_DB>/arch MANDATORY REOPEN=300’; u Run standard 11. 2. 0. 3 upgrade u 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; u 21 Ensure replay database parameters have equivalent values to capture database parameters © 2012 - Julian Dyke juliandyke. com

Replay User u Replay can be performed by SYS user u 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 u The contents of the capture directory should be copied across to the replay server. u It will be necessary to recreate the Oracle directory object: $ sqlplus ratuser/ratuser SQL> CREATE OR REPLACE DIRECTORY capture 1 AS ‘/home/oracle/capture 1’; 23 © 2012 - Julian Dyke juliandyke. com

Replay Preprocessing u Pre-process the capture for replay. u For example: BEGIN dbms_workload_replay. process_capture ( capture_dir => ‘CAPTURE 1’ ); END; / u u 24 Analyses the workload capture found in the CAPTURE_DIR Creates workload replay metadata files required to replay the given workload capture u Can be run multiple times on same capture directory u Must be run in same database version as replay u Can run multiple replays following execution of PROCESS_CAPTURE © 2012 - Julian Dyke juliandyke. com

Replay Pre-processing u 25 In Oracle 11. 2. 0. 3 the PROCESS_CAPTURE procedure creates a new subdirectory called pp 11. 2. 0. 3. 0 in the capture directory containing the following files: u wcr_calibrate. html u wcr_commits. extb u wcr_conn_data. extb u wcr_dep_graph. extb u wcr_login. pp u wcr_process. wmd u wcr_references. extb u wcr_scn_order. extb u wcr_seq_data. extb © 2012 - Julian Dyke juliandyke. com

Replay Preprocessing u To determine how long pre-processing will take use: SELECT dbms_workload_replay. process_capture_remaining_time FROM dual; u Returns an estimate of remaining capture time in minutes u An accurate estimate of remaining processing time cannot be determined during the first minute u Function returns NULL if invoked within first minute of capture preprocessing u To determine what percentage of capture pre-processing is complete use: SELECT dbms_workload_replay. process_capture_completion FROM dual; u u 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 u Workload Analyzer u Available in 11. 2. 0. 2 and above u Java program that analyses a workload capture directory u Identifies parts of captured workload that may not replay accurately e. g. u Insufficient data u Errors occurring during workload capture u Usage features not supported by Database Replay u Results are stored in capture directory in the following files: u wcr_cap_analysis. html u wcr_cap_analysis. xml u Executed automatically by Enterprise Manager during capture pre-processing u Must be invoked manually otherwise u Requires : u $ORACLE_HOME/jdbc/lib/dbranalyzer. jar u $ORACLE_HOME/jdbc/lib/dbrparser. jar u Java 1. 5 or above © 2012 - Julian Dyke juliandyke. com

Workload Analyzer u Syntax is java –classpath $ORACLE_HOME/jdbc/lib/ojdbc 5. jar: $ORACLE_HOME/rdbms/jlib/dbrparser. jar: $ORACLE_HOME/rdbms/jlib/dbranalyzer. jar oracle. dbreplay. workload. checker/Capture. Checker <capture_directory> <connection_string> u u u In Oracle 11. 2. 0. 3 this utility only works with JDBC thin client u JDBC thick client (OCI) fails Tested successfully with Java 1. 6 and ojdbc 6. jar u Analyzer will prompt for username/password of database user e. g. ratuser u 28 <capture_directory> - operating system path of the capture directory <connection_string> - connection string of Oracle database (11. 1 and above) u e. g. jdbc: oracle: thin: @myhost. mycompany. com: 1521: orcl © 2012 - Julian Dyke juliandyke. com

RAT Trap - System Clock 29 u Reset the system clock on the replay system to the start time of the capture u Ensure NTP daemon is disabled on replay server u Restart all database instances after resetting the system clock u 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 u Reconfigure references to external systems to avoid impact on production systems during replay u References that should be resolved include: u Database Links u External Tables u Directory Objects u URLs u E-mail servers © 2012 - Julian Dyke juliandyke. com

Initialize Replay u After workload capture has been pre-processed, replay data can be initialized u Loads necessary metadata required by workload replay into tables u captured connection strings are loaded into a table where they can be remapped for replay u For example: BEGIN dbms_workload_replay. initialize_replay ( replay_name => ‘CAPTURE 1_REPLAY 1’, replay_dir => ‘CAPTURE 1’ ); END; / u 31 Known to populate the following base tables: u WRR$_REPLAYS u WRR$_CONNECTION_MAP © 2012 - Julian Dyke juliandyke. com

Remap Connections u During capture, connection strings used to connect to production system are captured u 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 u Ensure that connections are remapped from the capture database to the replay database u If connections are not remapped, workload may be replayed against production database © 2012 - Julian Dyke juliandyke. com

Prepare Replay u Specify parameters for workload replay using the PREPARE_REPLAY procedure u For example: EXECUTE dbms_workload_replay. prepare_replay (synchronization => TRUE); u PREPARE_REPLAY parameters include: u SYNCHRONIZATION – Determines how replay will be synchronized. Options are: u SCN – COMMIT order in captured workload is preserved during replay u OBJECT_ID – COMMIT order is maintained for relevant objects only. Relevant objects are objects referenced within current action u OFF – replay is unsynchronized u 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 u 35 PREPARE_REPLAY parameters (continued): u THINK_TIME_SCALE – Scales elapsed time between successive user calls from same session. Default is 100%. If 0 then no delays between user calls u THINK_TIME_AUTO_CORRECT – Automatically correct think time between calls when user calls take longer to complete during replay than capture u SCALE_UP_MULTIPLIER – Defines number of times workload is scaled up during replay u Each captured session will be replayed number of times specified by this parameter u Only one session in each set will execute both queries and updates u Remaining sessions in set will only execute queries u CAPTURE_STS – specifies whether or not to capture a SQL tuning set in parallel with workload replay u STS_CAP_INTERVAL – specifies duration of SQL tuning set capture from cursor cache in seconds © 2012 - Julian Dyke juliandyke. com

RAT Trap - Synchronization 36 u Most workloads require full synchronization u Failure to synchronize will increase amount of divergence u Potentially invalidates results u 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 u Changing connect times and/or think times u Can introduce or alleviate contention u May not be representative of workload u No granularity u Connect / think times must be changed for entire workload u For example cannot distinguish between OLTP and batch within workload u Beware of u Time dependent workload e. g. feeds from other systems u External schedulers e. g. Control M u 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 u RAT offers limited scaling of workloads u For a specific session u SELECT statements can be scaled to multiple sessions u DML statements are only executed in one session u Therefore scaling can only be considered appropriate for read-only workloads u For all other workloads, u Further analysis will be required u Scaling may not be appropriate © 2012 - Julian Dyke juliandyke. com

Workload Replay Client 43 u Replay client is a multi-threaded program called wrc located in $ORACLE_HOME/bin u Included in Oracle 11. 2. 0. 2 and above client u Each thread submits a workload from a captured session u Database will wait for replay clients to connect before replay begins u Replay clients should run on separate hardware to database u Replay clients must have access to replay directory u Replay directory should contain pre-processed workload capture u Replay directory should be in different file system to database files u Replay client must specify username/password u user must have DBA role u user cannot be SYS © 2012 - Julian Dyke juliandyke. com

Workload Replay Client - Calibration u Run calibration to determine how many replay clients are required to support the replay workload u For example: wrc ratuser/ratuser MODE=calibrate REPLAYDIR=‘/home/oracle/capture 1’; u 44 Advanced parameters are: u PROCESS_PER_CPU - Maximum number of client process than can be run per CPU (Default: 4) u 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 u 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/capture 31 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 u To replay the workload specify the REPLAY mode u For example: wrc ratuser/ratuser MODE=replay REPLAYDIR=‘/home/oracle/capture 1’ u 46 Options include: u WORKDIR – directory for trace files u DEBUG – ON or OFF (Default : OFF) u 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 u 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 u 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 u After the replay clients have been started, the replay must be started on the database BEGIN dbms_workload_replay. start_replay; END; 47 u The START_REPLAY procedure does not take any arguments u When executed within SQL*Plus, u session returns to the prompt after replay has been started u replay continues to execute in background © 2012 - Julian Dyke juliandyke. com

Workload Replay Directories u u The replay creates a new replay directory within the capture directory For example if the capture directory is /home/oracle/rat/capture 31: u u /home/oracle/rat/capture 31/rep 930632346 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 u The workload replay creates several files within the replay directory: u wcr_replay. wmd - Replay workload metadata wcr_rep_uc_graph_930632346. extb - Replay user calls graph wcr_rr_930632346. xml - Replay report – XML format u u 48 © 2012 - Julian Dyke juliandyke. com

Managing a Workload Replay u To pause a workload replay use: EXECUTE dbms_workload_replay. pause_replay; u To resume a paused workload replay use: EXECUTE dbms_workload_replay. resume_replay; u To check if a replay is paused use: SELECT dbms_workload_replay. is_replay_paused FROM dual; u To cancel a workload replay use: EXECUTE dbms_workload_replay. cancel_replay; 49 © 2012 - Julian Dyke juliandyke. com

Exporting AWR Data u AWR data u can be exported to provide detailed workload analysis u is also required for AWR Compare Period report u To export AWR use the EXPORT_AWR procedure e. g: EXECUTE dbms_workload_replay. export_awr (replay_id=>107); / u Replay AWR data is exported into the replay directory, for example: u /home/oracle/rat/capture 31/rep 930632346 u The export files include the REPLAY_DIR_NUMBER: u wcr_ra_930632346. dmp u 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 u Identify the most recent replay ID SELECT MAX(id) AS id FROM dba_workload_replays WHERE status = 'COMPLETED'; ID 2 u 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 u The replay report format can be: u DBMS_WORKLOAD_REPLAY. TYPE_TEXT u DBMS_WORKLOAD_REPLAY. TYPE_HTML u DBMS_WORKLOAD_REPLAY. TYPE_XML u The AWR snapshots must still exist in the replay database to generate the replay report u The replay report includes the following: u Details of workload replay including name, filters, date and time u Replay options and number of replay clients started u Overall statistics about the replay and capture including total DB time, number of logins, number of transactions u Profile of replayed workload u Replay divergence u Error divergence u DML and SQL query data divergence © 2012 - Julian Dyke juliandyke. com

Divergence 53 u When a workload is replayed there can be divergence between the capture and the replay u There are two types of divergence u Performance Divergence – occurs when changes on the replay system affect overall performance. Changes may include u Software u Hardware u Parameters u Data Divergence – occurs when results of DML or SQL queries do not match u For example a SELECT statement may return fewer rows during replay than during capture © 2012 - Julian Dyke juliandyke. com

Listing Divergences 54 u Divergences are reported in DBA_WORKLOAD_REPLAY_DIVERGENCE u Each divergence is identified by u REPLAY_ID u STREAM_ID u CALL_COUNTER u 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 u Anonymous PL/SQL blocks are captured u PL/SQL calls within those blocks are not captured u SQL statement calls within those blocks are not captured u Only errors returned by PL/SQL block exceptions are recorded u If recursive exceptions are caught and handled internally these will not be reported u If PL/SQL logic is different, this may not be captured u Replay may be affected by u Changes in supplied PL/SQL packages u Changes in application PL/SQL packages © 2012 - Julian Dyke juliandyke. com

Replay Analysis u 56 The following types of reports are available to analyse workload capture and replay: u Capture Report u Replay Report u Compare Period Report u Compare SQLSET report © 2012 - Julian Dyke juliandyke. com

Importing a Capture AWR u Before running the compare reports, it is necessary to import the capture AWR u The workload analyzer attempts to import the capture AWR, but this version does not appear to be usable u The capture AWR should be imported into a new schema. u In this example the new schema is called CAPTURE 31 u RESOURCE role appears to be necessary and sufficient CREATE USER capture 31 IDENTIFIED BY capture 31; GRANT RESOURCE TO capture 31; u Identify the ID of the capture workload SELECT id FROM dba_workload_captures WHERE name = ‘CAPTURE 31’; ID 115 57 © 2012 - Julian Dyke juliandyke. com

Importing a Capture AWR u 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 => 'CAPTURE 31‘ ); dbms_output. put_line (‘DBID = '||l_dbid); END; / DBID = 2128266044 u 58 The IMPORT_AWR function returns the new DBID assigned to the imported AWR. © 2012 - Julian Dyke juliandyke. com

Compare Period Reports 59 u Compare Period reports allow comparison of u Workload replay with workload capture u Workload replay with another workload replay from same capture u 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 u 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_id 1 => 31, replay_id 2 => NULL, format => DBMS_WORKLOAD_REPLAY. TYPE_HTML, result => : v_clob ); END; / 60 PRINT v_clob © 2012 - Julian Dyke juliandyke. com

RAT Trap – DB Time 61 u DB time is the best and possibly only metric to compare captures with replays u Other metrics can be used to compare replays with each other u DB time may be affected by u Changes in hardware e. g. u SSD , u 10 Gb. E networks u faster CPU u more memory u Changes in Oracle version u Additional functionality u Longer code paths u Background workload u Divergence u 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 u SQL Performance Analyzer (SPA) reports can be generated using the DBMS_WORKLOAD_REPLAY package u The SPA can be used to compare u a SQL tuning set from a workload replay with the SQL tuning set from the workload capture u 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 VARCHAR 2(200); BEGIN l_result : = dbms_workload_replay. compare_sqlset_report ( Compare replay with replay_id 1 => 44, original capture replay_id 2 => NULL, format => DBMS_WORKLOAD_REPLAY. TYPE_HTML, result => : v_clob ); END; / 63 PRINT v_clob SPOOL OFF © 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 VARCHAR 2(200); BEGIN l_result : = dbms_workload_replay. compare_sqlset_report ( Compare replay with replay_id 1 => 44, another replay_id 2 => 42, format => DBMS_WORKLOAD_REPLAY. TYPE_HTML, result => : v_clob ); END; / 64 PRINT v_clob SPOOL OFF © 2012 - Julian Dyke juliandyke. com

Conclusions u 65 RAT can be very good at capturing and replaying workloads u Requires a lot of user discipline u Follow the capture and replay recommendations u Unsupported workload features can increase divergence u For best results avoid using “enhancements” such as u Object synchronization u Connect time modifications u Think time modifications u Workload scaling © 2012 - Julian Dyke juliandyke. com

Acknowledgements u This presentation would not have been possible without the help of: u u u 66 Andrew Ashworth (Ash) Pete Taylor Thank you © 2012 - Julian Dyke juliandyke. com