Commit après 1000 enregistrements

Version imprimable

Voir 40 message(s) de cette discussion en une page

17/02/2012, 10h00
cornnery

Commit après 1000 enregistrements
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:

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 .

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:

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:

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

17/02/2012, 13h52
cornnery

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:

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 :

Citation:

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 .

Citation:

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:

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.

17/02/2012, 14h34
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
17/02/2012, 14h59
Mohamed.Houri

Citation:

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
17/02/2012, 15h07
cornnery

Ok ,

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

Merci encore

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:

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

Citation:

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 .

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:

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:

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

20/02/2012, 13h01
cornnery

Hi Mohamed ,

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

Code:

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:

execute immediate(DynamicSql);

?

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

Code:

v_fact_moc@DWS.cg.NET

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

Merci
20/02/2012, 13h39
Mohamed.Houri

Citation:

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

Code:

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.

Citation:

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?
20/02/2012, 13h48
cornnery

Ok ,

Je travaille sur

Citation:

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
20/02/2012, 14h21
Mohamed.Houri

Citation:

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
20/02/2012, 14h44
cornnery

1 pièce(s) jointe(s)

Hi Mohamed ,

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

Merci
20/02/2012, 15h33
mnitu

1 pièce(s) jointe(s)

Voilà le profile de votre traitement qui vous indique où le temps passe.
20/02/2012, 15h39
mnitu

10.3.18.2 SQL*Net message from dblink
20/02/2012, 15h39
Mohamed.Houri

1 pièce(s) jointe(s)
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:

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
20/02/2012, 16h11
cornnery

@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

Citation:

rownum <=100

.

Et je vous fait le feed-back .

Merci
20/02/2012, 17h06
pacmann

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 ?
20/02/2012, 17h22
cornnery

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 .
20/02/2012, 18h34
pacmann

Mais tu as besoin de données de prod pour alimenter une base de test ?
(C'est étonnant d'avoir un dblink vers la prod déjà...)

Peux-tu nous décrire exactement ton but dans cette opération ?
21/02/2012, 08h45
mnitu

Jusqu’au là la trace montre que la base A reçoit un lot des données met à jour son dictionnaire et se met en attente de la suite. La trace de la base A ne montre pas ce qui se passe sur la base B. Il est donc nécessaire de tracer la base B.
Une idée est de créer la table dans la base B et de faire le même insert dans cette base pour comprendre l’impact du trafique des données via le réseau.
21/02/2012, 11h24
cornnery

Bonjour à tous ,

@pacman , je récupère des données de la Prod que j'agrège sur un autre environnement qui servira pour les statistiques .

@mnitu, je pense que ce ne soit pas nécessaire de faire la trace sur l'environnement B pour deux raisons
1. C'est mon environnement de PROD
2. Lorsque je lance directement la vue sur la PROD celle ci ne fait que dix minutes environs pour m'afficher les résultats

@Mohamed , Ta proposition était juste, en effet en mettant un rownum<=1000 sur la vue depuis ma cible , la procédure me fait environ 8 minutes pour extraire puis insérer les données .

Si le problème est celui du volume de données, pensez-vous que ma solution insert/select via dblink est la solution adaptée, si oui à quel niveau puis-je "tuner" celle-ci afin d'avoir mes données dans les temps ( 30min, 50 min voir 1 heure ) parce dans l'état actuel ma procédure tourne sans jamais s'arrêter .

Merci

Citation:

Envoyé par cornnery

...
2. Lorsque je lance directement la vue sur la PROD celle ci ne fait que dix minutes environs pour m'afficher les résultats
...

C’est 10 minutes pour afficher les premiers N résultats ou 10 minutes pour afficher votre million des lignes ?

Pour vous faire une idée lancez sqlplus sur la base de prod et exécutez
Code:

1 2 3 set autotrace traceonly Select ...
comme dans l'exemple:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 SQL> select count(*) from big 2 / COUNT(*) ---------- 1586368 SQL> set timi on SQL> set autotrace traceonly; SQL> select * from big 2 / 1586368 ligne(s) sÚlectionnÚe(s). EcoulÚ : 00 :00 :59.77

