Blog

le 30/09/2015
Eric Leygonie
Oracle, Oracle 12c

Gérer des STS avec plusieurs instances RAC

Le but de l’opération est de capturer l’ensemble des requêtes associées à un schéma dans une configuration de base de données RAC, puis de pouvoir rejouer les requêtes capturées sur un autre environnement. Par exemple pour tester le comportement unitaire des requêtes via SPA entre une version 10G et une version 11G.
La difficulté provient de l’architecture RAC, car chaque instance possède sa propre SGA; il y aura donc une capture par un instance (un SQL Tuning Set pour chaque instance) et il faudra fusionner le contenu des STS obtenus avant de rejouer l’ensemble sur une base cible.
Les différentes étapes vont être :

Création d’un SQL Tuning Set pour chacune des instances.
Capture des requêtes depuis le cache des curseurs de chaque instance
Création d’une table de transfert dans la base source
Merge des informations des différents STS dans la table de transfert
Export de la table de transfert depuis la base RAC source
Transfert et import de la table de transfert dans la base cible
Création des STS dans la base cible depuis la table de transfert (« unpack »)
Fusion du contenu des STS dans un seul

Voici plus de détails en imaginant une base de données ORCL avec deux instances ORCL1 et ORCL2 respectivement sur les nœuds « host01 » et « host0 » :
1 ) Créer un STS pour chaque instance avec un nom différent par instance
Connexion « SYS as SYSDBA » depuis n’importe quelle instance, le schéma RAT est prévu pour administrer toutes les opérations de rejeu et de transfert.

$ssh oracle@host01
passwd : **********
$ . oraenv
>ORCL1
$sqlplus / as sysdba
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
SQLSET_NAME => 'SQLSET_10G_ORCL1',
DESCRIPTION => 'SQL queries 10G',
SQLSET_OWNER => 'RAT');
END;
/
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
SQLSET_NAME => 'SQLSET_10G_ORCL2',
DESCRIPTION => 'SQL queries 10G',
SQLSET_OWNER => 'RAT');
END;
/

2) Déclencher la capture des requêtes depuis le cache des curseurs sur chaque instance
Pour rappel, à la fin de la capture, le SQL Tuning Set contient pour chaque requête SQL répondant au critère de filtre, le texte de la requête, le SQL_ID, les statistiques d’exécution, les variables attachées, le plan d’exécution déroulé.
Depuis l’instance ORCL1 :

$ssh oracle@host01
passwd: ********
$ . oraenv
>ORCL1
$export DUREE=28880
$nohup sqlplus / as sysdba < $DUREE,
REPEAT_INTERVAL => 10,
CAPTURE_OPTION => 'MERGE',
CAPTURE_MODE => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS,
BASIC_FILTER => 'parsing_schema_name = ''APPLI''',
SQLSET_OWNER => 'RAT');
END;
/
EXIT
EOF
$

Depuis l’instance ORCL2 :

$ssh oracle@host02
passwd: ******
$ . oraenv
>ORCL2
$export DUREE=28880
$nohup sqlplus / as sysdba <'SQLSET_10G_ORCL2',
TIME_LIMIT => $DUREE,
REPEAT_INTERVAL => 10,
CAPTURE_OPTION => 'MERGE',
CAPTURE_MODE => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS,
BASIC_FILTER => 'parsing_schema_name = ''APPLI''',
SQLSET_OWNER => 'RAT');
END;
/
EXIT
EOF
$

Le déclenchement en mode « nohup » est nécessaire car la session reste ouverte temps que le délai n’a pas expiré. La variable DUREE est le nombre de secondes pour la capture, la valeur de 28880 correspond à huit heures. Le filtre appliqué correspond aux requêtes déclenchées par l’utilisateur « APPLI »

3) Une fois la période de capture terminée, création de la table de transfert et packaging des deux STS dans la table.
Opération réalisée sur la base depuis l’une ou l’autre instance :

$ssh oracle@host01
passwd : **********
$ . oraenv
>ORCL1
$sqlplus / as sysdba
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
SCHEMA_NAME => 'RAT',
TABLE_NAME => 'SQLSETS_10G');
END;
/
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
SQLSET_NAME => 'SQLSET_10G_ORCL1',
SQLSET_OWNER => 'RAT',
STAGING_TABLE_NAME => 'SQLSETS_10G',
STAGING_SCHEMA_OWNER => 'RAT');
END;
/
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
SQLSET_NAME => 'SQLSET_10G_ORCL2',
SQLSET_OWNER => 'RAT',
STAGING_TABLE_NAME => 'SQLSETS_10G',
STAGING_SCHEMA_OWNER => 'RAT');
END;
/

