lenteur dans l'insertion de donnees en 10Gr2

**JUSTIN Loïc** · 12/09/2006, 13h18

Bonjour,
Je suis sur une base Oracle 10Gr2 fonctionnant sous windows 2K.
Je fais l'inserion de donnéés à partir d'un script sql avec des commandes INSERT INTO. Je trouve que l'insertion est trop long. 27' pour 104000 enregistrements. Quand je verifie avec ORACLE entreprise manager, il me dit que c'est le type SQL (PL/SQL execute) qui a consommé 14,05 %
Si je regarde le detail

begin DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); end;

Il ya t'il un moyen d'optmiser .

D'avance merci

Ps:voir le graphique joint

**Oraman** · 13/09/2006, 17h29

est ce qu'on pourra voir un échantillion de ton script ?

**JUSTIN Loïc** · 13/09/2006, 17h43

bien sure

INSERT INTO detail_serveur_mois
(id_serv, dates, obj_sauve_t, vol_sauve_t, nb_sauve_t, err_sauve_t, obj_sauve_b, vol_sauve_b, nb_sauve_b, err_sauve_b, obj_sauve_i, vol_sauve_i, nb_sauve_i, err_sauve_i, obj_other, vol_other, nb_other, err_other, obj_rest, vol_rest, nb_rest, err_rest)
VALUES (10493, '2006-07-28', 31970, 77987, 21, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO detail_serveur_mois
(id_serv, dates, obj_sauve_t, vol_sauve_t, nb_sauve_t, err_sauve_t, obj_sauve_b, vol_sauve_b, nb_sauve_b, err_sauve_b, obj_sauve_i, vol_sauve_i, nb_sauve_i, err_sauve_i, obj_other, vol_other, nb_other, err_other, obj_rest, vol_rest, nb_rest, err_rest)
VALUES (16345, '2006-07-28', 455171, 14756, 8, 0, 478486, 21997, 8, 0, 1371, 342, 19, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO detail_serveur_mois
(id_serv, dates, obj_sauve_t, vol_sauve_t, nb_sauve_t, err_sauve_t, obj_sauve_b, vol_sauve_b, nb_sauve_b, err_sauve_b, obj_sauve_i, vol_sauve_i, nb_sauve_i, err_sauve_i, obj_other, vol_other, nb_other, err_other, obj_rest, vol_rest, nb_rest, err_rest)
VALUES (16310, '2006-07-28', 2894660, 87905, 40, 0, 2675604, 206489, 9, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO detail_serveur_mois
(id_serv, dates, obj_sauve_t, vol_sauve_t, nb_sauve_t, err_sauve_t, obj_sauve_b, vol_sauve_b, nb_sauve_b, err_sauve_b, obj_sauve_i, vol_sauve_i, nb_sauve_i, err_sauve_i, obj_other, vol_other, nb_other, err_other, obj_rest, vol_rest, nb_rest, err_rest)
VALUES (17415, '2006-07-28', 915719, 51534, 25, 0, 671483, 32499, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO detail_serveur_mois
(id_serv, dates, obj_sauve_t, vol_sauve_t, nb_sauve_t, err_sauve_t, obj_sauve_b, vol_sauve_b, nb_sauve_b, err_sauve_b, obj_sauve_i, vol_sauve_i, nb_sauve_i, err_sauve_i, obj_other, vol_other, nb_other, err_other, obj_rest, vol_rest, nb_rest, err_rest)
VALUES (17463, '2006-07-28', 959009, 115036, 25, 0, 648977, 43110, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,0);
INSERT INTO detail_serveur_mois
(id_serv, dates, obj_sauve_t, vol_sauve_t, nb_sauve_t, err_sauve_t, obj_sauve_b, vol_sauve_b, nb_sauve_b, err_sauve_b, obj_sauve_i, vol_sauve_i, nb_sauve_i, err_sauve_i, obj_other, vol_other, nb_other, err_other, obj_rest, vol_rest, nb_rest, err_rest)
VALUES (17464, '2006-07-28', 1046104, 54684, 25, 0, 703720, 30039, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,0);

Voici un échantillon de données qui pose probleme.
D'avance merci

**laurentschneider** · 13/09/2006, 17h54

utilise les bind variable pour ce genre d'insert...

**JUSTIN Loïc** · 13/09/2006, 18h05

aurais-tu un exemple sous la main. S'il te plait ?

D'avance merci

**laurentschneider** · 13/09/2006, 18h12

par exemple, si tu fais

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
 
SQL> create table t(x number);
 
Table created.
 
SQL> var n number
SQL> exec :n:=1
 
PL/SQL procedure successfully completed.
 
SQL> insert into t values(:n);
 
1 row created.
 
SQL> exec :n:=2
 
PL/SQL procedure successfully completed.
 
SQL> insert into t values(:n);
 
1 row created.
 
SQL> exec :n:=3
 
PL/SQL procedure successfully completed.
 
SQL> insert into t values(:n);
 
1 row created.

le curseur pour l'insert pour être réutilisé et ça économisera la shared_pool.

Je te conseille de lire les bouquin de Tom Kyte, voire de t'inscrire pour son séminaire
tom kyte à Paris la semaine prochaine (une occasion exceptionnelle, s'il y a encore de la place)

**orafrance** · 13/09/2006, 18h16

je ne pense pas que le problème vienne du parsing... en revanche un INSERT de masse pourrait être intéressant... exemple :

Code :

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

1
2
3
4
5
INSERT INTO matable
VALUES (1);
 
INSERT INTO matable
VALUES (2);

Devient :

Code :

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

1
2
3
4
5
6
7
 
INSERT INTO matable
SELECT ( 
   SELECT 1 FROM DUAL
   UNION ALL 
   SELECT 2 FROM DUAL
           );

**Oraman** · 13/09/2006, 18h43

d'aprés justin :

il me dit que c'est le type SQL (PL/SQL execute) qui a consommé 18,05 %

je n'ai pas compris comment interpreter cette valeur du graphe ci -joint

le PL/SQL execute est générer par quoi ?

**Arturius** · 13/09/2006, 19h48

Je pense que le PL/SQL doit être lié aux procédures de mesures de l'activité de la base de données.

**pifor** · 13/09/2006, 21h20

Je crois que Laurent et Tom ont raison: voici les résultats reproductibles dans mon environnement d'un petit test sur Windows XP effectué avec 1 seule instance Oracle démarrée, 1 fenêtre cmd.exe, 1 éditeur de texte et Firefox.
On a plus de 3 minutes sans bind variables et 5 secondes avec bind variables.

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
75
SQL> 
SQL> select * from v$version;

BANNER                                                                          
----------------------------------------------------------------                
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod                
PL/SQL Release 10.2.0.1.0 - Production                                          
CORE	10.2.0.1.0	Production                                                      
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production                         
NLSRTL Version 10.2.0.1.0 - Production                                          

SQL> 
SQL> 
SQL> drop table t;

Table dropped.

SQL> 
SQL> create table t(x  number primary key, y varchar(20), z date);

Table created.

SQL> 
SQL> create or replace procedure ins_sans_binds
  2  is
  3  begin
  4   for i in 1..100000
  5   loop
  6  	     execute immediate
  7  	      'insert into t values(' || i || ', to_char(' || i || '), sysdate)';
  8   end loop
  9   commit;
 10  end;
 11  /

Procedure created.

SQL> show errors
No errors.
SQL> 
SQL> create or replace procedure ins_avec_binds
  2  is
  3  begin
  4   for i in 1..100000
  5   loop
  6  	     insert into t values(i, to_char(i), sysdate);
  7   end loop
  8   commit;
  9  end;
 10  /

Procedure created.

SQL> show errors
No errors.
SQL> 
SQL> set timing on
SQL> 
SQL> exec ins_sans_binds;

PL/SQL procedure successfully completed.

Elapsed: 00:03:22.21
SQL> truncate table t;

Table truncated.

Elapsed: 00:00:01.85
SQL> exec ins_avec_binds;

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.01
SQL> 
SQL> exit

Ceci dit, je n'utilise pas OEM mais je pense que le meilleur moyen d'analyser les problèmes de performances reste toujours la trace SQL et tkprof.

Il se peut que le code PL/SQL soit lié à l'exécution d'un trigger (ou plus improbable à du SQL récursif pour vérification de contraintes d'intégrités). Utiliser la trace SQL et tkprof avec l'option sys=yes devrait permettre d'en avoir le coeur net.

**Oraman** · 14/09/2006, 09h58

à toi de jouer justin , pourrai tu nous faire un test et nous dire qu'est ce ça a donné chez toi.
je suis trés curieux de connaitre le résultat

**JUSTIN Loïc** · 14/09/2006, 10h14

Bonjour,

Merci beaucoup à tous pour votre aide. Mais la franchement, je me suis pris une grande claque . Car je croyais connaître, et je me rends compte que je suis tout petiiiit. Il me reste plus qu' à assimiler la quantité d'information, pour ensuite faire les tests. Il faut être indulgent avec moi. La réponse ne sera peut-être pas immédiate.
Encore merci

**JUSTIN Loïc** · 14/09/2006, 11h33

bon et bien,
je me suis lancé en refaisant sur ma base les memes tests que proposé par PIFOR. J'ai controlé la version, elle est identique voir ci-dessous:

SQL> select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Maintenant les resultats

09:57:38 SQL> exec ins_sans_binds;

Procédure PL/SQL terminée avec succès.

Validation (commit) effectuée.
Ecoulé : 00 :10 :15.07

10:10:56 SQL> exec ins_avec_binds;

Procédure PL/SQL terminée avec succès.

Validation (commit) effectuée.
Ecoulé : 00 :00 :30.55

Comme on peut le constater le temps est plus de 3 fois superieurs dans la procedure ins_sans_binds et 6 fois superieurs dans la procedure ins_avec_binds
Lors des tests je n'avais que la base ORACLE, sqlplusw et l'invite de commande de demarrer.

**Oraman** · 14/09/2006, 11h41

ben tout dépend aussi des params de config de ta base (sga...)

**pifor** · 14/09/2006, 12h02

Comme on peut le constater le temps est plus de 3 fois superieurs dans la procedure ins_sans_binds et 6 fois superieurs dans la procedure ins_avec_binds

Vous comparez mes résultats bruts obtenus sur Windows XP avec un Athlon 3400 et disque SATA avec NTFS avec votre machine qui est peut-être un PC (?) ou un serveur (?) qui semble plus lent et comme le souligne Oraman la configuration de la base joue forcément (taille du cache et taille des redo logs entre autres). C'est une comparaison difficile à analyser et qui n'est sans doute pas essentielle pour votre problème. L'essentiel est que dans les 2 cas faire 100000 insert avec binds est très rapide par rapport à 100000 insert sans binds (chez moi: environ 40 fois, chez vous environ 20 fois ).

Essayez de créer une procédure stockée qui prend en paramètre toutes les colonnes de la table et se contente de faire un INSERT. Modifiez votre fichier sql pour remplacer chaque INSERT par l'appel à cette procédure. Rajoutez un COMMIT tout à la fin et ça devrait être beaucoup plus rapide.

**JUSTIN Loïc** · 14/09/2006, 13h05

bonjour,
voila, j'essai de creer ma procedure mais j'ai un avertissement
Procedure créée avec erreurs de compilation
et quand je visualise les erreurs

11:51:29 SQL>sho errors
Erreurs pour PROCEDURE INS_DETAIL_SERVEUR_MOIS :
4/16 PL/SQL: SQL Statement ignored
6/66 pl/SQL: ORA-00984: Un nom de colonne n'est pas autorisé ici

Voici le detail de la procedure

create or replace procedure ins_detail_serveur_mois
is
begin
INSERT INTO detail serveur_mois VALUES (id_serv, dates, obj_sauve_t, vol_sauve_t, nb_sauve_t, err_sauve_t, obj_sauve_b, vol_sauve_b, nb_sauve_b, err_sauve_b, obj_sauve_i, vol_sauve_i, nb_sauve_i, err_sauve_i, obj_other, vol_other, nb_other, err_other, obj_rest, vol_rest, nb_rest,err_rest);
end;
/

Voila ou j'en suis ....

**pifor** · 14/09/2006, 13h11

Il faut déclarer en paramètre de la procédure toutes les variables mentionnées dans le INSERT:

Code :

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

1
2
3
4
5
6
7
8
9
create or replace procedure ins_detail_serveur_mois (
id_serv detail_serveur_mois%TYPE,
dates detail_serveur_mois%TYPE,
etc.
)
is
begin
INSERT INTO detail_serveur_mois VALUES (id_serv, dates, obj_sauve_t, vol_sauve_t, nb_sauve_t, err_sauve_t, obj_sauve_b, vol_sauve_b, nb_sauve_b, err_sauve_b, obj_sauve_i, vol_sauve_i, nb_sauve_i, err_sauve_i, obj_other, vol_other, nb_other, err_other, obj_rest, vol_rest, nb_rest,err_rest);
end;

**Oraman** · 14/09/2006, 13h32

je dirai qu'il faut regarder cette difference spectaculaire entre :
Ecoulé : 00 :10 :15.07 et Ecoulé : 00 :00 :30.55
donc approximativement tu vas pas avoir pas plusque 2min dans ton cas origine du problème des 27mn mais tous depend aussi des indexes sur ta table ... de toute façon j'attend toujours le résultat sur ton cas réel

**pifor** · 14/09/2006, 13h50

En fait, si on veut optimiser au maximum, il faudrait mettre tous les appels à la procédure stockée d'insertion dans une autre procédure stockée sinon on aura 100000 échanges client/server entre SQLPLUS et le processus Oracle serveur dédié pour exécuter les 100000 appels à la procédure ... Ou il vaudrait mieux essayer d'utiliser SQL*LOADER ?

**JUSTIN Loïc** · 14/09/2006, 15h26

Je me retourne vers vous car je ne comprends pas

comment declarer mes variables dans ma procedures. J'ai beau chercher dans la documentation, je n'ai pas de declic.
je souhaite donc déclarer les variables dans ma procedure ins_detail_serveur_mois.
(je dois avoir le cerveau lent en ce moment

)

lenteur dans l'insertion de donnees en 10Gr2

SQL Oracle

Discussions similaires

Partager

Partager