Real Applications Testing est une nouvelle option de Oracle Database 11g à ne pas confondre avec Real Application Clusters. Cette option permet d’évaluer les impacts de changements en terme de performance à partir d’informations capturées sur vos environnements de production. Vous serez, maintenant capable de quantifier les gains ou problèmes liées à une nouvelle version, à la création de structures, aux changements de paramètres, de matériels. Cet option permet également de répondre à des questions qui peuvent rapporter ou couter plusieurs dizaines de milliers d’euros comme l’impact de l’utilisation d’autres options comme Real Application Clusters, Partitioning ou OLAP : fini les conjectures ! Si vous planifiez de migrer en 11g, débrouillez vous comme vous voulez avec votre commercial Oracle. Je ne peux déjà plus vivre sans cette option et ses composants :

• Oracle Database Replay vous permet de capturer une charge sur un système et de le rejouer sur un autre.
• SQL Performance Analyzer vous permet de mesurer l’impact d’un changement sur des ordres SQL capturer via un SQL Tuning Set, y compris ceux capturés sur votre base de données 10g
• peut-être même en 11g Release 1 mais pas encore disponible dans la dernière Beta Release sur laquelle je fais mes tests

Cet article est composé de 2 parties, celle-ci dédiée à Oracle Database Replay et une
suivante dédiée à SQL Performance Analyzer. Il est possible selon le contenu de la version 11g Release 1 que je doivent développer d’autres parties plus tard. Anyway, ces 2 outils ouvrent vraiment des horizons incroyables pour ce que sera notre travail désormais. En 10g, nous sommes devenu administrateur système avec Oracle Enterprise Linux, ASM et le Clusterware; en 11g, nous pourrons predirel’impact d’un changement sur les performances du futur systeme..

Oracle database Replay avec SQL*Plus

Cette première partie couvre les différents aspects de Database Replay, à savoir :

1. Avant la capture
2. Le démarrage de la capture de l’activité
3. La supervision de la capture de l’activité
4. L’arrêt de la capture
5. L’impression du rapport de capture
6. Le traitement des informations de capture en vue du Replay
7. La restauration de l’environnement de production pour les tests
8. Configurer et exécuter le Replay
9. Visualiser le rapport de Replay
10. D’autres commandes utiles
11. Conclusion

0. Schéma d’exemple

Pour les besoin de l’illustration, créez un utilisateur « arkzoyd » et exécutez le script ci-dessous. Nous allons capturer l’activité des sessions connectées à cet utilisateur :

create table gark(
id number primary key,
name varchar2(25) not null,
value number,
description varchar2(250)
default 'Don''t tables to create sequenced values');

create index gark_uk on gark(name);

insert into gark(id, name, value)
values (1,'GARK_SQ1', 1);

commit;

create or replace function
get_nextval(p_name varchar2)
return number is
v_return number;
begin
lock table gark in exclusive mode;

select value into v_return
from gark where name=p_name;

update gark
set value=value+1
where name=p_name;

commit;

return v_return;
end;
/

1. Avant de capturer l’activité

Voici les étapes préalables au démarrage de la capture de l’activité :

• Avant tout il faut s’assurer qu’on saura reconstituer les données utilisées pendant la capture, quelque soit la technique : export/import, RMAN, une Standby avec la flashback active ou n’importe qu’elle autre technique.
• Ensuite, il faut théoriquement arrêter et redémarrer la base de données. Non pas que l’on puisse activer la capture en ligne mais parce que vous pourriez rencontrer des différences de données du fait des transactions commencées avant le début de la capture
• Enfin il faut définir les filtres de votre capture, c’est à dire essentiellement les sessions que vous voulez capturer. Il faut noter que vous ne capturerez ni les accès directs aux fichiers comme SQL*Loader en mode Direct, ni les activités Streams, ni les transactions distribuées.
• Choisir le répertoire où les fichiers de capture seront stockés. Dans le cas de RAC, mieux vaut choisir un système de fichiers partagés comme NFS ou OCFS.

