Inside Oracle 11g Database replay Oracle11g Tips by Burleson Consulting

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>

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

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.

To remap connection string in the test database to the required connection strings, you use REMAP_CONNECTION procedure.

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:

Then, run wrc in replay mode (the default) on the client host(s):

8. Start the replay process using START_REPLAY procedure (notice that wrc client(s) were previously started):

If, for any reason, you want to cancel the replay process before it finishes, use CANCEL_REPLAY procedure.

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.

For full details on workload-based tuning, see my new book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 30%-off.