{"id":6011,"date":"2016-06-15T11:23:52","date_gmt":"2016-06-15T10:23:52","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=6011"},"modified":"2019-09-13T13:01:31","modified_gmt":"2019-09-13T12:01:31","slug":"chainesoracle","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/","title":{"rendered":"Les Chaines Oracle"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Hello amis dba’s et bienvenue sur cet article !<\/p>\n

Au menu aujourd’hui un cas concret d’utilisation d’un sujet assez peu abord\u00e9 et pourtant bien utile: les chaines Oracle.<\/p>\n

Les chaines sont un composant du planificateur Oracle qui permet de g\u00e9rer une s\u00e9rie d’actions d\u00e9clench\u00e9es hi\u00e9rarchiquement un peu \u00e0 la mani\u00e8re d’un ordonnanceur. On r\u00e9alise une action A si celle-ci \u00e9choue on part sur une action B sinon on lance C etc etc..<\/p>\n

Dans le cas d’utilisation qui suit nous \u00e9tudions 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\u00e9lioration.<\/p>\n

Nous partons d’une \u00e9dition standard (donc pas de parall\u00e9lisme) en 10.2.0.4 et passerons sur les modifications faites \u00e0 la commande de calcul elle-m\u00eame (auto-echantillonnage, exclusion des tables de travail applicatives etc)<\/p>\n

Globalement partant de l’id\u00e9e qu’un calcul de statistiques r\u00e9alise un calcul s\u00e9quentiel par objet nous allons “simuler” une forme de parall\u00e9lisme en nous appuyant sur les chaines:<\/p>\n

Une chaine est associ\u00e9e \u00e0 un job qui est cr\u00e9\u00e9 dynamiquement par Oracle ou d\u00e9fini explicitement, ce job lance lui-m\u00eame des programmes ou d’autres chaines imbriqu\u00e9es qui cr\u00e9ent elles-m\u00eame d’autres jobs qui lancent des programmes. Si vous n’avez rien compris c’est normal moi non plus \ud83d\ude42 donc voyons de quelle mani\u00e8re cela peut \u00eatre utile pour notre cas.<\/p>\n

Nous souhaitons avoir une proc\u00e9dure qui accepte en param\u00e8tre le “degr\u00e9” de parall\u00e9lisme et en param\u00e8tre optionnel le nom du sch\u00e9ma concern\u00e9 par le calcul de statistique (par d\u00e9faut la valeur est % donc tous les sch\u00e9mas seront embarqu\u00e9s s’il n’y en a pas un de pr\u00e9cis\u00e9). En substance nous allons cr\u00e9er une premi\u00e8re chaine “MASTER” qui lancera en simultan\u00e9e autant de chaine “SLAVE” que le degr\u00e9 de parall\u00e9lisme indiqu\u00e9. Chaque chaine “SLAVE” se verra attribuer des t\u00e2ches qui seront elles-m\u00eames lanc\u00e9es s\u00e9quentiellement, chacune de ces t\u00e2ches correspondantes a un calcul de stats pour une seule table. la proc\u00e9dure est l\u00e0 pour cr\u00e9er dynamiquement le jeu de chaine qui va bien pour arriver \u00e0 cette organisation (merci paint pour la qualit\u00e9 irr\u00e9prochable de ce sch\u00e9ma :)):<\/p>\n

\"\"<\/p>\n

 <\/p>\n

Dans le vocabulaire oracle une t\u00e2che correspond \u00e0 un programme qui pour nous correspond \u00e0 une commande de calcul de statistiques pour une seule table. Chaque \u00e9tape ou step de chaque chaine slave lance un programme et donc un calcul de statistique pour une table.<\/p>\n

Le d\u00e9but de la proc\u00e9dure se charge de cr\u00e9er le jeu de chaine complet (maitre\/esclave):<\/p>\n

