ITPub博客

首页 > 数据库 > Oracle > 11g Database Replay使用详解

11g Database Replay使用详解

原创 Oracle 作者:oliseh 时间:2014-10-30 16:35:56 1 删除 编辑

Database Replay将生产系统上的负荷进行采集后还原到测试系统上进行重放,这个特性有助于我们在数据库升级、主机参数修改、数据库参数修改等重大变更实施前在测试系统上完全仿照生产系统的负荷进行全面的测试,量化评估出变更实施后对现有的性能的影响程度。

Database Replay实施的流程是workload_capture->workload preprocess->replay client prepare->replay->generate replay report,对于同一个capture可以进行多次replay

实施过程中需要的服务器如下:

生产数据库服务器:用于workload_capture,即负载捕获

测试数据库服务器:用于workload_ preprocessreplay,即负载预处理和负载重放

Replay client:用于发起workload的客户端进程

如果纯粹是用于测试那么生产数据库、测试数据库、Replay client可以指向同一台机器。为了较为清晰的展示Database replay的整个过程,接下来的演示中我们会用到三台主机:

Prod db Server:生产数据库服务器

Replay Client:用于发起workload的客户端进程wrc

Replay db Server:测试数据库服务器

////////////////////////////////////////////////////////////////////////////

//1 workload_capture阶段,这些操作均在prod Server上执行

////////////////////////////////////////////////////////////////////////////

###创建capture dir,确保目录下无任何文件

mkdir -p /oradata06/repdir

create or replace directory repdir as '/oradata06/repdir';

###创建过滤器,过滤器中的内容是被包含还是排除,取决于captureinclude还是exclude属性,如果是include,那么下面过滤器的内容将会被排除掉,此演示中将会采用includecapture所以我们过滤器中指定的是我们不需要的内容:排除掉OMSemagent这两个program;如果是exclusion属性,那么过滤器中的才是我们需要捕捉的,除此之外都会被排除;

exec dbms_workload_capture.add_filter(fname=>'filter_prog1',fattribute=>'PROGRAM',fvalue=>'%OMS%');

exec dbms_workload_capture.add_filter(fname=>'filter_prog2',fattribute=>'PROGRAM',fvalue=>'%emagent%');

col set_name format a20

col type format a10

col id format a20

col name format a15

col attribute format a15

col value format a18

set linesize 130

select * from dba_workload_filters;

TYPE       ID                   STATUS SET_NAME             NAME            ATTRIBUTE       VALUE

---------- -------------------- ------ -------------------- --------------- --------------- ------------------

CAPTURE                         NEW                         FILTER_PROG2    PROGRAM         %emagent%

CAPTURE                         NEW                         FILTER_PROG1    PROGRAM         %OMS%

###开启workload capture之前,重启数据库到restricted模式->此步骤可选,为的是能一个不落的capture到所有session

shutdown immediate

startup restrict

###创建两张测试表

create table tabfix1 (id1 number,id1_mod number);

declare

begin

for i in 1..100000 loop

insert into tabfix1 values (i,i+dbms_random.value(-2,2));

end loop;

commit;

end;

/

create table tabvar1 (vid1 number,vid1_mod number);

declare

begin

for i in 1..100000 loop

insert into tabvar1 values (i,i+dbms_random.value(-2,2));

end loop;

commit;

end;

/

###准备模拟负载运行脚本s_cap1.shs_cap2.sh

---s_cap1.sh内容

while [ true ]

do

sqlplus system/xxxxxx@tstdb1 << EOF

update tabfix1 set id1_mod=id1_mod+dbms_random.value(-2,2) where id1<=50000;

commit;

select count(*) from tabfix1 where id1<=50000;

EOF

sleep 1

done

---s_cap2.sh内容

while [ true ]

do

sqlplus system/xxxxxx@tstdb1 << EOF

update tabvar1 set vid1_mod=vid1_mod+dbms_random.value(-2,2) where vid1_mod<=50000;

commit;

select count(*) from tabfix1 where id1<=50000;

EOF

sleep 1

done

###准备Replay db Server用于replay,将Replay db恢复到prod db Server开启capture前的状态,oracle推荐使用duplicate databaseexpdp & impdpsnapshot standby

