mysql oracle replay_11g Real Application Testing:Database Replay使用方法

exec DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(replay_name =>'&REPLAY_NAME',replay_dir => '&REPLAY_DIR');

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().