11g Real Application Testing:Database Replay使用方法

CAPTURE_NAME=> 本次capture的名字

可以通过 DBA_WORKLOAD_CAPTURES 视图监控

DIRECTORY_NAME=> ORACLE目录对象名，该目录用以存放catpure文件，现有测试表明在繁忙的OTLP数据中收集10分钟数据消耗磁盘空间1GB，建议为该目录分配足够的磁盘空间

set pagesize 0 long 30000000 longchunksize 1000

select dbms_workload_capture.report(&ID,’TEXT’) from dual;

其中ID是查询1获得的ID值

注意 Capture Preprocess By Process_capture Can Not Complete [ID 1265686.1]

Bug 9742032  Database replay: dbms_workload_replay.process_capture takes a lot of time

— ***********************************************************

—  PROCESS_CAPTURE

—    Processes the workload capture found in capture_dir in place.

—

—    Analyzes the workload capture found in the capture_dir and

—    creates new workload replay specific metadata files that are

—    required to replay the given workload capture.

—    This procedure can be run multiple times on the same

—    capture directory – useful when this procedure encounters

—    unexpected errors or is cancelled by the user.

—

—    Once this procedure runs successfully, the capture_dir can be used

—    as input to INITIALIZE_REPLAY() in order to replay the captured

—    workload present in capture_dir.

—

—    Before a workload capture can be replayed in a particular database

—    version, the capture needs to be “processed” using this

—    PROCESS_CAPTURE procedure in that same database version.

—    Once created, a processed workload capture can be used to replay

—    the captured workload multiple times in the same database version.

—

—    For example:

—      Say workload “foo” was captured in “rec_dir” in Oracle

—      database version 10.2.0.4

—

—      In order to replay the workload “foo” in version 11.1.0.1

—      the workload needs to be processed in version 11.1.0.1

—      The following procedure needs to be executed in a 11.1.0.1 database

—      in order to process the capture directory “rec_dir”

—

—        DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(‘rec_dir’);

—

—      Now, rec_dir contains a valid 11.1.0.1 processed workload capture

—      that can be used to replay the workload “foo” in 11.1.0.1 databases

—      as many number of times as required.

—

—    The procedure will take as input the following parameters:

—      capture_dir – name of the workload capture directory object.

—                    (case sensitive)

—                    The directory object must point to a valid OS

—                    directory that has appropriate permissions.

—                    New files will be added to this directory.

—                    (MANDATORY)

—      parallel_level – number of oracle processes used to process the

—                       capture in a parallel fashion.

—                       The NULL default value will auto-compute the

—                       parallelism level, whereas a value of 1 will enforce

—                       serial execution.

Recommendation:

Consider using at least 75 clients divided among 19 CPU(s)

You will need at least 142 MB of memory per client process.

If your machine(s) cannot match that number, consider using more clients.

Workload Characteristics:

– max concurrency: 2830 sessions

– total number of sessions: 70362

Assumptions:

– 1 client process per 50 concurrent sessions

– 4 client process per CPU

– 256 KB of memory cache per concurrent session

– think time scale = 100

– connect time scale = 100

– synchronization = TRUE

alter database open resetlogs;

startup;               ==>启动另一个实例

create user orasupport identified by oracle;

grant dba to orasupport;

exec DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(synchronization=>false,

connect_time_scale=>80,think_time_scale=>25, SCALE_UP_MULTIPLIER=>1);

synchronization=》指定了commit order是否要求一致 ， 对于压力测试可以为false，

connect_time_scale=》连接时间比例，设置为80%，意为原本要capture 10分钟之后才会登录的session，现在8分钟后就会登录 ，注意设置该值过低会导致大量session登录，可能出现ORA-18/ORA-20错误； 这里设置为80为了加大负载压力

think_time_scale=》指2个SQL CALL之间间隔的时间比例，如本来2个SQL之间为100s，设置think_time_scale为25后，其间隔变为25s。

SCALE_UP_MULTIPLIER=>指定查询的倍数，建议一开始设置为1:1,

— ***********************************************************

—  PREPARE_REPLAY

—    Puts the DB state in REPLAY mode. The database

—    should have been initialized for replay using

—    DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(), and optionally any

—    capture time connection strings that require remapping have been

—    already done using DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION().

—

—    One or more external replay clients (WRC) can be started

—    once the PREPARE_REPLAY procedure has been executed.

—

—    The procedure will take as input the following parameters:

—      synchronization – Turns synchronization to the given scheme during

—                        workload replay.

—                        When synchronization is SCN, the COMMIT order

—                        observed during the original workload capture

—                        will be preserved during replay.

—                        Every action that is replayed will be executed

—                        ONLY AFTER all of it’s dependent COMMITs (all

—                        COMMITs that were issued before the given action

—                        in the original workload capture) have finished

—                        execution.

—                        When synchronization is OBJECT_ID, a more advanced

