/*
|| Oracle 11g Database Replay, Part 3: Code Examples
||
|| Demonstrates advanced Oracle 11g Database Replay features,
|| including:
|| - Creation of …
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11gR1
|| Database Replay (DBR), and they should be carefully proofread before being
|| executed against any existing Oracle database to avoid potential damage!
*/
/*
|| Listing 3.1:
|| Adding filters to a workload before its capture
*/
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER(
fname => 'ORACLE MANAGEMENT AGENT (DEFAULT)'
,fattribute => 'PROGRAM'
,fvalue => 'emagent%'
);
DBMS_WORKLOAD_CAPTURE.ADD_FILTER(
fname => 'ORACLE MANAGEMENT SERVICE (DEFAULT)'
,fattribute => 'PROGRAM'
,fvalue => 'OMS'
);
END;
/
-----
-- Listing 3.2:
-- Starting the capture of a database-wide workload. Note that for an Oracle
-- 10.2.0.4 DBR Workload Capture, the dynamic initialization parameter
-- PRE_11G_ENABLE_CAPTURE +must+ be set to true!
-----
ALTER SYSTEM SET PRE_11G_ENABLE_CAPTURE=TRUE;
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE(
name => 'DBR_CAPTURE_100'
,dir => 'DBRCONTROL'
,duration => NULL
,default_action => 'INCLUDE'
,auto_unrestrict => TRUE
);
END;
/
/*
|| Listing 3.3:
|| Stopping an active workload capture
*/
BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(
timeout => 90
,reason => 'Termination (after 90 seconds)'
);
END;
/
/*
|| Listing 3.4:
|| Printing the results of a successful DBR workload capture
*/
VARIABLE capture_report CLOB;
BEGIN
:capture_report :=
DBMS_WORKLOAD_CAPTURE.REPORT(
capture_id => 2
,format => DBMS_WORKLOAD_CAPTURE.TYPE_TEXT
);
END;
/
PRINT :capture_report
/*
|| Listing 3.5:
|| Preparing for DBR Workload Replay
*/
#####
# Create and start new RAC Service(s) via SRVCTL
#####
$> srvctl add service -d racdb -s TESTLBA -r racdb1,racdb2
$> srvctl start service -d racdb -s TESTLBA
-----
-- Set up Connection Load Balancing (CLB) and Load Balancing Advisor (LBA)
-- goals for TESTLBA Service so that it uses the Load Balancing Advisor for
-- the shortest connection times
-----
BEGIN
DBMS_SERVICE.MODIFY_SERVICE(
service_name => 'TESTLBA'
,goal => DBMS_SERVICE.GOAL_SERVICE_TIME
,clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT
,aq_ha_notifications => TRUE
);
END;
/
#####
# This network configuration information needs to be added on each node within
# file $ORACLE_HOME/network/admin/tnsnames.ora
#####
TESTLBA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = racnode2-vip)(PORT = 1521))
(LOAD_BALANCE = YES)
(FAILOVER = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTLBA)
)
)
/*
|| Listing 3.6:
|| Preprocessing ("massaging") the previously captured DBR Workload
*/
#####
# Create physical directories in each node's file system
#####
$> mkdir /home/oracle/DBRControl
$> ssh racnode2 "mkdir /home/oracle/DBRControl"
-----
-- Create a corresponding directory object in database RACDB. Note that this
-- directory object maps to either physical directory on nodes RACNODE1 and
-- RACNODE2.
-----
DROP DIRECTORY DBRControl;
CREATE DIRECTORY DBRControl AS '/home/oracle/DBRControl';
GRANT READ, WRITE ON DIRECTORY DBRControl TO PUBLIC;
-----
-- After all the files that represent the captured DBR workload have been
-- copied to the Database Replay workload in directory /home/oracle/DBRControl
-- on each node, prepare that workload for replay
-----
BEGIN
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(
capture_dir => 'DBRCONTROL'
);
END;
/
/*
|| Listing 3.7:
|| Setting up DBR Replay of the processed, captured workload
*/
-----
-- Place the database into INIT FOR REPLAY mode. This is a prerequisite to
-- executing procedure PREPARE_REPLAY
-----
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
replay_name => 'DBR_RPL_100'
,replay_dir => 'DBRCONTROL'
);
END;
/
-----
-- Remap connections made during the last Workload Replay to the appropriate
-- connection identifier (TESTLBA) in the RAC clustered database
-----
BEGIN
DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(
connection_id => 1
,replay_connection => '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=racnode2-vip)(PORT=1521))(LOAD_BALANCE=YES)(FAILOVER=YES)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTLBA)))'
);
END;
/
-----
-- View: DBA_WORKLOAD_CONNECTION_MAP
-- Purpose: Shows which connections have been captured and
-- how they have been remapped for replay (if applicable)
-----
TTITLE 'Currently Remapped Connections|(From DBA_WORKLOAD_CONNECTION_MAP)'
COL replay_id FORMAT 99999 HEADING 'Rply|ID'
COL conn_id FORMAT 99999 HEADING 'Conn|ID'
COL capture_conn FORMAT A60 HEADING 'Captured Connection String' WRAP
COL replay_conn FORMAT A60 HEADING 'Replay Connection String' WRAP
SELECT
replay_id
,conn_id
,capture_conn
,replay_conn
FROM dba_workload_connection_map
ORDER BY replay_id, conn_id
;
TTITLE OFF
/*
|| Listing 3.8:
|| Placing the target database into PREPARE FOR REPLAY mode (i.e., just before
|| starting up Workload Replay Client session(s))
*/
-----
-- Procedure: PREPARE_REPLAY
-- Places the database into PREPARE FOR REPLAY mode, setting
-- the following values for the impending replay of the workload:
-- 1.) SYNCHRONIZATION: TRUE (default) preserves the order of
-- transactions.
-- 2.) CONNECT_TIME_SCALE: 100 (default) means that the same number
-- of user connections will be created; setting this higher or
-- lower will create either proportionally more or fewer user
-- connections.
-- 3.) THINK_TIME_SCALE: 100 (default) means that the same delay
-- time between each user call within a session will be preserved;
-- setting this higher or lower will either increase or decrease
-- the amount of time between calls.
-- 4.) THINK_TIME_AUTO_CORRECT: TRUE (default) insures that DBR
-- automatically adjusts the overall execution time of the replay
-- so that it completes in the same amount of time as it originally
-- occurred.
-----
BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(
synchronization => TRUE
,connect_time_scale => 100
,think_time_scale => 100
,think_time_auto_correct => TRUE
);
END;
/
/*
|| Listing 3.9:
|| Starting the DBR Workload Replay
*/
BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY;
END;
/
/*
|| Listing 3.10:
|| Monitoring the progress of the DBR Workload Replay
*/
TTITLE 'Replayed Workloads|(From DBA_WORKLOAD_REPLAYS)'
COL id FORMAT 999 HEADING 'Rply|ID#'
COL capture_id FORMAT 999 HEADING 'Cptr|ID#'
COL name FORMAT A12 HEADING 'Replay|Operation'
COL status FORMAT A12 HEADING 'Status'
COL dbname FORMAT A12 HEADING 'Replayed|On Database'
COL dbversion FORMAT A10 HEADING 'Replay|DB Version'
COL directory FORMAT A16 HEADING 'Replay|Directory'
COL prepare_dtm FORMAT A11 HEADING 'Replay|Prepare|Time' WRAP
COL start_dtm FORMAT A11 HEADING 'Replay|Start|Time' WRAP
COL end_dtm FORMAT A11 HEADING 'Replay|End|Time' WRAP
COL duration_secs FORMAT 99999999 HEADING 'Replay|Time (s)'
COL user_calls FORMAT 99999 HEADING '# of|User|Calls'
COL num_clients FORMAT 99999 HEADING 'Total|Clnt|Sess'
COL num_clients_done FORMAT 99999 HEADING 'Clnt|Sess|Done'
SELECT
id
,name
,dbname
,dbversion
,directory
,capture_id
,user_calls
,status
,TO_CHAR(prepare_time,'yyyy-mm-dd hh24:mi:ss') prepare_dtm
,TO_CHAR(start_time,'yyyy-mm-dd hh24:mi:ss') start_dtm
,TO_CHAR(end_time,'yyyy-mm-dd hh24:mi:ss') end_dtm
,duration_secs
,num_clients
,num_clients_done
FROM dba_workload_replays
;
TTITLE OFF
/*
|| Listing 3.11:
|| Printing a summary DBR Workload Replay report
*/
VARIABLE replay_report CLOB;
VARIABLE capture_id NUMBER;
VARIABLE replay_id NUMBER;
BEGIN
:capture_id :=
DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(
dir => 'DBRCONTROL'
);
-- Get last replay for the captured workload
SELECT max(id)
INTO :replay_id
FROM dba_workload_replays
WHERE capture_id = :capture_id;
BEGIN
:replay_report :=
DBMS_WORKLOAD_REPLAY.REPORT(
replay_id => :replay_id
,format => 'TEXT'
);
END;
END;
/
SET LONGCHUNKSIZE 1000
SET LONG 30000000
SET LINESIZE 2000
SET PAGESIZE 0
PRINT :replay_report
/*
|| Listing 3.12:
|| Creating AWR Reports for multiple RAC instances after DBR Workload Replay
|| operations are completed
*/
DEFINE dbid = 646658104;
DEFINE db_name = 'RACDB';
DEFINE inst_name = 'RACDB1';
DEFINE inst_num = 1;
DEFINE num_days = 1;
DEFINE begin_snap = 70;
DEFINE end_snap = 71;
DEFINE report_type = 'TEXT';
DEFINE report_name = /home/oracle/DBR_RACDB1_AWR_70_71.log
@@?/rdbms/admin/awrrpti
DEFINE dbid = 646658104;
DEFINE db_name = 'RACDB';
DEFINE inst_name = 'RACDB2';
DEFINE inst_num = 2;
DEFINE num_days = 1;
DEFINE begin_snap = 70;
DEFINE end_snap = 71;
DEFINE report_type = 'TEXT';
DEFINE report_name = /home/oracle/DBR_RACDB2_AWR_70_71.log
@@?/rdbms/admin/awrrpti
/*
|| Listing 3.13:
|| Displaying DBR Workload metadata from other data dictionary views:
|| - DBA_WORKLOAD_CAPTURES
|| - DBA_WORKLOAD_REPLAY_DIVERGENCE
|| - V$WORKLOAD_REPLAY_THREAD
*/
TTITLE 'Captured Workloads|(From DBA_WORKLOAD_CAPTURES)'
COL id FORMAT 999 HEADING 'Cptr|ID#'
COL name FORMAT A16 HEADING 'Capture Name'
COL dbname FORMAT A12 HEADING 'DB Name'
COL dbversion FORMAT A10 HEADING 'Captured|DB Version'
COL lastprocver FORMAT A10 HEADING 'Last|Processed|DB Version'
COL directory FORMAT A16 HEADING 'Capture|Directory'
COL status FORMAT A10 HEADING 'Status'
COL start_dtm FORMAT A11 HEADING 'Start|Time' WRAP
COL end_dtm FORMAT A11 HEADING 'End|Time' WRAP
COL capture_size FORMAT 99999999 HEADING 'Capture|Size'
COL duration_secs FORMAT 99999999 HEADING 'Capture|Time (s)'
COL default_action FORMAT A12 HEADING 'Default|Action'
COL filters_used FORMAT 9999 HEADING '# of|Fltr|Used'
SELECT
id
,name
,dbname
,dbversion
,last_processed_version lastprocver
,directory
,status
,TO_CHAR(start_time,'yyyy-mm-dd hh24:mi:ss') start_dtm
,TO_CHAR(end_time,'yyyy-mm-dd hh24:mi:ss') end_dtm
,duration_secs
,capture_size
,default_action
,filters_used
FROM dba_workload_captures
;
TTITLE OFF
-----
-- View: DBA_WORKLOAD_REPLAY_DIVERGENCE
-- Purpose: Is used to monitor workload divergence
-----
TTITLE 'Workload Replay Divergence Statistics|(From DBA_WORKLOAD_REPLAY_DIVERGENCE)'
SELECT
replay_id
,timestamp
,divergence_type
,is_query_data_divergence
,is_dml_data_divergence
,is_error_divergence
,is_thread_failure
,expected_row_count
,observed_row_count
,expected_error#
,observed_error#
,stream_id
,call_counter
,sql_id
,session_id
,session_serial#
,service
,module
,action
FROM dba_workload_replay_divergence
;
TTITLE OFF
-----
-- View: V$WORKLOAD_REPLAY_THREAD
-- Purpose: Monitors the status of external replay clients
-----
TTITLE 'Current External Replay Clients Status|(From V$WORKLOAD_REPLAY_THREAD)'
COL wrc_id FORMAT 99999 HEADING 'WRC|ID'
COL sid FORMAT 99999 HEADING 'SessID'
COL serial# FORMAT 99999 HEADING 'Serial#'
COL logon_user FORMAT A8 HEADING 'User|Login'
COL logon_dtm FORMAT A11 HEADING 'Login|Time' WRAP
COL session_type FORMAT A10 HEADING 'Sess|Type'
COL action_type FORMAT 999 HEADING 'Action|Type'
COL file_name FORMAT A24 HEADING 'Replay File Name' WRAP
COL dbtime FORMAT 99999999 HEADING 'DB|Time'
COL network_time FORMAT 99999 HEADING 'Ntwrk|Time'
COL think_time FORMAT 9999999 HEADING 'Think|Time'
COL time_gain FORMAT 999999999 HEADING 'Time|Gain'
COL time_loss FORMAT 999999999 HEADING 'Time|Lost'
COL user_calls FORMAT 99999 HEADING 'User|Calls'
SELECT
wrc_id
,sid
,serial#
,logon_user
,TO_CHAR(logon_time,'yyyy-mm-dd hh24:mi:ss') logon_dtm
,session_type
,action_type
,file_name
,dbtime
,network_time
,think_time
,time_gain
,time_loss
,user_calls
FROM v$workload_replay_thread
ORDER BY wrc_id, sid
;
TTITLE OFF