Temps d'exécution procédure stockée

**c0rwyn** · 20/10/2008, 16h32

Bonjour,

j'ai développé une procédure stockée qui modulo un certain nombre de paramètres fait des insert en base de données.

Mon souci est le suivant : lorsque je fais mon insert en direct sur la base, celà me prend entre 2 et 3 minutes alors que via la procedure cela me prend plus de quatre heures.

Ci-dessous ma procedure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
 
create or replace procedure SUIVI_CCO_GENERE_ERREUR(P_NUM_PAN IN VARCHAR2,
                                                     P_NUM_CLIENT IN VARCHAR2,
                                                     P_SCA_ID IN NUMBER,
                                                     P_DATE_DEBUT IN VARCHAR2,
                                                     P_DATE_FIN IN VARCHAR2,
                                                     P_FORCE_TRAITEMENT IN NUMBER,
                                                     P_CODE_RETOUR OUT NUMBER,
                                                     P_MSG_RETOUR OUT VARCHAR2)
 
 
  IS
 
  /********************************************************************************
     NAME:       SUIVI_CCO_GENERE_ERREUR
     PURPOSE:    Calcule et sauvegarde les champs utiles au rapport de CCO en erreur
 
 
     REVISIONS:  10/10/2008, ALI, Mantis 10304 : création
 
 
     PARAMETERS     : P_NUM_PAN le numero de badge (null si tous les PANS)
                      P_NUM_CLIENT le numéro de client (null si global)
                      P_SCA_ID l'identifiant de la SCA (null si toutes les SCA)
                      P_DATE_DEBUT la date de debut du rapport
                      P_DATE_FIN la date de fin du rapport
                      P_FORCE_TRAITEMENT indicateur permettant de forcer le traitement meme si le rapport existe deja
                      P_CODE_RETOUR le code erreur ou l'identifiant du rapport
                      P_MSG le message de traitements
 
     NOTES          :
 
  ******************************************************************************/
 
  l_scer_id NUMBER(19);
  l_req VARCHAR2(5);
  l_date_debut DATE;
  l_date_fin DATE;
  l_num_client CLIENT.CLI_NUMERO_CLIENT%TYPE;
  l_num_PAN PORTEUR.POR_NUMERO_PORTEUR%TYPE;
  l_traitement BOOLEAN;
 