2. Capturer l’activité

Le package DBMS_WORKLOAD_CAPTURE vous permettra de démarrer la capture de l’activité de votre base de données alors que les vues DBA_WORKLOAD_CAPTURES et
DBA_WORKLOAD_FILTERS vous permettront de superviser cette capture.

• Ajouter des filtres.

Pour ajouter des filtres et, par exemple ne pas capturer l’activite de la database console ou des DBA, vous utiliserez les procedures ADD_FILTER et DELETE_FILTER du package DBMS_WORKLOAD_CAPTURE. Voici un exemple d’utilisation :

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

• Contrôler les filtres.

La vue DBA_WORKLOAD_FILTERS affiche la liste des filtres comme vous pouvez le constater ci-dessous :

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

• Créer un directory pour stocker les informations de capture.

On supposera qu’on a cree au préalable un répertoire /u01/app/oracle/oradata/ORCL/capture :

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

• Démarrer la capture avec dbms_workload_capture.start_capture comme ci-dessous. Les paramètres utilisés sont :
  • name est le nom de la capture
  • dir est le directory crée à l’étape précédente
  • duration indique la durée de la capture. NULL indique que la capture sera active jusqu’à ce que la procédure finish_capture la désactive.
  • default_action indique si les règles du filtres incluent l’activité ou au contraire l’excluent
  • no_restart_mode indique si les instances sont redémarrées à l’issue de la commande

BEGIN
DBMS_WORKLOAD_CAPTURE.START_CAPTURE (
name            => 'gark_capture',
dir             => 'CAPTURE_DIR',
duration        => null,
default_action  => 'INCLUDE',
NO_RESTART_MODE => true);
END;
/

• Vérifier que la capture est active à l’aide de la vue DBA_WORKLOAD_CAPTURES

col id format 99
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
--- -------------- ------------ ---------- ---- ----- -----

1   gark_capture   CAPTURE_DIR  INCLUDE    1     3394     0

3. Attendre et Surveiller

Dans la réalité, il vous faut attendre que la charge soit effective. Pour ce test, nous allons
générer de l’activité nous-même. Pour cela, nous allons lancer en parallèle 3 sessions connectées à l’utilisateur ARKZOYD qui exécuteront le code suivant en simultané :

declare
v_out number;
begin
for i in 1..10000 loop
v_out:=get_nextval('GARK_SQ1' );
end loop;
end;
/

Vous pouvez surveiller le déroulement du test à l’aide de la commande suivante :

select name, value
from gark;

NAME                           VALUE
------------------------- ----------
GARK_SQ1                       30001

Et aussi suivre l’activité de la capture à l’aide de la commande suivante :

col id format 99
col NAME format a12
col status format a12
col CSIZ format 99999
col TRANS format 999999
select ID,
NAME,
STATUS,
CAPTURE_SIZE CSIZE,
TRANSACTIONS TRANS
from DBA_WORKLOAD_CAPTURES;

ID  NAME         STATUS            CSIZE   TRANS
--- ------------ ------------ ---------- -------
12  gark_capture IN PROGRESS      592218   30000

4. Arrêter la capture

La procédure FINISH_CAPTURE arrête la capture en cours.

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

Comme pour l’étape précédente, vous pouvez connaitre le statut de la capture en interrogeant la vue DBA_WORKLOAD_CAPTURES :

col id format 99
col NAME format a12
col status format a12
col CSIZ format 99999
col TRANS format 999999
select ID,
NAME,
STATUS,
CAPTURE_SIZE CSIZE,
TRANSACTIONS TRANS
from DBA_WORKLOAD_CAPTURES;

5. Imprimer le rapport de capture

Une fois la capture terminée, le rapport de capture est normalement généré automatiquement
dans le répertoire de capture. Vous pouvez l’ouvrir avec votre navigateur web :

Quoiqu’il en soit vous pouvez le régénérer à tout moment à l’aide de la commande suivante :

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     => '&db_directory',
filename     => '&report_name',
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;
/

