Bonjour,

j'ai un problème de performances sur une procédure pl/sql qui me croise les données de 2 grosses tables pour les mettre dans une 3ème.

la 1ere table s'appelle T_HEXACLE et contient ~20 000 000 d'enregistrements sur 4 champs.

la 2ème table s'appelle T_HEXAVIA et contient ~2 000 000 d'enregistrements sur 33 champs.

Ces 2 tables n'ont pas de clé primaire/unique !
Le croisement des données se fait sur les champs "MATRICULE_VOIE" (auxquels j'ai ajouté des index)

la 3ème table, celle qui recevra le résultat, s'appelle T_ADRESSES_SIG (et devra donc comprendre 20 000 000 d'enregistrements et 37 colonnes, 36 + un id clé primaire unique que j'ai rajouté)

Voici ma procédure :

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
 
create or replace PROCEDURE PROC_FUSION_HEXA AS
  cpt NUMBER:=0;
 
  var_type_enr T_ADRESSES_SIG.TYPE_ENR%type;
  var_insee_localite T_ADRESSES_SIG.INSEE_LOCALITE%type;
  var_insee_global T_ADRESSES_SIG.INSEE_GLOBAL%type;
  var_localite T_ADRESSES_SIG.LOCALITE%type;
  var_pluridistribution T_ADRESSES_SIG.PLURIDISTRIBUTION%type;
  var_lieu_dit T_ADRESSES_SIG.LIEU_DIT%type;
  var_roudis2000 T_ADRESSES_SIG.ROUDIS2000%type;
  var_code_postal T_ADRESSES_SIG.CODE_POSTAL%type;
  var_insee_commune_admin T_ADRESSES_SIG.INSEE_COMMUNE_ADMIN%type;
  var_localite_fiab T_ADRESSES_SIG.LOCALITE_FIAB%type;
  var_maj T_ADRESSES_SIG.MAJ%type;
  var_acheminement T_ADRESSES_SIG.ACHEMINEMENT%type;
  var_matricule_voie T_ADRESSES_SIG.MATRICULE_VOIE%type;
  var_matricule_voie_s T_ADRESSES_SIG.MATRICULE_VOIE_S%type;
  var_dernier_voie T_ADRESSES_SIG.DERNIER_VOIE%type;
  var_libelle_voie T_ADRESSES_SIG.LIBELLE_VOIE%type;
  var_libelle_voie_s T_ADRESSES_SIG.LIBELLE_VOIE_S%type;
  var_type_voie T_ADRESSES_SIG.TYPE_VOIE%type;
  var_type_voie_s T_ADRESSES_SIG.TYPE_VOIE_S%type;
  var_descripteur_voie T_ADRESSES_SIG.DESCRIPTEUR_VOIE%type;
  var_descripteur_voie_s T_ADRESSES_SIG.DESCRIPTEUR_VOIE_S%type;
  var_standardisation T_ADRESSES_SIG.STANDARDISATION%type;
  var_scindage T_ADRESSES_SIG.SCINDAGE%type;
  var_borne_ii T_ADRESSES_SIG.BORNE_II%type;
  var_ext_ii T_ADRESSES_SIG.EXT_II%type;
  var_borne_is T_ADRESSES_SIG.BORNE_IS%type;
  var_ext_is T_ADRESSES_SIG.EXT_IS%type;
  var_borne_pi T_ADRESSES_SIG.BORNE_PI%type;
  var_ext_pi T_ADRESSES_SIG.EXT_PI%type;
  var_borne_ps T_ADRESSES_SIG.BORNE_PS%type;
  var_ext_ps T_ADRESSES_SIG.EXT_PS%type;
  var_roudis T_ADRESSES_SIG.ROUDIS%type;
  var_appellation T_ADRESSES_SIG.APPELLATION%type;
 
  var_num_voie T_ADRESSES_SIG.NUM_VOIE%type;
  var_extension_voie T_ADRESSES_SIG.EXTENSION_VOIE%type;
  var_cle_hexacle T_ADRESSES_SIG.CLE_HEXACLE%type;
 
CURSOR CUR IS
  SELECT MATRICULE_VOIE, NUM_VOIE, EXTENSION_VOIE, CLE_HEXACLE FROM T_HEXACLE;
 
CURSOR CUR2 IS
  SELECT TYPE_ENR, INSEE_LOCALITE, INSEE_GLOBAL, LOCALITE, PLURIDISTRIBUTION, LIEU_DIT, ROUDIS2000, CODE_POSTAL, INSEE_COMMUNE_ADMIN, LOCALITE_FIAB,  MAJ, ACHEMINEMENT, MATRICULE_VOIE_S, DERNIER_VOIE, LIBELLE_VOIE, LIBELLE_VOIE_S, TYPE_VOIE, TYPE_VOIE_S, DESCRIPTEUR_VOIE, DESCRIPTEUR_VOIE_S, STANDARDISATION, SCINDAGE, BORNE_II, EXT_II, BORNE_IS, EXT_IS, BORNE_PI, EXT_PI, BORNE_PS, EXT_PS, ROUDIS, APPELLATION
  FROM T_HEXAVIA WHERE MATRICULE_VOIE=var_matricule_voie AND (TYPE_ENR LIKE 'V' OR TYPE_ENR LIKE 'W');
 
BEGIN
  DELETE FROM T_ADRESSES_SIG;
  OPEN CUR;
  loop
    FETCH CUR INTO var_matricule_voie, var_num_voie, var_extension_voie, var_cle_hexacle;
    EXIT WHEN CUR%NOTFOUND;
 
        OPEN CUR2;
        loop
          FETCH CUR2 INTO var_type_enr, var_insee_localite, var_insee_global, var_localite, var_pluridistribution, var_lieu_dit, var_roudis2000, var_code_postal, var_insee_commune_admin, var_localite_fiab, var_maj, var_acheminement, var_matricule_voie_s, var_dernier_voie, var_libelle_voie, var_libelle_voie_s, var_type_voie, var_type_voie_s, var_descripteur_voie, var_descripteur_voie_s, var_standardisation, var_scindage, var_borne_ii, var_ext_ii, var_borne_is, var_ext_is, var_borne_pi, var_ext_pi, var_borne_ps, var_ext_ps, var_roudis, var_appellation;
          EXIT WHEN CUR2%NOTFOUND;
 
          cpt:=cpt+1;
          INSERT INTO T_ADRESSES_SIG(TYPE_ENR, INSEE_LOCALITE, INSEE_GLOBAL, LOCALITE, PLURIDISTRIBUTION, LIEU_DIT, ROUDIS2000, CODE_POSTAL, INSEE_COMMUNE_ADMIN, LOCALITE_FIAB, MAJ, ACHEMINEMENT, MATRICULE_VOIE, NUM_VOIE, EXTENSION_VOIE, CLE_HEXACLE, MATRICULE_VOIE_S, DERNIER_VOIE, LIBELLE_VOIE, LIBELLE_VOIE_S, TYPE_VOIE, TYPE_VOIE_S, DESCRIPTEUR_VOIE, DESCRIPTEUR_VOIE_S, STANDARDISATION, SCINDAGE, BORNE_II, EXT_II, BORNE_IS, EXT_IS, BORNE_PI, EXT_PI, BORNE_PS, EXT_PS, ROUDIS, APPELLATION)
          VALUES(var_type_enr, var_insee_localite, var_insee_global, var_localite, var_pluridistribution, var_lieu_dit, var_roudis2000, var_code_postal, var_insee_commune_admin, var_localite_fiab, var_maj, var_acheminement, var_matricule_voie, var_num_voie, var_extension_voie, var_cle_hexacle, var_matricule_voie_s, var_dernier_voie, var_libelle_voie, var_libelle_voie_s, var_type_voie, var_type_voie_s, var_descripteur_voie, var_descripteur_voie_s, var_standardisation, var_scindage, var_borne_ii, var_ext_ii, var_borne_is, var_ext_is, var_borne_pi, var_ext_pi, var_borne_ps, var_ext_ps, var_roudis, var_appellation);
 
        end loop;
        close CUR2;
        if mod(cpt, 5000)=0 then
          COMMIT;
        end if;
  end loop;
  dbms_output.put_line('Total : ' || cpt);
  CLOSE CUR;
END PROC_FUSION_HEXA;
j'ai surveillé le temps d'exécution dans V_$SESSION_LONGOPS et c'est pas rassurant :
les 100 000 1ères lignes ont été traitées en 6 minutes
les 50 000 suivantes en 9 minutes
les 50 000 suivantes en 13 minutes
les 50 000 suivantes en 15 minutes
les 50 000 suivantes en 19 minutes
...et le TIME_REMAINING est estimé à 70 heures (et continue de grimper !!!)

Il s'agit d'un Oracle 11.1.0 qui tourne pourtant sur une machine de guerre (quadriprocesseur quadcore + 64Go de Ram + disques 15000rpm).

Si vous avez des idées d'optimisation de ma procédure je suis preneur...
Merci d'avance pour vos réponses.