Comparer Capture et Replay de Real Application Testing

Replongeons-nous, encore une fois, dans Oracle Database Replay et illustrons notamment la nouvelle possibilité offerte à partir de la version 11.2.0.2 de générer des SQL Tuning Set (STS) puis de les comparer… J’ai écrit les 2 premiers articles d’introduction à Real Application Testing de ce blog, il y a 4 ans déjà !. L’objectif était d’expliquer le B.A-BA à travers un exemple simple ; si vous n’avez jamais utilisé cette option d’Oracle, c’est toujours, un bon moyen de démarrer.

Il manquait, sans doute, un exemple plus riche… C’est l’objectif de ce nouvel article qui illustre notamment :

  • L’utilisation du référentiel AWR en simultané à DB Replay
  • Un cas simple de divergence entre la capture et le replay et la vue associée pour plus de détail
  • La constitution des rapports de comparaisons entre les tirs
  • La mise en oeuvre et la comparaison de plusieurs replays à partir de la même capture
  • La génération de SQL Tuning Set et leur comparaison entre les replays

Vous l’aurez compris, cet article s’adresse plutôt à des utilisateurs de niveau intermédiaire. Et de fait, ça donne un exemple assez long et sans doute moins facile à décrypter que si tout était expliqué pas à pas. Cela étant, pas besoin d’être un expert non plus ! Vous ferez facilement le lien avec la documentation. Le travail n’est pas mâché, c’est tout ;-). Et puis, si avec Enterprise Manager, utiliser Real Application Testing se résume à 40 ou 50 clics, utiliser l’API PL/SQL à ses avantages : l’ubiquité, une meilleure compréhension des mécanismes sous-jascents, la stabilité.

Enfin voilà, avant de vous laisser dépouiller l’exemple, le critiquer s’il manque encore des choses et éventuellement trouver une ou 2 typo, voici une dernière remarque. Real Application Testing, et notamment DB Replay, est un incroyable outil pour appréhender les mises à jour de l’infrastructure et toute une gamme d’évolutions techniques en quelques jours et avec une précision redoutable. Pourtant, au delà de l’outil, il vous faut de la méthode. Comprenez ses limites et utilisez d’autres raccourcis. Evaluez la représentativité de vos tests. Estimez les zones non-couvertes autrement. Quantifiez les améliorations avec précision. Gérez les risques de manière intelligente. Communiquez avec clarté pour ne pas créer des attentes trop grandes. Utilisez des dispositifs complémentaires ! avec méthode.

Introduction

Comme vous le découvrirez, cet exemple est livré brut. Posez des questions si vous le jugez nécessaire en laissant vos commentaires. La configuration utilisée s’appuie sur 2 bases de données installées sur le même serveur :

  • RED est une base de données et une instance Oracle 10.2.0.5. C’est la source de la capture. Du fait de la version, vous ne pouvez pas générer de STS lors de la capture
  • WHITE est la base de données et l’instance sur laquelle sont réalisés 2 replays. Il s’agit d’une version 11.2.0.2
  • La phase de constitution de WHITE à partir de RED est occulté dans cet article. La raison est que ce n’est pas la fonction de RAT mais de RMAN, Active Data Guard, Datapump ou ce que vous voulez. Vous le verrez, vous passerez simplement quelques commandes dans le schéma ARKZOYD ; la première fois pour créer un schéma identique ; la seconde pour corriger le schéma

Si vous voulez visualiser les rapports de comparaison générés dans cet exemple, vous trouverez un fichier qui contient tous ces rapports à cette URL.

Capture et export

Pour commencer, vous allez capturer une charge; le script ci-dessous met en oeuvre quelques commandes à cet effet dans le schéma ARKZOYD. Il exporte ensuite les éléments de capture et le référentiel AWR associé.

Note :
On ne capture ici que les ordres exécutés via l’utilisateur ARKZOYD du fait du paramètre default_action=>'EXCLUDE' de dbms_workload_capture.start_replay
La capture est supprimée de la base de données une fois les éléments collectés. N’adoptez pas cette approche dans un cas d’utilisation réelle

. oraenv
RED

sqlplus / as sysdba
startup