BEGIN
 
  l_traitement := false;
 
 
  -- Vérification des dates
  BEGIN
    l_req := 'Req1';
    SELECT to_date(P_DATE_DEBUT, 'dd/MM/YYYY')
    INTO l_date_debut
    FROM dual;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR (-20001, 'le format de la date de debut n''est pas reconnu');
  END;
 
  BEGIN
    l_req := 'Req2';
    SELECT to_date(P_DATE_FIN, 'dd/MM/YYYY')
    INTO l_date_fin
    FROM dual;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR (-20001, 'le format de la date de fin n''est pas reconnu');
 
  END;
 
  -- Recherche de l'existence du rapport ou d'un rapport moins filtré pouvant etre utilisé
  l_req := 'Req3';
  BEGIN
     SELECT scer_id INTO l_scer_id
       FROM (
           SELECT first_value(scer_id) OVER (PARTITION BY CLI_NUMERO_CLIENT, SCA_ID, POR_NUMERO_PORTEUR, SCER_DAT_DEBUT, SCER_DAT_FIN  ORDER BY SCER_DAT_DEMANDE DESC) scer_id
             FROM SUIVI_CCO_ERREUR
            WHERE (CLI_NUMERO_CLIENT IS NULL OR CLI_NUMERO_CLIENT = P_NUM_CLIENT)
              AND (por_numero_porteur IS NULL OR por_numero_porteur = P_NUM_PAN)
              AND (SCA_ID IS NULL OR SCA_ID = P_SCA_ID)
              AND SCER_DAT_DEBUT = l_date_debut
              AND SCER_DAT_FIN = l_date_fin
            )
      WHERE rownum = 1;
 
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_traitement := true;
 
  END;
 
 
  IF l_traitement OR P_FORCE_TRAITEMENT = 1 THEN
 
    l_req := 'Req4';
    SELECT SQ_SCER.NEXTVAL INTO l_scer_id FROM dual;
 
 
    l_req := 'Req5';
    -- Vérification du numéro client
    IF P_NUM_CLIENT IS NOT NULL THEN
      BEGIN
        SELECT cli_numero_client
        INTO l_num_client
        FROM client
        WHERE cli_numero_client = P_NUM_CLIENT;
 
      EXCEPTION
        WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR (-20002, 'le numéro client n''est pas reconnu');
 
      END;
 
    END IF;
 
    -- Vérification du PAN
    IF P_NUM_PAN IS NOT NULL THEN
      BEGIN
        SELECT po.por_numero_porteur
        INTO l_num_PAN
        FROM PORTEUR po
        WHERE po.por_numero_porteur = P_NUM_PAN;
 
      EXCEPTION
        WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR (-20002, 'le numéro client n''est pas reconnu');
 
      END;
 
    END IF;
 
 
 
 
    -- Insertion de l'entete du rapport
    l_req := 'Req6';
    INSERT INTO SUIVI_CCO_ERREUR (SCER_ID, CLI_NUMERO_CLIENT, POR_NUMERO_PORTEUR, SCA_ID, SCER_DAT_DEBUT, SCER_DAT_FIN, SCER_DAT_DEMANDE)
    VALUES (l_scer_id, P_NUM_CLIENT, P_NUM_PAN, P_SCA_ID, l_date_debut, l_date_fin, sysdate);
    commit;
 
 
    -- Recherche des trajets sans CCO pour un PAN avec CCO
    l_req := 'Req7';
 
    INSERT INTO SUIVI_CCO_ERREUR_LIGNE(
      SCER_ID,
      POR_NUMERO_PORTEUR,
      SCA_ID,
      CCO_SCA,
      CCO_SOUSCRITE,
      CCO_TRAJET,
      TRA_DHM_E,
      TRA_DHM_S,
      TRA_CODE_GARE_E,
      TRA_CODE_GARE_S,
      TRA_ID
    )
    (SELECT
         l_scer_id
        ,po.por_numero_porteur
        ,ccc.sca_id
        ,r17.cod_ccl
        ,ccc.ccc_code --as CCO_souscrite  
        ,tra.tra_code_prodt_serv --as CCO_trajet
        ,tra.tra_dhm_e
        ,tra.tra_dhm_s
        ,tra.tra_code_gare_e
        ,tra.tra_code_gare_s
        ,tra.tra_id        
      FROM 
        eurotoll.transaction tra  
        JOIN contrat ctr on tra.cta_id = ctr.cta_id
        JOIN client cli on ctr.cli_id = cli.cli_id
        JOIN porteur po on tra.tra_code_porteur = po.por_numero_porteur
        JOIN condition_com_selectionnee ccs on ccs.por_id = po.por_id
        JOIN valeur_attribut_service vas on vas.csl_id = ccs.csl_id  
        JOIN condition_com_circulee ccc on vas.ccc_id = ccc.ccc_id
        JOIN sca on ccc.sca_id=sca.sca_id
        JOIN (SELECT cod_ccl,
                     sca_circulee, 
                     num_cpt_pan, 
                     ROW_NUMBER() OVER(PARTITION BY sca_circulee, num_cpt_pan ORDER BY dat_deb_ccl desc) rank
                FROM europl.r17_contrat_ccial r17) r17 on r17.num_cpt_pan = po.por_numero_porteur and r17.rank = 1 and sca.sca_code=substr(r17.sca_circulee,1,3) || substr(r17.sca_circulee,7,2)  
      where 1=1
        and (P_NUM_PAN IS NULL OR tra.tra_code_porteur=P_NUM_PAN)
        and (P_NUM_CLIENT IS NULL OR cli.cli_numero_client=P_NUM_CLIENT)
        and (P_SCA_ID IS NULL OR CCC.SCA_ID = P_SCA_ID)
        and to_date(substr(tra.tra_dhm_s,0,8), 'YYYYMMDD') <= to_date(P_DATE_FIN,'DD/MM/YYYY')
        and to_date(substr(tra.tra_dhm_s,0,8), 'YYYYMMDD') >= to_date(P_DATE_DEBUT,'DD/MM/YYYY')
        and ((ltrim(tra.tra_code_prodt_serv) is null and ltrim(ccc.ccc_code) is not null) 
        or (ltrim(tra.tra_code_prodt_serv) is not null and ltrim(ccc.ccc_code) is null))      
);
 
    commit;
 
    P_MSG_RETOUR := 'OK';
 
  ELSE
    P_MSG_RETOUR := 'OK - Traitement déjà effectué';
 
  END IF;
 
 
  P_CODE_RETOUR := l_scer_id;
 
  EXCEPTION
    WHEN OTHERS THEN
      P_MSG_RETOUR := 'Erreur ' || l_req || ' ' || sqlerrm;
      P_CODE_RETOUR := sqlcode;
      rollback;
 
