运行database replay进行升级测试

最近要做10g到11g的升级,对于性能方面的测试,一般使用RAT(Oracle Real Application Testing)技术。RAT中主要包含了两种技术,一种叫Database Replay(数据库重演),另一种叫SQL Performance Analyzer(SQL性能分析)。先来了解一下什么是Database Replay,这里有一幅图。

QQ20140420225910.jpg

通过这幅图我们可以看到其实就是把生产环境的成百上千个用户执行的语句进行捕获。然后把这一部分负载信息拿到测试环境上进行播放。播放的过程就相当于模拟了成百上千的用户对数据库的操作。但是这个过程有些操作是不支持的。具体参照下面列出的情景。

Workload Capture Restrictions

The following types of client requests are not supported.

  • Direct path load of data from external files using utilities such as SQL*Loader
  • Non-PL/SQL based Advanced Queuing (AQ)
  • Flashback queries
  • Oracle Call Interface (OCI) based object navigations
  • Non SQL-based object access
  • Distributed transactions (any distributed transactions that are captured will be replayed as local transactions)
  • Oracle Streams/Advanced Replication workload is not supported prior to 11.2.
  • Database session migration
  • Database Resident Connection Pooling ( DRCP )
  • XA transactions
  • Workloads having Object Out Bind

因此要避免这类的问题,我们可以实施过滤操作,将部分操作过滤出去。

使用Database Replay首先我们需要去查看官方文档Document 560977.1   Using Real Application Testing Functionality in Earlier Releases这个文档列出了一些先决条件,比如我们要从10.2.0.5的生产环境捕获负载,然后拿到11.2.0.4环境上回放。这需要你在10.2.0.5和11.2.0.4上安装相应的patch。其实我做了些测试,不安装这个patch,也能够测试成功。但是据Oracle人员称会有一些影响。为了能够精准的执行测试,建议还是安装这些补丁

Source DB Upgrade from release Destination DB Upgrade to any release What patch you need to apply? Download Information Comments
10.2.0.5.0 >=11.2.0.3.0 10.2.0.5.0 Patchset + one-off patch 9373986 and 11.2.0.3.0 + one-off patch 13947480
or
16086826 (16086826 includes 13947480)
or
17411249
(17411249 includes 16086826 and all other previous patches)
One-off patch can be downloaded or requested from MOS One-off patch can be downloaded or requested from MOS For patch
10.2.0.5.0 >=11.2.0.4.0 10.2.0.5.0 Patchset + one-off patch 9373986and no mandatory patch required for 11.2.0.4.0 One-off patch can be downloaded or requested from MOS One-off patch can be downloaded or requested from MOS For patch

安装完补丁之后,我们就可以做database replay的操作了,该操作主要分成四个步骤。

106545

  • Workload Capture
  • Workload Processing
  • Workload Replay – including DML and SQL queries
  • Analysis and Reporting

我这边做的一个例子是从10.2.0.5捕获系统负载,然后拿到11.2.0.4上面去重放。按照Using Real Application Testing Functionality in Earlier Releases的要求,我们需要在10.2.0.5上面安装Bug 9373986 : WORKLOAD DATA IS NOT CAPTURED WHENEVER RESTARTING WORKLOAD CAPTURE因为我这只是模拟测试,所以没有安装,实际上可以捕获负载成功。

一、负载捕获

第一个任务就是做捕获,可以通过命令行或者是EM来实现捕获。

1.捕获会把数据库运行的整个活动写入到指定的目录当中。这里我采用系统默认的DATA_PUMP_DIR目录。

SQL> select * from dba_directories where directory_name='DATA_PUMP_DIR'; 
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH 

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

SYS                            DATA_PUMP_DIR                  /oracle/app/oracle/product/10.2.0/db_1/rdbms/log

2.为了更好的演示捕获的过程,需要创建一个表,然后写一些负载的脚本。这里我选择了一些插入和查询的SQL。

SQL> connect test/test 

Connected.
create table t1 ( 
name varchar2(20) 
)   
/