set tab off
create user arkzoyd
identified by arkzoyd
default tablespace users
temporary tablespace temp;

grant connect, resource, dba to arkzoyd;

connect arkzoyd/arkzoyd

create table gark(id number,
name varchar2(25) not null,
value number,
description varchar2(250));

create table mydb(dbname varchar2(80),
transactions number);

insert into gark(id, name, value, description)
select rownum,'SEQ1', rownum, rpad('X',200,'X') from dual connect by level <=5000;

insert into mydb
select name, 0 from v$database;

commit;

exec dbms_stats.gather_table_stats(user, 'GARK')
exec dbms_stats.gather_table_stats(user, 'MYDB')

connect / as sysdba

begin
dbms_workload_capture.add_filter(
fname=>'gark_filter1',
fattribute=>'USER',
fvalue=>'ARKZOYD');
end;
/

col type format a10
col id format 999
col status format a6
col name format a15
col attribute format a20
col value format a20
select *
from DBA_WORKLOAD_FILTERS;

TYPE ID STATUS NAME ATTRIBUTE VALUE
---------- ---- ------ --------------- -------------------- --------
CAPTURE 0 NEW GARK_FILTER1 USER ARKZOYD


!mkdir -p /u01/app/oracle/oradata/ORCL/capture

create directory capture_dir
as '/u01/app/oracle/oradata/ORCL/capture';

alter system set PRE_11G_ENABLE_CAPTURE=true scope=spfile;
startup force restrict
create restore point start_rat;

select name, scn from v$restore_point;

NAME SCN
--------------- ----------
START_RAT 8916865


BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (
name => 'gark_capture',
dir => 'CAPTURE_DIR',
duration => null,
default_action => 'EXCLUDE',
AUTO_UNRESTRICT=> true);
end;
/

col id format 9999
col NAME format a12
col DIRECTORY format a12
col ACTION format a10
col FILTS format 99
col CSIZ format 99999
col TRANS format 9999
select ID,
NAME,
DIRECTORY,
DEFAULT_ACTION ACTION,
FILTERS_USED FILS,
CAPTURE_SIZE CSIZE,
TRANSACTIONS TRANS
from DBA_WORKLOAD_CAPTURES;

ID NAME DIRECTORY ACTION FILS CSIZE TRANS
----- ------------ ------------ ---------- ---------- ---------- -----
111 gark_capture CAPTURE_DIR EXCLUDE 1 0 0


exec dbms_workload_repository.create_snapshot

exit

cat >execload1.sh <<EOF
sqlplus -s /nolog <<END
connect arkzoyd/arkzoyd@pink:1521/RED

declare
v_out number;
begin
for i in 1..50000 loop
select id into v_out
from gark
where id=100 for update;
commit;
end loop;
end;
/
exit
END
EOF

cat >execload2.sh <<EOF
sqlplus -s /nolog <<END
connect arkzoyd/arkzoyd@pink:1521/RED

exec dbms_application_info.set_module('ARKZOYD', 'Test')

declare
v_out number;
begin
for i in 1..50000 loop
select id into v_ out
from gark
where id=100 for update;
commit;
end loop;
end;
/
exit
END
EOF

cat >execload3.sh <<EOF
sqlplus -s /nolog <<END
connect arkzoyd/arkzoyd@pink:1521/RED

select count(*)
from mydb
where dbname='RED';

select *
from mydb
where dbname='RED';

exit
END
EOF

nohup time sh execload1.sh &
nohup time sh execload2.sh &
nohup time sh execload3.sh &
nohup time sh execload1.sh &
nohup time sh execload2.sh &
nohup time sh execload3.sh &
nohup time sh execload1.sh &
nohup time sh execload2.sh &
nohup time sh execload3.sh &
nohup time sh execload1.sh &
nohup time sh execload2.sh &
nohup time sh execload3.sh &
nohup time sh execload1.sh &
nohup time sh execload2.sh &
nohup time sh execload3.sh &

sqlplus / as sysdba

set tab off
col name format a15
select s.NAME,c.VALUE
from v$statname s, v$sesstat c
where c.sid in (select sid from v$session where username='ARKZOYD')
and c.STATISTIC#=s.STATISTIC#
and name='user commits'
order by 2;