end SUIVI_CCO_GENERE_ERREUR;

Maintenant, le code que j'exécute pour la mise à jour en direct :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
 INSERT INTO SUIVI_CCO_ERREUR_LIGNE(
      SCER_ID,
      POR_NUMERO_PORTEUR,
      SCA_ID,
      CCO_SCA,
      CCO_SOUSCRITE,
      CCO_TRAJET,
      TRA_DHM_E,
      TRA_DHM_S,
      TRA_CODE_GARE_E,
      TRA_CODE_GARE_S,
      TRA_ID
    )
SELECT
         2
        ,po.por_numero_porteur
        ,ccc.sca_id
        ,r17.cod_ccl
        ,ccc.ccc_code --as CCO_souscrite  
        ,tra.tra_code_prodt_serv --as CCO_trajet
        ,tra.tra_dhm_e
        ,tra.tra_dhm_s
        ,tra.tra_code_gare_e
        ,tra.tra_code_gare_s
        ,tra.tra_id        
      FROM 
        eurotoll.transaction tra  
        JOIN contrat ctr on tra.cta_id = ctr.cta_id
        JOIN client cli on ctr.cli_id = cli.cli_id
        JOIN porteur po on tra.tra_code_porteur = po.por_numero_porteur
        JOIN condition_com_selectionnee ccs on ccs.por_id = po.por_id
        JOIN valeur_attribut_service vas on vas.csl_id = ccs.csl_id  
        JOIN condition_com_circulee ccc on vas.ccc_id = ccc.ccc_id
        JOIN sca on ccc.sca_id=sca.sca_id
        JOIN (SELECT cod_ccl,
                     sca_circulee, 
                     num_cpt_pan, 
                     ROW_NUMBER() OVER(PARTITION BY sca_circulee, num_cpt_pan ORDER BY dat_deb_ccl desc) rank
                FROM europl.r17_contrat_ccial r17) r17 on r17.num_cpt_pan = po.por_numero_porteur and r17.rank = 1 and sca.sca_code=substr(r17.sca_circulee,1,3) || substr(r17.sca_circulee,7,2)  
      where 1=1
        and ('3156480006000000011' IS NULL OR tra.tra_code_porteur='3156480006000000011')
        and (null IS NULL OR cli.cli_numero_client=null)
        and (null IS NULL OR CCC.SCA_ID = null)
        and to_date(substr(tra.tra_dhm_s,0,8), 'YYYYMMDD') <= to_date('29/02/2008','DD/MM/YYYY')
        and to_date(substr(tra.tra_dhm_s,0,8), 'YYYYMMDD') >= to_date('01/02/2008','DD/MM/YYYY')
        and ((ltrim(tra.tra_code_prodt_serv) is null and ltrim(ccc.ccc_code) is not null) 
        or (ltrim(tra.tra_code_prodt_serv) is not null and ltrim(ccc.ccc_code) is null)
        )

