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...

[McM]

Le ORDER BY dans l'INSERT n'est pas nécessaire.
Tu as des triggers sur INT_NAVIGATION ?

[niiccoo]

en effet, le sort n'est peut être nécessaire pour l'insertion, mais ensuite pour la restitution il sert peut être à accélérer la sélection des données dans la table non ?

non, je n'ai aucun trigger sur la table int_navigation.

[McM]

Le tri est effectué en mémoire après la récupération des données.

Le seul avantage que je vois c'est lorsque tu fais un select sans tri par la suite, tu auras tes données triées

[niiccoo]

d'accord merci, je vais donc supprimer ce tri ça sera déjà 10secondes de gagnées

[McM]

Sinon quelle est la définition des tablespaces de la table d'insertion et de ses index ?
Comment est déclarée la table (init, extend...) ?

[niiccoo]

J'ai un tablespace pour les index et un pour les données.

Voici le schéma de ma table :

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
 
CREATE TABLE INT_NAVIGATION ( 
  ...
  CONSTRAINT PK_INT_NAVIGATION
  PRIMARY KEY ( ID_USAGE, ID_SESSION, TPS_ID, NUM_ORDRE_SESSION ) 
    USING INDEX 
     TABLESPACE INDX PCTFREE 10
     STORAGE ( INITIAL 2015232 NEXT 10240000 PCTINCREASE 0 ))
   TABLESPACE DATA NOLOGGING 
   PCTFREE 10
   PCTUSED 40
   INITRANS 1
   MAXTRANS 255
  STORAGE ( 
   INITIAL 9199616
   NEXT 3686400
   MINEXTENTS 1
   MAXEXTENTS 2147483645
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE;

et le schéma de création des index :

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
 
CREATE INDEX INDX_NAVIGATION_EI ON 
  INT_NAVIGATION(ID_INTERNE_EI) 
  TABLESPACE INDX PCTFREE 10  STORAGE(INITIAL 16384 NEXT 104857600 PCTINCREASE 0 ) 
; 
 
CREATE INDEX INDX_NAVIGATION_SESSION ON 
  INT_NAVIGATION(ID_SESSION) 
  TABLESPACE INDX PCTFREE 0  STORAGE(INITIAL 443072512 NEXT 15007744 PCTINCREASE 0 ) 
; 
 
CREATE INDEX INDX_STRUCT_LIAISON ON 
  INT_NAVIGATION(ID_INTERNE_STRUCT_LIAISON) 
  TABLESPACE INDX PCTFREE 10  STORAGE(INITIAL 16384 NEXT 104857600 PCTINCREASE 0 ) 
; 
 
CREATE INDEX INT_NAVIGATION_SESSION ON 
  INT_NAVIGATION(TPS_ID, ID_SESSION) 
  TABLESPACE INDX PCTFREE 10  STORAGE(INITIAL 443072512 NEXT 30007296 PCTINCREASE 0 ) 
;

[orafrance]

essaye de mettre un NEXT = INITIAL

Les tablespaces sont en LOCALLY MANAGED ?

[niiccoo]

Dès que j'ai trouvé la commande pour modifier ma table, je teste

par contre pour le tablespace, je n'en ai aucune idée, comment puis je voir ça ? et mon environement de dév n'est pas répliqué à l'identique au niveau des tablespace :/ et même, en prod je n'ai pas les droits pour modifier la structure du tablespace.

[McM]

Faut voir combien de place prend ta table.
Mais pour 45 millions d'enreg, je trouve ça très peu 10 Mo de depart et 4 Mo d'extend.

Code :

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

1
2
3
4
5
6
7
8
 
TABLESPACE DATA NOLOGGING 
 
 
STORAGE ( 
 
INITIAL 9199616 => 10 Mo
NEXT 3686400 => 3.5 Mo

Pour voir tes tablespaces :

Code :

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

1
2
3
4
 
SELECT tablespace_name, initial_extent, next_extent, 
pct_increase, extent_management, segment_space_management
FROM DBA_TABLESPACES

Pour voir la taille de ta table

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT a.segment_name, b.initial_extent, b.next_extent, COUNT(*) fragments, 
SUM(a.blocks) nb_blocks, SUM(a.bytes) AS init 
FROM DBA_EXTENTS a, USER_TABLES b
WHERE a.segment_type = 'TABLE'
AND a.tablespace_name = 'DATA'
AND a.owner = 'TON_OWNER'
AND b.table_name = a.segment_name
AND b.tablespace_name = 'DATA'
AND a.segment_name = 'INT_NAVIGATION'
GROUP BY segment_name, b.initial_extent, b.next_extent;

[niiccoo]

en effet, c'est extrèmement petit, les données de la table font 6Go,dc un extends tous les 3Mo, ça ne m'étonnerais pas que ça me foute dedans niveau perfs ! les valeurs utilisées pour l'index de la clé sont bien plus correctes.

Je suis entrain de travailler pour retailler tout ceci, je vous tiendrais au courant !

Merci de m'avoir ouvert les yeux !

[Jaouad]

tu peux aussi essayer de parallélliser l'insert et la re création d'index

[niiccoo]

comment se fait la parrallélisation ? en rajoutant le hint /*+ PARRALLEL */ ?

[orafrance]

avec un seul R

[niiccoo]

Bonjour

j'ai travaillé sur la structure de ma table, j'ai en fait retaillé la taille initiale et la taille des extends. les données faisant 6Go au total, j'ai mis une taille initiale de 1Go et des extends de 100Mo.

Rien que avec ceci, je suis passé à environ une dizaine de minutes pour insérer 650 000 enregistrements

La suppression de données en masse est également plus rapide, x2 environ.

Je donne ma solution et passe le sujet à résolu. Merci à tous ceux qui m'ont aidés.
Nico