NAME VALUE
--------------- ----------
user commits 1142
user commits 1164
user commits 1166
user commits 1198
user commits 1254
user commits 1314
user commits 1348
user commits 1431
user commits 1433
user commits 1708


exec dbms_workload_repository.create_snapshot

set tab off
col id format 9999 new_value id
col NAME format a12
col status format a12
col CSIZ format 99999
col TRANS format 999999999
select ID,
NAME,
STATUS,
CAPTURE_SIZE CSIZE,
TRANSACTIONS TRANS
from DBA_WORKLOAD_CAPTURES;

ID NAME STATUS CSIZE TRANS
---- ------------ ------------ ---------- ----------
111 gark_capture IN PROGRESS 31904297 500000


BEGIN
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE (
timeout => 0,
Reason => 'Capture finished OK');
END;
/

col id format 999 new_value id
col NAME format a12
col status format a12
col CSIZ format 99999
col TRANS format 999999999
select ID,
NAME,
STATUS,
CAPTURE_SIZE CSIZE,
TRANSACTIONS TRANS
from DBA_WORKLOAD_CAPTURES;

ID NAME STATUS CSIZE TRANS
---- ------------ ------------ ---------- ----------
111 gark_capture COMPLETED 31904297 500000


exec dbms_workload_capture.export_awr(&&id)

!find /u01/app/oracle/oradata/ORCL/capture -name "*.dmp"
/u01/app/oracle/oradata/ORCL/capture/wcr_ca.dmp

DECLARE
cap_id NUMBER;
cap_rpt CLOB;
fh UTL_FILE.FILE_TYPE;
buffer VARCHAR2(32767);
amount BINARY_INTEGER;
offset NUMBER(38);
BEGIN
cap_id := DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO(
dir => 'CAPTURE_DIR');

cap_rpt := DBMS_WORKLOAD_CAPTURE.REPORT(
capture_id => cap_id,
format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);

fh := UTL_FILE.FOPEN(
location => 'CAPTURE_DIR',
filename => 'capture.html',
open_mode => 'w',
max_linesize => 32767);

amount := 32767;
offset := 1;

WHILE amount >= 32767 LOOP
DBMS_LOB.READ(
lob_loc => cap_rpt,
amount => amount,
offset => offset,
buffer => buffer);

offset := offset + amount;

UTL_FILE.PUT(
file => fh,
buffer => buffer);

UTL_FILE.FFLUSH(file => fh);
END LOOP;
UTL_FILE.FCLOSE(file => fh);
END;
/

!cp -rp /u01/app/oracle/oradata/ORCL/capture /u01/app/oracle/oradata/ORCL/replay
!rm -rf /u01/app/oracle/oradata/ORCL/capture

exec dbms_workload_capture.delete_capture_info(&&id);
drop user arkzoyd cascade;
drop directory capture_dir;
drop restore point start_rat;
shutdown abort
exit

Premier cas de Replay

Cette seconde étape illustre un cas type de DB Replay en rejouant la charge à l’identique ; seule la version a changé. Vous verrez notamment (1) comment importer le référentiel AWR associé à la capture, (2) comment capturer un STS pendant l’exécution, (3) comment visualiser les SQL_ID des divergences entre les résultats constatés sur la base de capture et lors du replay ou (4) comment générer les rapports de comparaison entre capture et replay :

. oraenv
WHITE

sqlplus / as sysdba

startup

set tab off
create user arkzoyd
identified by arkzoyd
default tablespace users
temporary tablespace temp;

grant connect, resource, dba to arkzoyd;

connect arkzoyd/arkzoyd

create table gark(id number,
name varchar2(25) not null,
value number,
description varchar2(250));

create table mydb(dbname varchar2(80),
transactions number);

insert into gark(id, name, value, description)
select rownum,'SEQ1', rownum, rpad('X',200,'X') from dual connect by level <=5000;

insert into mydb
select name, 0 from v$database;

commit;

exec dbms_stats.gather_table_stats(user, 'GARK')
exec dbms_stats.gather_table_stats(user, 'MYDB')

connect / as sysdba

create directory replay_dir
as '/u01/app/oracle/oradata/ORCL/replay';


