Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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.

Fig 7-REPLAY.jpg
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

         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.

 
 
 
Get Complete Oracle Tuning Details 

The landmark book "Oracle Tuning: The Definitive Reference Third Edition" has been updated with over 800 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle performance and you can buy it for 40% off directly from the publisher.
 


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.