Blog

le 08/07/2011
Thomas Bordet
Archives blog-arkzoyd @fr

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 :

Thomas Bordet

Voir tous ses articles