begin
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (
capture_dir =>'REPLAY_DIR');
end;
/

set tab off
col id format 9999 new_value id
col name format a15
col status format a10
col dbversion format a10
select id, name, dbversion, status, awr_dbid
from dba_workload_captures
where id in (select max(id) from dba_workload_captures);

ID NAME DBVERSION STATUS AWR_DBID
----- --------------- ---------- ---------- ----------
91 gark_capture 10.2.0.5.0 COMPLETED


var r number
exec :r:=dbms_workload_capture.import_awr(&&id,'ARKZOYD', true);

print r

R
----------
1593568648


BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (
replay_name => 'GREPLAY1',
replay_dir => 'REPLAY_DIR');
END;
/

col id format 99 new_value replay_id
col name format a8
col dir format a12
col status format a11
col secs format 9999
col cli format 99
col sync format a5
select id,
name,
DIRECTORY dir,
STATUS,
DURATION_SECS secs,
NUM_CLIENTS cli,
SYNCHRONIZATION sync
from dba_workload_replays;

ID NAME DIR STATUS SECS CLI SYNC
--- -------- ------------ ----------- ----- --- -----
81 GREPLAY1 REPLAY_DIR INITIALIZED 0 SCN


col CONN_ID format 99 new_value CONNECT_ID
col ORIG format a30 wor wra
col NEW format a30 wor wra
select CONN_ID,
substr(CAPTURE_CONN, 1, 30) ORIG,
substr(REPLAY_CONN,1,30) NEW
from DBA_WORKLOAD_CONNECTION_MAP
where replay_id=&REPLAY_ID;

