DBMS_PARALLEL_EXECUTE.TASK_STATUS = crashed ! si je veux paralléliser une requête

Version imprimable

Bonjour,

Voilà mon code, j'essaye de paralléliser une requête par ROWID, dans le select, une fonction qui calcule et écrit dans une autre table (tout cela packagé) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DECLARE
 
    c_update_statement CONSTANT VARCHAR2 (2000)
    := 'select  PKG_STOCK.so_f_ea_insert_mb_new(ad.adhe_cod_prod, ad.adhe_no_adhes, decode(trunc(sysdate), trunc(last_day(sysdate)), trunc(SYSDATE)
                                                                                                                                  , decode(to_char(sysdate, ''D''), 5, trunc(SYSDATE)
                                                                                                                                                                   , trunc(SYSDATE)))) 
        from  /*+ ROWID (dda) */ ADHESIONS ad
        where ROWID BETWEEN :starting_rowid AND :ending_rowid';
 
    c_task_name   CONSTANT VARCHAR2 (20) := 'Give Raise';
    l_attempts    PLS_INTEGER := 1;
    retries_in    PLS_INTEGER := 2;
    l_d_effet     DATE;
 
BEGIN   
  BEGIN 
    DBMS_OUTPUT.put_line('[SQL-INFO] Lancement du script');
    DBMS_PARALLEL_EXECUTE.CREATE_TASK (c_task_name);
 
    DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID (task_name => c_task_name
                             , table_owner => USER
                             , table_name => 'ADHESIONS'
                             , by_row => TRUE
                             , chunk_size => 1000
                              );

Mais lorsque j'exécute le suite le statut de la tâche passe en CRASHED !!! et mon job se termine, pouvez-vous m'éclairer sur l'origine du crash et si ma requête initiale est bien implémentée, merci ! :roll:

Code:

1
2
3
4
5
DBMS_PARALLEL_EXECUTE.RUN_TASK (task_name => c_task_name
                  , sql_stmt => c_update_statement
                  , language_flag => DBMS_SQL.native
                  , parallel_level => 10
                   );

Cdlt,

J'ai lu que le hint ROWID était deprecated depuis Oracle 9.... :weird:

Donc j'ai remplacé ma requête telle que directement dans le code en supprimant la création et le lancement de TASK via DBMS_PARALLEL_EXECUTE :

Code:

1
2
3
4
5
6
7
8
9
10
BEGIN
      select  /*+ PARALLEL(ADHESIONS, 16) */ PKG_STOCK.so_f_ea_insert_mb_new(ad.adhe_cod_prod, ad.adhe_no_adhes, decode(trunc(sysdate), trunc(last_day(sysdate)), trunc(SYSDATE)
                                                                                                                                  , decode(to_char(sysdate, 'D'), 5, trunc(SYSDATE)
                                                                                                                                                                   , trunc(SYSDATE)))) 
      BULK COLLECT INTO l_tab_result
      from   ADHESIONS ad;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('[SQL-INFO] Erreur de parallélisation du script');
    END;

Mais j'ai l'impression que ça ne parallélise pas à l'exécution, dois-je conserver DBMS_PARALLEL_EXECUTE.RUNTASK dans mon code... et comment l'implémenter avec le hint /* +PARALLEL */ ??

19/12/2018, 11h47
Waldar

De toutes façons votre hint ROWID étant placé au niveau du FROM est gentillement ignoré par l'optimiseur.
Les hints comme vous l'avez fait dans la seconde requête se placent après le SELECT.

Par contre le dernier argument de votre fonction mérite d'être encadré :)

Code:

decode(trunc(sysdate), trunc(last_day(sysdate)), trunc(SYSDATE), decode(to_char(sysdate, 'D'), 5, trunc(SYSDATE), trunc(SYSDATE)))

C'est strictement identique à trunc(sysdate).

Sinon, le parallélisme de PARALLEL et de DBMS_PARALLEL_EXECUTE n'ont rien à voir.
Le premier se fait sur des requêtes unitaires, le second génère une file de traitement et en exécute X en parallèle, dès qu'un est terminé il pioche le suivant.

Donc... que voulez-vous faire ?
19/12/2018, 15h35
GO

Merci pour votre réponse, effectivement en date d'aujourd'hui le dernier paramètre = trunc(sysdate) qui est par défaut pour pourvoir de lancer tout les jours pour les besoins de mes tests sinon cela se fera un vendredi ou un dernier jour du mois (en enlevant le dernier argument ou en ne renvoyant une date invalide pour court-circuiter le traitement et le gérant dans la fonction appelée).

En fait j'ai placé la fonction dans le select car c'est elle le coeur du traitement pour chaque adhésion elle calcule des valeurs qui sont insérées dans une autre table j'ai d'ailleurs du mettre une session autonome dans la fonction appelée pour pouvoir écrire dans le select...

Si je fais bêtement ce traitement adhésion par adhésion cela met près de 10 heures (il y a 280000 adhésions), l'idée est de paralléliser les traitements en lançant plusieurs simultanément (qui appelle la fonction avec des n° d'adhésions différentes) tout cela par pool de 1000 ou 10000 adhésions par exemple.

Ma première idée "deprecated" sur la V12c d'Oracle semble posait problème, alors dans ma seconde idée c'était de découper la requête en bloc d'adhésions - en utilisant le hint parallel et/ou parallel_index - et les lancer simultanément aussi via la requête et non plus passer par des TASK/CHUNK, ceci semble créer plusieurs instances de mon traitement sur le serveur mais les résultats ne sont pas meilleurs que si j'utilise uniquement l'index principal de ma table ADHESIONS...