Quelqu'un a-t-il ma moindre idée de la cause de cette différence de temps d'exécution?

Merci d'avance.

**mnitu** · 20/10/2008, 16h54

Deux remarques :

Si tu veux comprendre ce qui se passe exécute ta procédure avec la trace sql activée. Comme ça tu peux savoir où le temps est passé.
Le code que t’exécutes en directe ne contient pas des variables de binding par contre la requête SQL incorporée dans PL/SQL le fait. Donc tu ne compares pas la même chose.

PS. De quelle version de base Oracle parle-t-on ?

**c0rwyn** · 20/10/2008, 17h16

Merci du temps que tu consacres à ce sujet.

Quand je passe ma procedure en debug, il me faut moins de 2 secondes pour arriver dans le insert cco_erreur_ligne et ensuite en visualisant les sessions, la requête sql est celle u insert pendant les quatre heures.

Pour la version d'oracle, il s'agit de la 9.2.0.4.

**SheikYerbouti** · 20/10/2008, 17h18

Vous pouvez également utiliser l'outil Profiler pour tracer votre code PL/SQL instruction par instruction et vérifier les goulets d'étranglement.

**mnitu** · 20/10/2008, 17h38

Envoyé par c0rwyn

Merci du temps que tu consacres à ce sujet.

Quand je passe ma procedure en debug, il me faut moins de 2 secondes pour arriver dans le insert cco_erreur_ligne et ensuite en visualisant les sessions, la requête sql est celle u insert pendant les quatre heures.

Pour la version d'oracle, il s'agit de la 9.2.0.4.

Personnellement j'aime analyser la trace sql plutôt que le debug.
Remplacez, pour tester, dans la requête SQL exécuté depuis le code PL/SQL les paramètres par les mêmes valeurs en dur que dans votre requête SQL exécuté en direct. Cella change ou pas ?

**c0rwyn** · 20/10/2008, 18h12

quand je passe les paramètres en dur dans la procédure, les temps d'exécution sont comparables à l'exécution de mon insert en direct.

Le problème viendrait donc effectivement du binding de mes variables. Y aurait-il des moyens pour accélerer le traitement avec ces variables?

**mnitu** · 20/10/2008, 21h15

Il y a plusieurs de possibilités mais avec les informations actuelles c'est très risquant de tirer des conclusions. Mais en examinant le code de ta procédure je te proposerais déjà d'utiliser le sql dynamique de la manière évoquée en fin de cette discussion pour éliminer le code de type

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
paramètre is NULL or colonne = paramètre.

Et peut être avant tout s'assurer que les statistiques sont à jour.

**c0rwyn** · 21/10/2008, 10h57

Ca y est j'ai résolu le problème en corrigeant ma clause where :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
 and (tra.tra_code_porteur = nvl(P_NUM_PAN,tra.tra_code_porteur))
        and (cli.cli_numero_client=nvl(P_NUM_CLIENT, cli.cli_numero_client))
        and (CCC.SCA_ID = nvl(P_SCA_ID, CCC.SCA_ID))
        and to_date(substr(tra.tra_dhm_s,0,8), 'YYYYMMDD') <= l_date_fin
        and to_date(substr(tra.tra_dhm_s,0,8), 'YYYYMMDD') >= l_date_debut
        and ((ltrim(tra.tra_code_prodt_serv) is null and ltrim(ccc.ccc_code) is not null) 
        or (ltrim(tra.tra_code_prodt_serv) is not null and ltrim(ccc.ccc_code) is null))

Une hypothèse est que lors du test null=null, l'optimiseur oracle dégage ce test et du coup vire le or et passe par les indexes lorsque les données sont "en dur".

En mettant le nvl dans la proc, on repasse par les indexes et les temps d'exécution redeviennent acceptables.

En tout cas, merci pour votre contribution.

Temps d'exécution procédure stockée

PL/SQL Oracle

Discussions similaires

Partager

Partager