Hello amis dba’s et bienvenue sur cet article !
Au menu aujourd’hui un cas concret d’utilisation d’un sujet assez peu abordé et pourtant bien utile: les chaines Oracle.
Les chaines sont un composant du planificateur Oracle qui permet de gérer une série d’actions déclenchées hiérarchiquement un peu à la manière d’un ordonnanceur. On réalise une action A si celle-ci échoue on part sur une action B sinon on lance C etc etc..
Dans le cas d’utilisation qui suit nous étudions un calcul de statistiques hebdomadaire sur une base de un peu plus de 1 To qui dure approximativement 28h et pour lequel un client nous demande une amélioration.
Nous partons d’une édition standard (donc pas de parallélisme) en 10.2.0.4 et passerons sur les modifications faites à la commande de calcul elle-même (auto-echantillonnage, exclusion des tables de travail applicatives etc)
Globalement partant de l’idée qu’un calcul de statistiques réalise un calcul séquentiel par objet nous allons “simuler” une forme de parallélisme en nous appuyant sur les chaines:
Une chaine est associée à un job qui est créé dynamiquement par Oracle ou défini explicitement, ce job lance lui-même des programmes ou d’autres chaines imbriquées qui créent elles-même d’autres jobs qui lancent des programmes. Si vous n’avez rien compris c’est normal moi non plus 🙂 donc voyons de quelle manière cela peut être utile pour notre cas.
Nous souhaitons avoir une procédure qui accepte en paramètre le “degré” de parallélisme et en paramètre optionnel le nom du schéma concerné par le calcul de statistique (par défaut la valeur est % donc tous les schémas seront embarqués s’il n’y en a pas un de précisé). En substance nous allons créer une première chaine “MASTER” qui lancera en simultanée autant de chaine “SLAVE” que le degré de parallélisme indiqué. Chaque chaine “SLAVE” se verra attribuer des tâches qui seront elles-mêmes lancées séquentiellement, chacune de ces tâches correspondantes a un calcul de stats pour une seule table. la procédure est là pour créer dynamiquement le jeu de chaine qui va bien pour arriver à cette organisation (merci paint pour la qualité irréprochable de ce schéma :)):
Dans le vocabulaire oracle une tâche correspond à un programme qui pour nous correspond à une commande de calcul de statistiques pour une seule table. Chaque étape ou step de chaque chaine slave lance un programme et donc un calcul de statistique pour une table.
Le début de la procédure se charge de créer le jeu de chaine complet (maitre/esclave):
(...)
/* Debut phase de création des chaines maitres/esclaves */
/* debut création de la chaine maitre */
SYS.DBMS_SCHEDULER.CREATE_CHAIN (chain_name => 'MASTER_CHAIN',
rule_set_name => NULL,
evaluation_interval => NULL,
comments => NULL);
SYS.DBMS_SCHEDULER.ENABLE (name => 'MASTER_CHAIN');
/* fin création de la chaine maitre */
FOR I IN 1 .. P_FACTOR
LOOP
v_chain_start := v_chain_start || 'SLAVE_CHAIN_' || I || ',';
v_chain_stop := v_chain_stop || 'SLAVE_CHAIN_' || I || ' COMPLETED AND ';
/* debut creation des chaines esclaves qui seront lancées en paralléle par la chain maitre */
SYS.DBMS_SCHEDULER.CREATE_CHAIN (chain_name => 'SLAVE_CHAIN_' || I,
rule_set_name => NULL,
evaluation_interval => NULL,
comments => NULL);
SYS.DBMS_SCHEDULER.ENABLE (name => 'SLAVE_CHAIN_' || I);
/* pour chaque chaine slave un step dans la chaine master correspondant doit être créé */
SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'MASTER_CHAIN',
step_name => 'SLAVE_CHAIN_' || I,
program_name => 'SLAVE_CHAIN_' || I);
END LOOP;
(...)
La variable P_FACTOR correspond à la valeur passée en paramètre de la procédure qui détermine le nombre de threads de chaines esclaves qui seront créés. Dans la boucle chaque chaine esclave est définie comme une étape de la chaine maitre. il s’agit de nested chain ou chaines imbriquées. c’est la commande DBMS_SCHEDULER.CREATE_CHAIN qui est utilisée pour ça.
Une chaine oracle doit comporter des étapes mais aussi des règles de déclenchement, par défaut si aucune règle n’existe aucun step n’est lancé, même s’ils sont déjà présents dans la chaine.
Pour définir ces règles on utilise DBMS_SCHEDULER.DEFINE_CHAIN_RULE
(...)
/* fin creation des chaines esclaves qui seront lancées en paralléle par la chain maitre */
/* la ligne ci-dessous sert a créer la règle qui permet de lancer tous les process esclaves en parallèle */
DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('MASTER_CHAIN',
'TRUE',
TRIM (TRAILING ',' FROM v_chain_start));
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
'MASTER_CHAIN',
SUBSTR (v_chain_stop, 1, LENGTH (v_chain_stop) - 5),
'END');
(...)
Indépendamment des relations qui peuvent exister entre chaque step, la chaine doit avoir une condition qui se vérifie tout le temps pour permettre à au moins une première étape de se déclencher lorsqu’aucun step de départ n’est indiqué dans la commande de lancement. Elle doit également comporter une condition de sortie, de préférence une condition qui a 100% de chance de se produire. Dans le cas ci-dessus même si on ne voit pas très bien a quoi correspondent les variable v_chain_start & v_chain_stop ( je compose l’action à la volée car il dépend de P_FACTOR qui n’est pas fixe) j’indique à Oracle: “Lorsque TRUE renvoit vrai (tout le temps à priori) tu démarres toutes les chaines esclaves, et lorsqu’elles sont toutes completed la chaine maitre se termine”
A ce stade, nous devons créer un programme par table et les affecter en tant que step à chaque chaine esclave:
FOR table_courante
IN ( SELECT table_name, owner
FROM dba_tables
WHERE owner LIKE utilisateur
AND NOT REGEXP_LIKE (table_name, '^TMP_[0-9]+$')
AND table_name NOT LIKE 'TMP\_%\__\_________' ESCAPE '\'
AND table_name NOT LIKE '%TEST%'
AND table_name NOT LIKE 'BIN$%'
AND OWNER NOT IN
('SYS',
'SYSTEM',
'TSMSYS',
'ORACLE_OCM',
'DIP',
'DBSNMP',
'DBSNMP',
'OUTLN',
'WMSYS')
ORDER BY BLOCKS DESC NULLS LAST)
LOOP
SYS.DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'SYS.PRG_PARA_' || v_cpt_prg,
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
dbms_stats.gather_table_stats(
ownname=>'
|| DBMS_ASSERT.enquote_literal (
table_courante.owner)
|| ',
tabname=>'
|| DBMS_ASSERT.enquote_literal (
table_courante.table_name)
|| ',
granularity=>''ALL'',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=>TRUE,
degree=>DBMS_STATS.AUTO_DEGREE);
END;',
number_of_arguments => 0,
enabled => TRUE,
comments => NULL);
SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'SLAVE_CHAIN_' || v_curr_slave_chain,
step_name => 'STEP_PARA_' || v_cpt_prg,
program_name => 'SYS.PRG_PARA_' || v_cpt_prg);
/* debut ajout du prog courant en tant que step du process slave courant */
SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'SLAVE_CHAIN_' || v_curr_slave_chain,
step_name => 'STEP_PARA_' || v_cpt_prg,
program_name => 'SYS.PRG_PARA_' || v_cpt_prg);
/* fin ajout du prog courant en tant que step du process slave courant */
/* v_curr_slave_chain :cette variable me sert à me positionner sur la chaine esclave suivante */
v_curr_slave_chain := v_curr_slave_chain + 1;
v_cpt_prg := v_cpt_prg + 1;
IF v_curr_slave_chain > P_FACTOR
THEN
v_curr_slave_chain := 1;
END IF;
END LOOP;
/* fin de la distribution des tâches entre les process slave */
Dans cette partie les programmes correspondent à des calcul de statistiques mais il pourrait s’agir de tout autre chose. Chaque step est affecté à une chaine de manière circulaire ajouté à l’order by BLOCK de la requête se sélection des tables ceci permet de ne pas affecter les tables les plus lourdes à une seule et même chaine esclave qui pourrait finir bien après les autres et limiter l’intérêt de paralléliser.
Comme on a vu précédemment une chaine a besoin d’étapes et de conditions. Pour créer ces conditions on utilise deux boucles imbriquées:
FOR CHAINE_COURANTE
IN ( SELECT CHAIN_NAME,
MAX (step_name) "STARTING_STEP",
MIN (step_name) "ENDING_STEP"
FROM DBA_SCHEDULER_CHAIN_STEPS
WHERE CHAIN_NAME LIKE 'SLAVE_CHAIN%'
GROUP BY chain_name)
LOOP
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAINE_COURANTE.CHAIN_NAME,
'TRUE',
'START ' || CHAINE_COURANTE.STARTING_STEP); --je positionne mon step de depart
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAINE_COURANTE.CHAIN_NAME,
CHAINE_COURANTE.ENDING_STEP || ' COMPLETED',
'END'); -- je positionne mon step de fin
v_last_step := CHAINE_COURANTE.STARTING_STEP; --cette variable mer sert a connaitre le dernier step exécuté donc le step de démarrage a ce stade la procedure
FOR STEP_COURANT
IN ( SELECT STEP_NAME
FROM DBA_SCHEDULER_CHAIN_STEPS
WHERE CHAIN_NAME = CHAINE_COURANTE.CHAIN_NAME
AND STEP_NAME <> CHAINE_COURANTE.STARTING_STEP
ORDER BY REPLACE (step_name, '_', 'Z') DESC)
LOOP
/* ici on balaye l'ensemble des steps de la chain en cours en triant le nom par ordre decroissant ce qui nous fera finir par le step de fin, j'exclue le step de démarrage
car celui-ci a déjà été manuellement positionné ci-dessus*/
--DBMS_OUTPUT.PUT_LINE('chaine: '||CHAINE_COURANTE.CHAIN_NAME||', STEP: '||STEP_COURANT.STEP_NAME); --affichage de controle oseb
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAINE_COURANTE.CHAIN_NAME,
v_last_step || ' COMPLETED',
'START ' || STEP_COURANT.STEP_NAME);
/*cette regle me permet d'indiquer à oracle que le dernier step complété lance le step courant dans la boucle qui devient alors le dernier step lancé et ainsi de suite jusqu'au dernier*/
v_last_step := STEP_COURANT.STEP_NAME;
END LOOP;
END LOOP;
/* FIN phase de création des chaines maitres/esclaves */
Une fois ces règles définies il ne reste plus qu’à lancer la chaine maitre qui lancera en cascade toutes les chaines esclaves et avec le système poussé de logging du scheduler oracle nous allons pouvoir connaitre quel temps on a mis pour chaque table individuellement.
Comme avec le parallélisme classique il faut être très vigilant sur la consommation cpu que cela peut engendrer, donc ne pas exagérer le nombre de thread créés…
Pour ceux qui souhaitent faire le test voici le code complet de la procédure:
CREATE OR REPLACE PROCEDURE SYS.CALCUL_STATS_PARALLEL (
P_FACTOR NUMBER,
Utilisateur VARCHAR2 DEFAULT '%')
IS
v_code NUMBER;
v_errm VARCHAR2 (64);
v_chain_start VARCHAR2 (30000) := 'START ';
v_chain_stop VARCHAR2 (30000) := NULL;
v_curr_slave_chain NUMBER := 1;
v_last_step VARCHAR2 (30);
v_cpt_prg NUMBER (5) := 0;
CLEAN VARCHAR2 (32);
cpt NUMBER (10) := 1;
BEGIN
/*********************************************************************************************/
/* C@pdata LE 17 mai 2016
/********************************************************************************************/
/* Debut phase de création des chaines maitres/esclaves */
/* debut création de la chaine maitre */
SYS.DBMS_SCHEDULER.CREATE_CHAIN (chain_name => 'MASTER_CHAIN',
rule_set_name => NULL,
evaluation_interval => NULL,
comments => NULL);
SYS.DBMS_SCHEDULER.ENABLE (name => 'MASTER_CHAIN');
/* fin création de la chaine maitre */
FOR I IN 1 .. P_FACTOR
LOOP
v_chain_start := v_chain_start || 'SLAVE_CHAIN_' || I || ',';
v_chain_stop := v_chain_stop || 'SLAVE_CHAIN_' || I || ' COMPLETED AND ';
/* debut creation des chaines esclaves qui seront lancées en paralléle par la chain maitre */
SYS.DBMS_SCHEDULER.CREATE_CHAIN (chain_name => 'SLAVE_CHAIN_' || I,
rule_set_name => NULL,
evaluation_interval => NULL,
comments => NULL);
SYS.DBMS_SCHEDULER.ENABLE (name => 'SLAVE_CHAIN_' || I);
/* pour chaque chaine slave un step dans la chaine master correspondant doit être créé */
SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'MASTER_CHAIN',
step_name => 'SLAVE_CHAIN_' || I,
program_name => 'SLAVE_CHAIN_' || I);
END LOOP;
/* fin creation des chaines esclaves qui seront lancées en paralléle par la chain maitre */
/* la ligne ci-dessous sert a créer la règle qui permet de lancer tous les process esclaves en parallèle */
DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('MASTER_CHAIN',
'TRUE',
TRIM (TRAILING ',' FROM v_chain_start)); /* le trim sert a retirer la derniere virgule qui casse les c.. a la fin de la variable v_chain_start */
/* la ligne ci-dessous sert à créer la regle de fin d'exécution de la chaine maitre on remanipule un peu la chaîne au passage */
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
'MASTER_CHAIN',
SUBSTR (v_chain_stop, 1, LENGTH (v_chain_stop) - 5),
'END');
--dbms_output.put_line('chaine: '||substr(v_chain_stop,1,length(v_chain_stop)-5));
/* debut de la distribution des tâches entre les process slave */
FOR table_courante
IN ( SELECT table_name, owner
FROM dba_tables
WHERE owner LIKE utilisateur
AND NOT REGEXP_LIKE (table_name, '^TMP_[0-9]+$')
AND table_name NOT LIKE 'TMP\_%\__\_________' ESCAPE '\'
AND table_name NOT LIKE '%TEST%'
AND table_name NOT LIKE 'BIN$%'
AND OWNER NOT IN
('SYS',
'SYSTEM',
'TSMSYS',
'ORACLE_OCM',
'DIP',
'DBSNMP',
'DBSNMP',
'OUTLN',
'WMSYS')
ORDER BY BLOCKS DESC NULLS LAST)
LOOP
SYS.DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'SYS.PRG_PARA_' || v_cpt_prg,
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
dbms_stats.gather_table_stats(
ownname=>'
|| DBMS_ASSERT.enquote_literal (
table_courante.owner)
|| ',
tabname=>'
|| DBMS_ASSERT.enquote_literal (
table_courante.table_name)
|| ',
granularity=>''ALL'',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=>TRUE,
degree=>DBMS_STATS.AUTO_DEGREE);
END;',
number_of_arguments => 0,
enabled => TRUE,
comments => NULL);
SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'SLAVE_CHAIN_' || v_curr_slave_chain,
step_name => 'STEP_PARA_' || v_cpt_prg,
program_name => 'SYS.PRG_PARA_' || v_cpt_prg);
/* debut ajout du prog courant en tant que step du process slave courant */
SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'SLAVE_CHAIN_' || v_curr_slave_chain,
step_name => 'STEP_PARA_' || v_cpt_prg,
program_name => 'SYS.PRG_PARA_' || v_cpt_prg);
/* fin ajout du prog courant en tant que step du process slave courant */
/* v_curr_slave_chain :cette variable me sert à me positionner sur la chaine esclave suivante */
v_curr_slave_chain := v_curr_slave_chain + 1;
v_cpt_prg := v_cpt_prg + 1;
IF v_curr_slave_chain > P_FACTOR
THEN
v_curr_slave_chain := 1;
END IF;
END LOOP;
/* fin de la distribution des tâches entre les process slave */
FOR CHAINE_COURANTE
IN ( SELECT CHAIN_NAME,
MAX (step_name) "STARTING_STEP",
MIN (step_name) "ENDING_STEP"
FROM DBA_SCHEDULER_CHAIN_STEPS
WHERE CHAIN_NAME LIKE 'SLAVE_CHAIN%'
GROUP BY chain_name)
LOOP
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAINE_COURANTE.CHAIN_NAME,
'TRUE',
'START ' || CHAINE_COURANTE.STARTING_STEP); --je positionne mon step de depart
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAINE_COURANTE.CHAIN_NAME,
CHAINE_COURANTE.ENDING_STEP || ' COMPLETED',
'END'); -- je positionne mon step de fin
v_last_step := CHAINE_COURANTE.STARTING_STEP; --cette variable mer sert a connaitre le dernier step exécuté donc le step de démarrage a ce stade la procedure
FOR STEP_COURANT
IN ( SELECT STEP_NAME
FROM DBA_SCHEDULER_CHAIN_STEPS
WHERE CHAIN_NAME = CHAINE_COURANTE.CHAIN_NAME
AND STEP_NAME <> CHAINE_COURANTE.STARTING_STEP
ORDER BY REPLACE (step_name, '_', 'Z') DESC)
LOOP
/* ici on balaye l'ensemble des steps de la chain en cours en triant le nom par ordre decroissant ce qui nous fera finir par le step de fin, j'exclue le step de démarrage
car celui-ci a déjà été manuellement positionné ci-dessus*/
--DBMS_OUTPUT.PUT_LINE('chaine: '||CHAINE_COURANTE.CHAIN_NAME||', STEP: '||STEP_COURANT.STEP_NAME); --affichage de controle oseb
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAINE_COURANTE.CHAIN_NAME,
v_last_step || ' COMPLETED',
'START ' || STEP_COURANT.STEP_NAME);
/*cette regle me permet d'indiquer à oracle que le dernier step complété lance le step courant dans la boucle qui devient alors le dernier step lancé et ainsi de suite jusqu'au dernier*/
v_last_step := STEP_COURANT.STEP_NAME;
END LOOP;
END LOOP;
/* FIN phase de création des chaines maitres/esclaves */
/* Debut phase de lancement et monitoring */
DBMS_SCHEDULER.RUN_CHAIN
(
chain_name => 'SYS.MASTER_CHAIN'
,start_steps => null
);--lancement de la chaine maitre
WHILE cpt <> 0
LOOP
SELECT COUNT (*)
INTO cpt
FROM dba_scheduler_running_chains
WHERE chain_name = 'MASTER_CHAIN';
DBMS_LOCK.SLEEP (30);
END LOOP;
/* FIN phase de lancement et monitoring */
/* DEBUT nettoyage des chaines */
FOR CLEAN in (
SELECT chain_name
FROM SYS.DBA_SCHEDULER_CHAINS
WHERE chain_name LIKE 'MASTER_%' OR chain_name LIKE 'SLAVE_%'
) LOOP
dbms_scheduler.drop_chain(CLEAN.CHAIN_NAME,force => TRUE);
END LOOP;
/* FIN nettoyage des chaines */
/* DEBUT nettoyage des programmes */
FOR CLEAN in (
SELECT P.PROGRAM_NAME
FROM SYS.DBA_OBJECTS O, SYS.DBA_SCHEDULER_PROGRAMS P
WHERE O.OWNER = P.OWNER
AND O.OBJECT_NAME = P.PROGRAM_NAME
AND O.OBJECT_TYPE = 'PROGRAM'
AND O.OWNER = 'SYS'
AND PROGRAM_TYPE = 'PLSQL_BLOCK'
AND PROGRAM_NAME LIKE 'PRG_PARA_%') LOOP
DBMS_SCHEDULER.DROP_PROGRAM(CLEAN.PROGRAM_NAME);
END LOOP;
/* FIN nettoyage des programmes */
EXCEPTION
WHEN OTHERS
THEN
v_code := SQLCODE;
v_errm := SUBSTR (SQLERRM, 1, 64);
DBMS_OUTPUT.PUT_LINE (
'Sortie de la procedure en erreur: ORA-' || v_code || '- ' || v_errm);
END;
/
N’hésitez pas à nous faire des retours 😉
Continuez votre lecture sur le blog :
- Réplication MySQL : Resynchronisation d’un Slave MySQL (Capdata team) [MySQL]
- Meltdown sur OLE 7 et performances (Capdata team) [Oracle]
- Oracle et SQL Server: Les Statistiques (Benjamin VESAN) [OracleSQL Server]
- Déplacer le répertoire de données (datadir) MySQL vers un nouvel emplacement sur Debian (Capdata team) [MySQL]
- Oracle Text pour DBA Oracle : Partie 2 (Capdata team) [Oracle]