Commit après 1000 enregistrements

**cornnery** · 17/02/2012, 10h00

Bonjour ,

je fais des insertions des données qui proviennent d'une autre table via dblink.
mails la table cible contient plusieurs milliers de lignes , et j'ai des problèmes de performances , je souhaiterais faire des commit après un certain nombre d’enregistrements insérés ,ci-dessous ma requête .

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 insert into ma_table
    (
	champs_1,
	champs_2,
	champs_3,
	champs_4,
	champs_5,
	champs_6,
	champs_7,
	champs_7
    )
    select * from la_table_cible@mon_dblink ;
 commit ;

Toute aide sera la bienvenue , merci .

**Mohamed.Houri** · 17/02/2012, 11h40

Bonjour,

Avant de pouvoir vous donner un conseil, j’aurai aimé connaitre comment êtes vous arrivé à savoir que "commiter" tous 1000 lignes améliorera la performance de votre select?

Cary Millsap: a dit une chose très sensée: ''Why Guess when you can know?'' Pourquoi devinez lorsque vous pouvez savoir ?

Aussi, dans ce genre de situations, il vaut mieux diagnostiquer correctement le problème de performance avant de lui apporter par la suite le remède qui lui sied parfaitement.

Ceci dit j’ai quelques conseils à vous donner quand même:
Si votre table d’insertion n’a ni trigger ni contrainte d’intégrité (FK) pensez alors à utiliser un ''direct path load'' en utilisant le hint /*+ append */.

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
 
INSERT /*+ append */ INTO ma_table
    (
	champs_1,
	champs_2,
	champs_3,
	champs_4,
	champs_5,
	champs_6,
	champs_7,
	champs_7
    )
select champs_1,
	champs_2,
	champs_3,
	champs_4,
	champs_5,
	champs_6,
	champs_7,
	champs_7 
  from la_table_cible@mon_dblink ;

Cela ne génèrera pas d’UNDO sur la table et les indexes seront chargés avec un ''bulk collect''. Si certaines conditions supplémentaires sont vérifiées il n’y aura pas de Redo aussi :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
Table Mode    Insert Mode     ArchiveLog mode      result
-----------   -------------   -----------------    ----------
LOGGING       APPEND          ARCHIVE LOG          redo generated
NOLOGGING     APPEND          ARCHIVE LOG          no redo
LOGGING       no append       ""                   redo generated
NOLOGGING     no append       ""                   redo generated
LOGGING       APPEND          noarchive log mode   no redo
NOLOGGING     APPEND          noarchive log mode   no redo
LOGGING       no append       noarchive log mode   redo generated
NOLOGGING     no append       noarchive log mode   redo generated

http://asktom.oracle.com/pls/asktom/...:5280714813869

Attention ceci représente une solution valable lorsque vous ne faites pas de ''delete'' fréquents sur la table où lorsque vous insérez dans une table vide. En effet le ''direct path load'' insert directement au dessus du High Water Mark et n’utilise aucun espace libre existant dans les blocks de données.

Si vous partez d’une table vide, ou si le volume de données à insérer est largement supérieur au volume de données actuel de la table, il vaut mieux alors mettre tous les indexes dans un statut ‘DISABLED’ et faire un ‘rebuild’ après la fin de l’insert.

Si vous lisez le nouveau libre de Jonathan Lewis, vous réfléchiriez à plusieurs reprises avant d’utiliser un select *. Je vous conseille alors de remplacer le select * par les colonnes nécessaires à l’insert uniquement.

Est-ce que votre table est partitionnée ? d'autres suggestions peuvent être envisagées dans ce cas aussi

Enfin, comme je l’ai signalé au début, pensez à tracer votre insert/select via l’event 10046 afin de connaître réellement où se trouve l’origine du ralentissement de votre insert

**cornnery** · 17/02/2012, 13h52

Bonjour Mohamed ,

Bravo, c'est ce qu'on appelle par une réponse remplie de bon sens .

Effectivement je pensais que les insertions étaient stockées dans les UNDO, c'est pourquoi j'ai pensé qu'en effectuant des commit intermédiaires , j'aurais résolu mon problème mais apparemment non .. .