\r\n(...)\r\n   \/* Debut phase de cr\u00e9ation des chaines maitres\/esclaves *\/\r\n   \/* debut cr\u00e9ation de la chaine maitre *\/\r\n   SYS.DBMS_SCHEDULER.CREATE_CHAIN (chain_name            => 'MASTER_CHAIN',\r\n                                    rule_set_name         => NULL,\r\n                                    evaluation_interval   => NULL,\r\n                                    comments              => NULL);\r\n   SYS.DBMS_SCHEDULER.ENABLE (name => 'MASTER_CHAIN');\r\n\r\n   \/* fin cr\u00e9ation de la chaine maitre *\/\r\n\r\n   FOR I IN 1 .. P_FACTOR\r\n   LOOP\r\n      v_chain_start := v_chain_start || 'SLAVE_CHAIN_' || I || ',';\r\n      v_chain_stop := v_chain_stop || 'SLAVE_CHAIN_' || I || ' COMPLETED AND ';\r\n\r\n      \/* debut creation des chaines esclaves qui seront lanc\u00e9es en parall\u00e9le par la chain maitre *\/\r\n      SYS.DBMS_SCHEDULER.CREATE_CHAIN (chain_name            => 'SLAVE_CHAIN_' || I,\r\n                                       rule_set_name         => NULL,\r\n                                       evaluation_interval   => NULL,\r\n                                       comments              => NULL);\r\n      SYS.DBMS_SCHEDULER.ENABLE (name => 'SLAVE_CHAIN_' || I);\r\n      \/* pour chaque chaine slave un step dans la chaine master correspondant doit \u00eatre cr\u00e9\u00e9 *\/\r\n      SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (\r\n         chain_name     => 'MASTER_CHAIN',\r\n         step_name      => 'SLAVE_CHAIN_' || I,\r\n         program_name   => 'SLAVE_CHAIN_' || I);\r\n   END LOOP;\r\n\r\n (...)\r\n<\/pre>\n
La variable P_FACTOR correspond \u00e0 la valeur pass\u00e9e en param\u00e8tre de la proc\u00e9dure qui d\u00e9termine le nombre de threads de chaines esclaves qui seront cr\u00e9\u00e9s. Dans la boucle chaque chaine esclave est d\u00e9finie comme une \u00e9tape de la chaine maitre. il s’agit de nested chain ou chaines imbriqu\u00e9es. c’est la commande DBMS_SCHEDULER.CREATE_CHAIN qui est utilis\u00e9e pour \u00e7a.<\/p>\n
Une chaine oracle doit comporter des \u00e9tapes mais aussi des r\u00e8gles de d\u00e9clenchement, par d\u00e9faut si aucune r\u00e8gle n’existe aucun step n’est lanc\u00e9, m\u00eame s’ils sont d\u00e9j\u00e0 pr\u00e9sents dans la chaine.<\/p>\n
Pour d\u00e9finir ces r\u00e8gles on utilise DBMS_SCHEDULER.DEFINE_CHAIN_RULE<\/p>\n
 \r\n (...)\r\n\r\n  \/* fin creation des chaines esclaves qui seront lanc\u00e9es en parall\u00e9le par la chain maitre *\/\r\n\r\n   \/* la ligne ci-dessous sert a cr\u00e9er la r\u00e8gle qui permet de lancer tous les process esclaves en parall\u00e8le *\/\r\n   DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('MASTER_CHAIN',\r\n                                     'TRUE',\r\n                                     TRIM (TRAILING ',' FROM v_chain_start));\r\n\r\n   DBMS_SCHEDULER.DEFINE_CHAIN_RULE (\r\n      'MASTER_CHAIN',\r\n      SUBSTR (v_chain_stop, 1, LENGTH (v_chain_stop) - 5),\r\n      'END');\r\n\r\n(...)\r\n<\/pre>\n
Ind\u00e9pendamment des relations qui peuvent exister entre chaque step, la chaine doit avoir une condition qui se v\u00e9rifie tout le temps pour permettre \u00e0  au moins une premi\u00e8re \u00e9tape de se d\u00e9clencher lorsqu’aucun step de d\u00e9part n’est indiqu\u00e9 dans la commande de lancement. Elle doit \u00e9galement comporter une condition de sortie, de pr\u00e9f\u00e9rence une condition qui a 100% de chance de se produire. Dans le cas ci-dessus m\u00eame si on ne voit pas tr\u00e8s bien a quoi correspondent les variable v_chain_start & v_chain_stop ( je compose l’action \u00e0 la vol\u00e9e car il d\u00e9pend de P_FACTOR qui n’est pas fixe) j’indique \u00e0 Oracle: “Lorsque TRUE renvoit vrai (tout le temps \u00e0 priori) tu d\u00e9marres toutes les chaines esclaves, et lorsqu’elles sont toutes completed la chaine maitre se termine”<\/p>\n
A ce stade, nous devons cr\u00e9er un programme par table et les affecter en tant que step \u00e0  chaque chaine esclave:<\/p>\n
 \r\nFOR table_courante\r\n      IN (  SELECT table_name, owner\r\n              FROM dba_tables\r\n             WHERE     owner LIKE utilisateur\r\n                   AND NOT REGEXP_LIKE (table_name, '^TMP_[0-9]+$')\r\n                   AND table_name NOT LIKE 'TMP\\_%\\__\\_________' ESCAPE '\\'\r\n                   AND table_name NOT LIKE '%TEST%'\r\n                   AND table_name NOT LIKE 'BIN$%'\r\n                   AND OWNER NOT IN\r\n                          ('SYS',\r\n                           'SYSTEM',\r\n                           'TSMSYS',\r\n                           'ORACLE_OCM',\r\n                           'DIP',\r\n                           'DBSNMP',\r\n                           'DBSNMP',\r\n                           'OUTLN',\r\n                           'WMSYS')\r\n          ORDER BY BLOCKS DESC NULLS LAST)\r\n   LOOP\r\n      SYS.DBMS_SCHEDULER.CREATE_PROGRAM (\r\n         program_name          => 'SYS.PRG_PARA_' || v_cpt_prg,\r\n         program_type          => 'PLSQL_BLOCK',\r\n         program_action        => 'BEGIN \r\n                                dbms_stats.gather_table_stats(\r\n                                    ownname=>'\r\n                                 || DBMS_ASSERT.enquote_literal (\r\n                                       table_courante.owner)\r\n                                 || ',\r\n                                    tabname=>'\r\n                                 || DBMS_ASSERT.enquote_literal (\r\n                                       table_courante.table_name)\r\n                                 || ',\r\n                                    granularity=>''ALL'',\r\n                                    estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,\r\n                                    cascade=>TRUE,\r\n                                    degree=>DBMS_STATS.AUTO_DEGREE);\r\n                                END;',\r\n         number_of_arguments   => 0,\r\n         enabled               => TRUE,\r\n         comments              => NULL);\r\n\r\n      SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (\r\n         chain_name     => 'SLAVE_CHAIN_' || v_curr_slave_chain,\r\n         step_name      => 'STEP_PARA_' || v_cpt_prg,\r\n         program_name   => 'SYS.PRG_PARA_' || v_cpt_prg);\r\n\r\n      \/* debut ajout du prog courant en tant que step du process slave courant *\/\r\n      SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (\r\n         chain_name     => 'SLAVE_CHAIN_' || v_curr_slave_chain,\r\n         step_name      => 'STEP_PARA_' || v_cpt_prg,\r\n         program_name   => 'SYS.PRG_PARA_' || v_cpt_prg);\r\n      \/* fin ajout du prog courant en tant que step du process slave courant *\/\r\n\r\n      \/* v_curr_slave_chain  :cette variable me sert \u00e0 me positionner sur la chaine esclave suivante *\/\r\n      v_curr_slave_chain := v_curr_slave_chain + 1;\r\n      v_cpt_prg := v_cpt_prg + 1;\r\n\r\n      IF v_curr_slave_chain > P_FACTOR\r\n      THEN\r\n         v_curr_slave_chain := 1;\r\n      END IF;\r\n   END LOOP;\r\n\r\n   \/* fin de la distribution des t\u00e2ches entre les process slave *\/\r\n<\/pre>\n
Dans cette partie les programmes correspondent \u00e0 des calcul de statistiques mais il pourrait s’agir de tout autre chose. Chaque step est affect\u00e9 \u00e0 une chaine de mani\u00e8re circulaire ajout\u00e9 \u00e0 l’order by BLOCK de la requ\u00eate se s\u00e9lection des tables ceci permet de ne pas affecter les tables les plus lourdes \u00e0 une seule et m\u00eame chaine esclave qui pourrait finir bien apr\u00e8s les autres et limiter l\u2019int\u00e9r\u00eat de parall\u00e9liser.<\/p>\n
Comme on a vu pr\u00e9c\u00e9demment une chaine a besoin d’\u00e9tapes et de conditions. Pour cr\u00e9er ces conditions on utilise deux boucles imbriqu\u00e9es:<\/p>\n
 \r\n  FOR CHAINE_COURANTE\r\n      IN (  SELECT CHAIN_NAME,\r\n                   MAX (step_name) \"STARTING_STEP\",\r\n                   MIN (step_name) \"ENDING_STEP\"\r\n              FROM DBA_SCHEDULER_CHAIN_STEPS\r\n             WHERE CHAIN_NAME LIKE 'SLAVE_CHAIN%'\r\n          GROUP BY chain_name)\r\n   LOOP\r\n      DBMS_SCHEDULER.DEFINE_CHAIN_RULE (\r\n         CHAINE_COURANTE.CHAIN_NAME,\r\n         'TRUE',\r\n         'START ' || CHAINE_COURANTE.STARTING_STEP); --je positionne mon step de depart\r\n      DBMS_SCHEDULER.DEFINE_CHAIN_RULE (\r\n         CHAINE_COURANTE.CHAIN_NAME,\r\n         CHAINE_COURANTE.ENDING_STEP || ' COMPLETED',\r\n         'END');                              -- je positionne mon step de fin\r\n      v_last_step := CHAINE_COURANTE.STARTING_STEP; --cette variable mer sert a connaitre le dernier step ex\u00e9cut\u00e9 donc le step de d\u00e9marrage a ce stade la procedure\r\n\r\n      FOR STEP_COURANT\r\n         IN (  SELECT STEP_NAME\r\n                 FROM DBA_SCHEDULER_CHAIN_STEPS\r\n                WHERE CHAIN_NAME = CHAINE_COURANTE.CHAIN_NAME\r\n                      AND STEP_NAME <> CHAINE_COURANTE.STARTING_STEP\r\n             ORDER BY REPLACE (step_name, '_', 'Z') DESC)\r\n      LOOP\r\n         \/* 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\u00e9marrage\r\n         car celui-ci a d\u00e9j\u00e0 \u00e9t\u00e9 manuellement positionn\u00e9 ci-dessus*\/\r\n\r\n         --DBMS_OUTPUT.PUT_LINE('chaine: '||CHAINE_COURANTE.CHAIN_NAME||', STEP: '||STEP_COURANT.STEP_NAME); --affichage de controle oseb\r\n         DBMS_SCHEDULER.DEFINE_CHAIN_RULE (\r\n            CHAINE_COURANTE.CHAIN_NAME,\r\n            v_last_step || ' COMPLETED',\r\n            'START ' || STEP_COURANT.STEP_NAME);\r\n\r\n         \/*cette regle me permet d'indiquer \u00e0 oracle que le dernier step compl\u00e9t\u00e9 lance le step courant dans la boucle qui devient alors le dernier step lanc\u00e9 et ainsi de suite jusqu'au dernier*\/\r\n\r\n         v_last_step := STEP_COURANT.STEP_NAME;\r\n      END LOOP;\r\n   END LOOP;\r\n\/* FIN phase de cr\u00e9ation des chaines maitres\/esclaves *\/\r\n<\/pre>\n
Une fois ces r\u00e8gles d\u00e9finies il ne reste plus qu’\u00e0 lancer la chaine maitre qui lancera en cascade toutes les chaines esclaves et  avec le syst\u00e8me pouss\u00e9 de logging du scheduler oracle nous allons pouvoir connaitre quel temps on a mis pour chaque table individuellement.<\/p>\n
Comme avec le parall\u00e9lisme classique il faut \u00eatre tr\u00e8s vigilant sur la consommation cpu que cela peut engendrer, donc ne pas exag\u00e9rer le nombre de thread cr\u00e9\u00e9s…<\/p>\n
Pour ceux qui souhaitent faire le test voici le code complet de la proc\u00e9dure:<\/p>\n
 <\/p>\n
 \r\nCREATE OR REPLACE PROCEDURE SYS.CALCUL_STATS_PARALLEL (\r\n   P_FACTOR       NUMBER,\r\n   Utilisateur    VARCHAR2 DEFAULT '%')\r\nIS\r\n   v_code               NUMBER;\r\n   v_errm               VARCHAR2 (64);\r\n   v_chain_start        VARCHAR2 (30000) := 'START ';\r\n   v_chain_stop         VARCHAR2 (30000) := NULL;\r\n   v_curr_slave_chain   NUMBER := 1;\r\n   v_last_step          VARCHAR2 (30);\r\n   v_cpt_prg            NUMBER (5) := 0;\r\n   CLEAN                VARCHAR2 (32);\r\n   cpt                  NUMBER (10) := 1;\r\nBEGIN\r\n   \/*********************************************************************************************\/\r\n   \/* C@pdata LE 17 mai 2016\r\n   \/********************************************************************************************\/\r\n\r\n   \/* Debut phase de cr\u00e9ation des chaines maitres\/esclaves *\/\r\n   \/* debut cr\u00e9ation de la chaine maitre *\/\r\n   SYS.DBMS_SCHEDULER.CREATE_CHAIN (chain_name            => 'MASTER_CHAIN',\r\n                                    rule_set_name         => NULL,\r\n                                    evaluation_interval   => NULL,\r\n                                    comments              => NULL);\r\n   SYS.DBMS_SCHEDULER.ENABLE (name => 'MASTER_CHAIN');\r\n\r\n   \/* fin cr\u00e9ation de la chaine maitre *\/\r\n\r\n   FOR I IN 1 .. P_FACTOR\r\n   LOOP\r\n      v_chain_start := v_chain_start || 'SLAVE_CHAIN_' || I || ',';\r\n      v_chain_stop := v_chain_stop || 'SLAVE_CHAIN_' || I || ' COMPLETED AND ';\r\n\r\n      \/* debut creation des chaines esclaves qui seront lanc\u00e9es en parall\u00e9le par la chain maitre *\/\r\n      SYS.DBMS_SCHEDULER.CREATE_CHAIN (chain_name            => 'SLAVE_CHAIN_' || I,\r\n                                       rule_set_name         => NULL,\r\n                                       evaluation_interval   => NULL,\r\n                                       comments              => NULL);\r\n      SYS.DBMS_SCHEDULER.ENABLE (name => 'SLAVE_CHAIN_' || I);\r\n      \/* pour chaque chaine slave un step dans la chaine master correspondant doit \u00eatre cr\u00e9\u00e9 *\/\r\n      SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (\r\n         chain_name     => 'MASTER_CHAIN',\r\n         step_name      => 'SLAVE_CHAIN_' || I,\r\n         program_name   => 'SLAVE_CHAIN_' || I);\r\n   END LOOP;\r\n\r\n   \/* fin creation des chaines esclaves qui seront lanc\u00e9es en parall\u00e9le par la chain maitre *\/\r\n\r\n   \/* la ligne ci-dessous sert a cr\u00e9er la r\u00e8gle qui permet de lancer tous les process esclaves en parall\u00e8le *\/\r\n   DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('MASTER_CHAIN',\r\n                                     'TRUE',\r\n                                     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 *\/\r\n   \/* la ligne ci-dessous sert \u00e0 cr\u00e9er la regle de fin d'ex\u00e9cution de la chaine maitre on remanipule un peu la cha\u00eene au passage *\/\r\n\r\n   DBMS_SCHEDULER.DEFINE_CHAIN_RULE (\r\n      'MASTER_CHAIN',\r\n      SUBSTR (v_chain_stop, 1, LENGTH (v_chain_stop) - 5),\r\n      'END');\r\n\r\n   --dbms_output.put_line('chaine: '||substr(v_chain_stop,1,length(v_chain_stop)-5));\r\n   \/* debut de la distribution des t\u00e2ches entre les process slave *\/\r\n\r\n   FOR table_courante\r\n      IN (  SELECT table_name, owner\r\n              FROM dba_tables\r\n             WHERE     owner LIKE utilisateur\r\n                   AND NOT REGEXP_LIKE (table_name, '^TMP_[0-9]+$')\r\n                   AND table_name NOT LIKE 'TMP\\_%\\__\\_________' ESCAPE '\\'\r\n                   AND table_name NOT LIKE '%TEST%'\r\n                   AND table_name NOT LIKE 'BIN$%'\r\n                   AND OWNER NOT IN\r\n                          ('SYS',\r\n                           'SYSTEM',\r\n                           'TSMSYS',\r\n                           'ORACLE_OCM',\r\n                           'DIP',\r\n                           'DBSNMP',\r\n                           'DBSNMP',\r\n                           'OUTLN',\r\n                           'WMSYS')\r\n          ORDER BY BLOCKS DESC NULLS LAST)\r\n   LOOP\r\n      SYS.DBMS_SCHEDULER.CREATE_PROGRAM (\r\n         program_name          => 'SYS.PRG_PARA_' || v_cpt_prg,\r\n         program_type          => 'PLSQL_BLOCK',\r\n         program_action        => 'BEGIN \r\n                                dbms_stats.gather_table_stats(\r\n                                    ownname=>'\r\n                                 || DBMS_ASSERT.enquote_literal (\r\n                                       table_courante.owner)\r\n                                 || ',\r\n                                    tabname=>'\r\n                                 || DBMS_ASSERT.enquote_literal (\r\n                                       table_courante.table_name)\r\n                                 || ',\r\n                                    granularity=>''ALL'',\r\n                                    estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,\r\n                                    cascade=>TRUE,\r\n                                    degree=>DBMS_STATS.AUTO_DEGREE);\r\n                                END;',\r\n         number_of_arguments   => 0,\r\n         enabled               => TRUE,\r\n         comments              => NULL);\r\n\r\n      SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (\r\n         chain_name     => 'SLAVE_CHAIN_' || v_curr_slave_chain,\r\n         step_name      => 'STEP_PARA_' || v_cpt_prg,\r\n         program_name   => 'SYS.PRG_PARA_' || v_cpt_prg);\r\n\r\n      \/* debut ajout du prog courant en tant que step du process slave courant *\/\r\n      SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (\r\n         chain_name     => 'SLAVE_CHAIN_' || v_curr_slave_chain,\r\n         step_name      => 'STEP_PARA_' || v_cpt_prg,\r\n         program_name   => 'SYS.PRG_PARA_' || v_cpt_prg);\r\n      \/* fin ajout du prog courant en tant que step du process slave courant *\/\r\n\r\n      \/* v_curr_slave_chain  :cette variable me sert \u00e0 me positionner sur la chaine esclave suivante *\/\r\n      v_curr_slave_chain := v_curr_slave_chain + 1;\r\n      v_cpt_prg := v_cpt_prg + 1;\r\n\r\n      IF v_curr_slave_chain > P_FACTOR\r\n      THEN\r\n         v_curr_slave_chain := 1;\r\n      END IF;\r\n   END LOOP;\r\n\r\n   \/* fin de la distribution des t\u00e2ches entre les process slave *\/\r\n\r\n   FOR CHAINE_COURANTE\r\n      IN (  SELECT CHAIN_NAME,\r\n                   MAX (step_name) \"STARTING_STEP\",\r\n                   MIN (step_name) \"ENDING_STEP\"\r\n              FROM DBA_SCHEDULER_CHAIN_STEPS\r\n             WHERE CHAIN_NAME LIKE 'SLAVE_CHAIN%'\r\n          GROUP BY chain_name)\r\n   LOOP\r\n      DBMS_SCHEDULER.DEFINE_CHAIN_RULE (\r\n         CHAINE_COURANTE.CHAIN_NAME,\r\n         'TRUE',\r\n         'START ' || CHAINE_COURANTE.STARTING_STEP); --je positionne mon step de depart\r\n      DBMS_SCHEDULER.DEFINE_CHAIN_RULE (\r\n         CHAINE_COURANTE.CHAIN_NAME,\r\n         CHAINE_COURANTE.ENDING_STEP || ' COMPLETED',\r\n         'END');                              -- je positionne mon step de fin\r\n      v_last_step := CHAINE_COURANTE.STARTING_STEP; --cette variable mer sert a connaitre le dernier step ex\u00e9cut\u00e9 donc le step de d\u00e9marrage a ce stade la procedure\r\n\r\n      FOR STEP_COURANT\r\n         IN (  SELECT STEP_NAME\r\n                 FROM DBA_SCHEDULER_CHAIN_STEPS\r\n                WHERE CHAIN_NAME = CHAINE_COURANTE.CHAIN_NAME\r\n                      AND STEP_NAME <> CHAINE_COURANTE.STARTING_STEP\r\n             ORDER BY REPLACE (step_name, '_', 'Z') DESC)\r\n      LOOP\r\n         \/* 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\u00e9marrage\r\n         car celui-ci a d\u00e9j\u00e0 \u00e9t\u00e9 manuellement positionn\u00e9 ci-dessus*\/\r\n\r\n         --DBMS_OUTPUT.PUT_LINE('chaine: '||CHAINE_COURANTE.CHAIN_NAME||', STEP: '||STEP_COURANT.STEP_NAME); --affichage de controle oseb\r\n         DBMS_SCHEDULER.DEFINE_CHAIN_RULE (\r\n            CHAINE_COURANTE.CHAIN_NAME,\r\n            v_last_step || ' COMPLETED',\r\n            'START ' || STEP_COURANT.STEP_NAME);\r\n\r\n         \/*cette regle me permet d'indiquer \u00e0 oracle que le dernier step compl\u00e9t\u00e9 lance le step courant dans la boucle qui devient alors le dernier step lanc\u00e9 et ainsi de suite jusqu'au dernier*\/\r\n\r\n         v_last_step := STEP_COURANT.STEP_NAME;\r\n      END LOOP;\r\n   END LOOP;\r\n\/* FIN phase de cr\u00e9ation des chaines maitres\/esclaves *\/\r\n\r\n\/* Debut phase de lancement et monitoring *\/\r\nDBMS_SCHEDULER.RUN_CHAIN\r\n   (\r\n    chain_name => 'SYS.MASTER_CHAIN'\r\n    ,start_steps => null\r\n   );--lancement de la chaine maitre\r\n\r\nWHILE cpt <> 0\r\n   LOOP\r\n      SELECT COUNT (*)\r\n        INTO cpt\r\n        FROM dba_scheduler_running_chains\r\n       WHERE chain_name = 'MASTER_CHAIN';\r\n       DBMS_LOCK.SLEEP (30);\r\n   END LOOP;\r\n\r\n\/* FIN phase de lancement et monitoring *\/\r\n\r\n\/* DEBUT nettoyage des chaines *\/\r\n\r\nFOR CLEAN in (\r\nSELECT   chain_name\r\n  FROM SYS.DBA_SCHEDULER_CHAINS\r\nWHERE chain_name LIKE 'MASTER_%' OR chain_name LIKE 'SLAVE_%'\r\n                    ) LOOP\r\ndbms_scheduler.drop_chain(CLEAN.CHAIN_NAME,force  => TRUE);\r\nEND LOOP;\r\n\/* FIN nettoyage des chaines *\/\r\n\/* DEBUT nettoyage des programmes *\/\r\nFOR CLEAN in (\r\nSELECT  P.PROGRAM_NAME\r\n  FROM SYS.DBA_OBJECTS O, SYS.DBA_SCHEDULER_PROGRAMS P\r\n WHERE     O.OWNER = P.OWNER\r\n       AND O.OBJECT_NAME = P.PROGRAM_NAME\r\n       AND O.OBJECT_TYPE = 'PROGRAM'\r\n       AND O.OWNER = 'SYS'\r\n       AND PROGRAM_TYPE = 'PLSQL_BLOCK'\r\n       AND PROGRAM_NAME LIKE 'PRG_PARA_%') LOOP\r\nDBMS_SCHEDULER.DROP_PROGRAM(CLEAN.PROGRAM_NAME);\r\nEND LOOP;\r\n\/* FIN nettoyage des programmes *\/\r\n\r\nEXCEPTION\r\n   WHEN OTHERS\r\n   THEN\r\n      v_code := SQLCODE;\r\n      v_errm := SUBSTR (SQLERRM, 1, 64);\r\n      DBMS_OUTPUT.PUT_LINE (\r\n         'Sortie de la procedure en erreur: ORA-' || v_code || '- ' || v_errm);\r\nEND;\r\n\/\r\n<\/pre>\n
 <\/p>\n
N’h\u00e9sitez pas \u00e0 nous faire des retours \ud83d\ude09<\/p>\n\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"
Hello amis dba’s et bienvenue sur cet article ! Au menu aujourd’hui un cas concret d’utilisation d’un sujet assez peu abord\u00e9 et pourtant bien utile: les chaines Oracle. Les chaines sont un composant du planificateur Oracle qui permet de g\u00e9rer… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":7814,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-6011","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle"],"yoast_head":"\nLes Chaines Oracle - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Les Chaines Oracle - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"Hello amis dba’s et bienvenue sur cet article ! Au menu aujourd’hui un cas concret d’utilisation d’un sujet assez peu abord\u00e9 et pourtant bien utile: les chaines Oracle. Les chaines sont un composant du planificateur Oracle qui permet de g\u00e9rer… Continuer la lecture →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2016-06-15T10:23:52+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-09-13T12:01:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/06\/chaines.png\" \/>\n\t<meta property=\"og:image:width\" content=\"652\" \/>\n\t<meta property=\"og:image:height\" content=\"363\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Capdata team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Capdata team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"15 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/\"},\"author\":{\"name\":\"Capdata team\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9\"},\"headline\":\"Les Chaines Oracle\",\"datePublished\":\"2016-06-15T10:23:52+00:00\",\"dateModified\":\"2019-09-13T12:01:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/\"},\"wordCount\":934,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"articleSection\":[\"Oracle\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/\",\"name\":\"Les Chaines Oracle - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2016-06-15T10:23:52+00:00\",\"dateModified\":\"2019-09-13T12:01:31+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Les Chaines Oracle\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9\",\"name\":\"Capdata team\",\"sameAs\":[\"https:\/\/www.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Les Chaines Oracle - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/","og_locale":"fr_FR","og_type":"article","og_title":"Les Chaines Oracle - Capdata TECH BLOG","og_description":"Hello amis dba’s et bienvenue sur cet article ! Au menu aujourd’hui un cas concret d’utilisation d’un sujet assez peu abord\u00e9 et pourtant bien utile: les chaines Oracle. Les chaines sont un composant du planificateur Oracle qui permet de g\u00e9rer… Continuer la lecture →","og_url":"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2016-06-15T10:23:52+00:00","article_modified_time":"2019-09-13T12:01:31+00:00","og_image":[{"width":652,"height":363,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2016\/06\/chaines.png","type":"image\/png"}],"author":"Capdata team","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Capdata team","Dur\u00e9e de lecture estim\u00e9e":"15 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/"},"author":{"name":"Capdata team","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9"},"headline":"Les Chaines Oracle","datePublished":"2016-06-15T10:23:52+00:00","dateModified":"2019-09-13T12:01:31+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/"},"wordCount":934,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"articleSection":["Oracle"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/","url":"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/","name":"Les Chaines Oracle - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2016-06-15T10:23:52+00:00","dateModified":"2019-09-13T12:01:31+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/chainesoracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Les Chaines Oracle"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9","name":"Capdata team","sameAs":["https:\/\/www.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/6011","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=6011"}],"version-history":[{"count":22,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/6011\/revisions"}],"predecessor-version":[{"id":6126,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/6011\/revisions\/6126"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/7814"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=6011"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=6011"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=6011"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}