21/02/2012, 11h46
mnitu

Citation:

Envoyé par cornnery

...Si le problème est celui du volume de données, pensez-vous que ma solution insert/select via dblink est la solution adaptée, si oui à quel niveau puis-je "tuner" celle-ci afin d'avoir mes données dans les temps ( 30min, 50 min voir 1 heure ) parce dans l'état actuel ma procédure tourne sans jamais s'arrêter .

Est-ce que le problème est au niveau du réseau ou il est au niveau de la vue de la base de production?

Si, comme vous l'a signalé, marius vous êtes satisfait du select fait dans la base distante, je pense qu’il faudrait peut-être essayer de faire votre insert avec du PL/SQL. Quelque chose comme suit (que je donne en vrac sans l'avoir 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 create type distant_typ as object (champs_1 number,champs_2 number,champs_3 number); 2 / create type distant_typ_tab as table of distant_typ; 2 / DECLARE Cursor get_data_from_db is Select champs_1, champs_2, champs_3, from la_table_cible@mon_dblink where …... BEGIN Open get_data_from_db; loop Fetch get_data_from_db bulk collect into distant_typ_tab LIMIT 100; insert into INSERT /*+ append */ INTO ma_table ( champs_1, champs_2, champs_3 ) select champs_1, champs_2, champs_3 from table (distant_typ_tab); exit when get_data_from_db%notfound; end loop; commit;
En jouant sur la clause LIMIT qui devrait tourner entre 100 et 1000 afin de ne pas submerger la PGA.

C'est une proposition que je n'ai pas essayée. Lorsque je fut confronté au même problème que vous il y a trois ans, j'ai suivi les mêmes étapes que ce que nous (marius et moi) avons suggéré à savoir (a) tracer le select/insert (b) profiler le fichier trace obtenu (c) analyser et apporter la solution adéquate. Sauf que dans mon cas je fus plus chanceux car 78% de mon temps d'insert/select étaient consommés par un "dbfile sequential read" que j'ai résolu en travaillant sur l'index et la table concernés par ce "dbfile sequential read".

Pour votre cas, essayez ce que je vous propose plus haut et dites nous si cela s'arrange ou pas

21/02/2012, 13h49
mnitu

Mohamed, cette solution dans le meilleur des cases peut faire aussi bien que insert into ... select ... from ... mais pas plus.
21/02/2012, 14h04
mnitu

Après un test tracé d'un insert dans une autre base via le dblink de ma table big je pense que vous devez concentrer tous vos efforts sur l'optimisation de la vue de la base de production.

1 pièce(s) jointe(s)

HI Marius ,

en PROD voici les résultats que j'ai :

Code:

1
2
3
4
5
6
7
8
9
 
SQL> set timing on
SQL> select count(*) from v_fact_moc_mtn ;
 
  COUNT(*)
----------
   1896535
 
Elapsed: 00:02:05.25

Une partie du outuput du trace

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
63
64
65
66
67
68
69
70
71
72
73
74
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  12 - filter(TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'000','99999999999')<=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'231','99999999999'))
 
  13 - access("UE"."GSM_CALL_REFERENCE_NUMBER"="MS"."GSM_CALL_REFERENCE_NUMBER"(+) AND "UE"."TRAFFIC_TYPE_ID"="MS"."TRAFFIC_TYPE_ID"(+))
 
  16 - access("UE"."USAGE_EVENT_ID"="UJE"."USAGE_EVENT_ID"(+))
  20 - filter("UE"."TIME_HOUR_ID">=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'000','99999999999') 
 AND      "UE"."TIME_HOUR_ID"<=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'231','99999999999') 
 AND ("UE"."CALLED_NUMBER" IS NULL OR NVL("UE"."CALLED_NUMBER",' ') LIKE '24206%' 
 OR  NVL("UE"."CALLED_NUMBER",' ') LIKE '06%' 
 OR NVL("UE"."CALLED_NUMBER",' ') LIKE '0024206%') 
 AND NVL("UE"."CALLED_NUMBER",' ')<>'242066099501' 
 AND NVL("UE"."CALLED_NUMBER",' ')<>'242066099500' 
 AND NVL("UE"."CALLED_NUMBER",' ')<>'242066099503' 
 AND NVL("UE"."CALLED_NUMBER",' ')<>'242066099502'