Ceci dit mon contexte est celui ci:
Ma table contient déjà des données (des millions de lignes) précédemment enregistrées, elle a aussi des contraintes de Primary key , de Unique Key mais pas de FK .

Si je fais un

Code :

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

SELECT * FROM la_table_cible@mon_dblink ;

pour récupérer mes données c'est parce que de l'autre côté ma table cible n'est qu'une vue qui contient les noms de colonnes des tables dont je désire récupérer les données .

Parraport à vos conseils , je pense que la solution :

Si vous partez d’une table vide, ou si le volume de données à insérer est largement supérieur au volume de données actuel de la table, il vaut mieux alors mettre tous les indexes dans un statut ‘DISABLED’ et faire un ‘rebuild’ après la fin de l’insert.

serait adaptée à mon contexte .

La table cibles sont partitionnée , ma table de destination (celle qui doit contenir les données extraites ) est aussi partitionnée .

Merci toujours pour vos conseils si éclairés .

**Mohamed.Houri** · 17/02/2012, 14h24

Envoyé par cornnery

La table cibles sont partitionnée , ma table de destination (celle qui doit contenir les données extraites ) est aussi partitionnée .

Si les deux tables sont partitionnées des deux côtés, vous pouvez faire un insert/select par partition. Et dans ce cas il faudra uniquement "disabler" les indexes locaux correpondants à la partition p1 comme suit par exemple:

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
 
mhouri > alter index part_ind modify partition p1 unusable;
 
Index altered.
 
mhorui> select index_name, status
  2  from user_ind_partitions
  3  where status = 'UNUSABLE';
 
INDEX_NAME                     STATUS                                                                                   
------------------------------ --------                                                                                 
PART_IND                       UNUSABLE                                                                                 
 
INSERT /*+ append */ INTO ma_table partition (p1)
    (
	champs_1,
	champs_2,
	champs_3,
	champs_4,
	champs_5,
	champs_6,
	champs_7,
	champs_7
    )
SELECT champs_1,
	champs_2,
	champs_3,
	champs_4,
	champs_5,
	champs_6,
	champs_7,
	champs_7 
 FROM la_table_cible@mon_dblink 
 WHERE cle_partition = :cle_qui_correspond a la partition p1
;
 
mhouri> alter index part_ind rebuild partition p1;
 
Index altered.
 
mhouri> select index_name, status
  2  from user_ind_partitions
  3  where status = 'UNUSABLE';
 
no rows selected

Et ainsi de suite pour les autres partitions.

**cornnery** · 17/02/2012, 14h34

Hi Mohamed ,

intéressant , sauf que dans mon cas la table n'a pas été construite avec des index locaux , mais avec un index global, dans ce cas dois je le "disabler" de façon globale et le reconstruire ? ça ne serait pas un traitement lourd pour une table contenant des millions de ligne ???

Merci

**Mohamed.Houri** · 17/02/2012, 14h59

Envoyé par cornnery

Hi Mohamed ,

intéressant , sauf que dans mon cas la table n'a pas été construite avec des index locaux , mais avec un index global, dans ce cas dois je le "disabler" de façon globale et le reconstruire ? ça ne serait pas un traitement lourd pour une table contenant des millions de ligne ???

Merci

Ce que je vous ai suggéré a un sens pour les indexes locallement partitionnés car on ne ''disable'' que la partition vide (ou à insérer) et on refait le 'rebuild' après une fois cette partition chargée.

Pour les indexes globaux, cela n'a pas de sens surtout lorsque le volume de données apporté par l'insert est largement inférieur au volume des données avant l'insert. En d'autres mots, si vous insérez 1 million de lignes dans une table qui en contient déjà 10 millions, vous devriez dans ce cas éviter de faire le ''disable'' des indexes avant insert; autrement vous auriez à faire un rebuild d'un index de 11 millions de lignes; ce qui n'est pas négligeable

**cornnery** · 17/02/2012, 15h07

Ok ,

je teste tout ça et je vous fait un feed-back .

Merci encore

**cornnery** · 20/02/2012, 10h48

Hi Mohamed ,

Je reviens encore avec mon problème de performance ,

après avoir suivi vos conseils j'ai modifié mon script comme ci-dessous:

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
CREATE OR REPLACE PROCEDURE facts.proc_fact_moc IS
   DynamicSql varchar2(10000);
   dateAndTime varchar(10000);
   msg        varchar(10000);
   p1   varchar(100);
   ecode NUMBER;
   emesg VARCHAR2(10000);
