Exécution requête se bloque

Version imprimable

19/05/2014, 13h42
mbx90

Exécution requête se bloque

Bonjour,

J'ai un problème lors de l’exécution de quelques requêtes PL SQL, mais je n'arrive pas à savoir d'ou vient ce problème car c'est assez étrange !

Je m'explique, j'ai une procédure P qui exécute des traitements, en lançant des requêtes (Insert, update, merge...) une par une, depuis une table cible.

Pour chaque ligne de la table cible -->

Code:

EXECUTE IMMEDIATE MA_REQUETE;

(les requêtes sont stockées dans un champs "TEXT_REQ")

Le traitement est divisé en deux parties, chaque partie contient un nombre de requêtes à exécuter, l'exécution de la 1ere partie passe sans problème, mais quand je lance la 2eme partie elle se bloque dans certaines requêtes (requête Merge par exemple). Il n'y a pas d'erreur dans la requête c'est juste que le temps d'exécution est infini.

Quand je prend les requêtes bloquantes et je les exécute manuellement, sans passer par la procédure P, ça passe sans problème. Aussi ce qui est bizarre c'est que quand je laisse un certain temps (4h ou 5h) entre l’exécution de la 1er et de la 2eme partie, il n'y a aucun blocage au niveau de l’exécution des requêtes de la deuxième partie. Je ne comprend pas pourquoi je dois attendre 4 heure pour que les requêtes ne se bloquent pas !!

Je précise que quand la première partie est fini, il n'y a aucun traitement qui tourne ou qui reste bloqué.

J’espère que j'ai bien expliqué la problématique.

Si vous avez des idées, n'hésiter pas.

Merci d'avance

Cordialement,
19/05/2014, 14h11
karlogod

si je comprend bien, la partie 1 et 2 ne s'exécute pas au même moment mais sont lancé l'un à la suite de l'autre.

avez vous d'autres traitements qui modifient les tables présentes dans la partie 2 et qui s'exécute en même temps ?

cela laisse fortement à penser que vous avez des enregistrements de verrouillés lors de l'exécution de la partie 2 ce qui fait que l'ordre dml en cours attend la libération du verrou avant de pour voir s'exécuter ce qui peut prendre plus ou moins de temps.
19/05/2014, 16h43
mnitu

Quand vous ne comprenez-pas tracez!
19/05/2014, 17h28
mbx90

J'ai tracé ça bloque lors de l'exécution de la requête

Voici le code de la procédure, quand ça bloque c'est au niveau de l'exécution de la requête --> ligne en rouge

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
PROCEDURE PRC_TRAIT_MIG_CYC_REQ (VAR_SYS_SRC IN VARCHAR2) IS
      CURSOR CUR_REQ  IS  SELECT * FROM MIG_CYC_REQ WHERE SYS_SRC = VAR_SYS_SRC
                          AND FLAG_ACTIV= 'O' ORDER BY ORD_EXEC ;
      REC_CUR_REQ  CUR_REQ%ROWTYPE;
      MY_SQLERRM VARCHAR2(255);      
      BEGIN
      OPEN CUR_REQ;
       LOOP
        FETCH  CUR_REQ INTO  REC_CUR_REQ ;
        EXIT WHEN CUR_REQ%NOTFOUND;
        UPDATE  MIG_CYC_REQ SET DT_DEB_EXEC = NULL, DT_FIN_EXEC = NULL, IN_ERROR =NULL 
        WHERE NUM_REQ = REC_CUR_REQ.NUM_REQ ;
        PRC_EXEC_REQ_CYC('COMMIT');
        -- Sauvegarde des index apres chaque alimentation d une table
        if  REC_CUR_REQ.TYP_REQ = 'I' then 
             PRC_INS_DROP_INDEX_CYC(trim(REC_CUR_REQ.NOM_TABLE));
        end if;     
        DBMS_OUTPUT.PUT_LINE(REC_CUR_REQ.TEXT_REQ);
        -- Date debut d execution 
        UPDATE MIG_CYC_REQ SET DT_DEB_EXEC = SYSDATE
        WHERE NUM_REQ = REC_CUR_REQ.NUM_REQ;
        PRC_EXEC_REQ_CYC('COMMIT');        
        -- Execution de la requete
        PRC_EXEC_REQ_CYC(REC_CUR_REQ.TEXT_REQ);
        -- Date fin d execution 
        UPDATE MIG_CYC_REQ SET DT_FIN_EXEC = SYSDATE
        WHERE NUM_REQ = REC_CUR_REQ.NUM_REQ;
        PRC_EXEC_REQ_CYC('COMMIT');  
        -- Recreation des index
        if  REC_CUR_REQ.TYP_REQ = 'I' then
             PRC_EXEC_INDEX_CYC(trim(REC_CUR_REQ.NOM_TABLE));
        end if;
      END LOOP;      
      -- Gestion des erreurs
      EXCEPTION
      WHEN OTHERS THEN 
      MY_SQLERRM := SUBSTR(SQLERRM, 1, 200);      
      UPDATE  MIG_CYC_REQ SET IN_ERROR = MY_SQLERRM
      WHERE NUM_REQ = REC_CUR_REQ.NUM_REQ;
      PRC_EXEC_REQ_CYC('COMMIT');
      RAISE;      
