Optimisation de la commande INSERT

Version imprimable

21/04/2010, 21h40
labolabs

Optimisation de la commande INSERT

Bonjour,

J'ai besoin d'insérer plus de 6 millions de records dans une table (partitionnée). Pour l'instant mon script ne fait que des commandes INSERT normaux...et ceci me prend à peu près 2h pour tout charger !!

Y a t-il un moyen pour optimiser mes insertions?

(Les indexes sur ma table sont supprimés avant l'insertion, recrées après)

Merci d'avance.
22/04/2010, 02h23
yong321

Beaucoup de insert ... values ou un insert ... select? Si insert ... select, optimisez select (la requête) et utilisez insert /*+ append */.

Quelle est la principale wait event pendant l'insert?
select event, p1, p2, p3 from v$session_wait where sid = ...
22/04/2010, 08h26
mnitu

Citation:

Envoyé par labolabs

Bonjour,

J'ai besoin d'insérer plus de 6 millions de records dans une table (partitionnée). Pour l'instant mon script ne fait que des commandes INSERT normaux...et ceci me prend à peu près 2h pour tout charger !!

Y a t-il un moyen pour optimiser mes insertions?

Oui! Mais, il va falloir donner un peu plus d'informations sur le traitement: d'où le données provient, comment les insert sont fait, etc.
22/04/2010, 09h33
labolabs

Voici le scénario de mon script:

1 - Je récupère les données à partir d'une table T1
2 - Je fais des contrôles sur les champs de T1
3 - Si tous les contrôles sont valides, je fais un INSERT INTO T2 VALUES ...
22/04/2010, 09h53
Waldar

Quel genre de contrôles ?
Si ce sont des contrôles SQL, vous pouvez les intégrer directement à la requête initiale, puis tout insérer dans votre table en mode APPEND.
22/04/2010, 09h57
mnitu

Bref, il manque encore quelques infos...
Voici un lien qui explore l'utilisation des fonctions pipelined comme solution. Mais, dans un premier temps vous pouvez peut être vous limitez à l'utilisation du bulk et forall (si votre script est en PL/SQL bien sur).
22/04/2010, 10h40
labolabs

Merci pour vos réponses. Oui il s'agit de contrôles SQL, du genre:

* Si Champ 1 est NULL
* Si Champ 2 > Champ 3
* Si Champ 4 NOT IN (...) etc.

Le souci est qu'il y à peu près 100 contrôle de ce genre !! tout mettre dans une seule requête n'est pas la bonne idée à mon avis...

Je vais essayer l'insertion en mode APPEND puis les fonctions pipelined et tester les performances...Je vous tiens au courant du résultat.
22/04/2010, 10h47
sgora

Au fait, un petit détail, le more insert /*+ APPEND */ ne fonctionne pas avec la clause values, seulement avec des insert select...
22/04/2010, 14h50
labolabs

Je suis entrain de tester des insertions en masse en utilisant FORALL, sauf que j'ai un problème de PK !!

Dans la table où j'insère il y a une séquence qui doit s'incrémenter selon la valeur d'un autre champ; Exemple:
FIELD_NAME|SEQUENCE
ABC|1
ABC|2
ABC|3
ADB|1
ADB|2
...
(je ne peux pas utiliser de séquence définie dans Oracle pour faire de NEXTVAL !)

En gros j'utilise un SELECT MAX()+1 de de ma table, mais apparemment ceci ne marche pas !!

Quelqu'un a une idée comment éviter ce problème de PK?

Merci d'avance
22/04/2010, 15h17
Waldar

Il faut donner toutes les informations, notamment la description de la PK !
Ce que vous cherchez à obtenir de ce fait pas avec une séquence, utilisez la fonction row_number().
22/04/2010, 20h34
pachot

Bonjour,

Citation:

Le souci est qu'il y à peu près 100 contrôle de ce genre !! tout mettre dans une seule requête n'est pas la bonne idée à mon avis...

Et pourquoi pas ? En tout cas entre une requête de 3 pages et 6 millions d'inserts, je n'hésite pas une seconde ;)
Et si c'est trop, découper en plusieurs requêtes avec tables temporaires.