On retrouve dans la table de transfert le contenu des deux STS, mais il n’y pas de fusion possible, chaque contenu reste lié à son STS.
4) Export de la table de transfert via l’utilitaire datapump
L’opération peut être faite depuis n’importe lequel des nœuds :

$ssh oracle@host01
passwd : **********
$ . oraenv
>ORCL1
$expdp userid=SYSTEM/***** directory=SPA_DIR dumpfile=SQLSETS_10G.dmp logfile=SQLSETS_10G.log tables=RAT.SQLSETS_10G

SPA_DIR est un objet répertoire de la base qui pointe vers le répertoire physique « /home/oracle/spadir »
5) Transfert du fichier SQLSETS_10G.dmp vers la base cible en 11G , nommée ORCL11G, création et chargement de la table de transfert :

$scp /home/oracle/spadir/SQLSETS_10G.dmp oracle@host11g:/home/oracle/spadir
$ssh oracle@host11g
passwd: *****
. oraenv
>ORCL11G
$impdp userid=SYSTEM/****** directory=SPA_DIR dumpfile=SQLSETS_10G.dmp logfile=SQLSETS_10G.log tables=RAT.SQLSETS_10G table_exists_action=TRUNCATE

Option positionnée de suppression des lignes de la table SQLSETS_10G si celle-ci existe déjà avant le chargement.
6) Création et remplissage des deux STS dont les informations sont contenues dans la table de transfert (dans la base ORCL11G)

$sqlplus / as sysdba
exec dbms_sqltune.drop_sqlset('SQLSET_10G_ORCL1', 'RAT');
exec dbms_sqltune.drop_sqlset('SQLSET_10G_ORCL2', 'RAT');
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
SQLSET_NAME => 'SQLSET_10G_ORCL1',
DESCRIPTION => 'SQL queries 10G from ORCL1',
SQLSET_OWNER => 'RAT');
END;
/
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
SQLSET_NAME => 'SQLSET_10G_ORCL2',
DESCRIPTION => 'SQL queries 10G from ORCL2',
SQLSET_OWNER => 'RAT');
END;
/
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
SQLSET_NAME => 'SQLSET_10G_ORCL1',
SQLSET_OWNER => 'RAT',
REPLACE => TRUE,
STAGING_TABLE_NAME => 'SQLSETS_10G',
STAGING_SCHEMA_OWNER => 'RAT');
END;
/
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
SQLSET_NAME => 'SQLSET_10G_ORCL2',
SQLSET_OWNER => 'RAT',
REPLACE => TRUE,
STAGING_TABLE_NAME => 'SQLSETS_10G',
STAGING_SCHEMA_OWNER => 'RAT');
END;
/
-- Verification par interrogation de DBA_SQLSET
SELECT owner, name, statement_count FROM DBA_SQLSET;

7) Afin de disposer de toutes les requêtes dans un seul SQLSET, récupération des curseurs contenus dans SQLSET_10G_ORCL2 pour les insérer dans SQLSET_10G_ORCL1 :

DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p) from table(dbms_sqltune.select_sqlset(sqlset_name => 'SQLSET_10G_ORCL2', sqlset_owner => 'RAT')) p;
dbms_sqltune.load_sqlset(
sqlset_name => 'SQLSET_10G_ORCL1',
sqlset_owner => 'RAT',
populate_cursor => cur
);
commit;
close cur;
END;
/
-- Verification par interrogation de DBA_SQLSET
SELECT owner, name, statement_count FROM DBA_SQLSET;

Le contenu de SQLSET_10G_ORCL1 contient bien l’ensemble des requêtes de l’utilisateur « APPLI » capturées sur les deux instances ORCL1 et ORCL2 pendant l’intervalle souhaité. L’outil SPA (SQL Performance Analyzer) de l’option RAT (Real Application Testing) peut maintenant utiliser ce tuning set pour rejouer les requêtes vers une base en version 10G et une base en version 11G (bases contenant vos objets applicatifs à jour) pour comparer le comportement des requêtes dans chaque configuration. C’est là que notre utilisateur « RAT » va entrer en jeu, mais c’est une nouvelle histoire …
Notes :
Les opérations de synchronisation de la base cible avec la base source ne sont pas vues dans cet article, ni l’utilisation des tâches d’analyse SPA.
L’opération peut être répétée pour n instances, un peu d’automatisation sera alors la bienvenue.

Eric Leygonie

Voir tous ses articles

Blog

Gérer des STS avec plusieurs instances RAC

Articles récents