Changer les valeurs &db_directory et &eport_name selon vos besoins

6. Traiter les informations capturées en vue de leur réutilisation

Cette étape s’exécute sur l’environnement de test et n’impacte pas l’environnement de production. Pour réaliser cette opération, vous devez :

• Copier les fichiers du répertoire créé à l’étape 2 dans un répertoire sur l’environnement de test.
• Créer un directory qui fait le lien avec ce répertoire. Nous supposerons que le répertoire et le directory utilisés sur l’environnement de test sont identiques à ceux de production

Une fois le directory disponible, vous pouvez lancer le traitement à l’aide de la commande ci-dessous :

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

Cette opération construit les fichiers nécessaires pour rejouer l’activité sur la base de donnees de test. Vous pouvez vérifier que ces fichiers sont désormais crées dans le répertoire ou sont situées les fichiers de capture.

7. Restaurer l’environnement de production.

Vous devez maintenant reconstruire l’environnement de test pour qu’il ressemble au maximum à la production. Dans notre cas, il suffira de réinitialiser la table GARK avec la commande ci-dessous :

update gark
set value=1
where name='GARK_SQ1';
commit;

Dans un cas plus complexe, ça peut vouloir dire :

• Restaurer la base de données
• Reconstruire les accès à d’autres bases via DB Links ou vers d’autres sources comme les DIRECTORY, les tables externes ou les URLs utilisées par la base de données
• Changer la date système pour qu’elle corresponde au jour et à l’heure de la capture (POur les appels à sysdate dans les select
• Recréer les alias TNS sur la base de données pour que les clients puissent se connecter ou, si vous utilisez RAC pour répartir la charge

8. Configurer et exécuter le « Replay »

• Initialiser le replay en vue de changer les paramètres.

L’étape suivante consiste à initialiser le replay afin de pouvoir modifier les paramètres et en particulier les connexions à la base de donnees. Ci-dessous le script correspondant :

BEGIN
DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY (
replay_name => 'GREPLAY1',
replay_dir  => 'CAPTURE_DIR');
END;
/
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,
DIRECTORY dir,
STATUS,
DURATION_SECS secs,
NUM_CLIENTS cli,
SYNCHRONIZATION sync
from dba_workload_replays;

ID  NAME     DIR          STATUS      SECS  CLI SYNC
--- -------- ------------ ----------- ----- --- -----
1   GREPLAY1 CAPTURE_DIR  INITIALIZED     0     TRUE

• Modifier les connexions à la base de données

L’étape suivante consiste à initialiser le replay afin de pouvoir modifier les paramètres et en particulier les connexions à la base de donnees. Ci-dessous le script correspondant :

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=(ADDRESS=(PROTOCO

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=&R
EPLAY_ID;

CONN_ID ORIG NEW
------- ------------------------------ ------------------------------
1       (DESCRIPTION=(ADDRESS=(PROTOCO ORCL

• Préparer pour ré-exécuter l’activité et modifier les paramètres
  

La commande ci-dessous prepare l’activite à etre rejouee et en particulier positionne les options de ci-dessous

BEGIN
DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY (
synchronization => TRUE);
END;
/

• Démarrer les clients de replay

La première étape nécessaire à l’exécution de la charge sur l’environnement de test consiste à calibrer le nombre de clients qui seront nécessaires. Pour cela, il faut lancer la commande wrc (Workload Replay Client) avec les informations de connexion

• mode=calibrate indique que vous voulez connaitre le nombre
• de clients a executer
• replaydir=
  

Ci-dessous la copie de la recommandation du nombre de clients à lancer.

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

Workload Replay Client: Release 11.1.0.5.0
- Beta on Mon Jul 2 11:09:22 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.
Report for Workload in: /u01/app/oracle/oradata/ORCL/capture
------------------------------------------------------

Recommendation:
Consider using at least 1 clients
divided among 1 CPU(s).

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

Assumptions:
- 1 client process per 50 concurrent
sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE

Une fois le nombre de clients determines, lancez les (dans ce cas 1 seul) en mode replay comme ci-dessous :

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

Workload Replay Client: Release 11.1.0.5.0
- Beta on Mon Jul 2 11:12:57 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (11:12:57)

• Démarrer et visualiser l’avancement du mode Replay

Le script ci-dessous démarre et visualise l’état du mode Replay :

BEGIN
DBMS_WORKLOAD_REPLAY.START_REPLAY ();
END;
/
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(sysdate,'DD/MM/YYYY HH24:MI:SS') SYS_DATE,
to_char(start_time,'DD/MM/YYYY HH24:MI:SS') START_DATE,
to_char(END_time,'DD/MM/YYYY HH24:MI:SS') END_DATE,
STATUS,
DURATION_SECS secs,
NUM_CLIENTS cli,
SYNCHRONIZATION sync
from dba_workload_replays
where id=&REPLAY_ID;

ID NAME     SYS_DATE            START_DATE          END_DATE
--- -------- ------------------- ------------------- -------------------
STATUS       SECS CLI SYNC
----------- ----- --- -----
5 GREPLAY1 02/07/2007 13:40:44 02/07/2007 13:38:02
IN PROGRESS         1 TRUE

/
ID NAME     SYS_DATE            START_DATE          END_DATE
--- -------- ------------------- ------------------- -------------------
STATUS       SECS CLI SYNC
----------- ----- --- -----
5 GREPLAY1 02/07/2007 13:48:49 02/07/2007 13:38:02 02/07/2007 13:43:28
COMPLETED     326   1 TRUE

9. Visualiser le rapport de « Replay »

Le « Replay » prend, selon notre paramétrage , le même temps que la capture (y compris le temps d’inactivité). Une fois celui-ci termine, pour imprimer le rapport, utilisez le script ci-dessous :

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;

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

fh := UTL_FILE.FOPEN(
location     => '&db_capture_dir',
filename     => '&report_name',
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 vous permet notamment de visualiser les modifications et/ou les erreurs du Replay.
10. Avant de terminer

Vous trouverez ci-dessous quelques commandes utiles pour « nettoyer » l’environnement ci-dessous.

• Pour supprimer les informations de capture, utilisez :

col id format 99
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;

BEGIN
DBMS_WORKLOAD_CAPTURE.DELETE_CAPTURE_INFO (
capture_id=>&capture_id);
END;
/
col id format 99
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;

no rows selected

D’autre part, supprimez les fichiers de capture dans le repertoire correspondant. Ils commencent par « wcr_ »

• Pour supprimer les informations de Replay, utilisez :

SELECT ID,
NAME,
DURATION_SECS,
STATUS
FROM DBA_WORKLOAD_REPLAYS;

ID NAME     DURATION_SECS STATUS
--- -------- ------------- -----------
5 GREPLAY1           326 COMPLETED

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

SELECT ID,
NAME,
DURATION_SECS,
STATUS
FROM DBA_WORKLOAD_REPLAYS;

Supprimez les fichiers de capture et de replay dans le repertoire correspondant. Ils commencent par « wcr_ ».

• Pour supprimer les objets du schéma, utilisez

drop index gark_uk;
drop table gark;

Et supprimer l’utilisateur si vous l’avez crée précédemment.

• Pour annuler une operation de Replay, utilisez :

begin
DBMS_WORKLOAD_REPLAY.cancel_replay(
'Replay Terminated by Cancel');
end;
/

11. Conclusion

Database Replay est très simple à mettre en oeuvre en ligne de commande. Utilisé avec Flashback Database active sur l’environnement de test, il permet de réaliser extrêmement
simplement et avec les compétences du seul DBA tous ce qu’on peut imaginer comme modifications. Dans la prochaine partie de ce Post, nous ferons un plongeon dans SQL Performance Analyzer, mais c’est déjà une autre histoire…

-GarK!