Discussion :

[niiccoo]

Bonjour,

Me revoilà avec mes questions d'optimisations d'insertion, cette fois ci je vais essayer d'être encore plus précis.

Le script que j'optimise permet d'alimenter une table de fait dans un DataMart à partir d'un flux journalier.
J'ai donc deux tables principales :
* int_navigation : la table de fait dans laquelle on insère les informations de session
* imp_navigation : la table d'importation dans laquelle on insère les données brutes (sqlldr)

Les volumétries sont les suivantes :
* int_navigation : 45 millions d'enregistremnts
* imp_navigation : 1 million d'enregistrements

Les index sont les suivants :
(ma notation pour les index table(col[,col]) )
* int_navigation(id_usage, id_session, tps_id, num_ordre_session) (ma clé primaire)
* int_navigation(id_interne_ei)
* int_navigation(id_interne_struct_liaison)
* int_navigation(tps_id,id_session)
* int_navigation(id_session)
* imp_navigation (status)

Au début, dans la procédure pl/sql, il y avait un curseur qui insérait enregistrement par enregistrement. Dans ce curseur on trouvait également le calcul de données non présentes dans imp_navigation.
Comme je sais qu'il est préférable de faire un gros insert que plusieurs petits, je ne me sert du curseur que pour mettre à jour des informations dans la table d'importation, afin d'avoir toutes mes données prêtes pour l'insert final.

Or mon problème est que cet insertion est extrèmement longue, voici la requête qui me pose un problème de rapidité :

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
 
   insert /*+ APPEND */ into int_navigation (ID_INTERNE_STRUCT_LIAISON,
            ID_INTERNE_EI,
            ID_USAGE,
            TPS_ID,
            ID_SESSION,
            ID_INTERNE_INDIVIDU,
            id_hierarchique,
            DAT_SESSION,
            NUM_ORDRE_SESSION,
            DAT_CREA,
            id_interne_lien,
            origine_session,
            IP,
            ORI_IP,
            EI_AFFICHE          ,
            SL_AFFICHE          ,
            IMPRESSION          ,
            ENVOI_AMI           ,
            ARCHIVER            ,
            TELECHARGER         ,
            FORUM               ,
            FORMULAIRE_CONTACT  ,
            FORMULAIRE_AT       ,
            VOIX_CLIENT			,
            ID_HIERARCHQIUE_EI ,
            COOKIE
            )
select 
        nvl(a.ID_INTERNE_STRUCT_LIAISON,0),
        nvl(a.ID_INTERNE_EI,0),
        a.ID_USAGE,
        to_char(a.date_hit, 'YYYYMMDD'),
        a.ID_SESSION,
        nvl(a.ID_INTERNE_INDIVIDU,0),
        nvl(a.id_hierarchique,0),
        a.date_hit,
        a.num_ordre,
        sysdate,
        b.id_interne_lien,
        a.origine_session,
        a.IP,
        999,
        decode(nvl(a.ID_INTERNE_EI,0),0,0,1),
        decode(nvl(a.ID_INTERNE_STRUCT_LIAISON,0),0,0,1),
        decode(a.ID_USAGE,'10',1,0),
        decode(a.ID_USAGE,'11',1,0),
        decode(a.ID_USAGE,'23',1,0),
        decode(a.ID_USAGE,'24',1,0),
        decode(a.ID_USAGE,'25',1,0),
        decode(a.ID_USAGE,'26',1,0),
        decode(a.ID_USAGE,'27',1,0),
        decode(a.ID_USAGE,'28',1,0),
        nvl(a.id_hierarchique_ei,0),
        a.COOKIE
from imp_navigation   a
        left outer join INT_LIEN b on a.lien_libelle = b.lien_libelle
        left outer join INT_ORI_IP h on (a.IP = h.IP  and ( h.FLAG_SUPP is null or h.FLAG_SUPP = 0 ))
where a.status = 'N' 
   and h.ORI_IP is null
   and a.num_ordre is not null
order by a.id_session, a.date_hit;

La requête de sélection est très rapide, moins de 20 secondes et sélectionne environ 700 000 enregistrements.
Par contre l'insertion dure plus d'une heure :'(
Je sais qu'il y a beaucoup d'index dans la table int_navigation, et que la clé primaire est mal foutue, mais je ne peux pas toucher au modèle, les optimisations ne peuvent provenir que de ma procédure d'alimentation.
J'utilise déjà le hint conseillé par Fred_d lors de ma dernière question, (/*+ APPEND */).
J'aimerais donc savoir si il existe d'autres façons de faire plus performantes ? car là je sèche un petit peu.

Merci d'avance
Nico

[Nick_Holmes]

Si la procédure d'insertion dure longtemps, fais un test entre la procédure normale et une procédure en dropppant ou invalidant les index. Quelquefois pour insérer bcp de lignes, le fait de dropper puis recréer les index est plus rapide que de les laisser. IL FAUT TESTER LE GAIN OU LA PERTE DE PERFORMANCE ENTRE LES 2 METHODES AVANT DE SE PRONONCER !!!

@+,
NicK.

[niiccoo]

J'essayerais cette méthode, mais je ne pense qu'elle me convienne, car je sais que de recréer tous mes index me prend une heure.

Qu'entend tu par "invalider" les index ? c'est différent que de les dropper et de les recréer ensuite ?

[dyvim]

Si ton index est INVALID, ca veut dire qu'il n'est plus à jour et donc plus utilisé.
Donc quand tu insères, on ne le met pas à jour...
Il faut le REBUILDer pour qu'il redevienne actif ce qui est différent de le DROPer et de le le recréer (et risque d'être un peu plus rapide mais ce n'est pas sûr).

Par contre je ne connais pas de code pour "invalider" un index, pour moi cela se faisait uniquement involontairement ou en conséquence d'un drop de partition par exemple.

[niiccoo]

Hum, je vais chercher la syntaxe pour invalider l'index. J'espère que je vais trouver !

PS: il manque un e à free dans l'url de ton blog dans ta signature

[dyvim]

Hum, je vais chercher la syntaxe pour invalider l'index. J'espère que je vais trouver !

PS: il manque un e à free dans l'url de ton blog dans ta signature

Bonne recherche... Et merci pour la signature...