数据库重放测试

stonebox

Database Replay主要利用2个package: DBMS_WORKLOAD_CAPTURE 和 DBMS_WORKLOAD_REPLAY，分4个步骤：
1. 捕捉产品数据库的负载。
2. 预处理捕获的负载信息。
3. 重播数据库负载。
4. 创建报告并分析结果。



捕获：
mkdir /home/oracle/db_replay_capture
CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY db_replay_capture_dir AS '/home/oracle/db_replay_capture/';
BEGIN
  DBMS_WORKLOAD_CAPTURE.start_capture (name     => 'test_capture_1',
                                       dir      => 'DB_REPLAY_CAPTURE_DIR',
                                       duration => NULL);
END;
/
CREATE USER db_replay_test IDENTIFIED BY db_replay_test QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO db_replay_test;
CONN db_replay_test/db_replay_test
CREATE TABLE db_replay_test_tab (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT db_replay_test_tab_pk PRIMARY KEY (id)
);

BEGIN
  FOR i IN 1 .. 500000 LOOP
    INSERT INTO db_replay_test_tab (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
END;
/
CONN / AS SYSDBA
BEGIN
  DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
/
$ cd /home/oracle/db_replay_capture
$ ls
wcr_4f9rtgw00238y.rec  wcr_cr.html       wcr_scapture.wmd
wcr_4f9rtjw002397.rec  wcr_cr.text
wcr_4f9rtyw00239h.rec  wcr_fcapture.wmd
CONN / AS SYSDBA
SELECT DBMS_WORKLOAD_CAPTURE.get_capture_info('DB_REPLAY_CAPTURE_DIR') FROM   dual;
COLUMN name FORMAT A30
SELECT id, name FROM dba_workload_captures;
DECLARE
  l_report  CLOB;
BEGIN
  l_report := DBMS_WORKLOAD_CAPTURE.report(capture_id => 12,
                                           format     => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;
/
BEGIN
  DBMS_WORKLOAD_CAPTURE.export_awr (capture_id => 12);
END;
/
$ cd /home/oracle/db_replay_capture
$ ls
wcr_4f9rtgw00238y.rec  wcr_ca.dmp   wcr_cr.text
wcr_4f9rtjw002397.rec  wcr_ca.log   wcr_fcapture.wmd
wcr_4f9rtyw00239h.rec  wcr_cr.html  wcr_scapture.wmd


重放：
mkdir /home/oracle/db_replay_capture
CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY db_replay_capture_dir AS '/home/oracle/db_replay_capture/';
BEGIN
  DBMS_WORKLOAD_REPLAY.process_capture('DB_REPLAY_CAPTURE_DIR');
  DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'test_capture_1',
                                          replay_dir  => 'DB_REPLAY_CAPTURE_DIR');
  DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE);
END;
/
$ wrc mode=calibrate replaydir=/home/oracle/db_replay_capture
$ wrc system/123456 mode=replay replaydir=/home/oracle/db_replay_capture
CONN / AS SYSDBA
BEGIN
  DBMS_WORKLOAD_REPLAY.start_replay;
END;
/
SELECT table_name FROM dba_tables WHERE owner = 'DB_REPLAY_TEST';
SELECT COUNT(*) FROM db_replay_test.db_replay_test_tab;
COLUMN name FORMAT A30
SELECT id, name FROM dba_workload_replays;
DECLARE
  l_report  CLOB;
BEGIN
  l_report := DBMS_WORKLOAD_REPLAY.report(replay_id => 1,
                                          format     => DBMS_WORKLOAD_REPLAY.TYPE_HTML);
END;
/
set long 100000
select dbms_workload_replay.report(1,'TEXT') from dual;