下面开始准备两段PL/SQL的代码,模拟大批量的插入t1表和查询scott.emp下的表.

declare 
    v_sql varchar2(256); 
  BEGIN 
  FOR i IN 1 .. 10 LOOP 
     v_sql:='insert into t1  values (''Name for ' ||i||''')'; 
     execute immediate v_sql; 
     commit; 
  END LOOP; 
  END; 
  / 

declare 
    v_sql varchar2(2000); 
  BEGIN 
  FOR i IN 1 .. 10 LOOP 
     v_sql:='select * from scott.emp where rownum<'||i; 
      execute immediate v_sql; 
  END LOOP; 
  END; 
  /

3.设置过滤条件。这里我们可以设置不捕捉SYS用户的,不捕捉是PL/SQL program发起的。当然还有很多种可以过滤的方式,下面罗列了一下。设置完成过滤后,在我们运行捕获负载的时候,我们可以设置INCLUDE和EXCLUDE两个选项。INCLUDE的意思就是所有的Database请求都会捕获,除了定义的过滤条件外。而EXCLUDE正好相反,只会捕捉我们定义的过滤。

If INCLUDE is used, by default all user requests to the database will be captured, except for the part of the workload defined by the filters. If EXCLUDE is used, by default no user request to the database will be captured, except for the part of the workload defined by the filters. Other FILTER attributes which can be used are:-

  • — SESSION_ID – type NUMBER
  • — USER – type STRING
  • — MODULE – type STRING
  • — ACTION – type STRING
  • — PROGRAM – type STRING
  • — SERVICE – type STRING
SQL> exec dbms_workload_capture.ADD_FILTER(fname =>'FILTER_SYS',fattribute => 'USER',fvalue => 'TEST'); 
PL/SQL procedure successfully completed.

SQL> select type, name, attribute, status, value from dba_workload_filters; 

TYPE                           NAME                      ATTRIBUTE            STATUS VALUE 
------------------------------ ------------------------- -------------------- ------ -------------------- 
CAPTURE                        TEST_CAP_FILTER1          USER                 NEW    test

4.开始捕捉,这里要记住很重要的一点,我们执行开始和结束,都要在这个session下运行。

SQL> BEGIN 
  2  DBMS_WORKLOAD_CAPTURE.start_capture (name => 'test_capture_1', 
  3                                        dir =>'DATA_PUMP_DIR', 
  4                                        default_action=>'EXCLUDE', 
  5                                        duration => NULL); 
  6  END;                    
  7  / 
BEGIN 
* 
ERROR at line 1: 
ORA-15591: cannot start capture because parameter "PRE_11G_ENABLE_CAPTURE" is not enabled 
ORA-06512: at "SYS.DBMS_WORKLOAD_CAPTURE", line 799 
ORA-06512: at line 2

执行出错,这儿需要我们把参数PRE_11G_ENABLE_CAPTURE设置成ture.

SQL> alter system set PRE_11G_ENABLE_CAPTURE=true; 
System altered.

SQL> BEGIN 
  2  DBMS_WORKLOAD_CAPTURE.start_capture (name => 'test_capture_1', 
  3                                        dir =>'DATA_PUMP_DIR', 
  4                                        default_action=>'EXCLUDE', 
  5                                        duration => NULL); 
  6  END;                    
  7  / 
PL/SQL procedure successfully completed.

5.新开一个会话,运行语句。这里我们运行我们前面写的PL/SQL代码,同时我们在SYS下面在执行一些事务。看看我们设置的过滤有没有生效。

SQL> connect test/test 
Connected. 
SQL> declare 
  2    v_sql varchar2(256); 
  3  BEGIN 
  4   FOR i IN 1 .. 10 LOOP 
  5     v_sql:='insert into t1  values (''Name for ' ||i||''')'; 
  6     execute immediate v_sql; 
  7     commit; 
  8   END LOOP; 
  9  END; 
10  / 
PL/SQL procedure successfully completed. 

SQL> declare 
  2    v_sql varchar2(2000); 
  3  BEGIN 
  4   FOR i IN 1 .. 10 LOOP 
  5     v_sql:='select * from scott.emp where rownum<'||i; 
  6      execute immediate v_sql; 
  7   END LOOP; 
  8  END; 
  9  / 
