Tuesday Mar 19, 2013

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

Database Replay使用方法

1.1       捕获性能负载

1. 针对需要捕获性能负载时段执行如下PL/SQL脚本:

   execute dbms_workload_capture.start_capture(‘&CAPTURE_NAME’,'&DIRECT_NAME’,default_action=>’INCLUDE’); 

 

CAPTURE_NAME=> 本次capture的名字

可以通过 DBA_WORKLOAD_CAPTURES 视图监控

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

2. 可以通过如下SQL监控capture的情况

查询1:select id,name,status,start_time,end_time,connects,user_calls,dir_path from dba_workload_captures where id = (select max(id) from dba_workload_captures) ; 

 

set pagesize 0 long 30000000 longchunksize 1000

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

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

3. 当不再需要捕获更多负载时通过如下脚本结束capture:

execute dbms_workload_capture.finish_capture; 

 

1.2       预处理捕获

1. 将捕获到的capture file传输到目标数据库主机上,并创建必要的Oracle Directory 对象

2.  在目标数据库预处理capture file

execute DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(‘&DIRECTORY_NAME’); 

 

注意 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.

3. 以上预处理可能因为bug:8919603

1.3       开始REPLAY重放

通过 wrc工具的calibrate模式评估需要多少个客户端机

wrc mode=calibrate replaydir=$REPLAY_DIR$REPLAY_DIR指定预处理过的目录

 

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

准备数据库环境,将数据库闪回到capture时间点并创建用户:

shutdown abort;      ==》关闭2个实例startup mount;        ==》 启动一个实例到mountflashback database to restore point prereplay;

 

alter database open resetlogs;

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

create user orasupport identified by oracle;

grant dba to orasupport;

执行如下脚本准备capture:

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

以上完成后启动WRC 客户端:

nohup  wrc  orasupport/oracle replaydir=$REPLAY_DIR  DSCN_OFF=TRUE &

 

建议一个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

exec DBMS_WORKLOAD_REPLAY.start_replay();

建议通过EM或者下面的脚本查询进度:

Select id, name,status from dba_workload_replays; 

 

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

set pagesize 0 long 30000000 longchunksize 1000

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

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


http://www.askmaclean.com/archives/database-replay.html 

Sunday Mar 10, 2013

SQL Performance Analyzer SPA常用脚本汇总

Oracle Database 11g 引入了 SQL 性能分析器;使用该工具可以准确地评估更改对组成工作量的 SQL 语句的影响。SQL 性能分析器可帮助预测潜在的更改对 SQL 查询工作量的性能影响。这种功能可向 DBA 提供有关 SQL 语句性能的详细信息,例如,执行前后的统计信息,提高或降低性能的语句。这样一来,您就可以执行诸如以下操作的操作:在测试环境中进行更改,以确定数据库升级是否会改进工作量性能。[Read More]

Saturday Dec 08, 2012

了解Database Replay Capture内部原理

Database Replay是11g中很酷的特性,对于workload capture的内部工作原理大家理解的不多,这里就介绍一下。

对于Workload Capture需要考虑的因素:

  • 负载捕获文件消耗定量的磁盘空间,这些捕获文件是2进程文件,无法直接阅读,有测试表明在大并发量的OLTP环境中可以达到capture 10分钟占用1G磁盘空间
  • 数据库重启:
    • 可能是保证捕获所有事务的可靠重放的唯一路径
      • 使用startup restrict启动实例,避免不完整的事务捕获
      • 启动capture会取消restrict模式
    • 基于负载类型重启不是必要的
  • 为重放目的恢复数据库的多种方法:
    • 基于scn或时间的物理恢复
    • 逻辑恢复应用数据
    • 闪回或者快照数据
  • Capture可以指定过滤器作为捕获workload子集的方法
  • 需要的权限包括SYSDBA、SYSOPER和合适的OS权限
  • 性能消耗:
    • 在TPCC测试中capture的性能损耗为4.5%
    • 对于每个session会多消耗64KB的内存
    • 必要的Workload Capture耗费文件系统磁盘空间

这里需要注意的有2点, 对于RAC集群存在workload capture  file的目录必须位于共享文件系统上,否则start_capture时会报错。

对于每个session会多消耗64KB的内存,这是由于本质上写出负载信息到workload  capture file的同样是Server Process服务进程本身,但是这种写出并非在parse解析或execution执行阶段,Server Process将其LOGON、LOGOFF、SQL执行等信息记录存放在PGA中,主要是WCR Capture PG、WCR Capture PGA中,当PGA中的工作负载历史记录达到一定数目时,Server Process本身负责写出这些数据到WCR文件中,在写出这些WCR文件时Server Process进入’WCR: capture file IO write’等待事件。

[Read More]
About

author's avatar The Maclean Liu
Advanced Customer Services
AskMaclean Logo 10g_ocm SHOUG

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today