 |
|
Oracle Database replay tips
Oracle11g Tips by Burleson Consulting |
Question: I would
like an overview of using Oracle database
replay. Can you describe Oracle
database replay?
Answer: Oracle Database Replay is
the database upgrade companion tool. The Database Replay
enables users to perform real-time, real-world testing by
capturing actual database workloads on the production system
and replaying them on the clone database system. The test on
the clone database can be performed with production
characteristics including timing and transactions
concurrency.
The main feature of Database Replay is it provides analysis and
reporting to highlight potential problems such as slow completion of a
report on the new environment. The Oracle Database Replay has two
interfaces ? Oracle Enterprise Manager and Oracle PL/SQL supplied
packages. Below is the Oracle Database Replay workflow, which is
composed of three tasks:
?
Capture Workload. Captures
a workload from the production (source) environment. The capture is
done during normal business hours to create
a real-world
baseline.
?
Pre-Process Workload. This
task prepares the capture files for replay on the target system. The
workload capture files must be moved to the target system before the
next task, when the pre-process is done on the source system.
?
Replay Workload. This task
replays the pre-processed workload on the clone database.
Oracle Database Replay
Also see
dbms_workload_capture tips.
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.
By capturing and replaying a
representative SQL workload, you can verify many holistic settings,
before diving into the tuning of specific SQL statements. Full
workload tuning is essential because you want to tune as much SQL as
possible at the system level:
- 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
Inside the database replay utility
While Oracle Enterprise manager has
a GUI interface to capture and replay workloads, most experienced
DBA's choose to use the command-line interface provided by the
dbms_workload_capture package.
Dr. Tim Hall has
this great write-up on the Oracle 11g database replay features,
a component used with many Oracle 11g tools including the SQL
performance analyzer
(Inside the 11g
SQL Performance Advisor)
Dr. Hall
is the author of several popular Oracle books "Oracle
Job Scheduling" and "Oracle
PL/SQL Tuning Secrets".
Dr Hall
describes the installation process for the dbms_workload_capture
package:
The 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).
Dr. Hall notes the invocation 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;
/
Once the
workload is captured, Dr. Hall shows how the replay mechanism
operates.
Doing a
database workload replay
Dr. Hall
shows the usage of the dbms_workload_replay package, and how
it is invoked from the command line:
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 calibration step suggest a single client on a
single CPU is enough, so we only need to start a single replay
client, which is shown below.
$ 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)
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>
Ahmed Baraka has these notes on database replay.
Replaying the Workload
Typically, at this stage, you perform the changes
you want to undertake on the system. Then you start the replay
process. Replaying Workload is done by performing of the following
steps:
1. Restore the test database from the backup you
made in the production database. The target is to make the same
application become in the same state as it has been in the
production database.
2. It is recommended to set the time of the test
system to the time when the workload was captured on the production
system. This is to avoid any invalid time-based data or
job-scheduling issues.
3. Take steps to resolve, if any, external
references including: database links, external tables, directory
objects, and URLs.
4. Initialize the Replay Data: this process means
metadata will be read from Workload Capture files and loaded into
tables. Workload replay process will read from those tables when it
operates. Initializing the replay data is done by invoking the
procedure
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
REPLAY_NAME =>'1JAN_WORKLOAD',
REPLAY_DIR =>'REPLAY_DIR'); -- directory name should
always be in upper case.
END;
5. Remapping Connections: if any session in the
production database during the workload capturing used a
connection
to access an
external database, this connection should be remapped in the test
database so that it connects to the desired database.
To display the connection mapping information for a
workload replay, query the view
DBA_WORKLOAD_CONNECTION_MAP.
SELECT REPLAY_ID, CONN_ID, CAPTURE_CONN, REPLAY_CONN
FROM DBA_WORKLOAD_CONNECTION_MAP
To remap connection string in the test database to
the required connection strings, you use
REMAP_CONNECTION procedure.
BEGIN
DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (
CONNECTION_ID =>1, REPLAY_CONNECTION =>
'myprod:1521/mydb' );
END;
6. Preparing the Workload Replay: following are the
three options (set by parameters) that can be configured before you
start the replay process:
a. SYNCHRONIZATION:
(default true) Ensure the replay observes the commit sequence of the
capture. ie any work is run only after dependent commits in the
replay are completed. If you know that transactions in your workload
capture are independent, you can set this parameter to false.
b.
CONNECT_TIME_SCALE: (default
100) this parameter uses the elapsed time between the time when the
workload capture began and when sessions connect. You can use this
option to manipulate the session connect time during replay with a
given percentage value. The default value is 100, which will attempt
to connect all sessions as captured. Setting this parameter to 0
will attempt to connect all sessions immediately.
c. THINK_TIME_SCALE:
(default 100) think time is the elapsed time while the user waits
between issuing calls. To control replay speed, use the
THINK_TIME_SCALE parameter to scale user think time
during replay. If user calls are being executed slower during replay
than during capture, you can make the database replay attempt to
catch up by setting the THINK_TIME_AUTO_CORRECT
parameter to TRUE (the default). This will make the replay client
shorten the think time between calls, so that the overall elapsed
time of the replay will more closely match the captured elapsed
time.
7. Starting Replay Client(s): replay client
(represented by the executable
wrc)
controls the replay of the workload data. You may need to run more
wrc from in more than one host. This depends on the maximum number
of sessions that a single wrc thread can handle and the total number
of sessions captured by the workload capture.
For example, if the workload capture has data of 400
sessions and a single host can handle only 150 sessions, in this
case you need three hosts with
wrc
installed and run on each.
To know how many hosts and wrc clients you need to
operate for your workload capture, run the wrc in the
calibrate mode as shown below:
wrc system/<password> mode=calibrate replaydir=C:\Oracle\admin\ora11g\replay
Then, run wrc in
replay
mode (the default) on
the client host(s):
wrc system/<password> mode=replay replaydir=C:\Oracle\admin\ora11g\replay
8. Start the replay process using
START_REPLAY procedure (notice that
wrc client(s) were previously started):
exec DBMS_WORKLOAD_REPLAY.START_REPLAY();
If, for any reason, you want to cancel the replay
process before it finishes, use CANCEL_REPLAY
procedure.
For the workload replay, notice the following:
o While the
workload is replaying, you can query V$WORKLOAD_REPLAY_THREAD view to list information about all
sessions from the replay clients.
o You can
obtain information about the workload replays, after they finish, by
querying the view DBA_WORKLOAD_REPLAYS.
o After
workload replay finishes, all AWR snapshots related to the replay
time period is automatically exported. This can also be done
manually using EXPORT_AWR procedure.
o Exported snapshots can be
imported into the AWR schema owned by SYS user using
IMPORT_AWR procedure.
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

|
|
|
|
|