Voici une fonctionnalité disponible depuis la version 11gR2 plutôt méconnue que j’ai eu l’occasion d’utiliser pour exécuter des séries d’updates massifs sur de (très) grosses tables partitionnées ou non en un temps acceptable, afin de réaliser des anonymisations de données.
Il existe diverses méthodes pour optimiser les performances des updates massifs, à ce propos je vous recommande la lecture de cet article : 8 Bulk Update Methods Compared.
La note MOS 1066555.1 fournit de premières informations sur l’utilisation du package, de même que la documentation officielle.
Cet article présente succinctement l’utilisation générale du package DBMS_PARALLEL_EXECUTE (DPE), avec ses principales procédures et quelques exemples de contrôles.
Fonctionnement
Le principe est de découper un update (par exemple) en un certain nombre de flux parallèles travaillant sur des ensembles de lignes distincts répartis en « chunks », c’est à dire des paquets de lignes identifiées par leurs rowid qui seront traités dans un même lot.
Le traitement de chaque chunk est lancé par un job du scheduler Oracle dont on peut suivre l’avancement. Chaque chunk de lignes donne lieu a un commit ou un rollback distinct, et en fonction du statut de traitement de la tâche on peut aller programme un ou plusieurs retry du traitement d’un chunk en erreur (par exemple en cas de verrouillage temporaire).
L’intérêt majeur de ce package est qu’il fait le boulot de découpage de la table en chunks de lignes à notre place et exécute l’update sur ceux-ci en parallèle. Bien entendu, on peut utiliser ce package pour autre chose que des updates, et avec un peu d’astuce paralléliser toute sorte de traitements via le lancement par le scheduler Oracle, par exemple pour calculer des statistiques en parallèle.
Privilèges nécessaires : Le privilège CREATE JOB doit avoir été assigné à l’utilisateur exécutant le package.
La première étape est de créer une tâche (task) pour le traitement à effectuer et lui attribuer un nom qui permettra de la retrouver parmi d’autres. Il n’est pas possible de réutiliser une task existante, même terminée, il faudra d’abord la supprimer avec la procédure DROP_TASK, ce qui supprimera aussi ses chunks et données d’exécution.
Création d’une task
DBMS_PARALLEL_EXECUTE.create_task (task_name=>'TASK_UPD_MA_TABLE_1', comment=>'Upate MA_TABLE chunks rowid' );
Je vous conseille de donner des noms explicites à vos tasks et de les indicer via une variable passée à la procédure, dans la limites des 30 caractères permis. Il existe par ailleurs une procédure pour générer un nom de task à partir d’un préfixe donné (GENERATE_TASK_NAME).
Création des chunks
Il existe 3 méthodes de création des chunks couvrant la majorité des cas d’usage. Il existe une procédure pour supprimer les chunks d’une table en cas de relance de la task.
Les 3 procédures de génération de chunks sont les suivantes :
- CHUNKS_BY_NUMBER_COL
Cette procédure découpe la table en chunks basés sur la valeur d’une colonne spécifiée. La colonne doit être de type NUMBER. Basiquement, la répartition en chunk est faite en divisant uniformément l’intervalle MIN-MAX des valeurs de la colonne par la valeur du chunk_size indiqué dans la procédure.
begin dbms_parallel_execute.create_chunks_by_number_col ( task_name => 'TASK_CHUNKS_BY_COL', table_owner => user, table_name => 'MATABLE', table_column => ID', chunk_size => 1000 ); end; /
- CREATE_CHUNKS_BY_ROWID
Cette procédure, comme son nom l’indique, découpe la table par tranches de ROWID, c’est la plus utilisée car très simple à mettre en œuvre. La table ne peut pas être une table organisée en index (IOT). Pour cela, le paramètre by_row permet de spécifier quelle statistique de la table utiliser pour déterminer la taille du chunk :
- NUM_ROWS si by_row = TRUE
- BLOCKS si by_row = FALSE
begin dbms_parallel_execute.create_chunks_by_rowid ( task_name => 'TASK_CHUNKS_BY_ROWID', table_owner => user, table_name => 'MATABLE', by_row => true, chunk_size => 1000 ); end; /
- CREATE_CHUNKS_BY_SQL
Cette procédure permet d’utiliser une requête personnalisée pour créer le découpage en chunks. Celle-ci doit retourner 2 valeurs : START_ID et END_ID qui indiquent les bornes de chaque chunk. On utilisera cette procédure lorsque le découpage simple par rowid n’est pas adapté ou performant.
Le type de ces colonnes peut être soit ROWID pour avoir un découpage personnalisé par rowid ou de type NUMBER pour un découpage semblable à CHUNKS_BY_NUMBER_COL.
begin dbms_parallel_execute.create_chunks_by_sql ( task_name => 'TASK_CHUNKS_BY_SQL', sql_stmt => 'select 1 start_id, 2 end_id from dual union all select 3 start_id, 4 end_id from dual', by_rowid => false ); end; /
Exécution d’une task
Pour exécuter une task, on utilise la procédure run_task qui permet d’exécuter un ordre SQL sur l’ensemble des chunks précédemment affectés. Un ordre SQL peut ici être un simple update ou bien une procédure plus complexe dans un block PL/SQL BEGIN…END.
Pour chaque chunk, la procédure va lancer un job en parallèle via le scheduler Oracle, ceci dans la limite du paramètre « parallel_level » spécifié en paramètre de la procédure run_task. Il est donc déconseillé d’ajouter encore du parallélisme dans l’ordre SQL à exécuter !
Le plus important est d’écrire l’ordre à exécuter de telle manière qu’il utilise les variables « :start_id » et « :block_id« . Ceux-ci vont correspondre aux rowid ou aux nombres (dans le cas du découpage par number) de début et fin du chunk qui seront traités par un job.
Exemple dans le cas de chunks par plages de rowid :
l_sql_stmt := 'update EMPLOYEES e
SET e.salary = e.salary + 10
WHERE rowid BETWEEN :start_id AND :end_id';
Exemple dans le cas de chunks par valeur de la colonne manager_id :
l_sql_stmt := 'update EMPLOYEES e
SET e.salary = e.salary + 10
WHERE manager_id between :start_id and :end_id';
L’ordre SQL exécuté peut aussi être une procédure :
sql_stmt => 'BEGIN PROC(:start_id, :end_id); END;'
Lancement de la tâche :
dbms_parallel_execute.run_task(task_name => 'MY_TASK', sql_stmt => l_sql_stmt, language_flag => DBMS_SQL.NATIVE, parallel_level => 20);
- Si parallel_level n’est pas spécifié, le parallélisme par défaut est utilisé. Cette valeur indique donc le nombre de jobs du scheduler qui seront lancés simultanément, attention à rester dans les limites acceptables par votre système sous peine de le saturer !
- Il est possible également de spécifier une job_class qui sera utilisée par le scheduler pour exécuter la task sur un service donné (donc possiblement sur plusieurs instances), sinon ce sera la job_class par défaut, c’est à dire l’instance courante.
- En cas d’erreur, il est possible de reprendre l’exécution de la task avec la procédure resume_task (ci-dessous dans sa forme la plus simple).
DBMS_PARALLEL_EXECUTE.RESUME_TASK ( task_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Utilisation du package DPE
On trouve aisément dans la documentation Oracle un exemple complet d’utilisation où il suffit quasiment pour un cas simple de personnaliser l’ordre SQL à exécuter pour commencer à utiliser le package.
Il est aussi possible de développer son propre framework d’exécution pour un utilisation plus spécifique en utilisant les multiples procédures mises à disposition par le package et ainsi gérer soit-même la lecture des chunks, lire et modifier le statut de ces derniers ainsi que celui des tâches. Vous trouverez facilement des exemples de ce type, en commençant par ici : Doc Oracle 12c.
Quelques petites choses utiles à savoir
A l’utilisation de DPE, on peut rencontrer quelques problèmes, en voici quelques-uns parmi les plus courants :
- privilège « create job » non octroyé à l’utilisateur qui exécute le package, les jobs ne se lancent pas
- job_queue_processes : le paramètre doit être configuré correctement afin que les jobs parallèles puissent être lancés. La valeur par défaut est de 1000 en version 11gR2 et calculée (cpu_count * 20, sessions/4) en 19c.
- un chunk ne peut comporter des lignes ou blocs que sur un seul extent d’une table. Par conséquent, en fonction de la taille de ceux-ci, du chunk_size demandé et du remplissage des blocs de la table, on peut obtenir des situations avec un très grande nombre de chunks à traiter et/ou des chunks quasiment vides en dehors des premiers. Il faudra donc faire attention à bien utiliser des chunk_size adaptés. Il en est de même en travaillant par blocks (by_rows=>false).
- Limitation des chunks par les extents : un chunk par rowid ne peut pas s’étendre sur plusieurs extents, ce qui peut conduire à un très grand nombre de chunks à traiter si le chunk_size demandé est trop grand par rapport à la taille d’1 extent.
- Il existe, de plus, un bug sournois en version 11.2.0.4 car on ne le remarque pas forcément tout de suite, qui provoque un traitement en série et non en parallèle des chunks : (DBMS_PARALLEL_EXECUTE Is Not Distributing The Chunks Uniformly Resulting In Performance Issue (Doc ID 1916579.1). Il faut appliquer le patch 18966843 pour retrouver un fonctionnement nominal.
Statut des chunks et tâches
Un chunk peut passer par les 4 états ci-dessous, pendant l’exécution d’une task. Au départ il a le statut « UNASSIGNED ».
Le suivi des statuts des chunks permet de mesurer l’avancement du traitement, comme nous le verrons plus loin.
De même, pour une task, les différents statuts successifs qu’elle peut prendre est indiquée dans le tableau. Une fois la task créée et démarrée, elle commence par créer les chunks selon les critères demandés avant de les traiters. En fonction de la taille de la table, et du type de découpage demandé (notamment par valeur de colonne) la phase « CHUNKING » peut durer un peu…
| Name | Data Type | Value |
| Chunk Status Value | ||
| UNASSIGNED | NUMBER | 0 |
| ASSIGNED | NUMBER | 1 |
| PROCESSED | NUMBER | 2 |
| PROCESSED_WITH_ERROR | NUMBER | 3 |
| Task Status Value | ||
| CREATED | NUMBER | 1 |
| CHUNKING | NUMBER | 2 |
| CHUNKING_FAILED | NUMBER | 3 |
| NO_CHUNKS | NUMBER | 4 |
| CHUNKED | NUMBER | 5 |
| PROCESSING | NUMBER | 6 |
| FINISHED | NUMBER | 7 |
| FINISHED_WITH_ERROR | NUMBER | 8 |
| CRASHED | NUMBER | 9 |
Vues de suivi d’exécution
Le package fournit bien sûr quelques vues permettant le suivi des opérations sur les chunks et tasks, dont celles-ci avec leur pendant ‘ALL_ » et « USER_ » :
- DBA_PARALLEL_EXECUTE_CHUNKS
- DBA_PARALLEL_EXECUTE_TASKS
- DBA_SCHEDULER_JOB_RUN_DETAIL fournit d’autres infos intéressantes vues côté scheduler Oracle
- DBMS_PARALLEL_EXECUTE_CHUNKS$ est très utile car elle permet d’obtenir des infos détaillées sur le contenu et le statut d’un chunk en particulier.
SQL>desc dbms_parallel_execute_chunks$ Name Null? Type ----------------------------------------- -------- ---------------------------- CHUNK_ID NOT NULL NUMBER TASK_OWNER# NOT NULL NUMBER TASK_NAME NOT NULL VARCHAR2(128) STATUS NOT NULL NUMBER START_ROWID ROWID END_ROWID ROWID START_ID NUMBER END_ID NUMBER JOB_NAME VARCHAR2(30) START_TS TIMESTAMP(6) END_TS TIMESTAMP(6) ERROR_CODE NUMBER ERROR_MESSAGE VARCHAR2(4000)
Quelques requêtes de suivi
Statut des chunks en cours en cours de traitement
Dans cet exemple on voit que le parallélisme ne fonctionne pas car il n’y a qu’un chunk à l’état « ASSIGNED » ( le patch n’était pas appliqué sur les binaires Oracle)
SELECT task_name,status, COUNT(*) FROM user_parallel_execute_chunks GROUP BY task_name,status ORDER BY task_name,status; TASK_NAME STATUS COUNT(*) ------------------------------ -------------------- ---------- TEST_UPD_DPE ASSIGNED 1 TEST_UPD_DPE PROCESSED 1532 TEST_UPD_DPE UNASSIGNED 420
Répartition des rowid dans les chunks (chunk_size demandé = 100.000)
select t.*, ( select count(*) from MA_TABLE where rowid between start_rowid and end_rowid) cnt from ( select chunk_id,start_rowid,end_rowid from dba_parallel_execute_chunks order by chunk_id ) t where rownum <=10 CHUNK_ID START_ROWID END_ROWID CNT ---------- ------------------ ------------------ ---------- 343981 AAFhvdAAtAAAAEAAAA AAFhvdAAtAAAAX/CcP 52808 343982 AAFhvdAAtAABvUAAAA AAFhvdAAtAABvn/CcP 62278 343983 AAFhvdAAuAAA98AAAA AAFhvdAAuAAA+P/CcP 62367 343984 AAFhvdAAvAABS4AAAA AAFhvdAAvAABTL/CcP 53586 343985 AAFhvdAAvAADvwAAAA AAFhvdAAvAADwD/CcP 66692 343986 AAFhvdAAwAAABAAAAA AAFhvdAAwAAABT/CcP 69255 343987 AAFhvdAAwAAEJEAAAA AAFhvdAAwAAEJX/CcP 53826 343988 AAFhvdABEAAABAAAAA AAFhvdABEAAABT/CcP 53262 343989 AAFhveAAtAAAAYAAAA AAFhveAAtAAAAr/CcP 53725 343990 AAFhveAAtAACTkAAAA AAFhveAAtAACT3/CcP 60482
Statut des tasks
SELECT job_name, status FROM user_scheduler_job_run_details WHERE job_name LIKE (SELECT job_prefix || '%' FROM user_parallel_execute_tasks WHERE task_name = 'TEST_UPD_DPE'); JOB_NAME STATUS -------------------- -------------------- TASK$_1482_1 SUCCEEDED TASK$_1482_2 SUCCEEDED TASK$_1482_4 SUCCEEDED TASK$_1482_3 SUCCEEDED
Visualiser les erreurs d’exécution
select owner,job_name, status, error#,ADDITIONAL_INFO from dba_scheduler_job_run_details where job_name like 'TASK%' order by job_name;
Voilà pour cette petite présentation du package DBMS_PARALLEL_EXECUTE qui est bien pratique à utiliser.
Je vous invite à le tester et à avoir sous la main un petit script rapidement adaptable pour pouvoir rapidement lancer un update performant (par exemple) sur une grosse table.
Le script fourni dans la documentation Oracle fait parfaitement l’affaire.
Vous pourrez aussi développer votre framework pour un utilisation plus spécifique si besoin.