AND NVL("UE"."CALLED_NUMBER",' ')<>'00242066099501' 
AND NVL("UE"."CALLED_NUMBER",' ')<>'00242066099500' AND NVL("UE"."CALLED_NUMBER",' ')<>'00242066099503' AND
 NVL("UE"."CALLED_NUMBER",' ')<>'00242066099502')
 
24 - filter("UJE"."TIME_HOUR_ID"(+)>=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'000','99999999999') 
AND          "UJE"."TIME_HOUR_ID"(+)<=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'231','99999999999'))
 
28 - filter("MS"."TIME_HOUR_ID"(+)>=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'000','99999999999') 
AND "MS"."TIME_HOUR_ID"(+)<=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'231','99999999999'))
 
32 - filter(TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'000','99999999999')<=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'231','99999999999'))
 
33 - access("UE"."GSM_CALL_REFERENCE_NUMBER"="MS"."GSM_CALL_REFERENCE_NUMBER"(+) *
AND "UE"."TRAFFIC_TYPE_ID"="MS"."TRAFFIC_TYPE_ID"(+))
 
38 - filter("UE"."CALLED_NUMBER" IS NOT NULL 
  AND (NVL("UE"."CALLED_NUMBER",' ')='1053' 
  OR NVL("UE"."CALLED_NUMBER",' ')='1051' 
  OR NVL("UE"."CALLED_NUMBER",' ')='242066099501' 
  OR NVL("UE"."CALLED_NUMBER",' ')='242066099500'
OR NVL("UE"."CALLED_NUMBER",' ')='242066099503' OR NVL("UE"."CALLED_NUMBER",' ')
='242066099502' OR NVL("UE"."CALLED_NUMBER",' ')='242130' 
OR NVL("UE"."CALLED_NUMBER",' ')='130' 
OR NVL("UE"."CALLED_NUMBER",' ')='123' 
OR NVL("UE"."CALLED_NUMBER",' ')='00242130' 
OR     NVL("UE"."CALLED_NUMBER",' ')='00242066099503' 
OR NVL("UE"."CALLED_NUMBER",' ')='00242066099502' 
OR NVL("UE"."CALLED_NUMBER",' ')='00242066099501'
OR        NVL("UE"."CALLED_NUMBER",' ')='00242066099500') 
AND "UE"."TIME_HOUR_ID">=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'000','99999999999') 
AND"UE"."TIME_HOUR_ID"<=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'231','99999999999'))
 
40 - filter("UJE"."TIME_HOUR_ID"(+)>=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')
||'000','99999999999') AND "UJE"."TIME_HOUR_ID"(+)<=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')
||'231','99999999999'))
 
41 - access("UE"."USAGE_EVENT_ID"="UJE"."USAGE_EVENT_ID"(+))
45 - filter("MS"."TIME_HOUR_ID"(+)>=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'000','99999999999') AND
 "MS"."TIME_HOUR_ID"(+)<=TO_NUMBER(TO_CHAR(SYSDATE@!-2,'yyyyMMdd')||'231','99999999999'))
 
 
 
Statistics
----------------------------------------------------------
        212  recursive calls
          3  db block gets
     678993  consistent gets
     483744  physical reads
        592  redo size
  124692665  bytes sent via SQL*Net to client
    1391305  bytes received via SQL*Net from client
     126437  SQL*Net roundtrips to/from client
         17  sorts (memory)
          0  sorts (disk)
    1896535  rows processed

l'autre partie en pièce -jointe , j'ai pensé que ça serait illisible de vous le présenter en HTML .

Merci

21/02/2012, 16h05
mnitu

