procédure PLSQL trop lente

Version imprimable

11/11/2019, 08h56
LandGreen

procédure PLSQL trop lente

j'ai une procédure qui contient un simple code ; curseur insertion résultat dans deux tables
aupravant la procédure prend au maximum 1 minutes por s'exécuter,
hier j'ai lancé la procédure elle a pris plus de 11600 seconds (plus de 03 heures)
quels sont les points majeurs a vérifier afin d’optimiser la requête sachant quand j'exécute le select tout seul il prend même pas 10 séconds?
merci par avance
11/11/2019, 10h17
kabkab

Bonjour,
Sans le code de ta requête, difficile (impossible) de te répondre.

Citation:

Envoyé par kabkab

Bonjour,
Sans le code de ta requête, difficile (impossible) de te répondre.

voici le code

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
43
44
45
46
47
48
49
50
51
52
53
CREATE OR REPLACE PROCEDURE MY_PRO
IS code_error 
varchar2(500);
 msg_error varchar2(500); 
 z varchar2(3276); 
 idcontrat number;
 
   CURSOR CURS IS
        SELECT *
          FROM MY_VIEW
         WHERE (DATE_S, SEQ, CODESTRUC) NOT IN
               (SELECT DATE_S, SEQ, CODESTRUC
                  FROM TABLE_TEMP
                 where DATE_S > '01/11/2019') 
AND nvl(SI_VALID(GET_ID(CODESTRUC,CLIENT)),0) = 1                 ;
 
 
 
BEGIN
   FOR i IN CURS LOOP
      IDCON:=GET_ID(i.CODESTRUC,i.CLIENT);
 
     IF SI_VALID (IDCON)=1  THEN
 
      IF IDCON=15655 THEN -- Ajouté le 17/06/2019
        z:=GEN_CLI(REPLACE(REPLACE(REPLACE(GET_TEXT(101),'MON',i.ALTC),'x.xxx.xxx',sep_decim(i.MONTANT)),'xxxxxxxxxxxxxxxxxxxx',i.CODEACC),IDCON);
 
      ELSE
 
        z:=GEN_CLI(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TEXT8SEND(101),'MON',i.ALTC),'x.xxx.xxx',sep_decim(i.MONTANT)),'xxxxxxxxxxxxxxxxxxxx',i.CODEACC),'MY_PRO',i.des_eng),'CPT',i.TEXT_ABR),IDCON);
 
      END IF;
 
         INSERT INTO MY_TAB (TXT,IDD,DH,HH,id_contrat,AG,NUM,STAT)
         VALUES(z,101,trunc(sysdate),to_char(sysdate,'HH24:MI:SS'),IDCON,i.CODESTRUC,i.CLIENT,1);
 
 
         INSERT INTO TABLE_TEMP (SEQ,DATE_S,CODESTRUC,CLIENT,XDE,XE,XCV,MONTANT,DES,date_r,heure_r)
         VALUES (i.SEQ, i.DATE_S, i.CODESTRUC, i.CLIENT, i.XDE,i.XE,i.XCV,i.MONTANT,i.DES,trunc(sysdate),to_char(sysdate,'HH24:MI:SS'));
                 COMMIT;
 
      END IF;
   END LOOP;
 
 
   EXCEPTION WHEN OTHERS THEN
      ROLLBACK;
      code_error := SQLCODE;
      msg_error  := substr(SQLERRM(SQLCODE),1,100);
      INSERT INTO SMS_ERRORS VALUES ('MY_PRO',code_error,msg_error,0,sysdate);
      COMMIT;
 
END MY_PRO;

sachant que le select de mon curseur s'exécute en 10 seconde !

11/11/2019, 10h52
LandGreen

je vous informe aussi que la procédure ne prenait pas plus de 1 minute avant cette semaine !
11/11/2019, 17h30
Michel.Priori

Bonjour,

Sur le select du curseur :
Le fait qu'il s’exécute en 10 secondes indique qu'il y a du volume de donnée.

Je défends l'usage de * quand c'est sur une vue. Ok pour moi
Une vue ne doit pas servir a du code ré-entrant - pas de ré-utilisabilité du code en SQL ; Fail :nono:

L'usage des fonctions si_valid(a) et Get_id(a,b) sont des problèmes potentiels de performance ; 1000 valeurs => 1000 exécutions pour faire le WHERE
Problème de performance surtout si elles font des select sur d'autres tables.

TABLE_TEMP est bien une table temporaire ?
Quels sont ses index ?
La définition de la colonne DATE_S est varchar() ou date ? quel est le paramètre NLS ?

Sur la boucle (fetch) :
C'est con de redemander le calcul de get_id alors que si elle était présente dans le select du curseur on n'aurait fait le calcul qu'une fois !