BEGIN
  select 'D'||to_char(sysdate -2,'yyyymmdd')into p1 from dual;
  BEGIN
    ----first insertion 
	msg := 'Start first insertion in FACT table';
    insert into facts_data_log values('v_fact_moc',sysdate,msg,0);
	commit;
 
    DynamicSql:=' insert /*+ append */ into fact_moc partition ('||p1||') 
    (
	msisdn,
	trafic_date,
	destination,
	traffic_type,
	vas_service,
	account_id,
	total_calls,
	total_revenu_appel,
	total_duree_appel,
	total_volume,
	apn_info,
	extension_code,
	service_class_id,
	location_number,
	service_offerings,
	cell_identity,
	country_code
    )
    select /*+ parallel(3) */ 
	msisdn,
    trafic_date,
    destination,
    traffic_type,
    vas_service,
    account_id,
    total_calls,
    total_revenu_appel,
    total_duree_appel,
    total_volume,
    apn_info,
    extension_code,
    service_class_id,
    location_number,
    service_offerings,
    cell_identity,
    country_code
	from v_fact_moc@DWS.cg.NET';
  execute immediate(DynamicSql);
  commit;
  msg := 'End of first insertion in FACT table';
  insert into facts_data_log values('v_fact_moc', sysdate, msg, 0);
  commit;
   EXCEPTION WHEN OTHERS THEN 
     ecode := SQLCODE;
     emesg := SQLERRM;
	 ROLLBACK;
	 msg :='An error has occured during the insertion in FACT_MOC from v_fact_moc_mtn ';
	 msg := msg ||': '||TO_CHAR(ecode) || '-' || emesg ;
     insert into facts_data_log values('v_fact_moc',sysdate,msg,0);
     commit;              
 
  END ;
 END proc_fact_moc ;

Hélas le script cours des heures et des heures sans s'arrêter , mais quand je lance la vue

v_fact_moc

directement sur ma cible , celle-ci ne prends qu'une dizaine de minutes .
Auriez-vous d'autres conseils intéressants à me donner pour optimiser cette requête , merci .

**Mohamed.Houri** · 20/02/2012, 11h23

Bonjour

Pourquoi utilisez-vous du SQL dynamique? Le SQL statique n'est pas possible?

Puisque vous êtes encore en environement de TEST, faites ce qui suit

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
 
alter session set events '10046 trace name context forever, level 12';
 
 insert /*+ append */ into fact_moc partition (p1) 
    ( msisdn,
      trafic_date,
      destination,
      traffic_type,
      vas_service,
      account_id,
      total_calls,
      total_revenu_appel,
      total_duree_appel,
      total_volume,
      apn_info,
      extension_code,
      service_class_id,
      location_number,
      service_offerings,
      cell_identity,
      country_code
    )
    select 
         msisdn,
        trafic_date,
        destination,
        traffic_type,
        vas_service,
        account_id,
        total_calls,
        total_revenu_appel,
        total_duree_appel,
        total_volume,
        apn_info,
        extension_code,
        service_class_id,
        location_number,
        service_offerings,
        cell_identity,
        country_code
from v_fact_moc@DWS.cg.NET
where -- clause qui correspond à la partition p1
 
alter session set events '10046 trace name context off';

Attention à la where clause

Code :

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

1
2
3
 
from v_fact_moc@DWS.cg.NET
where -- clause qui correspond à la partition p1

Il faudrait sélectionner uniquement les lignes qui peuvent être contenues dans la partition p1.

Avec le fichier trace généré vous allez savoir où est épuisé le temps d'execution de l'insert/select

**cornnery** · 20/02/2012, 13h01

Hi Mohamed ,

puis je savoir quelle est la conséquence en ajoutant la ligne ci-dessous

Code :

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

ALTER session SET events '10046 trace name context forever, level 12';

?

Pensez-vous qu'il serait plus performant d'utiliser le sql normal (statique) au sql dynamique

Code :

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

execute immediate(DynamicSql);

?

pour ce qui est de la restriction de la partition p1 , la vue

Code :

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

