Commit après 1000 enregistrements

**pacmann** · 20/02/2012, 18h34

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 ?

**mnitu** · 21/02/2012, 08h45

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.

**cornnery** · 21/02/2012, 11h24

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

**mnitu** · 21/02/2012, 11h37

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 :

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

1
2
3
 
set autotrace traceonly
Select ...

comme dans l'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
 
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

**mnitu** · 21/02/2012, 11h46

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?

**Mohamed.Houri** · 21/02/2012, 12h20

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 :

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

**mnitu** · 21/02/2012, 13h49

Mohamed, cette solution dans le meilleur des cases peut faire aussi bien que insert into ... select ... from ... mais pas plus.

**mnitu** · 21/02/2012, 14h04

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.

**cornnery** · 21/02/2012, 14h09

HI Marius ,

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

Code :

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

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 :

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

**mnitu** · 21/02/2012, 16h05

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.

**cornnery** · 21/02/2012, 16h26

Marius ,

le

Code :

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

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 :

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

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 :

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

select * from v_fact_moc_mtn

.

Merci

**mnitu** · 21/02/2012, 16h59

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 !

**Mohamed.Houri** · 22/02/2012, 09h20

Code :

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

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)

**mnitu** · 22/02/2012, 09h45

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.

**Mohamed.Houri** · 22/02/2012, 10h32

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

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

**pacmann** · 22/02/2012, 10h41

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 ?

**mnitu** · 22/02/2012, 14h29

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 ?

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

**Waldar** · 22/02/2012, 14h41

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.

**pacmann** · 22/02/2012, 14h53

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.

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

**mnitu** · 22/02/2012, 16h58

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 :

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

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

Commit après 1000 enregistrements

PL/SQL Oracle

Discussions similaires

Partager

Partager