Insertion en masse dans table de fait

Version imprimable

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:

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

24/05/2006, 10h29
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.
24/05/2006, 10h38
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 ?
24/05/2006, 11h03
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.
24/05/2006, 11h17
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
24/05/2006, 11h53
dyvim

Citation:

Envoyé par 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

Bonne recherche... Et merci pour la signature...:lol:
24/05/2006, 13h01
McM

Le ORDER BY dans l'INSERT n'est pas nécessaire.
Tu as des triggers sur INT_NAVIGATION ?
24/05/2006, 13h09
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.
24/05/2006, 13h26
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
24/05/2006, 13h40
niiccoo

d'accord merci, je vais donc supprimer ce tri ;) ça sera déjà 10secondes de gagnées :D
24/05/2006, 13h51
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...) ?

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

Voici le schéma de ma table :

Code:

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:

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 ) 
;

24/05/2006, 14h04
orafrance

essaye de mettre un NEXT = INITIAL ;)

Les tablespaces sont en LOCALLY MANAGED ?
24/05/2006, 14h10
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.

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:

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:

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:

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;

24/05/2006, 16h18
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 !
24/05/2006, 16h39
Jaouad

tu peux aussi essayer de parallélliser l'insert et la re création d'index
24/05/2006, 16h45
niiccoo

comment se fait la parrallélisation ? en rajoutant le hint /*+ PARRALLEL */ ?
24/05/2006, 16h48
orafrance

avec un seul R ;)
29/05/2006, 10h56
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 :D

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