MAis actuellement je pense revenir sur ma première idée en enlevant le hint ROWID et mettre un autre hint (parallel ??? ou parallel_index???) ou pas de hint mais en lançant des tâches simultanées via DBMS_PARALLEL_EXECUTE mais comment effectuer le découpage alors en lot d'adhésions ??
19/12/2018, 16h05
skuatamad

Citation:

Envoyé par GO

sinon cela se fera un vendredi

Attention, to_char(sysdate, 'D') est dépendant du paramètre de session NLS_TERRITORY, assurez-vous donc que le traitement s'exécute bien en NLS_TERRITORY=FRANCE, avec un alter session par exemple :

Code:

ALTER session SET nls_territory = 'FRANCE';

Citation:

Envoyé par GO

En fait j'ai placé la fonction dans le select car c'est elle le coeur du traitement pour chaque adhésion elle calcule des valeurs qui sont insérées dans une autre table j'ai d'ailleurs du mettre une session autonome dans la fonction appelée pour pouvoir écrire dans le select...

Ça c'est une très mauvais idée, transformer votre fonction en procédure, pas besoin alors de transaction autonome, ni de SELECT

Et inspirez-vous de cet exemple pour configurer l'appel à la procédure :
DBMS_PARALLEL_EXECUTE
A partir de :

Citation:

The following example shows the processing of a workload chunked by a number column. Notice that the workload is actually a stored procedure in this case.

[EDIT] Et n'utilisez pas WHEN OTHERS comme vous l'avez montré ci-dessus :
http://www.orafaq.com/wiki/WHEN_OTHERS
19/12/2018, 16h08
Waldar

Il faudrait voir ce que fait votre fonction, de manière assez générale la meilleure optimisation c'est de faire un traitement ou une succession de traitements ensemblistes, si c'est possible bien entendu.
Donc si votre fonction ne fait pas 10.000 lignes de codes et que vous pouvez la publier n'hésitez pas.
19/12/2018, 17h47
GO

Merci

Citation:

skuatamad

, ça a vraiment bien avancé, ça parallélise et c'est en train de tourner, j'espère qu'il y aura un gain après je peux changer le chunk_size et le parallel_level pour adapter j'ai commencé par 10000 et 16.

Par contre il fallait bien mettre autonomous transaction dans ma nouvelle procédure (:id_start, :id_end) sinon rien ne se commite !!! et c'est vérifié.

Et je suis bien en NLS_TERRITORY=FRANCE.

Citation:

Waldar

pas 10000 lignes mais pas loin puis c'est du code protégé ;) dans le cadre pro, donc désolé.

Encore merci

;););)
20/12/2018, 15h15
GO

Bonjour,

Voila le résultat de mes tests de parallélisation, voyez-vous une piste d'amélioration mise à part d'octroyer plus de ratio à mes cpu sur l'instance oracle embarquée sur mon serveur car pour l'instant le cpu tourne à 10 % d'occupation sur le serveur lors du batch ?

chunk_size parallel_level nb d'enregistrements insérés temps
10000 16 313000 10h
40000 10 313000 9,5h

Temps initial sans parallélisation juste le parcours de la table maître générant 1 traitement par enregistrement : entre 8 et 10h.

Je n'arrive pas réduire mon temps de traitement global...?
20/12/2018, 17h02
Waldar

Et oui. Vous faites du procédural en ligne à ligne dans une base de données qui est par nature ensembliste.
Vous pouvez blinder le CPU, la RAM, les IO vous aurez des performances du même ordre.

Si vous voulez améliorer votre traitement, laisser tomber le code ligne à ligne (row by row aka slow by slow) et faites... du SQL.
300.000 lignes pour 10h, ça me fait un peu mal au cœur.
Je ne sais pas ce que votre traitement fait, mais bon 10 minutes ça serait déjà un long traitement.
20/12/2018, 17h17
skuatamad

Je plussoie Waldar.

Juste une question, quelle est la valeur du paramètre job_queue_processes ?
20/12/2018, 17h24
GO

C'est exactement ce que je pensais faire, ie créer une table temporaire globale de calcul (utilisée unitairement ou par lot même conséquent + de 1M de lignes), insérer dans les colonnes mes infos de calculs et effectuer les calculs de taux, de valeurs, les SUM, les regroupements directement en SQL pur, je débute ce chantier en janvier !

Je ne peux pas vous envoyer le code mais effectivement le procédural pour récupérer des indices, des taux, des valeurs monétaires propres aux calculs et les tous les calculs intermédiaires sont très gourmands, sans parler de la table finale où sont enregistrés les résultats qui dépasse le 50M de lignes et qu'il serait nécessaire voir essentiel de partitionner également.
20/12/2018, 17h30
GO

Citation:

skuatamad

Où puis-je trouver cette valeur ? sur le monitoring ?

Juste une chose en visualisant toutes mes instances de tâches (correspondant au nb de parallel_level = 10), j'ai l'impression qu'il n'y en a que réellement 4 en même temps qui sont en NOT_IN_WAIT, les 6 autres sont en UNKNOWN et cela alternativement entre les 10 tâches. Je suis peut-être limité par une configuration de mon instance oracle à voir avec l'admin prochainement...?
21/12/2018, 16h01
Waldar

A moins que votre serveur Oracle date du début des année 2000, il n'y a aucune raison à ce qu'il ne sache pas gérer 50 M de lignes. Après ça dépend toujours un peu la taille de la ligne.
La table source occupe combien d'espace dans dba_segments ?

Pour le reste, récupérer les informations de taux etc, ce sera des jointures.
Les calculs de taux, ce sont des opérations arithmétiques simples.
Les agrégats demanderont eux de la mémoire.

Pour insérer dans votre table finale, si vous avez l'option de partitionnement, l'échange de partition c'est ce qu'il y a de plus rapide, mais il faut que votre traitement s'y prête.