—                        synchronization scheme is used.

—                        Every action that is replayed will be executed

—                        ONLY AFTER the RELEVANT COMMITs have finished

—                        executing. The relevant commits are the ones that

—                        were issued before the given action  in the

—                        orginal workload capture and that had modified

—                        at least one of the database objects the given

—                        action is referencing (either implicitely or

—                        explicitely).

—                        This OBJECT_ID scheme has the same logical

—                        property of making sure that any action will see

—                        the same data it saw during capture, but will

—                        allow more concurrency during replays for the

—                        actions that do not touch the same objects/tables.

—                        DEFAULT VALUE: SCN, preserve commit order.

—                        For legacy reason, there is a boolean version of

—                        this procedure:

—                          TRUE  means ‘SCN’

—                          FALSE means ‘OFF’

—

—      connect_time_scale       – Scales the time elapsed between the

—                                 instant the workload capture was started

—                                 and session connects with the given value.

—                                 The input is interpreted as a % value.

—                                 Can potentially be used to increase or

—                                 decrease the number of concurrent

—                                 users during the workload replay.

—                                 DEFAULT VALUE: 100

—

—                                 For example, if the following was observed

—                                 during the original workload capture:

—                                 12:00 : Capture was started

—                                 12:10 : First session connect  (10m after)

—                                 12:30 : Second session connect (30m after)

—                                 12:42 : Third session connect  (42m after)

—

—                                 If the connect_time_scale is 50, then the

—                                 session connects will happen as follows:

—                                 12:00 : Replay was started

—                                         with 50% connect time scale

—                                 12:05 : First session connect  ( 5m after)

—                                 12:15 : Second session connect (15m after)

—                                 12:21 : Third session connect  (21m after)

—

—                                 If the connect_time_scale is 200, then the

—                                 session connects will happen as follows:

—                                 12:00 : Replay was started

—                                         with 200% connect time scale

—                                 12:20 : First session connect  (20m after)

—                                 13:00 : Second session connect (60m after)

—                                 13:24 : Third session connect  (84m after)

—

—      think_time_scale         – Scales the time elapsed between two

—                                 successive user calls from the same

—                                 session.

—                                 The input is interpreted as a % value.

—                                 Can potentially be used to increase or

—                                 decrease the number of concurrent

—                                 users during the workload replay.

—                                 DEFAULT VALUE: 100

—

—                                 For example, if the following was observed

—                                 during the original workload capture:

—                                 12:00 : User SCOTT connects

—                                 12:10 : First user call issued

—                                         (10m after completion of prevcall)

—                                 12:14 : First user call completes in 4mins

—                                 12:30 : Second user call issued

—                                         (16m after completion of prevcall)

—                                 12:40 : Second user call completes in 10m

—                                 12:42 : Third user call issued

—                                         ( 2m after completion of prevcall)

—                                 12:50 : Third user call completes in 8m

—

—                                 If the think_time_scale is 50 during the

—                                 workload replay, then the user calls

—                                 will look something like below:

—                                 12:00 : User SCOTT connects

—                                 12:05 : First user call issued 5 mins

—                                         (50% of 10m) after the completion

—                                         of prev call

—                                 12:10 : First user call completes in 5m

—                                         (takes a minute longer)

—                                 12:18 : Second user call issued 8 mins

—                                         (50% of 16m) after the completion

—                                         of prev call

—                                 12:25 : Second user call completes in 7m

—                                         (takes 3 minutes less)

—                                 12:26 : Third user call issued 1 min

—                                         (50% of 2m) after the completion

—                                         of prev call

—                                 12:35 : Third user call completes in 9m

—                                         (takes a minute longer)

—

—      think_time_auto_correct  – Auto corrects the think time between calls

—                                 appropriately when user calls takes longer

—                                 time to complete during replay than

—                                 how long the same user call took to

—                                 complete during the original capture.

—                                 DEFAULT VALUE: TRUE, reduce

—                                 think time if replay goes slower

—                                 than capture.

—

—                                 For example, if the following was observed

—                                 during the original workload capture:

—                                 12:00 : User SCOTT connects

—                                 12:10 : First user call issued

—                                         (10m after completion of prevcall)

—                                 12:14 : First user call completes in 4m

—                                 12:30 : Second user call issued

—                                         (16m after completion of prevcall)

—                                 12:40 : Second user call completes in 10m

—                                 12:42 : Third user call issued

—                                         ( 2m after completion of prevcall)

—                                 12:50 : Third user call completes in 8m

—

—                                 If the think_time_scale is 100 and

—                                 the think_time_auto_correct is TRUE

—                                 during the workload replay, then

—                                 the user calls will look something

—                                 like below:

—                                 12:00 : User SCOTT connects

—                                 12:10 : First user call issued 10 mins

—                                         after the completion of prev call

—                                 12:15 : First user call completes in 5m

—                                         (takes 1 minute longer)

