Un des aspects associés à la mise en oeuvre d’Oracle Database Replay est la nécessité de reconstruire l’environnement avant chaque « replay ». La plupart des méthodes de rafraichissement supprime le jeu de résultats obtenus lors de l’exécution précédente. Bref, peu importe que vous utilisiez RMAN, Flashback Database ou des Flexclones, n’oubliez pas d’exporter et de réimporter les résultats de vos captures et replay avant chaque exécution pour comparer vos résultats successifs.
Cet article reprend l’exemple présenté dans Comparer Capture et Replay de Real Application Testing avec quelques variations pour illustrer les opérations d’export/import nécessaires. Vous noterez notamment que :
- les opérations de capture et replay sont effectuées sur la même base de données
WHITE11.2.0.2 ce qui permet de capturer les SQL Tuning Set - Les rafraichissements sont réalisés avec Flashback Database et, évidemment, vous utiliserez la méthode de votre choix !
Schéma d’exemple
Pour commencer, nous allons créer un schéma d’exemple et préparer la base de données pour la capture :
. 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
startup force restrict;
create restore point START_CAPTURE;
Capturer la charge
On suppose ici que la base de données utilise ses flashback logs et archivelogs. La seconde étape consiste à capturer la charge d’activité en incluant un SQL Tuning Set :
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 name, attribute, value
from DBA_WORKLOAD_FILTERS;
NAME ATTRIBUTE VALUE
--------------- -------------------- -------
GARK_FILTER1 USER ARKZOYD
!mkdir -p /u01/app/oracle/oradata/WHITE/capture
create directory capture_dir
as '/u01/app/oracle/oradata/WHITE/capture';
BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (
name => 'gark_capture',
dir => 'CAPTURE_DIR',
duration => null,
default_action => 'EXCLUDE',
AUTO_UNRESTRICT => true,
capture_sts => true,
sts_cap_interval=> 10);
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/WHITE
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/WHITE
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/WHITE
select count(*)
from mydb
where dbname='WHITE';
select *
from mydb
where dbname='WHITE';
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
exec dbms_workload_repository.create_snapshot
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 39965
user commits 40400
user commits 41149
user commits 41471
user commits 42065
user commits 43365
user commits 43432
user commits 43723
user commits 45333
user commits 46561
10 rows selected.
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
----- ------------ ------------ ---------- ----------
131 gark_capture IN PROGRESS 31909486 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
col sqlset format a30
select ID,
NAME,
STATUS,
CAPTURE_SIZE CSIZE,
TRANSACTIONS TRANS,
sqlset_owner||'.'||sqlset_name sqlset
from DBA_WORKLOAD_CAPTURES;
ID NAME STATUS CSIZE TRANS SQLSET
---- ------------ ------------ ---------- ---------- ---------------------------
131 gark_capture COMPLETED 31909524 500000 SYS.gark_capture_c_14175460
Exporter vos données de capture
La procé
dure pour permettre d’exporter toutes les informations résultantes de la capture dbms_workload_capture.export_awr ; elle extrait AWR et également, le SQL Tuning Set et le graphe des appels utilisateurs :
exec dbms_workload_capture.export_awr(&&id);
!ls -ltra /u01/app/oracle/oradata/WHITE/capture/cap
-rw-r--r-- 1 oracle oinstall 129 2011-07-14 12:22 wcr_scapture.wmd
drwxr-xr-x 4 oracle oinstall 4096 2011-07-14 12:30 ..
-rw-r--r-- 1 oracle oinstall 249 2011-07-14 12:30 wcr_fcapture.wmd
-rw-r--r-- 1 oracle oinstall 45892 2011-07-14 12:30 wcr_cr.html
-rw-r--r-- 1 oracle oinstall 20706 2011-07-14 12:30 wcr_cr.text
-rw-r--r-- 1 oracle oinstall 26415 2011-07-14 12:31 wcr_ca.log
-rw-r----- 1 oracle oinstall 10813440 2011-07-14 12:31 wcr_ca.dmp
-rw-r----- 1 oracle oinstall 12288 2011-07-14 12:31 wcr_cap_uc_graph.extb
drwxr-xr-x 2 oracle oinstall 4096 2011-07-14 12:31 .
-rw-r----- 1 oracle oinstall 1728512 2011-07-14 12:32 wcr_ca_sts.dmp
La procédure
dbms_workload_capture.export_performance_datasemble avoir le même comportement queexport_awr; son nom est probablement plus pertinent mais elle n’est pas encore documentée.
Rafraichissement de l’environnement
On peut remettre à jour la base de données au moment du démarrage de la capture avec Flashback database comme ceci :
startup force mount;
exit
rman target /
list restore point all;
using target database control file instead of recovery catalog
SCN RSP Time Type Time Name
---------------- --------- ---------- --------- ----
10685002 14-JUL-11 START_CAPTURE
flashback database to restore point START_CAPTURE;
Starting flashback at 14-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
starting media recovery
archived log for thread 1 with sequence 132 is already on disk as file /u01/app/oracle/oradata/WHITE/archivelog/1_132_754352641.dbf
archived log for thread 1 with sequence 133 is already on disk as file /u01/app/oracle/oradata/WHITE/archivelog/1_133_754352641.dbf
media recovery complete, elapsed time: 00:00:02
Finished flashback at 14-JUL-11
alter database open resetlogs;
exit
La capture a disparu de la base de données; on la réimporte ainsi que les données de charge et le SQL Tuning Set :
col id format 999 new_value id
col NAME format a12
col status format a12
col CSIZ format 99999
col TRANS format 999999999
col sqlset format a30
select ID,
NAME,
STATUS,
CAPTURE_SIZE CSIZE,
TRANSACTIONS TRANS,
sqlset_owner||'.'||sqlset_name sqlset
from DBA_WORKLOAD_CAPTURES;
no rows selected
create directory capture_dir
as '/u01/app/oracle/oradata/WHITE/capture';
var simid number;
exec :simid:=dbms_workload_capture.get_capture_info('CAPTURE_DIR');
set tab off
set lines 120
col id format 999 new_value id
col NAME format a12
col status format a12
col CSIZ format 99999
col TRANS format 999999999
col sqlset format a30
select ID,
NAME,
STATUS,
CAPTURE_SIZE CSIZE,
TRANSACTIONS TRANS,
sqlset_owner||'.'||sqlset_name sqlset
from DBA_WORKLOAD_CAPTURES
where id=:simid;
ID NAME STATUS CSIZE TRANS SQLSET
---- ------------ ------------ ---------- ---------- ----------
141 gark_capture COMPLETED 31906374 500000 .
var ndbid number
exec :ndbid:= dbms_workload_capture.import_awr(:simid, 'ARKZOYD', true);
set tab off
set lines 120
col id format 999 new_value id
col NAME format a12
col status format a12
col CSIZ format 99999
col TRANS format 999999999
col sqlset format a30
select ID,
NAME,
STATUS,
AWR_DBID,
sqlset_owner||'.'||sqlset_name sqlset
from DBA_WORKLOAD_CAPTURES
where id=:simid;
ID NAME STATUS AWR_DBID SQLSET
---- ------------ ------------ ---------- ---------------------------
141 gark_capture COMPLETED 312816118 SYS.gark_capture_c_14175460
Database Replay
Encore une fois, voici comment effectuer un tir de replay :
begin
DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (
capture_dir =>'CAPTURE_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
---- --------------- ---------- ---------- ----------
141 gark_capture 11.2.0.2.0 COMPLETED 312816118
BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (
replay_name => 'REPLAY1',
replay_dir => 'CAPTURE_DIR');
END;
/
col id format 999 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
---- -------- ------------ ----------- ----- --- -----
111 REPLAY1 CAPTURE_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
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/WHITE/capture
sqlplus / as sysdba
col id format 999 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
---- -------- ------------ ----------- ----- --- -----
111 REPLAY1 CAPTURE_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 14198
user commits 14213
user commits 14219
user commits 15189
user commits 15586
user commits 15627
user commits 15780
user commits 15978
user commits 17315
user commits 17601
col id format 999
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
---- -------- -------- -------- ----------- ----- -------------------
111 REPLAY1 15:31:41 15:37:38 COMPLETED 357 REPLAY1_r_11172880
col sql format a50
set lines 100
select sql_id, substr(sql_text,1,50) sql
from table(dbms_sqltune.select_sqlset('&&sqlset'))
where parsing_schema_name='ARKZOYD';
SQL_ID SQL
------------- --------------------------------------------------
b3vhqyc8yhufz SELECT ID FROM GARK WHERE ID=100 FOR UPDATE
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 => 'CAPTURE_DIR');
/* Get the latest replay for that capture */
SELECT max(id)
INTO rep_id
FROM dba_workload_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 => 'CAPTURE_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;
/
Le rapport correspondant au replay ressemble à celui ci :
Exporter le replay
L’étape préalable à un rafraichissement consiste à exporter les données de performances associées au Replay comme ci-dessous :
exec dbms_workload_replay.export_awr(&&replay_id)
!ls -ltra /u01/app/oracle/oradata/WHITE/capture/rep*
/u01/app/oracle/oradata/WHITE/capture/rep545711298:
total 11592
-rw-r--r-- 1 oracle oinstall 631 2011-07-14 15:37 wcr_replay.wmd
-rw-r--r-- 1 oracle oinstall 17796 2011-07-14 15:38 wcr_rr_545711298.xml
drwxr-xr-x 6 oracle oinstall 4096 2011-07-14 15:43 ..
-rw-r--r-- 1 oracle oinstall 28641 2011-07-14 15:51 wcr_ra_545711298.log
-rw-r----- 1 oracle oinstall 10854400 2011-07-14 15:51 wcr_ra_545711298.dmp
-rw-r----- 1 oracle oinstall 12288 2011-07-14 15:51 wcr_rep_uc_graph_545711298.extb
-rw-r----- 1 oracle oinstall 921600 2011-07-14 15:51 wcr_ra_sts545711298.dmp
drwxr-xr-x 2 oracle oinstall 4096 2011-07-14 15:51 .
Rafraichissement de l’environnement
On peut remettre à jour la base de données au moment du démarrage de la capture avec RMAN Flashback comme ceci :
startup force mount;
exit
rman target /
list restore point all;
using target database control file instead of recovery catalog
SCN RSP Time Type Time Name
---------------- --------- ---------- --------- ----
10685002 14-JUL-11 START_CAPTURE
flashback database to restore point START_CAPTURE;
Starting flashback at 14-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
starting media recovery
archived log for thread 1 with sequence 132 is already on disk as file /u01/app/oracle/oradata/WHITE/archivelog/1_132_754352641.dbf
archived log for thread 1 with sequence 133 is already on disk as file /u01/app/oracle/oradata/WHITE/archivelog/1_133_754352641.dbf
media recovery complete, elapsed time: 00:00:02
Finished flashback at 14-JUL-11
alter database open resetlogs;
exit
Réimporter Capture et Replay(s)
Après le flashback database, capture et replays ont disparu ; on les réimporte ainsi que les données de charge et les SQL Tuning Sets comme ceci :
sqlplus / as sysdba
col id format 999 new_value id
col NAME format a12
col status format a12
col CSIZ format 99999
col TRANS format 999999999
col sqlset format a30
select ID,
NAME,
STATUS,
CAPTURE_SIZE CSIZE,
TRANSACTIONS TRANS,
sqlset_owner||'.'||sqlset_name sqlset
from DBA_WORKLOAD_CAPTURES;
no rows selected
create directory capture_dir
as '/u01/app/oracle/oradata/WHITE/capture';
var simid number;
exec :simid:=dbms_workload_capture.get_capture_info('CAPTURE_DIR');
set tab off
set lines 120
col id format 999 new_value id
col NAME format a12
col status format a12
col CSIZ format 99999
col TRANS format 999999999
col sqlset format a30
select ID,
NAME,
STATUS,
awr_dbid,
sqlset_name sqlset
from DBA_WORKLOAD_CAPTURES
where id=:simid;
ID NAME STATUS AWR_DBID SQLSET
---- ------------ ------------ ---------- -------
141 gark_capture COMPLETED
var ndbid number
exec :ndbid:= dbms_workload_capture.import_awr(:simid, 'ARKZOYD', true);
set tab off
set lines 120
col id format 999 new_value id
col NAME format a12
col status format a12
col CSIZ format 99999
col TRANS format 999999999
col sqlset format a30
select ID,
NAME,
STATUS,
awr_dbid,
sqlset_name sqlset
from DBA_WORKLOAD_CAPTURES
where id=:simid;
ID NAME STATUS AWR_DBID SQLSET
---- ------------ ------------ ---------- -------------------------
141 gark_capture COMPLETED 1631350746 gark_capture_c_14175460
col id format 999
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:S S') START_DATE,
to_char(END_time,'HH24:MI:SS') END_DATE,
STATUS,
AWR_DBID,
SQLSET_NAME SQLSET
from dba_workload_replays;
no rows selected
var simid number;
exec :simid:=dbms_workload_replay.get_replay_info('CAPTURE_DIR');
col id format 999
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,
AWR_DBID,
SQLSET_NAME SQLSET
from dba_workload_replays;
ID NAME START_DA END_DATE STATUS AWR_DBID SQLSET
---- -------- -------- -------- ----------- ---------- -------------------------
111 REPLAY1 15:31:41 15:37:38 COMPLETED
declare
v_ndbid number;
begin
for i in (select id from dba_workload_replays) loop
v_ndbid:=dbms_workload_replay.import_awr(i.id, 'ARKZOYD', true);
end loop;
end;
/
ID NAME START_DA END_DATE STATUS AWR_DBID SQLSET
---- -------- -------- -------- ----------- ---------- -------------------------
111 REPLAY1 15:31:41 15:37:38 COMPLETED 1815806422 REPLAY1_r_11172880
Vous voilà prêt à relancer un nouveau tir avec Oracle Database Replay et malgré tout, conserver l’ensemble des informations correspondant à chaque tir ; vous pouvez ainsi lancer les rapports de comparaison.