Aujourd'hui on peut faire de grosses requêtes, performantes, lisibles et maintenables (possibilité de factoriser par exemple avec WITH). Il y a beaucoup de chose qu'on peut faire en sql, et qui sont faite pour ca (comme row_number() que suggère Waldar)

Cordialement,
Franck.
23/04/2010, 08h46
mnitu

Citation:

Envoyé par pachot

...
Et si c'est trop, découper en plusieurs requêtes avec tables temporaires.

...

Frank,
Ca c'est plutôt pour plomber les performances.
23/04/2010, 13h21
labolabs
Je viens de tester les insertions en utilisant les fonctions pipelined (lien): le résultat est très satisfaisant, je gagne à peu près 1/3 du temps que je faisait avant :ccool:

Est ce qu'il est possible d'utiliser ces fonctions pipelined pour insérer dans plusieurs tables à la fois? Pour l'instant voici la commande utilisée:
Code:

1 2 3 4 5 6 INSERT /*+ PARALLEL(f, 10) */ INTO T1 f ( code, name ) SELECT code, name FROM TABLE( MY_PACKAGE.LOAD_T ( CURSOR(SELECT /*+ PARALLEL(p, 10) */ * FROM TABLE_SOURCE p)));
Je devrais tester si TABLE_SOURCE.champ1 = 'Y', j'insère dans T1, sinon je devrais insérer dans T2. C'est possible de le faire?

Vous pouvez faire un insert conditionnel :

Code:

1
2
3
4
5
6
INSERT
  WHEN champ1 = 'Y' THEN
  INTO T1 VALUES (...)
  ELSE
  INTO T2 VALUES (...)
SELECT ...

Y a t-il une différence entre l'instruction:

Code:

1
2
3
4
5
6
INSERT
  WHEN champ1 = 'Y' THEN
  INTO T1 VALUES (...)
  ELSE
  INTO T2 VALUES (...)
SELECT ...

et celle ci ?

Code:

1
2
3
4
5
6
INSERT FIRST
  WHEN champ1 = 'Y' THEN
  INTO T1 VALUES (...)
  ELSE
  INTO T2 VALUES (...)
SELECT ...

Faisons le test :

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
create table t1 (nm number(1));
create table t2 (nm number(1));
 
insert 
  when nb <= 1 then
  into t1 (nm) values (nb)
  when nb <= 2 then
  into t2 (nm) values (nb)  
select 1 as nb from dual union all
select 2       from dual;
 
select * from t2; 
 
NM
1
2
 
rollback;
 
insert all
  when nb <= 1 then
  into t1 (nm) values (nb)
  when nb <= 2 then
  into t2 (nm) values (nb)  
select 1 as nb from dual union all
select 2       from dual;
 
select * from t2; 
 
NM
1
2
 
rollback;
 
insert first
  when nb <= 1 then
  into t1 (nm) values (nb)
  when nb <= 2 then
  into t2 (nm) values (nb)  
select 1 as nb from dual union all
select 2       from dual;
 
select * from t2; 
 
NM
2

Donc sans mot clef c'est un INSERT ALL.

Vous avez raison de soulever ce point car dans votre cas c'est bien un INSERT FIRST dont il semble que vous ayez besoin.

Merci pour la réponse Waldar. En effet j'aurais besoin d'un INSERT FIRST dans mon cas !

Aussi une autre remarque qui me vient à l'esprit ! Si je reprends votre example (modifé):
Code:

1 2 3 4 5 6 7 8 9 10 CREATE TABLE T_COURANT (nm number(1), statut char(4)); CREATE TABLE T_HISTORIQUE (nm number(1), statut char(4)); CREATE TABLE TABLE_SOURCE (nm number(1), statut char(4)); INSERT when statut = 'COUR' then INTO T_COURANT (nm, statut) VALUES (nm, statut) when statut = 'HIST' then INTO T_HISTORIQUE (nm, statut) VALUES (nm, statut) SELECT * FROM TABLE_SOURCE;
Il me semble dans ce cas qu'on ne fait pas des insertions au fur et à mesure, mais qu'on insère d'abord toutes les lignes qui ont statut = 'COUR' dans la table T_COURANT, puis après commence l'insertion dans la table T_HISTORIQUE quand statut = 'HIST' !!! sachant que ma table TABLE_SOURCE n'est pas triée (on peut avoir 3 lignes avec statut = 'COUR' puis une avec 'HIST' puis une autre 'COUR' etc.).