PL/SQL procedure successfully completed.

SQL> connect test/test 
Connected.

SQL> insert into t2 select * from dba_tables; 
1535 rows created. 

SQL> commit; 
Commit complete.

6.回到捕获的那个会话窗口,停止捕获。

SQL> exec dbms_workload_capture.finish_capture; 
PL/SQL procedure successfully completed.

7.从数据库中查看我们捕获的信息。这里我们可以看到我们的捕获的事务数是10,我们只是捕获了TEST用户下的操作,而对于我们SYS下执行事务的操作是没有捕获的。

SQL> select name, directory, status, start_time, end_time, USER_CALLS,TRANSACTIONS from dba_workload_captures; 

NAME                 DIRECTORY       STATUS          START_TIM END_TIME  USER_CALLS TRANSACTIONS 
-------------------- --------------- --------------- --------- --------- ---------- ------------ 
test_capture_1       DATA_PUMP_DIR   COMPLETED       20-APR-14 20-APR-14         56           10

8.从操作系统目录下查看我们捕获的文件。

[oracle@ora10g log]$ ls -lrt 
total 80 
-rw-r--r-- 1 oracle oinstall   126 Apr 20 13:47 wcr_scapture.wmd 
-rw-r--r-- 1 oracle oinstall  3190 Apr 20 13:48 wcr_56rwjh0000000.rec 
-rw-r--r-- 1 oracle oinstall   785 Apr 20 13:48 wcr_56rwuh0000002.rec 
-rw-r--r-- 1 oracle oinstall  4644 Apr 20 13:48 wcr_56rwrh0000001.rec 
-rw-r--r-- 1 oracle oinstall  3190 Apr 20 13:48 wcr_56rx0h0000003.rec 
-rw-r--r-- 1 oracle oinstall  1030 Apr 20 13:48 wcr_56rx6h0000004.rec 
-rw-r--r-- 1 oracle oinstall   930 Apr 20 13:48 wcr_56rxyh0000005.rec 
-rw-r--r-- 1 oracle oinstall  1044 Apr 20 13:48 wcr_56ryah0000006.rec 
-rw-r--r-- 1 oracle oinstall   195 Apr 20 13:49 wcr_fcapture.wmd 
-rw-r--r-- 1 oracle oinstall 11224 Apr 20 13:49 wcr_cr.text 
-rw-r--r-- 1 oracle oinstall 25514 Apr 20 13:49 wcr_cr.html 
[oracle@ora10g log]$ tree 
. 
|-- wcr_56rwjh0000000.rec 
|-- wcr_56rwrh0000001.rec 
|-- wcr_56rwuh0000002.rec 
|-- wcr_56rx0h0000003.rec 
|-- wcr_56rx6h0000004.rec 
|-- wcr_56rxyh0000005.rec 
|-- wcr_56ryah0000006.rec 
|-- wcr_cr.html 
|-- wcr_cr.text 
|-- wcr_fcapture.wmd 
`-- wcr_scapture.wmd 

0 directories, 11 files

9.查看捕获的报告。默认情况下执行捕获会生成2个快照,我们还可以通过这两个快照生成AWR报告,用于和重放的AWR进行比较。

SQL> select dbms_workload_capture.report(30,'TEXT') from dual; 

DBMS_WORKLOAD_CAPTURE.REPORT(30,'TEXT') 
-------------------------------------------------------------------------------- 

Database Capture Report For ORCL 

DB Name         DB Id    Release     RAC Capture Name               Status 
------------ ----------- ----------- --- -------------------------- ---------- 
ORCL          1349524903 10.2.0.5.0  NO  test_capture_1             COMPLETED 

                   Start time: 20-Apr-14 13:47:58 (SCN = 6740487) 
                     End time: 20-Apr-14 13:48:59 (SCN = 6740608) 
                     Duration: 1 minute 1 second 
                 Capture size: 13.84 KB 
             Directory object: DATA_PUMP_DIR 
               Directory path: /oracle/app/oracle/product/10.2.0/db_1/rdbms/log/ 

      Directory shared in RAC: TRUE 
                 Filters used: 1 INCLUSION filter 

Captured Workload Statistics                    DB/Inst: ORCL/  Snaps: 116-117 
-> 'Value' represents the corresponding statistic aggregated 
      across the entire captured database workload. 
-> '% Total' is the percentage of 'Value' over the corresponding 
      system-wide aggregated total. 

Statistic Name                                   Value   % Total 
---------------------------------------- ------------- --------- 
DB time (secs)                                    0.03     15.00 
Average Active Sessions                           0.00       N/A 
User calls captured                                 56     19.65 
User calls captured with Errors                      0       N/A 
Session logins                                       5     71.43 
Transactions                                        10     40.00 
          ------------------------------------------------------------- 

Top Events Captured                             DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top Service/Module Captured                     DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top SQL Captured                                DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top Sessions Captured                           DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top Events containing Unreplayable Calls        DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top Service/Module containing Unreplayable CallsDB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top SQL containing Unreplayable Calls           DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top Sessions containing Unreplayable Calls      DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top Events Filtered Out                         DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top Service/Module Filtered Out                 DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top SQL Filtered Out                            DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top Sessions Filtered Out                       DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top Events (Jobs and Background Activity)       DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top Service/Module (Jobs and Background Activity)DB/Inst: ORCL/  Snaps: 116-11 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top SQL (Jobs and Background Activity)          DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Top Sessions (Jobs and Background Activity)     DB/Inst: ORCL/  Snaps: 116-117 

                  No data exists for this section of the report. 
          ------------------------------------------------------------- 

Workload Filters                                DB/Inst: ORCL/  Snaps: 116-117 

  # Filter Name              Type    Attribute    Value 
--- ------------------------ ------- ------------ -------------------------- 
  1 FILTER_TEST              INCLUDE USER         TEST 
          ------------------------------------------------------------- 

End of Report

二、预处理重复数据

预处理需要我们把捕获的文件COPY到目标机器的Datadump目录下面。

[oracle@ora10g log]$ scp * oracle@192.168.56.102:/oracle/app/oracle/admin/ora11/dpdump/ 
oracle@192.168.56.102's password: 
wcr_56rwjh0000000.rec                                  100% 3190     3.1KB/s   00:00    
wcr_56rwrh0000001.rec                                  100% 4644     4.5KB/s   00:00    
wcr_56rwuh0000002.rec                                  100%  785     0.8KB/s   00:00    
wcr_56rx0h0000003.rec                                  100% 3190     3.1KB/s   00:00    
wcr_56rx6h0000004.rec                                  100% 1030     1.0KB/s   00:00    
wcr_56rxyh0000005.rec                                  100%  930     0.9KB/s   00:00    
wcr_56ryah0000006.rec                                  100% 1044     1.0KB/s   00:00    
wcr_cr.html                                            100%   25KB  24.9KB/s   00:00    
wcr_cr.text                                            100%   11KB  11.0KB/s   00:00    
wcr_fcapture.wmd                                       100%  195     0.2KB/s   00:00    
wcr_scapture.wmd                                       100%  126     0.1KB/s   00:00   

SQL> exec dbms_workload_replay.process_capture('DATA_PUMP_DIR'); 
PL/SQL procedure successfully completed.

执行完这个存储过程之后,我们会发现在目录下生成了一个pp11.2.0.4.0文件夹。文件夹有下列内容:

[oracle@11g pp11.2.0.4.0]$ ls -lrt 
total 96 
-rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_seq_data.extb 
-rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_scn_order.extb 
-rw-r--r-- 1 oracle oinstall   224 Apr 20 21:33 wcr_login.pp 
-rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_data.extb 
-rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_conn_data.extb 
-rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_references.extb 
-rw-r--r-- 1 oracle oinstall    35 Apr 20 21:33 wcr_process.wmd 
-rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_dep_graph.extb 
-rw-r----- 1 oracle oinstall 12288 Apr 20 21:33 wcr_commits.extb 
-rw-r--r-- 1 oracle oinstall  3512 Apr 20 21:33 wcr_calibrate.xml

检查需要多少个客户端进行播放及需要消耗的资源。

[oracle@11g pp11.2.0.4.0]$ wrc mode=calibrate replaydir=/oracle/app/oracle/admin/ora11/dpdump 
Workload Replay Client: Release 11.2.0.4.0 - Production on Sun Apr 20 21:39:27 2014 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
Report for Workload in: /oracle/app/oracle/admin/ora11/dpdump 
----------------------- 

Recommendation: 
Consider using at least 1 clients divided among 1 CPU(s) 
You will need at least 3 MB of memory per client process. 
If your machine(s) cannot match that number, consider using more clients. 

Workload Characteristics: 
- max concurrency: 1 sessions 
- total number of sessions: 3 

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

三、执行重放

1.执行重放之前,我们要初始化重放。这里仍然要注意,所有执行重放的系列操作都必须在同一个会话下执行。

SQL> exec dbms_workload_replay.initialize_replay (replay_name => 'test_replay_1', replay_dir  => 'DATA_PUMP_DIR'); 
PL/SQL procedure successfully completed.

SQL> select id,name,PARALLEL,CAPTURE_ID,STATUS,USER_CALLS from DBA_WORKLOAD_REPLAYS; 
        ID NAME                      PAR CAPTURE_ID STATUS                                   USER_CALLS 
---------- ------------------------- --- ---------- ---------------------------------------- ---------- 
        54 test_replay_1             NO          65 INITIALIZED

2.执行重放可以选择COMMIT的顺序是否一致。

SQL> exec DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE); 
PL/SQL procedure successfully completed.

SQL> select id,name,PARALLEL,CAPTURE_ID,STATUS,USER_CALLS from DBA_WORKLOAD_REPLAYS; 

        ID NAME                      PAR CAPTURE_ID STATUS                                   USER_CALLS 
---------- ------------------------- --- ---------- ---------------------------------------- ---------- 
        54 test_replay_1             NO          65 PREPARE

3.开始执行replay,首先去命令行执行一段指令,然后回到原本的会话执行开始命令。

[oracle@11g ~]$ wrc system/oracle mode=replay replaydir=/oracle/app/oracle/admin/ora11/dpdump 
Workload Replay Client: Release 11.2.0.4.0 - Production on Sun Apr 20 22:02:34 2014 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
Wait for the replay to start (22:27:21)

SQL> exec DBMS_WORKLOAD_REPLAY.START_REPLAY(); 
PL/SQL procedure successfully completed.

这个地方如果希望停止replay可以调用DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY()来取消replay。

回到replay的窗口,我们可以看到replay已经完成的屏幕输出。

[oracle@11g ~]$ wrc system/oracle mode=replay replaydir=/oracle/app/oracle/admin/ora11/dpdump 
Workload Replay Client: Release 11.2.0.4.0 - Production on Sun Apr 20 22:02:34 2014 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 

Wait for the replay to start (22:27:21) 
Replay started (22:27:48) 
Replay finished (22:28:51)

我们也可以观察后台日志。可以看到在执行完成后,启动了DM和DW进程进行了一个导出。

DBMS_WORKLOAD_REPLAY.START_REPLAY(): Starting database replay at 04/20/2014 22:27:48 
Sun Apr 20 22:28:07 2014 
DBMS_WORKLOAD_REPLAY: Database replay ran to completion at 04/20/2014 22:28:06 
Sun Apr 20 22:28:08 2014 
DM00 started with pid=31, OS id=20650, job SYS.SYS_EXPORT_TABLE_01 
Sun Apr 20 22:28:10 2014 
DW00 started with pid=32, OS id=20652, wid=1, job SYS.SYS_EXPORT_TABLE_01

我们再观察一下datadump的目录,发现replay完成之后,导出了以WRH$开头的表。

[oracle@11g dpdump]$ ls -lrt 
total 88 
-rw-r--r-- 1 oracle oinstall   126 Apr 20 22:23 wcr_scapture.wmd 
-rw-r--r-- 1 oracle oinstall   195 Apr 20 22:23 wcr_fcapture.wmd 
-rw-r--r-- 1 oracle oinstall 11224 Apr 20 22:23 wcr_cr.text 
-rw-r--r-- 1 oracle oinstall 25514 Apr 20 22:23 wcr_cr.html 
-rw-r--r-- 1 oracle oinstall  1044 Apr 20 22:23 wcr_56ryah0000006.rec 
-rw-r--r-- 1 oracle oinstall   930 Apr 20 22:23 wcr_56rxyh0000005.rec 
-rw-r--r-- 1 oracle oinstall  1030 Apr 20 22:23 wcr_56rx6h0000004.rec 
-rw-r--r-- 1 oracle oinstall  3190 Apr 20 22:23 wcr_56rx0h0000003.rec 
-rw-r--r-- 1 oracle oinstall   785 Apr 20 22:23 wcr_56rwuh0000002.rec 
-rw-r--r-- 1 oracle oinstall  4644 Apr 20 22:23 wcr_56rwrh0000001.rec 
-rw-r--r-- 1 oracle oinstall  3190 Apr 20 22:23 wcr_56rwjh0000000.rec 
drwxr-xr-x 2 oracle oinstall  4096 Apr 20 22:25 pp11.2.0.4.0 
drwxr-xr-x 2 oracle oinstall  4096 Apr 20 22:28 rep366872083

[oracle@11g dpdump]$ cd rep366872083 
[oracle@11g rep366872083]$ ls -lrt 
total 9168 
-rw-r--r-- 1 oracle oinstall     827 Apr 20 22:28 wcr_replay.wmd 
-rw-r--r-- 1 oracle oinstall   15671 Apr 20 22:28 wcr_ra_366872083.log 
-rw-r----- 1 oracle oinstall 9326592 Apr 20 22:28 wcr_ra_366872083.dmp 
-rw-r----- 1 oracle oinstall   12288 Apr 20 22:28 wcr_rep_uc_graph_366872083.extb 
-rw-r--r-- 1 oracle oinstall    8412 Apr 20 22:28 wcr_rr_366872083.xml

最后验证下我们的数据。

SQL> connect test/test 
Connected. 
SQL> select * from t1; 

NAME 
-------------------------------------------------------------------------------- 
Name for 1 
Name for 2 
Name for 3 
Name for 4 
Name for 5 
Name for 6 
Name for 7 
Name for 8 
Name for 9 
Name for 10 
10 rows selected.

四、生成报告

最后一步就是我们做完了replay,需要做个报告的对比,这里我们可以通过awr报告进行对比,也可以使用自带的包生成对比报告。如下所示:

SQL> select id,name,status,start_time,end_time,user_calls,dir_path from dba_workload_replays; 

        ID NAME                 STATUS               START_TIM END_TIME  USER_CALLS DIR_PATH 
---------- -------------------- -------------------- --------- --------- ---------- ---------------------------------------- 
        54 test_replay_1        COMPLETED            20-APR-14 20-APR-14         56 /oracle/app/oracle/admin/ora11/dpdump/

SQL> set long 999999 
SQL> select dbms_workload_replay.report(54,'TEXT') from dual; 

DBMS_WORKLOAD_REPLAY.REPORT(54,'TEXT') 
-------------------------------------------------------------------------------- 
DB Replay Report for test_replay_1 
-------------------------------------------------------------------------------- 
------------------- 

-------------------------------------------------------------------------- 
| DB Name | DB Id     | Release    | RAC | Replay Name   | Replay Status | 
-------------------------------------------------------------------------- 
| ORA11   | 753808255 | 11.2.0.4.0 | NO  | test_replay_1 | COMPLETED     | 
-------------------------------------------------------------------------- 

Replay Information 
-------------------------------------------------------------------------------- 
------------------- 
|  Information  | Replay                                 | Capture 
                  | 
-------------------------------------------------------------------------------- 
------------------- 
| Name          | test_replay_1                          | test_capture_1 
                  | 
-------------------------------------------------------------------------------- 
------------------- 
| Status        | COMPLETED                              | COMPLETED 
                  | 
-------------------------------------------------------------------------------- 
------------------- 
| Database Name | ORA11                                  | ORCL 
                  | 
-------------------------------------------------------------------------------- 
------------------- 
| Database      | 11.2.0.4.0                             | 10.2.0.5.0 
                  | 
| Version       |                                        | 
                  | 
-------------------------------------------------------------------------------- 
------------------- 
| Start Time    | 20-04-14 14:27:48                      | 20-04-14 13:47:58 
                  | 
-------------------------------------------------------------------------------- 
------------------- 
| End Time      | 20-04-14 14:28:07                      | 20-04-14 13:48:59 
                  | 
-------------------------------------------------------------------------------- 
------------------- 
| Duration      | 19 seconds                             | 1 minute 1 second 
                  | 
-------------------------------------------------------------------------------- 
------------------- 
| Directory     | DATA_PUMP_DIR                          | DATA_PUMP_DIR 
                  | 
| Object        |                                        | 
                  | 
-------------------------------------------------------------------------------- 
------------------- 
| Directory     | /oracle/app/oracle/admin/ora11/dpdump/ | /oracle/app/oracle/ad 
min/ora11/dpdump/ | 
| Path          |                                        | 
                  | 
-------------------------------------------------------------------------------- 
------------------- 

Replay Options 
--------------------------------------------------------- 
|       Option Name       | Value                       | 
--------------------------------------------------------- 
| Synchronization         | SCN                         | 
--------------------------------------------------------- 
| Connect Time            | 100%                        | 
--------------------------------------------------------- 
| Think Time              | 100%                        | 
--------------------------------------------------------- 
| Think Time Auto Correct | TRUE                        | 
--------------------------------------------------------- 
| Number of WRC Clients   | 1 (1 Completed, 0 Running ) | 
--------------------------------------------------------- 

Replay Statistics 
------------------------------------------------------------ 
|        Statistic        | Replay         | Capture       | 
------------------------------------------------------------ 
| DB Time                 |  0.451 seconds | 0.026 seconds | 
------------------------------------------------------------ 
| Average Active Sessions |            .02 |             0 | 
------------------------------------------------------------ 
| User calls              |             56 |            56 | 
------------------------------------------------------------ 
| Network Time            |  0.084 seconds |           N/A | 
------------------------------------------------------------ 
| Think Time              | 13.829 seconds |           N/A | 
------------------------------------------------------------ 

Replay Divergence Summary 
------------------------------------------------------------------- 
|                Divergence Type                | Count | % Total | 
------------------------------------------------------------------- 
| Session Failures During Replay                |     0 |    0.00 | 
------------------------------------------------------------------- 
| Errors No Longer Seen During Replay           |     0 |    0.00 | 
------------------------------------------------------------------- 
| New Errors Seen During Replay                 |     0 |    0.00 | 
------------------------------------------------------------------- 
| Errors Mutated During Replay                  |     0 |    0.00 | 
------------------------------------------------------------------- 
| DMLs with Different Number of Rows Modified   |     0 |    0.00 | 
------------------------------------------------------------------- 
| SELECTs with Different Number of Rows Fetched |     0 |    0.00 | 
------------------------------------------------------------------- 

-------------------------------------------------------------------------------- 
------------------- 
Workload Profile Top Events 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
Top Service/Module/Action 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
Top SQL with Top Events 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
Top Sessions with Top Events 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
Replay Divergence Session Failures By Application 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
Error Divergence By Application 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
By SQL 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
By Session 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
DML Data Divergence By Application 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
By SQL 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
By Divergence magnitude 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
SELECT Data Divergence By Application 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
By Divergence magnitude 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
Replay Clients Alerts 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 
Replay Filters 
-------------------------------------------------- 
| No data exists for this section of the report. | 
-------------------------------------------------- 

End of Report.

参考文档:Using Workload Capture and Replay in 11G (文档 ID 445116.1)

分享到: 更多