CONN_ID ORIG NEW
------- ------------------------------ ------------------------------
1 (DESCRIPTION=(CONNECT_DATA=(SE


col tns_alias new_value tns_alias

select 'localhost:1521/WHITE' tns_alias
from dual;< br />
BEGIN
DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (
connection_id => &CONNECT_ID,
replay_connection => '&TNS_ALIAS');
END;
/

col CONN_ID format 99
col ORIG format a30 wor wra
col NEW format a30 wor wra
select CONN_ID,
substr(CAPTURE_CONN, 1, 30) ORIG,
substr(REPLAY_CONN,1,30) NEW
from DBA_WORKLOAD_CONNECTION_MAP
where replay_id=&REPLAY_ID;

CONN_ID ORIG NEW
------- ------------------------------ ------------------------------
1 (DESCRIPTION=(CONNECT_DATA=(SE localhost:1521/WHITE


BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (
synchronization => TRUE,
think_time_auto_correct => true,
capture_sts => true,
sts_cap_interval => 60
);
END;
/
exit

wrc system/manager
mode=calibrate
replaydir=/u01/app/oracle/oradata/ORCL/replay

Workload Replay Client: Release 11.2.0.2.0 - Production on Thu Jul 7 22:12:06 2011

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


Report for Workload in: /u01/app/oracle/oradata/ORCL/replay
-----------------------

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

Workload Characteristics:
- max concurrency: 15 sessions
- total number of sessions: 15

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


wrc system/manager
mode=replay
replaydir=/u01/app/oracle/oradata/ORCL/replay

Workload Replay Client: Release 11.2.0.2.0 - Production on Thu Jul 7 22:12:59 2011

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


Wait for the replay to start (22:12:59)

sqlplus / as sysdba

BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY ();
END;
/

col id format 99 new_value replay_id
col name format a8
col dir format a12
col status format a11
col secs format 9999
col cli format 99
col sync format a5
select id,
name,
DIRECTORY dir,
STATUS,
DURATION_SECS secs,
NUM_CLIENTS cli,
SYNCHRONIZATION sync
from dba_workload_replays;

ID NAME DIR STATUS SECS CLI SYNC
--- -------- ------------ ----------- ----- --- -----
81 GREPLAY1 REPLAY_DIR IN PROGRESS 1 SCN


set tab off
col name format a15
select s.NAME,c.VALUE
from v$statname s, v$sesstat c
where c.sid in (select sid from v$session where username='ARKZOYD')
and c.STATISTIC#=s.STATISTIC#
and name='user commits'
order by 2;

NAME VALUE
--------------- ----------
user commits 7178
user commits 7622
user commits 7817
user commits 7961
user commits 7989
user commits 8361
user commits 8644
user commits 8644
user commits 9450
user commits 10114


col id format 99
col name format a8
col dir format a12
col status format a11
col secs format 9999
col cli format 99
col sync format a5
select id,
name,
to_char(start_time,'HH24:MI:SS') START_DATE,
to_char(END_time,'HH24:MI:SS') END_DATE,
STATUS,
DURATION_SECS secs,
NUM_CLIENTS cli,
SYNCHRONIZATION sync
from dba_workload_replays
where id=&REPLAY_ID;

ID NAME START_DA END_DATE STATUS SECS CLI SYNC
--- -------- -------- -------- ----------- ----- --- -----
81 GREPLAY1 22:13:37 22:19:45 COMPLETED 368 1 SCN


set tab off
col sql_id format a15 new_value sql_id
col STREAM_ID format 999999999999999999999
select IS_QUERY_DATA_DIVERGENCE,
IS_DML_DATA_DIVERGENCE,
IS_ERROR_DIVERGENCE,
IS_THREAD_FAILURE,
STREAM_ID,
CALL_COUNTER,
SQL_ID
from DBA_WORKLOAD_REPLAY_DIVERGENCE;

I I I I STREAM_ID CALL_COUNTER SQL_ID
- - - - ---------------------- ------------ -------------
Y N N N 1587719529520168964 21 9mg7c4chxpjcw
Y N N N 1587721728543424521 21 9mg7c4chxpjcw
Y N N N 1587721728543424522 21 9mg7c4chxpjcw
Y N N N 1587721728543424526 21 9mg7c4chxpjcw
Y N N N 1587721728543424528 21 9mg7c4chxpjcw


col id format 99
col name format a8
col dir format a12
col status format a11
col secs format 9999
col cli format 99
col sync format a5
col sqlset format a25 new_value sqlset
select id,
name,
to_char(start_time,'HH24:MI:SS') START_DATE,
to_char(END_time,'HH24:MI:SS') END_DATE,
STATUS,
DURATION_SECS secs,
SQLSET_NAME SQLSET
from dba_workload_replays
where id=&REPLAY_ID;

ID NAME START_DA END_DATE STATUS SECS SQLSET
--- -------- -------- -------- ----------- ----- -------------------------
81 GREPLAY1 22:13:37 22:19:45 COMPLETED 368 GREPLAY1_r_8131606


select * 
from table(dbms_xplan.DISPLAY_SQLSET('&&sqlset','&&sql_id', format=>'basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------------
SQL Tuning Set Name: GREPLAY1_r_8131606
SQL Tuning Set Owner: SYS
SQL_ID: 9mg7c4chxpjcw
SQL Text: select * from mydb where dbname='RED'
----------------------------------------------------

Plan hash value: 1139332635

----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| MYDB |
----------------------------------


DECLARE
cap_id NUMBER;
rep_id NUMBER;
rep_rpt CLOB;
fh UTL_FILE.FILE_TYPE;
buffer VARCHAR2(32767);
amount BINARY_INTEGER;
offset NUMBER(38);
BEGIN
cap_id := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(
dir => 'REPLAY_DIR');

/* Get the latest replay for that capture */
SELECT max(id)
INTO rep_id
FROM dba_workload_replays
WHERE capture_id = cap_id;

rep_rpt := DBMS_WORKLOAD_REPLAY.REPORT(
replay_id => rep_id,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML);

fh := UTL_FILE.FOPEN(
location => 'REPLAY_DIR',
filename => 'replay1.html',
open_mode => 'w',
max_linesize => 32767);

amount := 32767;
offset := 1;

WHILE amount >= 32767 LOOP
DBMS_LOB.READ(
lob_loc => rep_rpt,
amount => amount,
offset => offset,
buffer => buffer);

offset := offset + amount;
UTL_FILE.PUT(
file => fh,
buffer => buffer);

UTL_FILE.FFLUSH(file => fh);
END LOOP;
UTL_FILE.FCLOSE(file => fh);
END;
/

DECLARE
cap_id NUMBER;
rep_id NUMBER;
rep_rpt CLOB;
fh UTL_FILE.FILE_TYPE;
buffer VARCHAR2(32767);
amount BINARY_INTEGER;
offset NUMBER(38);
BEGIN
cap_id := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(
dir => 'REPLAY_DIR');

/* Get the latest replay for that capture */
SELECT max(id)
INTO rep_id
FROM dba_work load_replays
WHERE capture_id = cap_id;

DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT (
replay_id1 => rep_id,
replay_id2 => null,
format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML ,
result => rep_rpt );

fh := UTL_FILE.FOPEN(
location => 'REPLAY_DIR',
filename => 'compare1.html',
open_mode => 'w',
max_linesize => 32767);

amount := 32767;
offset := 1;

WHILE amount >= 32767 LOOP
DBMS_LOB.READ(
lob_loc => rep_rpt,
amount => amount,
offset => offset,
buffer => buffer);

offset := offset + amount;
UTL_FILE.PUT(
file => fh,
buffer => buffer);

UTL_FILE.FFLUSH(file => fh);
END LOOP;
UTL_FILE.FCLOSE(file => fh);
END;
/

col id new_value replay_id
SELECT ID,
NAME,
DURATION_SECS,
STATUS
FROM DBA_WORKLOAD_REPLAYS;

ID NAME DURATION_SECS STATUS
--- -------- ------------- -----------
81 GREPLAY1 368 COMPLETED

Second cas de Replay

Cette troisième étape consiste à corriger le schéma en ajoutant une contraite et en modifiant une donnée avant de rejouer une seconde fois la charge. Outre les opérations précédentes, cette étape illustre comment comparer les 2 exécutions à l’aide du rapport adéquat et comment générer le rapport de comparaison des SQL Tuning Set :

BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (
replay_name => 'GREPLAY1',
replay_dir => 'REPLAY_DIR');
END;
/


col id format 99 new_value replay_id
col name format a8
col dir format a12
col status format a11
col secs format 9999
col cli format 99
col sync format a5
select id,
name,
DIRECTORY dir,
STATUS,
DURATION_SECS secs,
NUM_CLIENTS cli,
SYNCHRONIZATION sync
from dba_workload_replays;

ID NAME DIR STATUS SECS CLI SYNC
--- -------- ------------ ----------- ----- --- -----
81 GREPLAY1 REPLAY_DIR COMPLETED 368 1 SCN
88 GREPLAY1 REPLAY_DIR INITIALIZED 0 SCN



col CONN_ID format 99 new_value CONNECT_ID
col ORIG format a30 wor wra
col NEW format a30 wor wra
select CONN_ID,
substr(CAPTURE_CONN, 1, 30) ORIG,
substr(REPLAY_CONN,1,30) NEW
from DBA_WORKLOAD_CONNECTION_MAP
where replay_id=&REPLAY_ID;

CONN_ID ORIG NEW
------- ------------------------------ ------------------------------
1 (DESCRIPTION=(CONNECT_DATA=(SE


col tns_alias new_value tns_alias

select 'localhost:1521/WHITE' tns_alias
from dual;

BEGIN
DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (
connection_id => &CONNECT_ID,
replay_connection => '&TNS_ALIAS');
END;
/

col CONN_ID format 99
col ORIG format a30 wor wra
col NEW format a30 wor wra
select CONN_ID,
substr(CAPTURE_CONN, 1, 30) ORIG,
substr(REPLAY_CONN,1,30) NEW
from DBA_WORKLOAD_CONNECTION_MAP
where replay_id=&REPLAY_ID;

CONN_ID ORIG NEW
------- ------------------------------ ------------------------------
1 (DESCRIPTION=(CONNECT_DATA=(SE localhost:1521/WHITE


connect arkzoyd/arkzoyd

alter table gark add constraint gark_pk primary key (id);

update mydb set dbname='RED';

commit;

connect / as sysdba

BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (
synchronization => TRUE,
think_time_auto_correct => true,
capture_sts => true,
sts_cap_interval => 60
);
END;
/
exit

wrc system/manager
mode=replay
replaydir=/u01/app/oracle/oradata/ORCL/replay

Workload Replay Client: Release 11.2.0.2.0 - Production on Thu Jul 7 22:56:42 2011

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


Wait for the replay to start (22:56:42)

sqlplus / as sysdba

BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY ();
END;
/

col id format 99 new_value replay_id
col name format a8
col dir format a12
col status format a11
col secs format 9999
col cli format 99
col sync format a5
select id,
name,
DIRECTORY dir,
STATUS,
DURATION_SECS secs,
NUM_CLIENTS cli,
SYNCHRONIZATION sync
from dba_workload_replays;

ID NAME DIR STATUS SECS CLI SYNC
--- -------- ------------ ----------- ----- --- -----
81 GREPLAY1 REPLAY_DIR COMPLETED 368 1 SCN
88 GREPLAY1 REPLAY_DIR IN PROGRESS 1 SCN


set tab off
col name format a15
select s.NAME,c.VALUE
from v$statname s, v$sesstat c
where c.sid in (select sid from v$session where username='ARKZOYD')
and c.STATISTIC#=s.STATISTIC#
and name='user commits'
order by 2;

NAME VALUE
--------------- ----------
user commits 10433
user commits 10624
user commits 10679
user commits 10724
user commits 10834
user commits 10880
user commits 11049
user commits 11326
user commits 11360
user commits 11691


set tab off
col id format 99
col name format a8
col dir format a12
col status format a11
col secs format 9999
col cli format 99
col sync format a5
select id,
name,
to_char(start_time,'HH24:MI:SS') START_DATE,
to_char(END_time,'HH24:MI:SS') END_DATE,
STATUS,
DURATION_SECS secs,
NUM_CLIENTS cli,
SYNCHRONIZATION sync
from dba_workload_replays
where id=&REPLAY_ID;

ID NAME START_DA END_DATE STATUS SECS CLI SYNC
--- -------- -------- -------- ----------- ----- --- -----
88 GREPLAY1 22:57:08 22:58:56 COMPLETED 108 1 SCN


set tab off
col sql_id format a15 new_value sql_id
col STREAM_ID format 999999999999999999999
select IS_QUERY_DATA_DIVERGENCE,
IS_DML_DATA_DIVERGENCE,
IS_ERROR_DIVERGENCE,
IS_THREAD_FAILURE,
STREAM_ID,
CALL_COUNTER,
SQL_ID
from DBA_WORKLOAD_REPLAY_DIVERGENCE
where replay_id=&&replay_id;

no rows selected

DECLARE
cap_id NUMBER;
rep_id NUMBER;
rep_rpt CLOB;
fh UTL_FILE.FILE_TYPE;
buffer VARCHAR2(32767);
amount BINARY_INTEGER;
offset NUMBER(38);
BEGIN
cap_id := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(
dir => 'REPLAY_DIR');

/* Get the latest replay for that capture */
SELECT max(id)
INTO rep_id
FROM dba_workload_replays
WHERE capture_id = cap_id;

rep_rpt := DBMS_WORKLOAD_REPLAY.REPORT(
replay_id => rep_id,
format => DBMS_WORKLOAD_REPLAY.TYPE_HTML);

fh := UTL_FILE.FOPEN(
location => 'REPLAY_DIR',
filename => 'replay2.html',
open_mode => 'w',
max_linesize => 32767);

amount := 32767;
offset := 1;

WHILE amount >= 32767 LOOP
DBMS_LOB.READ(
lob_loc => rep_rpt,
amount => amount,
offset => offset,
buffer => buffer);

offset := offset + amount;
UTL_FILE.PUT(
file => fh,
buffer => buffer);

UTL_FILE.FFLUSH(file => fh);
END LOOP;
UTL_FILE.FCLOSE(file => fh);
END;
/

DECLARE
cap_id NUMBER;
min_id NUMBER;
rep_id NUM BER;
rep_rpt CLOB;
fh UTL_FILE.FILE_TYPE;
buffer VARCHAR2(32767);
amount BINARY_INTEGER;
offset NUMBER(38);
BEGIN
cap_id := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(
dir => 'REPLAY_DIR');

/* Get the latest replay for that capture */
SELECT max(id)
INTO rep_id
FROM dba_workload_replays
WHERE capture_id = cap_id;

SELECT min(id)
INTO min_id
FROM dba_workload_replays
WHERE capture_id = cap_id;

DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT (
replay_id1 => min_id,
replay_id2 => rep_id,
format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML ,
result => rep_rpt );

fh := UTL_FILE.FOPEN(
location => 'REPLAY_DIR',
filename => 'compare2.html',
open_mode => 'w',
max_linesize => 32767);

amount := 32767;
offset := 1;

WHILE amount >= 32767 LOOP
DBMS_LOB.READ(
lob_loc => rep_rpt,
amount => amount,
offset => offset,
buffer => buffer);

offset := offset + amount;
UTL_FILE.PUT(
file => fh,
buffer => buffer);

UTL_FILE.FFLUSH(file => fh);
END LOOP;
UTL_FILE.FCLOSE(file => fh);
END;
/


set serveroutput on
DECLARE
cap_id NUMBER;
min_id NUMBER;
rep_id NUMBER;
rep_rpt CLOB;
out varchar2(80);
fh UTL_FILE.FILE_TYPE;
buffer VARCHAR2(32767);
amount BINARY_INTEGER;
offset NUMBER(38);
BEGIN
cap_id := DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(
dir => 'REPLAY_DIR');

/* Get the latest replay for that capture */
SELECT max(id)
INTO rep_id
FROM dba_workload_replays
WHERE capture_id = cap_id;

SELECT min(id)
INTO min_id
FROM dba_workload_replays
WHERE capture_id = cap_id;

out:=DBMS_WORKLOAD_REPLAY.COMPARE_SQLSET_REPORT (
replay_id1 => min_id,
replay_id2 => rep_id,
format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML ,
result => rep_rpt );

dbms_output.put_line(out);

fh := UTL_FILE.FOPEN(
location => 'REPLAY_DIR',
filename => 'compare_sts.html',
open_mode => 'w',
max_linesize => 32767);

amount := 32767;
offset := 1;

WHILE amount >= 32767 LOOP
DBMS_LOB.READ(
lob_loc => rep_rpt,
amount => amount,
offset => offset,
buffer => buffer);

offset := offset + amount;
UTL_FILE.PUT(
file => fh,
buffer => buffer);

UTL_FILE.FFLUSH(file => fh);
END LOOP;
UTL_FILE.FCLOSE(file => fh);
END;
/

TASK_330

Voici une copie d’écran qui illustre le rapport de comparaison des SQL Tuning Set :

Supprimer capture et replays de la base

Pour rejouer plusieurs fois l’opération, vous supprimerez la capture et les replays de la base de destination comme ci-dessous :

set tab off
col id format 9999 new_value id
col NAME format a12
col status format a12
col CSIZ format 99999
col TRANS format 999999999
select ID,
NAME,
STATUS,
CAPTURE_SIZE CSIZE,
TRANSACTIONS TRANS
from DBA_WORKLOAD_CAPTURES;

exec dbms_workload_capture.delete_capture_info(&&id);

col id format 99 new_value replay_id
col name format a8
col dir format a12
col status format a11
col secs format 9999
col cli format 99
col sync format a5
select id,
name,
DIRECTORY dir,
STATUS,
DURATION_SECS secs,
NUM_CLIENTS cli,
SYNCHRONIZATION sync
from dba_workload_replays;

BEGIN
DBMS_WORKLOAD_REPLAY.DELETE_REPLAY_INFO(
REPLAY_ID=>&replay_id);
END;
/

col id format 99 new_value replay_id
col name format a8
col dir format a12
col status format a11
col secs format 9999
col cli format 99
col sync format a5
select id,
name,
DIRECTORY dir,
STATUS,
DURATION_SECS secs,
NUM_CLIENTS cli,
SYNCHRONIZATION sync
from dba_workload_replays;

BEGIN
DBMS_WORKLOAD_REPLAY.DELETE_REPLAY_INFO(
REPLAY_ID=>&replay_id);
END;
/

drop user arkzoyd cascade;
drop directory replay_dir;
shutdown abort
!rm -rf /u01/app/oracle/oradata/ORCL/replay
exit;

Référence :

Pour avancez encore plus, lisez :