一、 执行流程
1.1 Replay 流程
Database Replay 实施的流程是workload_capture->workload preprocess->replay client prepare->replay->generate replay report，对于同一个capture可以进行多次replay
1.2 执行数据库
生产数据库：用于workload_capture，即负载捕获
迁移数据库：用于workload_ preprocess和replay，即负载预处理和负载重放
Replay client ：用于发起workload的客户端进程
二、生产数据库
2.1 创建 capture 目录
需要确保目录下无任何文件（目录rac各节点都要建），目录用来存放capture数据
mkdir -p /home/oracle/capdir
create or replace directory capdir as '/home/oracle/capdir';
2.2 创建过滤器（可以不用）
过滤器可以通过START_CAPTURE 过程的DEFAULT_ACTION 参数，参数值 INCLUDE 为排除过滤器 或者 EXCLUDE 为包含过滤器 。
创建TPCC用户执行信息的过滤器：
EXEC DBMS_WORKLOAD_CAPTURE.ADD_FILTER ('filter_user1', 'USER', 'TPCC');
过滤器其他方式：
exec dbms_workload_capture.ADD_FILTER( fname IN VARCHAR2, fattribute IN VARCHAR2,fvalueIN VARCHAR2);
Ø
fattribute:
· INSTANCE_NUMBER - type NUMBER
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%');
capture 所有的信息，可以跳过过滤器设置
查询过滤器
select * from dba_workload_filters;
删除过滤器
exec DBMS_WORKLOAD_CAPTURE.DELETE_FILTER( 'filter_user1' );
2.3 运行 Capture
10g 数据库需要开启PRE_11G_ENABLE_CAPTURE
alter system set PRE_11G_ENABLE_CAPTURE=true;
函数：
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (
name IN VARCHAR2,
dir IN VARCHAR2,
duration IN NUMBER DEFAULT NULL,
default_action IN VARCHAR2 DEFAULT 'INCLUDE',
auto_unrestrict IN BOOLEAN DEFAULT TRUE,
capture_sts IN BOOLEAN DEFAULT FALSE,
sts_cap_interval IN NUMBER DEFAULT 300);
注意：过滤器默认是排除，如果需要包含过滤器内容，default_action值需要设置为 EXCLUDE 。
Duration 单位为秒，设置为NULL时，则captureprocess 必须手工调用FINISH_CAPTURE 过程来结束。
|
BEGIN DBMS_WORKLOAD_CAPTURE.start_capture (name => 'test_capture_1' , dir => 'CAPDIR' , default_action=>'EXCLUDE' duration => NULL); END; / |
下面capture运行10分钟，不设置执行时间，可以手工进行停止。
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (
name => 'test_capture_1' ,
dir => 'CAPDIR' ,
duration => 600);
END;
/
2.4 Sql 执行
生产数据库运行阶段。
2.5 停止 capture
手动停止capture
|
BEGIN DBMS_WORKLOAD_CAPTURE.finish_capture; END; / |
2.6 生成文件
capture 捕获生成的文件如下：
|
$ ls -l total 18252 -rw-r--r--. 1 oracle oinstall 9294509 Feb 25 11:06 wcr_5wp4fn4002vhm.rec -rw-r--r--. 1 oracle oinstall 9308639 Feb 25 11:06 wcr_5wp4fnc002vhp.rec -rw-r--r--. 1 oracle oinstall 6495 Feb 25 11:06 wcr_5wp4fnn002vhr.rec -rw-r--r--. 1 oracle oinstall 1239 Feb 25 11:06 wcr_5wp4g8w002v4q.rec -rw-r--r--. 1 oracle oinstall 40590 Feb 25 11:06 wcr_cr.html -rw-r--r--. 1 oracle oinstall 20386 Feb 25 11:06 wcr_cr.text -rw-r--r--. 1 oracle oinstall 168 Feb 25 11:06 wcr_fcapture.wmd -rw-r--r--. 1 oracle oinstall 98 Feb 25 11:03 wcr_scapture.wmd |
2.7 获取 capture ID
SELECT DBMS_WORKLOAD_CAPTURE.get_capture_info('CAPDIR') FROM dual;
select id, name, status,dir_path from dba_workload_captures where id = (select max(id) from dba_workload_captures);
|
SQL> select id, name, status,dir_path from dba_workload_captures where id = (select max(id) from dba_workload_captures); ID NAME STATUS DIR_PATH ----- ---------------- --------------- ------------------------- 2 test_capture_1 COMPLETED /home/oracle/capdir |
2.8 查询捕获信息
可以在捕获路径下获取已生成的报告wcr_cr.html、wcr_cr.text。
select dbms_workload_capture.report ( 2,'TEXT') from dual;
|
SQL> set pagesize 0 long 30000000 longchunksize 2000 SQL> select dbms_workload_capture.report ( 4,'TEXT') from dual;
Database Capture Report For ORCL
DB Name DB Id Release RAC Capture Name Status ------------ ----------- ----------- --- -------------------------- ---------- ORCL 1560568364 10.2.0.4.0 NO test_capture_1 COMPLETED
Start time: 25-Feb-20 16:59:45 (SCN = 869461) End time: 25-Feb-20 17:08:58 (SCN = 905073) Duration: 9 minutes 13 seconds Capture size: 49.15 KB Directory object: CAPDIR Directory path: /home/oracle/capdir Directory shared in RAC: TRUE Filters used: 1 EXCLUSION filter
Captured Workload Statistics DB/Inst: ORCL/ Snaps: 10-11 -> '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) 1.77 1.50 Average Active Sessions 0.00 N/A User calls captured 166 1.41 User calls captured with Errors 1 N/A Session logins 10 83.33 Transactions 0 0.00 -------------------------------------------------------------
Top Events Captured DB/Inst: ORCL/ Snaps: 10-11
No data exists for this section of the report. -------------------------------------------------------------
Top Service/Module Captured DB/Inst: ORCL/ Snaps: 10-11
No data exists for this section of the report. -------------------------------------------------------------
Top SQL Captured DB/Inst: ORCL/ Snaps: 10-11
No data exists for this section of the report. -------------------------------------------------------------
Top Sessions Captured DB/Inst: ORCL/ Snaps: 10-11
No data exists for this section of the report. -------------------------------------------------------------
Top Events containing Unreplayable Calls DB/Inst: ORCL/ Snaps: 10-11
No data exists for this section of the report. -------------------------------------------------------------
Top Service/Module containing Unreplayable Calls DB/Inst: ORCL/ Snaps: 10-11
No data exists for this section of the report. -------------------------------------------------------------
Top SQL containing Unreplayable Calls DB/Inst: ORCL/ Snaps: 10-11
No data exists for this section of the report. -------------------------------------------------------------
Top Sessions containing Unreplayable Calls DB/Inst: ORCL/ Snaps: 10-11
No data exists for this section of the report. -------------------------------------------------------------
Top Events Filtered Out DB/Inst: ORCL/ Snaps: 10-11
Avg Active Event Event Class % Activity Sessions ----------------------------------- --------------- ---------- ---------- log file sync Commit 34.78 0.13 CPU + Wait for CPU CPU 20.77 0.08 -------------------------------------------------------------
Top Service/Module Filtered Out DB/Inst: ORCL/ Snaps: 10-11
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
orcl wish8.6@oracle12c (TNS V 55.56 UNNAMED 55.56
-------------------------------------------------------------
Top SQL Filtered Out DB/Inst: ORCL/ Snaps: 10-11
SQL ID % Activity Event % Event ----------------------- -------------- ------------------------------ ------- 8yvup05pk06ca 3.38 CPU + Wait for CPU 2.90 SELECT S_QUANTITY, S_DATA, S_DIST_01, S_DIST_02, S_DIST_03, S_DIST_04, S_DIST_05
, S_DIST_06, S_DIST_07, S_DIST_08, S_DIST_09, S_DIST_10 FROM STOCK WHERE S_I_ID
= :B2 AND S_W_ID = :B1
88fgqncchy6wg 2.90 CPU + Wait for CPU 2.90 SELECT I_PRICE, I_NAME, I_DATA FROM ITEM WHERE I_ID = :B1
dqyz792jar7w0 2.90 CPU + Wait for CPU 2.90 INSERT INTO ORDER_LINE (OL_O_ID, OL_D_ID, OL_W_ID, OL_NUMBER, OL_I_ID, OL_SUPPLY
_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DIST_INFO) VALUES (:B9 , :B8 , :B7 , :B6 , :B5
, :B4 , :B3 , :B2 , :B1 )
82tfppq8s0dc2 2.42 CPU + Wait for CPU 2.42 UPDATE STOCK SET S_QUANTITY = :B3 WHERE S_I_ID = :B2 AND S_W_ID = :B1
g5u7xuchhfu62 1.93 CPU + Wait for CPU 1.93 UPDATE WAREHOUSE SET W_YTD = W_YTD + :B2 WHERE W_ID = :B1
-------------------------------------------------------------
Top Sessions Filtered Out DB/Inst: ORCL/ Snaps: 10-11 -> '# Samples Active' shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity. -> 'XIDs' shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event -> For sessions running Parallel Queries, this section will NOT aggregate the PQ slave activity into the session issuing the PQ. Refer to the 'Top Sessions running PQs' section for such statistics.
Sid, Serial# % Activity Event % Event --------------- ---------- ------------------------------ ---------- User Program # Samples Active XIDs -------------------- ------------------------------ ------------------ -------- 1643, 31 28.02 log file sync 18.36 TPCC wish8.6@oracle12c (TNS V1-V3) 38/553 [ 7%] 0
CPU + Wait for CPU 9.18 19/553 [ 3%] 11
1635, 26 27.54 log file sync 16.43 TPCC wish8.6@oracle12c (TNS V1-V3) 34/553 [ 6%] 0
CPU + Wait for CPU 11.11 23/553 [ 4%] 21
-------------------------------------------------------------
Top Events (Jobs and Background Activity) DB/Inst: ORCL/ Snaps: 10-11
Avg Active Event Event Class % Activity Sessions ----------------------------------- --------------- ---------- ---------- log file parallel write System I/O 26.09 0.10 CPU + Wait for CPU CPU 8.70 0.03 control file parallel write System I/O 4.35 0.02 log file sequential read System I/O 3.38 0.01 -------------------------------------------------------------
Top Service/Module (Jobs and Background Activity) DB/Inst: ORCL/ Snaps: 10-11
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$BACKGROUND UNNAMED 43.96 UNNAMED 43.96
-------------------------------------------------------------
Top SQL (Jobs and Background Activity) DB/Inst: ORCL/ Snaps: 10-11
No data exists for this section of the report. -------------------------------------------------------------
Top Sessions (Jobs and Background Activity) DB/Inst: ORCL/ Snaps: 10-11 -> '# Samples Active' shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity. -> 'XIDs' shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event -> For sessions running Parallel Queries, this section will NOT aggregate the PQ slave activity into the session issuing the PQ. Refer to the 'Top Sessions running PQs' section for such statistics.
Sid, Serial# % Activity Event % Event --------------- ---------- ------------------------------ ---------- User Program # Samples Active XIDs -------------------- ------------------------------ ------------------ -------- 1651, 1 26.09 log file parallel write 26.09 SYS oracle@oracle11g (LGWR) 54/553 [ 10%] 0
1641, 1 6.76 log file sequential read 3.38 SYS oracle@oracle11g (ARC0) 7/553 [ 1%] 0
CPU + Wait for CPU 2.42 5/553 [ 1%] 0
1652, 1 6.28 CPU + Wait for CPU 6.28 SYS oracle@oracle11g (DBW0) 13/553 [ 2%] 0
1650, 1 4.35 control file parallel write 4.35 SYS oracle@oracle11g (CKPT) 9/553 [ 2%] 0
-------------------------------------------------------------
Workload Filters DB/Inst: ORCL/ Snaps: 10-11
# Filter Name Type Attribute Value --- ------------------------ ------- ------------ -------------------------- 1 FILTER_USER1 EXCLUDE USER TPCC -------------------------------------------------------------
End of Report |
2.9 导出 awr 报告
exec dbms_workload_capture.export_awr (capture_id => 2);
导出的awr报告可以导入到目标库上，用来做对比，生成compare period report需要。
|
$ ls -l total 25656 -rw-r--r--. 1 oracle oinstall 9294509 Feb 25 11:06 wcr_5wp4fn4002vhm.rec -rw-r--r--. 1 oracle oinstall 9308639 Feb 25 11:06 wcr_5wp4fnc002vhp.rec -rw-r--r--. 1 oracle oinstall 6495 Feb 25 11:06 wcr_5wp4fnn002vhr.rec -rw-r--r--. 1 oracle oinstall 1239 Feb 25 11:06 wcr_5wp4g8w002v4q.rec -rw-r-----. 1 oracle oinstall 7565312 Feb 25 11:17 wcr_ca.dmp -rw-r--r--. 1 oracle oinstall 10926 Feb 25 11:17 wcr_ca.log -rw-r--r--. 1 oracle oinstall 40590 Feb 25 11:06 wcr_cr.html -rw-r--r--. 1 oracle oinstall 20386 Feb 25 11:06 wcr_cr.text -rw-r--r--. 1 oracle oinstall 168 Feb 25 11:06 wcr_fcapture.wmd -rw-r--r--. 1 oracle oinstall 98 Feb 25 11:03 wcr_scapture.wmd |
2.10 查询 dba_workload_captures
可以获取到 capture ID 和 capture 的 snap 信息
select id, name from dba_workload_captures;
|
SQL> select id,AWR_BEGIN_SNAP,AWR_END_SNAP from dba_workload_captures; 4 10 11 |