三种方式,本演示中仅将上面两张测试表导入到replay db server

expdp system/xxxxxx tables=tabfix1,tabvar1 directory=hisdmp logfile=tabf.log dumpfile=tabf.dmp

scp /oradata01/hisdmp/monthly/tabf.dmp oracle@192.168.0.207:/oradata01/hisdmp/monthly/

impdp system/xxxxxx directory=hisdmp logfile=tabf.log dumpfile=tabf.dmp

###正式开启workload capturecapture包括了filter_prog1filter_prog2 两个filter以外的所有内容,capture动作开始时自动解除restrict模式,以10分钟为间隔同时捕捉sql tuning setsSTS可以在Replay报告比较阶段生产更细化的性能数据

exec dbms_workload_capture.start_capture(name=>'v1026c1',dir=>'REPDIR',duration=>600,default_action=>'INCLUDE',auto_unrestrict=>TRUE,capture_sts=>TRUE,sts_cap_interval=>300);

###运行模拟负载脚本

./s_cap1.sh

./s_cap2.sh

###capture期间可以查看dba_workload_captures了解capture状态

col name format a10

col status format a20

col sqlset_name format a25

set linesize 120

select id,name,status,duration_secs,awr_begin_snap,awr_end_snap,sqlset_name from dba_workload_captures;

        ID NAME       STATUS               DURATION_SECS AWR_BEGIN_SNAP AWR_END_SNAP SQLSET_NAME

---------- ---------- -------------------- ------------- -------------- ------------ -------------------------

        91 v1026c1    COMPLETED                      597            131          132 v1026c1_c_9196364

###dba_sqlsets标示着此sqlset来自于id=91capture

col description format a40

col owner format a10

col name format a20

set linesize 140

select * from dba_sqlset where name='v1026c1_c_9196364';

        ID NAME                 OWNER      DESCRIPTION                              CREATED      LAST_MODIFIE STATEMENT_COUNT

---------- -------------------- ---------- ---------------------------------------- ------------ ------------ ---------------

         8 v1026c1_c_9196364    SYSTEM     STS capture for capture with ID=91       27-OCT-14    27-OCT-14                378

                

###停止workload capture->此步骤可选,如果start capture时没有指定持续时间可以人工停止

exec dbms_workload_capture.finish_capture(timeout=>0);

###导出capture期间的AWR数据->此步骤可选,如果之后要生成compare period report,即两个replay的结果的比较报告或者replaycapture之间的比较报告,就要用到export_awr

select id from dba_workload_captures; --得到Capture_id

exec dbms_workload_capture.export_awr(capture_id=>91)

###生成capture阶段报告到v1026c1.html文件

set serveroutput on

spool /home/tstdb1/v1026c1.html

set long 200000

set pagesize 20000

DECLARE

  v_cap_id  NUMBER;

  v_cap_rpt CLOB;

BEGIN

  v_cap_id:=DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(dir => 'REPDIR');

  v_cap_rpt:=DBMS_WORKLOAD_CAPTURE.REPORT(capture_id =>v_cap_id,format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);

  dbms_output.put_line(v_cap_rpt);

END;

/

spool off

###capture报告内容截取如下:

主要包含了Capture ProfileCaptured Workload StatisticsTop Events CapturedTop SQL Captured等内容

注:在/oradata06/repdir/cap目录下其实已经存在wcr_cr.htmlwcr_cr.text两个文件分别对应了htmltxt版本的capture报告,拿来用即可

 

///////////////////////////

// 2preprocess 阶段

///////////////////////////

###先将capture fileprod db server复制到replay db server