END  PRC_TRAIT_MIG_CYC_REQ;

19/05/2014, 17h39
al1_24

Sans savoir ce que fait PRC_EXEC_REQ_CYC ni ce que contient REC_CUR_REQ.TEXT_REQ, il semble relativement difficile d'expliquer ce qui bloque :roll:

Oui j'ai oublié de mettre la procédure PRC_EXEC_REQ_CYC :

Code:

1
2
3
4
PROCEDURE PRC_EXEC_REQ_CYC (VAR_REQ IN  VARCHAR2 ) IS
    BEGIN
    EXECUTE IMMEDIATE VAR_REQ;
END PRC_EXEC_REQ_CYC;

TEXT_REQ contient des requêtes (update, delete, merge...)

Voici un exemple de requête qui se bloque, mais comme j'ai expliqué si j'attend par exemple le lendemain puis je relance la procédure, l’exécution ne bloque pas.

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
26
27
28
29
30
31
merge into MIG_ACTEUR a
                   using 
                   (select   
                          MCRA.CYC_BODY_NB,
                          MCRA.MIG_ID_ACTEUR,
                           MCRA.CD_CODSYS,
                           TBODY.NB_HOME_PHONE,
                            TBODY.NB_TEL_PRO,
                            TBODY.NB_CONTACT_PHONE,
                            TBODY.NB_MOBILE_TEL,
                            TBODY.NB_FAX,
                      CASE    
                      when TBODY.NAF2008_CD is NULL THEN  TBODY.NB_NAT_CMPNY ELSE  TBODY.NAF2008_CD end VAL_NAF  
                          from TBODY TBODY, MIG_CORR_REF_ACTEUR MCRA
                          where TBODY.NB             = MCRA.CYC_BODY_NB
                             and (TBODY.NB_HOME_PHONE is not null
                               or  TBODY.NB_TEL_PRO is not null
                               or  TBODY.NB_CONTACT_PHONE is not null
                               or  TBODY.NB_MOBILE_TEL is not null
                               or  TBODY.NB_FAX is not null)
                        ) b
                    on (A.MIG_ID_ACTEUR=b.MIG_ID_ACTEUR)
                    when matched then 
                    update set  
                      S_VAL_TEL_PERSO = b.NB_HOME_PHONE,
                      S_VAL_TEL_PRO = b.NB_TEL_PRO,
                      S_VAL_TEL_CONTACT = b.NB_CONTACT_PHONE,
                      S_VAL_TEL_POR = b.NB_MOBILE_TEL,
                      S_VAL_NUM_FAX = b.NB_FAX,
                      S_VAL_NAF = b.VAL_NAF ,   
                      VAL_REF_SOURCE = substr(VAL_REF_SOURCE,1,12)||'_'||B.CYC_BODY_NB

20/05/2014, 08h57
mnitu

Tracez == activer la trace SQL étendue dans le traitement. Cela permet de voir au delà de la requête se bloque!
Une possible explication: des tables sont chargé massivement. Des requêtes sur ces tables s'exécutent. Comme les statistiques ne sont pas à jour les estimation des cardinalités sont fausse et par conséquence les plans d'exécution sont farfelue. Dont les requêtes durent longtemps et vous avez l'impression que "ça bloque" (mais la lecture n'est jamais bloqué!). Pendant la nuit les statistiques sont recalculés, et les plans d'exécution sont bons; "ça passe"!
Et peut être que ça n'a rien à voir! Peut-être que juste la table impliqués dans la requête Merge a des enregistrements verrouillés par un autre batch/utilisateur d'une manière directe ou indirecte!
Comment le savoir ? Tracez!
22/05/2014, 12h53
mbx90

D'accord je vais encore chercher et voir si le problème vient des statistiques
Je vous tiendrai au courant
Merci pour vos réponses
29/05/2014, 10h54
mbx90

Les statistiques étaient bien calculé je pense.

Dans la première partie j'avais une requête d'insertion sur la même table qui a bloqué dans la seconde partie. Dans la requête d'insertion j'avais inversé l’ordre d'insertion pour quelques champs.

En corrigeant cette requête les autre requêtes ne se bloque plus.