Select count(*) from table n'est pas parreille que select col1, col2 ... from table et d'un coup je ne sais plus quoi faire des vos dernières informations.
Pour analyser la requête nous avons besoin de la requête et de son plan. Cella évite des malentendus.
21/02/2012, 16h26
cornnery

Marius ,

le

Code:

Select count(*)

c'était pour vous montrer le temps que ça prends sur la PROD et le nombre de lignes qu'il y a sur la PROD .

par contre le plan que je vous ai envoyé est celui du

Code:

select * from v_fact_moc_mtn

.

Si vous avez pris le temps de voir les résultats , vous remarquerez que la requête correspond bien à

Code:

select * from v_fact_moc_mtn

.

Merci
21/02/2012, 16h59
mnitu

C’est un explain plan de la requête n’est pas vrai (et non pas un dbms_xplan.display cursor) ?
A priori si je ne m’abuse pas il "estime" que ça va prendre plus de 9 heures !

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Statistics ---------------------------------------------------------- 212 recursive calls 3 db block gets 678993 consistent gets 483744 physical reads 592 redo size 124692665 bytes sent via SQL*Net TO client 1391305 bytes received via SQL*Net FROM client 126437 SQL*Net roundtrips TO/FROM client 17 sorts (memory) 0 sorts (disk) 1896535 rows processed
Quelques remarques:

Vous sélectionnez 1.896.535 lignes en faisant 126.437 aller-retour entre le client (sqlplus) et le serveur (base de données). Ce qui veut dire que vous utilisez le ‘’arraysize’’ par défaut qui est égale à 15 (1.896.535/126.437).

Pour sélectionnez 1.896.535 lignes il vous faut 678.993 lectures logiques ce qui veut dire 0.358 lecture logique par ligne sélectionnée (ce qui semble être bon). Par contre il y a presque autant de lectures physiques (483.744) que de lectures logiques (ce qui est énorme) et qui nécessite une amélioration de la requête.

Enfin, vous envoyez du serveur vers le client 124Méga de données. C’est tout simplement énorme. Pensez un peu au transfert de 128M via un dblink ? En combien de ‘’SQL*Net roundtrips’’ vous allez faire tout cela ? Même en augmentant le ‘’arraysize’’, je pense que cela ne sera pas suffisant.

J’aurai été à votre place je tenterai ce qui suit :

1. Je laisserai tomber le select * en le remplaçant par un select champs1, champ2, etc… ceci réduira la taille du volume de données échangé entre les deux bases

2. J’améliorerai la performance de la requête, peut-être que je commencerai par faire le select sans le mode parallèle, et j’utiliserai le dbms_xplan.display_cursor avec le hint /*+ gather_plan_statistics */ pour avoir un explain plan utilisable

3. Si je n’arrive pas à améliorer la performance de ma requête, je créerai une table dans la base distance dans laquelle j’insérerai le contenu de ma requête. J’utiliserai cette table pour faire mon select/insert via dblink. Bien sûr en essayant de transférer moins de volume par insert/select

4. Si l’étape 3 ne donne pas satisfaction, je génèrerai un fichier plat contenant le résultat de ma requête (par partition) que je déploierai dans ma base local et que j’utiliserai afin de remplir ma table local via SQL*Loader (ou en utilisant des tables externes)

22/02/2012, 09h45
mnitu

Citation:

Envoyé par Mohamed.Houri

...
3. Si je n’arrive pas à améliorer la performance de ma requête, je créerai une table dans la base distance dans laquelle j’insérerai le contenu de ma requête. J’utiliserai cette table pour faire mon select/insert via dblink. Bien sûr en essayant de transférer moins de volume par insert/select

4. Si l’étape 3 ne donne pas satisfaction, je génèrerai un fichier plat contenant le résultat de ma requête (par partition) que je déploierai dans ma base local et que j’utiliserai afin de remplir ma table local via SQL*Loader (ou en utilisant des tables externes)