La transformation de i.ALTC embarque des fonctions ; mêmes (2) remarques qu'au dessus.

Un commit à chaque ligne de curseur => forçage de l'écriture du log buffer dans les redo hyper fréquemment. (je pense qu'avec l'augmentation du nombre de lignes traitées c'est là le pb de perf)

sur Exception :
Pas d'information sur la ligne qui a été rollbackée
Le bloc sort en ayant trappé toutes les erreurs => le bloc se déroule toujours bien ! (à voir si lors des tests précédents la boucle n'était jamais allé jusqu'à la dernière ligne ; c'est plus rapide comme ça ;) )

Perso, je réprouve votre approche.
j'aurais simplement fait 2 [INSERT - SELECT] limités à 100000 lignes (par transaction) et bouclé tant qu'il reste des lignes à traiter.

[INSERT - SELECT] est une approche ensembliste, plus "SQL" que le traitement ligne à ligne.

Bonjour,

Attention, dire qu'une requête met 10 secondes, c'est 10 sec pour retourner toutes les lignes ou que les x premières (Toad ou sql developper ouvrent un curseur et limitent le nombre de lignes retournées)

Ensuite, une augmentation de temps peut avoir plusieurs sources : Une augmentation de volumétrie d'une table, des stats pas à jour, un nouveau trigger, etc...
Le NOT IN n'est pas vraiment conseillé pour ce genre de requête, mieux vaut utiliser un NOT EXISTS

Combien de lignes dans la TABLE_TEMP ? Combien de lignes à traiter par le curseur ?
le NVL ne sert à rien
Mettre un format sur la date
Code:

1 2 3 4 SELECT * FROM MY_VIEW v WHERE NOT EXISTS (SELECT 1 FROM TABLE_TEMP t WHERE t.date_s = v.date_s AND t.seq = v.seq AND t.codestruc = v.codestruc AND t.DATE_S > TO_DATE('01/11/2019', 'DD/MM/RRRR')) AND SI_VALID(GET_ID(CODESTRUC,CLIENT)) = 1
Tu peux mettre une trace toutes les 100 ou 1000 lignes avec un TIMESTAMP pour avoir le temps de traitement au sein de la boucle.
Tu peux aussi vérifier lorsque le traitement tourne quels sont les wait au niveau base, et s'il n'y a pas de lock

12/11/2019, 11h45
LandGreen

je vous remercie pour votre retour...

voici mes réponse à vos remarques et suggestions :

Citation:

Sur le select du curseur :
Le fait qu'il s’exécute en 10 secondes indique qu'il y a du volume de donnée.

en fait c'est selon le volume de données quotidien, par exemple aujourd’hui c'est en 5 seconds seulement

Citation:

Je défends l'usage de * quand c'est sur une vue. Ok pour moi
Une vue ne doit pas servir a du code ré-entrant - pas de ré-utilisabilité du code en SQL ; Fail :nono:

doit je utiliser les noms de toutes les colonnes?

Citation:

L'usage des fonctions si_valid(a) et Get_id(a,b) sont des problèmes potentiels de performance ; 1000 valeurs => 1000 exécutions pour faire le WHERE
Problème de performance surtout si elles font des select sur d'autres tables.

oui les selects se font sur d'autres tables
sinon comment régler ça?

Citation:

TABLE_TEMP est bien une table temporaire ?
Quels sont ses index ?

c'est une table contenant les données déjà traitées, afin de ne pas les re-traiter à nouveau lors du prochaine exécution.

Citation:

La définition de la colonne DATE_S est varchar() ou date ? quel est le paramètre NLS ?

pour l'utilisation des dates j'utilise le TRUNC afin de ne pas se retrouver dans une incohérence
l'ancien paramétrage est : DD-MM-YY
maintenant je l'ai modifier à DD/MM/YYYY

Citation:

Sur la boucle (fetch) :
C'est con de redemander le calcul de get_id alors que si elle était présente dans le select du curseur on n'aurait fait le calcul qu'une fois !

dois je l'enlever de la VUE?

Citation:

La transformation de i.ALTC embarque des fonctions ; mêmes (2) remarques qu'au dessus.

j'ai pas bien compris

Citation:

Un commit à chaque ligne de curseur => forçage de l'écriture du log buffer dans les redo hyper fréquemment. (je pense qu'avec l'augmentation du nombre de lignes traitées c'est là le pb de perf)

je mets une seule commit à la fin? avant le END LOOP?

Citation:

sur Exception :
Pas d'information sur la ligne qui a été rollbackée
Le bloc sort en ayant trappé toutes les erreurs => le bloc se déroule toujours bien ! (à voir si lors des tests précédents la boucle n'était jamais allé jusqu'à la dernière ligne ; c'est plus rapide comme ça ;) )

merci