notes/tiddlywiki/RAT example.md

Original article

Setup user workload

create user XIZOR identified by secret;
grant connect, resource to XIZOR;
grant unlimited tablespace to XIZOR;

connect XIZOR/secret

DROP TABLE test1 purge;
CREATE TABLE test1(id NUMBER, descr VARCHAR(50));

-- execute 4 times in order to generate 2.000.000 lines

DECLARE
  i NUMBER;
  nbrows NUMBER;
BEGIN
  SELECT NVL(MAX(id),0) INTO i FROM test1;
  i:=i+1;
  nbrows:=i+5000000;
  LOOP
    EXIT WHEN i>nbrows;
    INSERT INTO test1 VALUES(i, RPAD('A',49,'A'));
    i:=i+1;
  END LOOP;
  COMMIT;
END;
/

select count(*) from test1;

Setup capture environement

create or replace directory dbcapture AS '/home/oracle/rat';

Filter capture in order to catch only our user ations:

exec dbms_workload_capture.add_filter('XIZOR user','USER','XIZOR');


col type format a10
col status format a10
col name format a20
col attribute format a10
col value format a30
SET lines 150

SELECT type,status,name,attribute,value FROM dba_workload_filters;

Start capture

exec dbms_workload_capture.start_capture(name => 'XIZOR capture', dir => 'DBCAPTURE', default_action => 'EXCLUDE');


SET lines 150
col name FOR a20
col directory FOR a20
col status FOR a20
col filters_used FOR 999


SELECT id,name,directory,status,filters_used from DBA_WORKLOAD_CAPTURES;

Run user worlkoad

Run as XIZOR user:

SET serveroutput ON SIZE 999999
DECLARE
i NUMBER;
random_id NUMBER;
maxid NUMBER;
stmt VARCHAR2(100);
BEGIN
  SELECT NVL(MAX(id),0) INTO maxid FROM test1;
  FOR i IN 1..10 LOOP
    random_id:=ROUND(DBMS_RANDOM.VALUE(1,maxid));
    DBMS_OUTPUT.PUT_LINE('UPDATE test1 SET id=' || random_id || ' WHERE id=' || random_id || ';');
    UPDATE test1 SET id=random_id WHERE id=random_id;
  END LOOP;
  COMMIT;
END;
/

Finish capture

exec DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(timeout => 0, reason => 'Load test over');

Generate capture report:

SET lines 150
SET pagesize 1000
SET LONG 999999
SET longchunksize 150

select DBMS_WORKLOAD_CAPTURE.report(1,'HTML') from dual;

Prepare replay environement

exec DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE('DBCAPTURE'); 
exec DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(replay_name => 'XIZOR replay', replay_dir => 'DBCAPTURE'); 


SET lines 150
col name FOR a20
col directory FOR a20
col status FOR a20

select id,name,directory,status from dba_workload_replays;


exec DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY();

Calibrate replay:

wrc replaydir=/home/oracle/rat mode=calibrate

(optional) change structure

Before running the replay we will change the structure on database in order to generate a real difference between capture and replay scenario:

create index test1_idx_id ON test1(id);
exec dbms_stats.gather_table_stats('XIZOR','TEST1');

Replay

Create a user for RAT replay:

create user rat_user identified by secret;


create role rat_role;
grant create session to rat_role;
grant execute on dbms_workload_capture to rat_role;
grant execute on dbms_workload_replay to rat_role;
grant create session to rat_role;
grant create any directory to rat_role;
grant select_catalog_role to rat_role;
grant execute on dbms_workload_repository to rat_role;
grant administer sql tuning set to rat_role;
grant oem_advisor to rat_role;
grant create job to rat_role;
grant become user to rat_role;


grant rat_role to rat_user;

Start worker client:

wrc rat_user/secret replaydir=/home/oracle/rat

Start the replay:

exec DBMS_WORKLOAD_REPLAY.START_REPLAY();

Check replay status and wait until status is COMPLTED:

SET lines 150
col name FOR a20
col directory FOR a20
col status FOR a20

select id,name,directory,status from dba_workload_replays;

Generate reply report

Identify the ID of the replay and generate the report:

SET lines 150
SET pagesize 1000
SET LONG 999999
SET longchunksize 150

SELECT dbms_workload_replay.report(1,'HTML') FROM dual;