Je veux dire qu'il y aura pas de différence côté performances si j'avais écris un code comme celui là:
Code:

1 2 3 4 5 6 7 8 9 INSERT INTO T_COURANT (nm, statut) SELECT * FROM TABLE_SOURCE where statut = 'COUR'; INSERT INTO T_HISTORIQUE (nm, statut) SELECT * FROM TABLE_SOURCE where statut = 'HIST';
Je pensais qu'avec un INSERT WHEN on pouvait gagner du temps dans les insertions !

26/04/2010, 13h33
Waldar

Dans le premier cas vous lisez une fois les blocks, dans le second deux fois.
Après ça dépend de paramètres de cache, de partition, d'index etc etc etc.

Le plus simple (pour vous), c'est encore de comparer les deux solutions !
26/04/2010, 13h49
labolabs

J'avais fais le test pour insérer 2.5 millions de lignes !

Le premier cas a mis 8 minutes 10 secondes, le deuxième 8 minutes 18 secondes.

Après comme vous dîtes ça dépend des paramètres du cache, des indexes, etc.

Je vais utiliser finalement le INSERT FIRST WHEN ...
26/04/2010, 14h01
mnitu

Citation:

Envoyé par labolabs

...
Il me semble dans ce cas qu'on ne fait pas des insertions au fur et à mesure, mais qu'on insère d'abord toutes les lignes qui ont statut = 'COUR' dans la table T_COURANT, puis après commence l'insertion dans la table T_HISTORIQUE quand statut = 'HIST' !!! ...

Qu’est-ce qu'il vous fait croire ça ? Le plan d'exécution ne montre guerre de trace d'un pareil comportement.

Citation:

Envoyé par mnitu

Qu’est-ce qu'il vous fait croire ça ? Le plan d'exécution ne montre guerre de trace d'un pareil comportement.

J'ai fait la remarque car la requête:
Code:

1 2 3 4 5 6 INSERT when statut = 'COUR' then INTO T_COURANT (nm, statut) VALUES (nm, statut) when statut = 'HIST' then INTO T_HISTORIQUE (nm, statut) VALUES (nm, statut) SELECT * FROM TABLE_SOURCE;
a mis autant de temps pour insérer les lignes que celle-ci:
Code:

1 2 3 4 5 6 7 8 INSERT INTO T_COURANT (nm, statut) SELECT * FROM TABLE_SOURCE WHERE statut = 'COUR'; INSERT INTO T_HISTORIQUE (nm, statut) SELECT * FROM TABLE_SOURCE WHERE statut = 'HIST';
sachant que la deuxième requête fera 2 lectures de la table TABLE_SOURCE au lieu d'une dans la première requête.

26/04/2010, 14h30
Waldar

Avez-vous essayé en direct load (append) ?

Non je n'ai pas fait de Append, j'ai juste utilisé le Parallel Run:
Code:

1 2 3 4 5 6 INSERT /*+ PARALLEL(c, 10) */ when statut = 'COUR' then INTO T_COURANT (nm, statut) VALUES (nm, statut) when statut = 'HIST' then INTO T_HISTORIQUE (nm, statut) VALUES (nm, statut) SELECT * FROM TABLE_SOURCE;
Peut-on associer un Parallel Run avec un Append ?

26/04/2010, 15h56
Waldar

Oui je pense, mais il y a des contraintes pour le APPEND :
http://download.oracle.com/docs/cd/E...ments_9014.htm

Voir le premier gros paragraphe Conventional and Direct-Path INSERT.
26/04/2010, 16h20
labolabs

Je viens de jeter un coup d'œil sur cette article, apparemment pas besoin de APPEND quand il s'agit d'un INSERT avec le hint PARALLEL, vu que le APPEND est utilisé par défaut dans ce dernier cas.

(voir le paragraphe Oracle append hint and parallel DML)