v_fact_moc@DWS.cg.NET

fait déjà une restriction sur la partition où est hébergée la table cible .

Merci

**Mohamed.Houri** · 20/02/2012, 13h39

puis je savoir quelle est la conséquence en ajoutant la ligne ci-dessous

Code :

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

ALTER session SET events '10046 trace name context forever, level 12';

La conséquence c'est la génération d'un "trace file" dans le répertoire user_dump_test où est hébergée votre base de données. Ceci ralentira un peu votre insert/select mais permettra de savoir quelle partie du select/insert prend le plus de temps.

Pensez-vous qu'il serait plus performant d'utiliser le sql normal (statique) au sql dynamique [code]execute immediate(DynamicSql);

Il existe de bonnes habitudes en Oracle qu'il convient d'adopter aussi longtemps que cela est possible. Parmi ces bonnes habitudes c'est de n'utiliser le SQL dynamique que si le SQL statique n'est pas possible. L'explication se trouve dans ce site et dans d'autres également. Il suffit de chercher.

Dans quel environnement faites vous votre insert/select?

**cornnery** · 20/02/2012, 13h48

Ok ,

Je travaille sur

RHEL4 64 bits , oracle 10.2.0.1.0

.

Pour ce qui du fichier trace généré dans user_dump_test je l'ai localisé et sachant que ma requête prends des heures et heures pour s'exécuter ,
pensez-vous que le résultat provisoire qu'il a généré peut nous en dire long déjà sur les problèmes de performance que je rencontre ?

Merci

**Mohamed.Houri** · 20/02/2012, 14h21

Envoyé par cornnery

Ok ,

Je travaille sur .

Pour ce qui du fichier trace généré dans user_dump_test je l'ai localisé et sachant que ma requête prends des heures et heures pour s'exécuter ,
pensez-vous que le résultat provisoire qu'il a généré peut nous en dire long déjà sur les problèmes de performance que je rencontre ?

Merci

Oui tout à fait

**cornnery** · 20/02/2012, 14h44

Hi Mohamed ,

en pièce-jointe , le fichier trace "provisoire" généré .

Merci

**mnitu** · 20/02/2012, 15h33

Voilà le profile de votre traitement qui vous indique où le temps passe.

**mnitu** · 20/02/2012, 15h39

10.3.18.2 SQL*Net message from dblink

**Mohamed.Houri** · 20/02/2012, 15h39

98% de votre temps est passé dans "SQL*Net message from dblink [idle]" comme vous pouvez le constater via le fichier html attaché.

Vous devriez limiter le volume de données transférées entre les deux bases de données. Il est vrai que nous ne savons pas qu'est ce qui se cache derrière

Code :

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

1
2
 
FROM v_fact_moc@DWS.cg.NET

Combien de lignes envisagez-vous d’insérer avec ce select?

Il va falloir aller dans la base distante et faire un examen minutieux de ce select. Peut-être qu’il y a un moyen d’améliorer la performance du select qui se cache derrière ce qui semble être une vue.

Essayez de limiter le select à une dizaine de lignes (where rownum<10) et voir si l’insert/select s’exécute quand même très vite ou pas juste pour confirmer que le volume de données échangé entre les deux bases représente le problème à traiter

**cornnery** · 20/02/2012, 16h11

@mnitu

Merci pour le output en HTML .

@Mohamed ,

la vue renvoie en moyenne 1 million 800 mille lignes .

Je teste la requête avec un

rownum <=100

.

Et je vous fait le feed-back .

Merci

**pacmann** · 20/02/2012, 17h06

Salut !

Ca parait un peu barbare comme procédé...
C'est un fonctionnement de prod, où tu fais ça pour te créer de la donnée ?

Peut être qu'exporter d'un côté, charger de l'autre serait plus efficace ?

**cornnery** · 20/02/2012, 17h22

Bonjour pacmann ,

en effet au final c'est avec la prod que je dois faire cette manipulation,
et je n'ai pas le droit de créer une table (même temporaire) .
Si vous m'expliquez comment faire le dump d'une vue ... je suis preneur,
car comme vous le constaterez je fais le lien avec une vue et non avec une table directement .

Commit après 1000 enregistrements

PL/SQL Oracle

Discussions similaires

Partager

Partager