数据库重放

下面是执行数据库重放的典型步骤:

<script>t(void 0,'10')</script>

<script>t(0,1)</script>

1. <script>t(5,1)</script>

捕获数据库上的工作量。(任务 1)

<script>t(0,1)</script>

2. <script>t(5,1)</script>

(可选) 导出 AWR 数据。(任务 1)

<script>t(0,1)</script>

3. <script>t(5,1)</script>

还原测试系统上的重放数据库, 以匹配工作量捕获开始时的捕获数据库。

<script>t(0,1)</script>

4. <script>t(5,1)</script>

对测试系统进行必要的更改 (如执行升级)。

<script>t(0,1)</script>

5. <script>t(5,1)</script>

将工作量复制到测试系统。

<script>t(0,1)</script>

6. <script>t(5,1)</script>

预处理捕获的工作量。(任务 2)

<script>t(0,1)</script>

7. <script>t(5,1)</script>

配置测试系统以进行重放。

<script>t(0,1)</script>

8. <script>t(5,1)</script>

重放已还原的数据库上的工作量。(任务 3)

下面是执行数据库重放的典型步骤:

<script>t(void 0,'10')</script>

<script>t(0,1)</script>

1. <script>t(5,1)</script>

捕获数据库上的工作量。(任务 1)

<script>t(0,1)</script>

2. <script>t(5,1)</script>

(可选) 导出 AWR 数据。(任务 1)

<script>t(0,1)</script>

3. <script>t(5,1)</script>

还原测试系统上的重放数据库, 以匹配工作量捕获开始时的捕获数据库。

<script>t(0,1)</script>

4. <script>t(5,1)</script>

对测试系统进行必要的更改 (如执行升级)。

<script>t(0,1)</script>

5. <script>t(5,1)</script>

将工作量复制到测试系统。

<script>t(0,1)</script>

6. <script>t(5,1)</script>

预处理捕获的工作量。(任务 2)

<script>t(0,1)</script>

7. <script>t(5,1)</script>

配置测试系统以进行重放。

<script>t(0,1)</script>

8. <script>t(5,1)</script>

重放已还原的数据库上的工作量。(任务 3) I did it with em but I still did not know how to upload the scree shot.

see also:

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.