Database Replay - Oracle 11g New Feature
The Oracle 11g database replay features is an important move toward the use of real-world empirical workloads to remove the guesswork from Oracle tuning.
* Optimize/test initialization parameter
* Optimize/test changes to CBO statistics and histograms
* Test changes to I/O subsystem (RAID, ASM, &c)
* Test the effect of release upgrades on workload performance
#ReplayThe DBMS_WORKLOAD_CAPTURE package provides a set of procedures and functions to control the capture process. Before we can initiate the capture process we need an empty directory on the "prod-11g" database server to hold the capture logs.
mkdir /u01/app/oracle/db_replay_capture
#Next, we create a directory object pointing to the new directory.
CONN sys/password@prod AS SYSDBA
CREATE OR REPLACE DIRECTORY db_replay_capture_dir
AS '/u01/app/oracle/db_replay_capture/';
#-- Make sure existing processes are complete.
SHUTDOWN IMMEDIATE
STARTUP
#Once installed, you can invoke the dbms_workload_capture.start_capture and dbms_workload_capture.finish_capture procedures to capture a SQL tuning set (a representative workload of current SQL).
#syntax here:
BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (name => 'test_capture_1',
dir => 'DB_REPLAY_CAPTURE_DIR',
duration => NULL);
END;
/
#Once the work is complete we can stop the capture using the FINISH_CAPTURE procedure.
CONN sys/password@prod AS SYSDBA
BEGIN
DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
/
#Database workload replay
#Before we can start the replay, we need to calibrate and start a replay client using the "wrc" utility. The calibration step tells us the number of replay clients and hosts necessary to faithfully replay the workload.
$ wrc mode=calibrate replaydir=/u01/app/oracle/db_replay_capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Tue Oct 30 09:33:42 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Report for Workload in: /u01/app/oracle/db_replay_capture
-----------------------
Recommendation:
Consider using at least 1 clients divided among 1 CPU(s).
Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 3
Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE
$
#The replay client pauses waiting for replay to start. We initiate replay with the following command.
BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/
#If you need to stop the replay before it is complete, call the CANCEL_REPLAY procedure.
#The output from the replay client includes the start and finish time of the replay operation.
$ wrc system/password@test mode=replay replaydir=/u01/app/oracle/db_replay_capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Tue Oct 30 09:34:14 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (09:34:14)
Replay started (09:34:44)
Replay finished (09:39:15)
$
#Once complete, we can see the DB_REPLAY_TEST_TAB table has been created and populated in the DB_REPLAY_TEST schema.
SQL> CONN sys/password@test AS SYSDBA
Connected.
SQL> SELECT table_name FROM dba_tables WHERE owner = 'DB_REPLAY_TEST';
TABLE_NAME
------------------------------
DB_REPLAY_TEST_TAB
SQL> SELECT COUNT(*) FROM db_replay_test.db_replay_test_tab;
COUNT(*)
----------
500000
SQL>
