Petite présentation de DBMS_PARALLEL_EXECUTE

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.