—                                 12:30 : Second user call issued 15 mins

—                                         (16m minus the extra time of 1m

—                                          the prev call took) after the

—                                         completion of prev call

—                                 12:44 : Second user call completes in 14m

—                                         (takes 4 minutes longer)

—                                 12:44 : Third user call issued immediately

—                                         (2m minus the extra time of 4m

—                                          the prev call took) after the

—                                         completion of prev call

—                                 12:52 : Third user call completes in 8m

—      scale_up_multiplier      – Defines the number of times the query workload

—                                 is scaled up during replay. Each captured session

—                                 is replayed concurrently as many times as the

—                                 value of the scale_up_multiplier. However, only

—                                 one of the sessions in each set of identical

—                                 replay sessions executes both queries and updates.

—                                 The remaining sessions only execute queries.

—

—                                 More specifically note that:

—                                   1. One replay session (base session) of each set

—                                      of identical sessions will replay every call

—                                      from the capture as usual

—                                   2. The remaining sessions (scale-up sessions) will

—                                      only replay calls that are read-only.

—                                      Thus, DDL, DML, and PLSQL calls that

—                                      modified the database will be

—                                      skipped. SELECT FOR UPDATE statements are also skipped.

—                                   3. Read-only calls from the scale-up are

—                                      synchronized appropriately and obey the

—                                      timings defined by think_time_scale, connect_time_scale,

—                                      and think_time_auto_correct. Also the queries

—                                      are made to wait for the appropriate commits.

—                                   4. No replay data or error divergence

—                                      records will be generated for the

—                                      scale-up sessions.

—                                   5. All base or scale-up sessions that

—                                      replay the same capture file will connect

—                                      from the same workload replay client.

—

—          capture_sts – If this parameter is TRUE, a SQL tuning set

—                        capture is also started in parallel with workload

—                        capture. The resulting SQL tuning set can be

—                        exported using DBMS_WORKLOAD_REPLAY.EXPORT_AWR

—                        along with the AWR data.

—

—                        Currently, parallel STS capture

—                        is not supported in RAC. So, this parameter has

—                        no effect in RAC.

—

—                        Furthermore capture filters defined using the

—                        dbms_workload_replay APIs do not apply to the

—                        sql tuning set capture.

—

—                        The calling user must have the approriate

—                        privileges (‘administer sql tuning set’).

—

—                        DEFAULT VALUE: FALSE

—

—     sts_cap_interval – This parameter specifies the capture interval

—                        of the SQL set capture from the cursor cache in

—                        seconds. The default value is 300.

—

—

—    Prerequisites:

—      -> The database has been initialized for replay using

—         DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY().

—      -> Any capture time connections strings that require remapping

—         during replay have already been remapped using

—         DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION().

建议一个INST开75-100个WRC客户端，使用nohup 后台启动

MODE=REPLAY (default)

———————

Keyword     Description

—————————————————————-

USERID      username

PASSWORD    password

SERVER      server connection identifier (Default: empty string)

REPLAYDIR   replay directory (Default:.)

WORKDIR     directory for trace files

DEBUG       ON, OFF (Default: OFF)

CONNECTION_OVERRIDE  TRUE, FALSE (Default: FALSE)

TRUE   All replay threads connect using SERVER,

settings in DBA_WORKLOAD_CONNECTION_MAP will be ignore

FALSE  Use settings from DBA_WORKLOAD_CONNECTION_MAP

SERIALIZE_CONNECTS  TRUE, FALSE (Default: FALSE)

TRUE   All the replay threads will connect to

the database in a serial fashion one after

another. This setting is recommended when

the replay clients use the bequeath protocol

to communicate to the database server.

FALSE  Replay threads will connect to the database

in a concurrent fashion mimicking the original

capture behavior.

DSCN_OFF    TRUE, FALSE (Default: FALSE)

TRUE   Ignore all dependencies due to block

contention during capture when synchronizing

the replay.

FALSE  Honor all captured dependencies.

MODE=CALIBRATE

————–

Provides an estimate of the number of replay clients needed

Keyword     Description

—————————————————————-

REPLAYDIR   replay directory (Default:.)

Advanced parameters:

PROCESS_PER_CPU       Maximum number of client process than can be run

per CPU (Default: 4)

THREADS_PER_PROCESS   Maximum number of threads than can be run within

a client process (Default: 50)

MODE=LIST_HOSTS

—————

Displays all host names involved in the capture

Keyword     Description

—————————————————————-

REPLAYDIR   the workload directory (Default:.)

MODE=GET_TABLES

—————

Lists all objects referenced by captured SQL statements

Keyword     Description

—————————————————————-

REPLAYDIR   the workload directory (Default:.)

若replay完成可以在EM或者使用如下脚本获得replay报告：

set pagesize 0 long 30000000 longchunksize 1000

select dbms_workload_replay.report(&ID,’TEXT’) from dual;

&ID可以从上面的查询获得