Très bonne analyse mais j’ai un mal fou pour comprendre comment les solutions 3 et 4 peuvent améliorer les choses : la même requête est exécutée sur la même machine. Le même volume des données voyage par le réseau. Le même volume des données est chargé dans l’autre table. Comment est-il possible de gagner quelque chose en multipliant les manipulations. Vous vous rappelez ce que Carry Milsap dit : la meilleure façon d’optimiser une opération est de ne pas la faire.
22/02/2012, 10h32
Mohamed.Houri

Citation:

Vous vous rappelez ce que Carry Milsap dit : la meilleure façon d’optimiser une opération est de ne pas la faire.

:D

Oui je me rappelle très bien de cela. Je n'aurai jamais opté pour une solution autre qu'un insert/select bien que je suis fortement inspiré par Tom kyte quand il dit " never say always, never say never, I always say ".

Je pense que vous avez raison pour la solution 3 et 4 (que j'aurai du inverser dans l'ordre de préférence) qui ne peuvent être envisagées qu'en dernier ressort surtout si ce insert/select n'est fait qu'une seule fois et pas d'une manière répétée dans la vie de la base de données
22/02/2012, 10h41
pacmann

Sur ce coup, je suis pas d'accord pour que tu changes d'avis juste parce que Mnitu n'est pas d'accord :)

3 et 4 se tentent.

Parce que transférer 130 meg par ftp, c'est instantanné. C'est pas la même chose que 130 meg par sql*net.

D'autre part, est-on sûr que le plan d'exécution distant n'influe pas sur le nombre de roundtrips ?
22/02/2012, 14h29
mnitu

Citation:

Envoyé par pacmann

...
Parce que transférer 130 meg par ftp, c'est instantanné. C'est pas la même chose que 130 meg par sql*net.
...

Et si vous faisiez un test ?

Citation:

Envoyé par pacmann

...
...
D'autre part, est-on sûr que le plan d'exécution distant n'influe pas sur le nombre de roundtrips ?

Le nombre de roundtrips doit venir de la quantité des données à transfèrer divisé par la quantité de données transfèrés à un moment donné. Regardez le calcul du Mohamed 1.896.535 enregistrements / 15 enregistrements par lot = 126.437
22/02/2012, 14h41
Waldar

L'avantage de la quatrième solution de Mohamed.Houri est de séparer un problème complexe en plusieurs problèmes simples.

À peu de choses près, l'impact du coût de la vue sera parfaitement isolé pendant la création du fichier, l'impact du réseau sera parfaitement isolé pendant le transfert de ce fichier.
22/02/2012, 14h53
pacmann

Citation:

Envoyé par mnitu

Et si vous faisiez un test ?

Ca serait le mieux. Mais la problématique des roundtrip / arraysize n'est-elle pas déjà un bon indice ?
Même si tu augmentes l'arraysize, la limite réelle que tu atteins correspond à des problématiques concrète lié au SGBD, et non à du transfert réseau pur.

Citation:

Envoyé par mnitu

Le nombre de roundtrips doit venir de la quantité des données à transfèrer divisé par la quantité de données transfèrés à un moment donné. Regardez le calcul du Mohamed 1.896.535 enregistrements / 15 enregistrements par lot = 126.437

Au temps pour moi (j'avais une autre idée derrière la tête : pas tant les roundtrips entre client et server, mais entre server et server (distant))
22/02/2012, 16h58
mnitu
Citation:

Envoyé par Waldar

L'avantage de la quatrième solution de Mohamed.Houri est de séparer un problème complexe en plusieurs problèmes simples.

À peu de choses près, l'impact du coût de la vue sera parfaitement isolé pendant la création du fichier, l'impact du réseau sera parfaitement isolé pendant le transfert de ce fichier.

J’ai du mal à vous suivre :
Le "problème complexe" :

Code:

insert into table select … from table@viadblink

Le "problème simple" :
- Créez un fichier à partir de la requête Select … from table
- Transferer ce fichier via ftp sur l'autre macine
- Charger la table via sqlloader
:roll:

Voir 40 message(s) de cette discussion en une page