scp -r /oradata06/repdir/* oracle@192.168.0.207:/oradata01/hisdmp/monthly/

***copy完成后replay db server主机上的目录结构如下

ls -rlt /oradata01/hisdmp/monthly/

drwxr-xr-x    3 oracle   oinstall         96 Oct 29 14:59 capfiles

drwxr-xr-x    2 oracle   oinstall       8192 Oct 29 14:59 cap

-rw-r--r--    1 oracle   oinstall          0 Oct 29 16:00 wcr_cap_0002x.start

-rw-r--r--    1 oracle   oinstall          0 Oct 29 16:00 wcr_cap_0002w.start

###replay db server上进行preprocess

exec dbms_workload_replay.process_capture(capture_dir=>'HISDMP');

###preprocess完成后在replay db server/oradata01/hisdmp/monthly/目录下生成了pp11.2.0.3.0子目录

oracle@jq570322a:/oradata01/hisdmp/monthly>ls -rlt pp11.2.0.3.0

total 432

-rw-r-----    1 oracle   oinstall      28672 Oct 29 16:03 wcr_seq_data.extb

-rw-r-----    1 oracle   oinstall      28672 Oct 29 16:03 wcr_scn_order.extb

-rw-r--r--    1 oracle   oinstall      25787 Oct 29 16:03 wcr_login.pp

-rw-r-----    1 oracle   oinstall      12288 Oct 29 16:03 wcr_conn_data.extb

-rw-r-----    1 oracle   oinstall      12288 Oct 29 16:03 wcr_data.extb

-rw-r-----    1 oracle   oinstall      16384 Oct 29 16:03 wcr_references.extb

-rw-r--r--    1 oracle   oinstall         35 Oct 29 16:03 wcr_process.wmd

-rw-r-----    1 oracle   oinstall      36864 Oct 29 16:03 wcr_dep_graph.extb

-rw-r-----    1 oracle   oinstall      12288 Oct 29 16:03 wcr_commits.extb

-rw-r--r--    1 oracle   oinstall       3510 Oct 29 16:03 wcr_calibrate.xml

这些文件主要是对capfiles目录下.rec文件进行汇总,在之后的replay阶段起到索引的作用。其中wcr_calibrate.xml是对replay时发起replay client数量的预估结果,内容如下:

 

    v1026c1

    2028908302

    TSTDB1

    11.2.0.3.0

    NO

    HISDMP

    /oradata01/hisdmp/monthly

    TRUE

    COMPLETED

    27-10-14 20:03:35

    27-10-14 20:13:32

    9 minutes 57 seconds

    3022402

    3088331

    INCLUDE

    2

    1096881

    534902718

    .9

    605738506

    7939

    9345

    1222

    286

    469

    312

    370

    2

    NOT POSSIBLE

    0

    11.2.0.3.0

 

  11.2.0.3.0

 

    4

    50

 

 

    4

    313

    1

    1

    15

 

///////////////////////////////

//3 replay client准备阶段

///////////////////////////////

###准备replay clients环境,replay clients主要作用是模拟客户端连接到test database发起压力测试

首先,运行replay clients的主机至少要安装oracle client

replay client主机上的sqlnet.ora必须包含有DIAG_ADR_ENABLED=ON,否则会收到ORA-15555 "workload replay client encountered unexpected error: %s错误

###replay db server上执行:将capturepreprocess的结果copy到运行replay clients,需要指出的是这里不仅要将preprocess的结果copy过去,还要将capcapfiles两个目录一起copyreplay clients主机

scp -r /oradata01/hisdmp/monthly/pp11.2.0.3.0 oracle@192.168.0.221:/oradata01/hisdmp/monthly

scp -r /oradata01/hisdmp/monthly/cap oracle@192.168.0.221:/oradata01/hisdmp/monthly

scp -r /oradata01/hisdmp/monthly/capfiles oracle@192.168.0.221:/oradata01/hisdmp/monthly

###解释一下为何capcapfiles目录也要一起copy过去

原因很简单在preprocess生成一堆文件相当于给负载编制好的一套目录,其中有个wcr_login.pp文件记录了capture阶段生成的所有.rec文件,一个session登陆后就生成一个.rec文件,用strings命令可以清楚的看到:

strings /oradata01/hisdmp/monthly/pp11.2.0.3.0/wcr_login.pp | grep .rec | head -n 10

wcr_4wd4rh0000000.rec

wcr_4wd4sh0000003.rec

wcr_4wd4th0000004.rec

wcr_4wd4uh0000006.rec

wcr_4wd4uh0000007.rec

wcr_4wd4vh0000009.rec

以上每一个文件都能在/oradata01/hisdmp/monthly/capfiles/inst1/目录下找到,负载重放时会到此目录下读取对应的文件,如果仅将pp11.2.0.3.0目录copy过去在replay Client运行wrc的时候会出现:ORA-15559: workload replay client cannot open workload capture file

###每个客户端可以模拟出多个sessioncalibrate 模式下使用wrc可以根据捕捉到的workload信息估算出需要发起多少个Replay client,在replay client上执行:

wrc mode=calibrate replaydir=/oradata01/hisdmp/monthly

Workload Replay Client: Release 11.2.0.3.0 - Production on Wed Oct 29 15:07:23 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Report for Workload in: /oradata01/hisdmp/monthly

-----------------------

Recommendation:

Consider using at least 1 clients divided among 1 CPU(s)

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

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

Workload Characteristics:

- max concurrency: 4 sessions

- total number of sessions: 313

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

/////////////////////////////////////////////////

// 4replay 阶段,在replay db server上执行

/////////////////////////////////////////////////

###initialize replay,生成新的replay信息,同时会将将replay_dir目录下的capture信息、之前已经存在的replay信息一并导入到数据库,可以通过dba_workload_replaysdba_workload_connection_mapdba_workload_filters等视图查看

exec dbms_workload_replay.initialize_replay(replay_name=>'v1028r1',replay_dir=>'HISDMP');  --replay_dir指定的是包含有preprocess datacapture data的目录

***replay状态为initialized

col name format a30

set linesize 120

select id,name,status from dba_workload_replays;

        ID NAME                           STATUS

---------- ------------------------------ ----------------------------------------

         24 v1028r1                        INITIALIZED

###设置Replay时的filter->此为可选步骤

利用capture到的负载进行replay时如果要过滤掉部分sql,可以按照如下步骤,创建filter,过滤掉程序名包含emagentperlsession

exec dbms_workload_replay.add_filter(fname=>'v1028f1',fattribute=>'PROGRAM',fvalue=>'%emagent%');  

exec dbms_workload_replay.add_filter(fname=>'v1028f1_1',fattribute=>'PROGRAM',fvalue=>'%perl%');

              

***dba_workload_filter看到TYPE=REPLAY类型的filter,过滤掉program=perl或者emagentsession              

col set_name format a20

col type format a10

col id format a20

col name format a15

col attribute format a15

col value format a18

set linesize 130

select * from dba_workload_filters;

TYPE       ID                   STATUS SET_NAME             NAME            ATTRIBUTE       VALUE

---------- -------------------- ------ -------------------- --------------- --------------- ------------------

CAPTURE    34                   USED                        FILTER_PROG2    PROGRAM         %emagent%

CAPTURE    34                   USED                        FILTER_PROG1    PROGRAM         %OMS%

REPLAY                          NEW                         V1028F1         CONNECTION_STRI %(PROGRAM=%emagent

                                                                            NG              %)%

REPLAY                          NEW                         V1028F1_1       CONNECTION_STRI %(PROGRAM=%perl%)%

                                                                            NG

***将前面创建的filter加入到Filter_set,入参里没有指定filter_name,默认是把上一次create_filter_set执行后使用add_filter添加的所有filter加入到该filter_set

exec dbms_workload_replay.create_filter_set(replay_dir=>'HISDMP',filter_set=>'v1028fs1',default_action=>'INCLUDE');  

*** dba_workload_filterstype=REPLAYfilter状态从NEW变为了IN SET,表示已经加入到filter Set里了,ID列为空说明还未被任何replay使用

select * from dba_workload_filters;

TYPE       ID                   STATUS     SET_NAME             NAME            ATTRIBUTE       VALUE

---------- -------------------- ---------- -------------------- --------------- --------------- ------------------

CAPTURE    34                   USED                            FILTER_PROG2    PROGRAM         %emagent%

CAPTURE    34                   USED                            FILTER_PROG1    PROGRAM         %OMS%

REPLAY                          IN SET     v1028fs1             V1028F1_1       CONNECTION_STRI %(PROGRAM=%perl%)%

                                                                                NG

REPLAY                          IN SET     v1028fs1             V1028F1         CONNECTION_STRI %(PROGRAM=%emagent

                                                                                NG              %)%

---使用名为v1028fs1filter set过滤接下来的replaytype=REPLAY行的ID=24status=IN USE说明这个filter_set正在被replay所使用

exec dbms_workload_replay.use_filter_set(filter_set=>'v1028fs1'); 

col set_name format a20

col type format a10

col id format a20

col name format a15

col attribute format a15

col value format a18

set linesize 130

select * from dba_workload_filters;

TYPE       ID                   STATUS     SET_NAME             NAME            ATTRIBUTE       VALUE

---------- -------------------- ---------- -------------------- --------------- --------------- ------------------

CAPTURE    34                   USED                            FILTER_PROG2    PROGRAM         %emagent%

CAPTURE    34                   USED                            FILTER_PROG1    PROGRAM         %OMS%

REPLAY     24                   IN USE     v1028fs1             V1028F1         CONNECTION_STRI %(PROGRAM=%emagent

                                                                                NG              %)%

REPLAY     24                   IN USE     v1028fs1             V1028F1_1       CONNECTION_STRI %(PROGRAM=%perl%)%

                                                                                NG

                                                                           

###replay之前检查capture里包含的连接串信息

***capture内容里的连接串还是指向production database,在replay之前需要重定向到test database

col capture_conn format a50

set linesize 120 pagesize 120       

select * from dba_workload_connection_map;

 REPLAY_ID    CONN_ID CAPTURE_CONN

---------- ---------- --------------------------------------------------

REPLAY_CONN

------------------------------------------------------------------------------------------------------------------------

        24          1 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.14

                      1.209)(PORT=1521))(CONNECT_DATA=(SID=tstdb1)(CID=(

                      PROGRAM=E:\Program?Files??x86?\PLSQL?Developer\pls

                      qldev.exe)(HOST=CHHHHC)(USER=CHH))))

        24          2 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.14

                      1.209)(PORT=1521))(CONNECT_DATA=(SID=tstdb1)(SERVE

                      R=DEDICATED)(CID=(PROGRAM=sqlplus)(HOST=jq570322b)

                      (USER=tstdb1))))

        24          3 (DESCRIPTION=(CONNECT_DATA=(SID=tstdb1)(CID=(PROGR

                      AM=perl@jq570322b)(HOST=jq570322b)(USER=oracle)))(

                      ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.209)(Port=15

                      21)))

                     

###可选步骤:对于capture阶段捕捉到的连接串信息可以在Replay阶段将这些连接信息重新映射到其它数据库,例如下面的例子中将connect_id=123的连接串分别映射到test1test2test3对应的数据库

exec DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(connection_id=>1,replay_connection=>'test1');

exec DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(connection_id=>2,replay_connection=>'test2');

exec DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(connection_id=>3,replay_connection=>'test3');

***重新映射连接串后,replay_conn显示replay client时使用的连接串

col replay_conn format a15

select replay_id,conn_id,capture_conn,REPLAY_CONN from dba_workload_connection_map;

 REPLAY_ID    CONN_ID CAPTURE_CONN                                       REPLAY_CONN

---------- ---------- -------------------------------------------------- ---------------

        24          1 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.14 test1

                      1.209)(PORT=1521))(CONNECT_DATA=(SID=tstdb1)(CID=(

                      PROGRAM=E:\Program?Files??x86?\PLSQL?Developer\pls

                      qldev.exe)(HOST=CHHHHC)(USER=CHH))))

        24          2 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.14 test2

                      1.209)(PORT=1521))(CONNECT_DATA=(SID=tstdb1)(SERVE

                      R=DEDICATED)(CID=(PROGRAM=sqlplus)(HOST=jq570322b)

                      (USER=tstdb1))))

        24          3 (DESCRIPTION=(CONNECT_DATA=(SID=tstdb1)(CID=(PROGR test3

                      AM=perl@jq570322b)(HOST=jq570322b)(USER=oracle)))(

                      ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.209)(Port=15

                      21)))

                      

注意test1test2test3必须在replay client端能够tnsping通这些别名,或者干脆使用"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.209)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=shzw)))"这种显示指定的方法来实现重映射

但在实际使用时发现connection_remap特性无法正常用起来,在Replay client侧发起客户端连接进程的时候报ORA-15561错误

oracle@jq570314a:/oradata01/hisdmp/monthly>wrc system/XXXXXX@test mode=replay replaydir=/oradata01/hisdmp/monthly

Workload Replay Client: Release 11.2.0.3.0 - Production on Wed Oct 29 09:36:47 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Errors in file :

ORA-15561: workload replay client cannot connect to the remapped connection with conn_id : 1

要规避这个错误按照MOS 1135960.1的做法是在wrc命令里加上CONNECTION_OVERRIDE=TRUE参数,使用wrc命令里自带的连接串指向test database,但这个做法的问题在于只能将多个capture连接串重定义到同一个replay连接串,无法为每个capture连接串指定各自不同replay连接串

                     

###prepare replay,控制replay的参数设定,synchronization=>'OBJECT_ID'针对于DML操作,在维持同一个对象前后事务依赖性的前提下,必要时会略微调整DML语句的执行顺序(调整不会影响语句结果),特别是在多个事务并发操作不同的对象时,相比synchronization=>'SCN'更能提高replay期间DML的执行效率,capture_sts=>TRUE会在replay时同时生成STS

exec dbms_workload_replay.prepare_replay(synchronization=>'OBJECT_ID',capture_sts=>TRUE,sts_cap_interval=>300);

***prepare后在replay_dir下生成了一个repXXX开头的目录,其中XXXoracle为每个replay分配的唯一标识,可以在dba_workload_replays视图的replay_dir_number字段中查到

drwxr-xr-x    3 oracle   oinstall         96 Oct 29 14:59 capfiles

-rw-r--r--    1 oracle   oinstall          0 Oct 29 16:00 wcr_cap_0002x.start

-rw-r--r--    1 oracle   oinstall          0 Oct 29 16:00 wcr_cap_0002w.start

drwxr-xr-x    2 oracle   oinstall       8192 Oct 29 16:03 pp11.2.0.3.0

drwxr-xr-x    2 oracle   oinstall       8192 Oct 30 09:48 cap

drwxr-xr-x    2 oracle   oinstall         96 Oct 30 10:17 filter11.2.0.3.0

drwxr-xr-x    2 oracle   oinstall         96 Oct 30 10:20 rep423075657

set linesize 140

select name,replay_dir_number from dba_workload_replays

NAME                                                                                                 REPLAY_DIR_NUMBER

---------------------------------------------------------------------------------------------------- -----------------

v1028r1                                                                                                      423075657

***replay任务的状态变为了prepare

col name format a30

set linesize 120

select id,name,status from dba_workload_replays;

        ID NAME                           STATUS

---------- ------------------------------ ----------------------------------------

        24 v1028r1                        PREPARE

###replay client所在主机发起replay client,这时客户端提示等待start_replay发起,使用CONNECTION_OVERRIDE覆盖掉dba_workload_connection_map设置,规避掉ORA-15561错误

wrc system/xxxxxx@test mode=replay CONNECTION_OVERRIDE=TRUE REPLAYDIR=/oradata01/hisdmp/monthly

Wait for the replay to start (10:24:23)

###replay db server执行:开启replay

exec DBMS_WORKLOAD_REPLAY.START_REPLAY;

###replay client主机立刻显示replay开始

Wait for the replay to start (10:24:23)

Replay started (10:24:43)

###replay期间主要通过v$workload_replay_thread视图观察进展情况,视图里显示了每个session当前正在处理哪一个capture file以及当前所耗费的dbtime等信息

col event format a40

col file_name format a30

set linesize 130

select sid,session_type,event,file_name,dbtime from v$workload_replay_thread where session_type='REPLAY';

       SID SESSION_TYPE  EVENT                                    FILE_NAME                          DBTIME

---------- ------------- ---------------------------------------- ------------------------------ ----------

      1912 REPLAY        Disk file operations I/O                 wcr_4wdmch000008b.rec               74599

      3303 REPLAY        SQL*Net message from client              wcr_4wdhyh000006k.rec               69859

      3775 REPLAY        SQL*Net message from client              wcr_4wdhnh0000069.rec               87932

      5658 REPLAY        Disk file operations I/O                 wcr_4wdmch000008c.rec               73601

      7080 REPLAY        null event                               wcr_4wd59h000000t.rec                   0

###至此一个完整的replay完成,为了辅助讲解后面的compare period report,我们又进行了一次replay,产生一个id=26replay

select id,name,status,start_time,end_time from dba_workload_replays;

        ID NAME       STATUS     START_TIME        END_TIME

---------- ---------- ---------- ----------------- -----------------

        24 v1028r1    COMPLETED  20141030 10:23:37 20141030 10:33:36

        26 v1028r2    COMPLETED  20141030 12:16:00 20141030 12:26:00

////////////////////////////

//5、生成replay报告

////////////////////////////

Replay报告有几种,本文主要介绍replay reportcompare period report两种。

replay reportsreplay执行时的性能统计信息列出来,并与capture作简单的比较

compare period report侧重于在两个不同的replay之间,或者replaycapture之间从数据库参数配置、主机硬件环境、Top SQL耗用资源等方面形成全方位的诊断报告

###生成replay report

set serveroutput on

spool /home/oracle/v1028r1.html

set pagesize 20000

set long 200000

declare

v_offset number;

v_length number;

v_nowlength number;

v_char1 varchar2(32767);

v_reprpt clob;

begin

v_reprpt:=dbms_workload_replay.report(replay_id=>24,format=>dbms_workload_replay.TYPE_HTML);

v_nowlength:=1;

v_length:=dbms_lob.getlength(lob_loc=>v_reprpt);

v_offset:=1;

while ( v_offset < v_length ) loop

v_char1:=dbms_lob.substr(lob_loc=>v_reprpt,offset=>v_offset);

dbms_output.put_line(v_char1);

v_offset:=v_offset+32767;

end loop;

end;

/

spool off

###生成的replay报告主要包括replay informationoptionsstatisticsdivergence等信息,也会包含细化到SQL语句的负载分析

截图如下:

上图中Replay Divergence Summary用来反应replaycapture阶段执行SQL时遇到的错误次数,这些错误是否仅在replay还是capture阶段出现;DMLSELECT语句返回记录数是否不一致的情况等

上图对于SQL语句按照负荷从高到低进行排序,并对这些负荷的分布进行breakdown

###生成compare period report

select id,name,status,start_time,end_time from dba_workload_replays;

        ID NAME       STATUS     START_TIME        END_TIME

---------- ---------- ---------- ----------------- -----------------

        24 v1028r1    COMPLETED  20141030 10:23:37 20141030 10:33:36

        26 v1028r2    COMPLETED  20141030 12:16:00 20141030 12:26:00

###将之前生成的id=24id=26两个replay形成比较报告之前需要从prod db server上将capture阶段的AWR负载export出来,importreplay db server中,以下在product database上实施export_awr操作

exec DBMS_WORKLOAD_CAPTURE.EXPORT_AWR(capture_id=>91);

###prod db server上执行:将export_awr导出的文件传输到replay db server,以下标红的文件都要复制到replay db server

oracle@jq570322b:/oradata06/repdir/cap>ls -rlt

total 28336

-rw-r--r--    1 oracle   oinstall        162 Oct 27 20:03 wcr_scapture.wmd

-rw-r-----    1 oracle   oinstall        268 Oct 27 20:14 wcr_fcapture.wmd

-rw-r-----    1 oracle   oinstall      46708 Oct 27 20:14 wcr_cr.html

-rw-r-----    1 oracle   oinstall      19196 Oct 27 20:14 wcr_cr.text

-rw-r-----    1 oracle   oinstall      29141 Oct 30 09:36 wcr_ca.log

-rw-r-----    1 oracle   oinstall   12222464 Oct 30 09:36 wcr_ca.dmp

-rw-r-----    1 oracle   oinstall      12288 Oct 30 09:36 wcr_cap_uc_graph.extb

-rw-r-----    1 oracle   oinstall    2146304 Oct 30 09:36 wcr_ca_sts.dmp

scp /oradata06/repdir/cap/wcr_ca.log oracle@192.168.0.207:/oradata01/hisdmp/monthly/cap/

scp /oradata06/repdir/cap/wcr_ca.dmp oracle@192.168.0.207:/oradata01/hisdmp/monthly/cap/

scp /oradata06/repdir/cap/wcr_cap_uc_graph.extb oracle@192.168.0.207:/oradata01/hisdmp/monthly/cap/

scp /oradata06/repdir/cap/wcr_ca_sts.dmp oracle@192.168.0.207:/oradata01/hisdmp/monthly/cap/

***replay db server上操作:将上述export_awr的结果通过函数DBMS_WORKLOAD_CAPTURE.IMPORT_AWR导入到replay db serverDBMS_WORKLOAD_CAPTURE.IMPORT_AWR的返回值是随机生成的DBID,可以在dba_workload_captures.awr_dbid里找到

var v_randbid number;

exec :v_randbid:=DBMS_WORKLOAD_CAPTURE.IMPORT_AWR(capture_id=>34,staging_schema=>'SYSTEM',force_cleanup=>FALSE);

print :v_randbid;

 V_RANDBID

----------

 383379053

 

select id,name,status,start_time,end_time,dir_path,awr_dbid,awr_begin_snap,awr_end_snap from dba_workload_captures;

        ID NAME            STATUS     START_TIME        END_TIME          DIR_PATH               AWR_DBID AWR_BEGIN_SNAP AWR_END_SNAP

---------- --------------- ---------- ----------------- ----------------- -------------------- ---------- -------------- ------------

        34 v1026c1         COMPLETED  20141027 20:03:35 20141027 20:13:32 /oradata01/hisdmp/mo  383379053            131          132

                                                                          nthly

***dba_hist_snapshot里可以看到AWR负载已经导入

col begin_interval_time format a30

col end_interval_time format a30

set linesize 120

select snap_id,dbid,begin_interval_time,end_interval_time from dba_hist_snapshot where dbid=383379053;

   SNAP_ID       DBID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME

---------- ---------- ------------------------------ ------------------------------

       131  383379053 27-OCT-14 08.00.35.987 PM      27-OCT-14 08.03.32.072 PM

       132  383379053 27-OCT-14 08.03.32.072 PM      27-OCT-14 08.14.02.139 PM

###生成两个replay间的比较报告输出到v1028r1r2.html文件     

set serveroutput on

spool /home/oracle/v1028r1r2.html

declare

v_rlt clob;

v_replay_id1 number:=24;

v_replay_id2 number:=26;

v_snum number:=1;

v_length number;

v_char varchar2(32767);

begin

dbms_workload_replay.compare_period_report(replay_id1=>v_replay_id1,replay_id2=>v_replay_id2,format=>'HTML',result=>v_rlt);

v_length:=dbms_lob.GETLENGTH(v_rlt);

while ( v_snum < v_length ) loop

v_char:=dbms_lob.substr(lob_loc=>v_rlt,amount=>32767,offset=>v_snum);

v_snum:=v_snum+32767;

dbms_output.put_line(v_char);

end loop;

end;

/

spool off

###生成的compare period reporthardwaretop sqlDivergence等维度比对两次replay的结果

###如果要进行更细化的针对每条sql语句的分析,可以使用dbms_workload_replay.compare_sqlset_report函数,其实质是利用SPA对于两次replay期间生成的STS进行分析,函数dbms_workload_replay.compare_sqlset_report返回值是task_name,在dba_advisor_tasks中能够查到

set serveroutput on

spool /home/oracle/v1028r1r2_spa.html

declare

v_rlt clob;

v_replay_id1 number:=26;

v_replay_id2 number:=24;

v_snum number:=1;

v_length number;

v_char varchar2(32767);

v_ret varchar2(32767);

begin

v_ret:=dbms_workload_replay.compare_sqlset_report(replay_id1=>v_replay_id1,replay_id2=>v_replay_id2,format=>'HTML',result=>v_rlt);

v_length:=dbms_lob.GETLENGTH(v_rlt);

while ( v_snum < v_length ) loop

v_char:=dbms_lob.substr(lob_loc=>v_rlt,amount=>32767,offset=>v_snum);

v_snum:=v_snum+32767;

dbms_output.put_line(v_char);

end loop;

dbms_output.put_line(v_ret);

end;

/

spool off

###SPA报告截图如下

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1314843/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1616698