[oracle 8.0.5] insert très (trop) lent

Version imprimable

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

09/10/2006, 18h24
icoons

Citation:

Envoyé par remi4444

On se fait souvent avoir dans TOAD parceque lorsqu'il rend la main en affichant les premières ligne de la requête, il se peux que le temps affiché soit très court et pourtant la requête est loin d'être terminée. Par contre quand on insère les lignes dans une table temporaire, là on est sur que c'est le vrai temps total, et il peut être monstrueusement plus long...

J'entend par terminé quand le requêteur (toad, sqlplus, sqlplus worksheet...) m'indique combien de lignes ont été traitées par le select (n rows selected).

Aujourd'hui, par exemple, il y a peu de lignes (200-300) car tout est fermé le dimanche; Le test de select que j'ai effectué me donne 12 minutes.
Le process insert a, quant à lui, duré près de 1h30 ce matin (avec les mêmes données).

J'ai relancé un test d'insertion après avoir modifié la variable sort_array_size (128mo) et ça fait 52 minutes que la requête tourne,... sans succès.
09/10/2006, 18h53
remi4444
ok.....

Il arrive parfois que le plan d'exécution d'une requête soit différent du moment que tu insères ta requête dans une table, j'ai l'impression que c'est un bug de l'optimiseur mais je n'ai jamais réussi à en avoir le coeur net....

est-ce que tu as édité les plans dans les 2 cas et as-tu constaté une différence (hormis la 1iere ligne INSERT)?

Tu peux soit traiter par des optimisation classiques:
- Passage de stats
- création d'index appropriés si besoin
- utilisation du hint /*+ ALL_ROWS */
...

Jouer sur le paramètre de l'optimiseur suivant pour forcer le passage par les index

ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 5

Pour forcer oracle à d'abord résoudre sa requête avant l'insert, tu peux aussi utiliser le hint MERGE:

http://download-west.oracle.com/docs...hints.htm#5146

En gros ça donnerait:
Code:

1 2 3 4 5 6 insert /*+ MERGE(v) */ into TA_TABLE select * from ( SELECT g_t4.id_ref, g_t3.id_pvt, g_t3.id_emp, ... etc... etc... ) v
Mais attention c'est gourmant en espace temporaire ce genre de truc...
09/10/2006, 19h08
pifor

Il serait quand même intéressant d'avoir le vrai plan d'exécution, càd celui obtenu avec:
1.

Code:

ALTER SESSION SQL_TRACE=TRUE

2. récupération du fichier obtenu dans le répertoire désigné par user_dump_dest
3. exécution de tkprof sur le fichier obtenu.

On aurait tout le SQL executé, les statistiques des étapes parse, execute et fetch. Et avec l'option sys=yes, on peut même avoir le SQL récursif (au cas où il y aurait un trigger par exemple ?).
10/10/2006, 09h19
McM

Citation:

Envoyé par pifor

(au cas où il y aurait un trigger par exemple ?).

Purée, on a oublié la base !! :oops:

Citation:

Envoyé par pifor

au cas où il y aurait un trigger par exemple

Non, il n'y en a pas. Il n'y a aucun trigger sur cette base.
je n'ai pas lancé la trace avant que le traitement ne parte cette nuit (il tourne encore). je l'ai lancé -quelques minutes- pendant le traitement mais ça ne donne pas grand chose.
Je le relancerai avec la trace une fois que la prod sera terminée.

voilà quand même, en attendant, le résultat de la trace temporaire, mais ça ne sert pas à grand chose...

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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
TKPROF: Release 8.0.5.0.0 - Production on Ma Oct 10 10:29:12 2006
 
(c) Copyright 1998 Oracle Corporation.  All rights reserved.
 
Trace file: ora_28402_xdwa.trc
Sort options: execnt  fchcnt  
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
 
select file# 
from
 file$ where ts#=:1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       58      0.00       0.00          0          0          0           0
Execute     58      0.00       0.00          0          0          0           0
Fetch      116      0.00       0.00          0        174          0          58
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      232      0.00       0.00          0        174          0          58
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************
 
select  file#,block#,length 
from
 fet$       where length>=:1 and ts#=:2 and file#=:3
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       29      0.00       0.00          0          0          0           0
Execute     29      0.00       0.00          0          0          0           0
Fetch       29      0.00       0.00          0        232          0          29
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       87      0.00       0.00          0        232          0          29
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************
 
select file#,block#,length 
from
 uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and ext#=:4
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       29      0.00       0.00          0          0          0           0
Execute     29      0.00       0.00          0          0          0           0
Fetch       29      0.00       0.00          0        183          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       87      0.00       0.00          0        183          0           0
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************
 
select length 
from
 fet$ where file#=:1 and block#=:2 and ts#=:3
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       29      0.00       0.00          0          0          0           0
Execute     29      0.00       0.00          0          0          0           0
Fetch       29      0.00       0.00          0        232          0          29
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       87      0.00       0.00          0        232          0          29
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************
 
update ts$ set inc#=:2 
where
 ts#=:1
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       29      0.00       0.00          0          0          0           0
Execute     29      0.00       0.00          0        435         29          29
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       58      0.00       0.00          0        435         29          29
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************
 
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=
  :9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),
  groups=decode(:14, 65535, NULL, :14), cachehint=:15 
where
 ts#=:1 and file#=:2 and block#=:3
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       29      0.00       0.00          0          0          0           0
Execute     29      0.00       0.00          0        183         87          29
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       58      0.00       0.00          0        183         87          29
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************
 
insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,length)
values
 (:1, :2, :3, :4, :5, :6, :7)
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       29      0.00       0.00          0          0          0           0
Execute     29      0.00       0.00          0         60         62          29
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       58      0.00       0.00          0         60         62          29
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************
 
delete from fet$ 
where
 file#=:1 and block#=:2 and ts#=:3
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       29      0.00       0.00          0          0          0           0
Execute     29      0.00       0.00          0        435         29          29
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       58      0.00       0.00          0        435         29          29
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
 
 
 
********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 0
 
 
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      261      0.00       0.00          0          0          0           0
Execute    261      0.00       0.00          0       1113        207         116
Fetch      203      0.00       0.00          0        821          0         116
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      725      0.00       0.00          0       1934        207         232
 
Misses in library cache during parse: 0
 
    0  user  SQL statements in session.
  261  internal SQL statements in session.
  261  SQL statements in session.
    0  statements EXPLAINed in this session.
********************************************************************************
Trace file: ora_28402_xdwa.trc
Trace file compatibility: 7.03.02
Sort options: execnt  fchcnt  
       1  session in tracefile.
       0  user  SQL statements in trace file.
     261  internal SQL statements in trace file.
     261  SQL statements in trace file.
       8  unique SQL statements in trace file.
    2349  lines in trace file.

10/10/2006, 10h20
orafrance

une trace de niveau 8 sur la session permet de voir les attentes : http://orafrance.developpez.com/dbahelp/#L3
10/10/2006, 13h37
Hugues_78
tu peux essayer également les choses suivantes :

- tu commence par faire une copie vide de la table
Code:

1 2 create table toto pctfree 0 pctused 99 as select * from la_table_longue_a_inserer where rownum<0
==> la table n'est la que pour faire des insert, pas de mise à jour de prévue dessus d'ou les pctfree pctused

- faire l'insert dans la table toto
==> ça prend combien de temps sur une table sans index ?

Ensuite, tu va tenter en direct path write (au dessus de la HWM), par l'ajout du hint append et tu remesure le temps (fait un truncate table avant)
insert /*+ APPEND */ toto

tu va rendre la table compressée et tu refait l'etape precedante
alter table toto compress (puis truncate puis insert append)

puis tu la rend nologging
alter table toto nologging
et tu recommences

attention : le nologging n'a aucun effet sur les insert si tu n'utilises pas le hint APPEND... Ca ne marche qu'avec append ou sql*loader avec direct=true ou en create table as select

Merci de ton retour
10/10/2006, 14h03
icoons

avant de disposer de la trace, j'ai fait le test proposé par remi4444; J'ai créé une table temporaire, sans index dans laquelle j'ai inséré les données avant de les réintroduire dans la table cible (qui est indexée). C'est bien la première phase qui consomme le plus de temps(> 100 minutes), l'insertion des données dans la table cible est relativement assez rapide (< 2 minutes).
- Je n'ai pas réussi à obtenir la trace de niveau 8 (cf Fred_D)... je vais recommencer cela.

- j'essaie aussi le test de Hugues_78
10/10/2006, 14h39
remi4444

Le problème n'est donc pas physique mais bien (comme la plupart du temps...) un problème de plan d'exécution....

Je viens de re-regarder la partie select :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 FROM ods.tb_o_lig_tkv@xdwa g_t1, alm.tb_wdo_prx_ref_jou@xdwa g_t2, ods.tb_o_ent_tkv@xdwa g_t3, ods.tb_o_uvc@xdwa g_t4 WHERE g_t3.id_tps_jou = g_t2.id_jou AND g_t1.id_uvc = g_t4.id_uvc AND g_t3.id_ent_tkv = g_t1.id_ent_tkv AND g_t3.id_tps_jou >= TO_NUMBER (TO_CHAR (:1, 'yyyymmdd')) AND g_t1.cd_lig_tkv_typ <> 'MVTFI' AND g_t4.id_ref = g_t2.id_uvc GROUP BY TO_NUMBER (TO_CHAR (g_t3.dt_ent_tkv, 'yyyymmddHH24')), g_t4.id_ref, g_t3.id_pvt, g_t3.id_emp
@xdwa ==> c'est un db-link... Les données sont sur une autre instance Oracle !!! j'avais pas fait attention à ça.

Ce ne serait pas plutôt ton réseau qui sature ? Un moyen rapide sous toad, c'est d'utiliser le session browser plutot que le kill trace session. Tu sélectionne ta connection et tu regardes l'onglets waits...
Dans le v$session_wait on voit des attentes réseau (les sql*net n'ont pas d'importance en général, mais les sql*net ...more... si)
SQL*Net more data to client 139571 SQL*Net more data from client 43259 SQL*Net message from dblink 21
SQL*Net more data from dblink 38
SQL*Net break/reset to client 142

Quels sont les volumétries des 4 tables ? Et pour g_t3 (vu filtre sur date), la volumétrie que tu ramène ?

Si c'est un problème de driving site (l'endroit ou s'exécute la requête... au sens tri/jointure), essaye de créer toutes les données sur ta machine distante (tu fais un create table as select puis tu ramène tout le résultat en une seule fois avec insert).

Si g_t3 est la seule grosse table (uniquement dans ce cas la), tu peux essayer la chose suivante :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 with g_t3 as ( select /*+ MATERIALIZE */ ... from ods.tb_o_ent_tkv@xdwa g_t3 where g_t3.id_tps_jou >= TO_NUMBER (TO_CHAR (:1, 'yyyymmdd')) ) select ... FROM ods.tb_o_lig_tkv@xdwa g_t1, alm.tb_wdo_prx_ref_jou@xdwa g_t2, g_t3, ods.tb_o_uvc@xdwa g_t4 WHERE g_t3.id_tps_jou = g_t2.id_jou AND g_t1.id_uvc = g_t4.id_uvc AND g_t3.id_ent_tkv = g_t1.id_ent_tkv AND g_t3.id_tps_jou >= TO_NUMBER (TO_CHAR (:1, 'yyyymmdd')) AND g_t1.cd_lig_tkv_typ <> 'MVTFI' AND g_t4.id_ref = g_t2.id_uvc GROUP BY TO_NUMBER (TO_CHAR (g_t3.dt_ent_tkv, 'yyyymmddHH24')), g_t4.id_ref, g_t3.id_pvt, g_t3.id_emp
MATERIALIZE est un hint non documenté qui indique à Oracle qu'il doit créer une table temporaire avec le résultat du select.

10/10/2006, 15h12
orafrance

8O pas mal ce hint :bravo:
10/10/2006, 15h41
remi4444
Citation:

Envoyé par Hugues_78

@xdwa ==> c'est un db-link... Les données sont sur une autre instance Oracle !!! j'avais pas fait attention à ça.

BIEN VU!!! j'avais pas fait gaffe non plus :oops: et pourtant c'est exactement le coeur du problème, voici pourquoi.

- Lorsque toute les tables d'une requête sont sur le meme dblink, oracle exécute entièrement la requête sur le site distant puis rappatrie le résultat.

- Lorsque il y a un mélange de tables locales et tables distante, oracle choisi plus ou moins mal sur quel site il fait ses jointures, et c'est souvent catastrophique, par exemple il peut choisir de rapatrier entierement toute une table pour au final n'en filtrer que quelques lignes. Donc non seulement ça fait une requête qui rame mais ça écroule le réseau....

La faille chez oracle c'est que quand tu fait un :
Code:

1 2 3 4 insert into TABLE_LOCALE select .... from TABLE_DISTANTE_1@site_distant, TABLE_DISTANTE_2@site_distant where ....
Il considère qu'il y a un mélange local/distant donc il s'autorise à faire des jointures à travers le réseau... ça se voit quand tu édite le plan d'exécution, tu peux clairement constater la différence :roll:

La seule chose préconisée par oracle dans ces cas là est de faire une vue sur le site distant et de faire une insertion simple en local du "contenu" de ta vue...
10/10/2006, 15h47
icoons

il n'y a, en fait, pas de db_link. La requête est générée par un moteur ETL (hummingbird/genio) mais s'execute sur une seule et même base/instance (xdwa). toutes les tables, sources et cibles, sont situées sur la même machine.

pour info:
g_t1= 18714039 rows
g_t2= 2559 rows
g_t3= 15136201 rows
g_t4= 99534 rows
10/10/2006, 15h49
orafrance

mais il passe par le réseau quand même... spa très malin comme ETL :koi:

Citation:
Envoyé par icoons
il n'y a, en fait, pas de db_link. La requête est générée par un moteur ETL (hummingbird/genio) mais s'execute sur une seule et même base/instance (xdwa). toutes les tables, sources et cibles, sont situées sur la même machine.

.....
Code:

1 2 3 4 5 6 7 8 9 10 INSERT INTO alm.tb_wdo_vte .... SELECT ...... FROM ods.tb_o_lig_tkv@xdwa g_t1, alm.tb_wdo_prx_ref_jou@xdwa g_t2, ods.tb_o_ent_tkv@xdwa g_t3, ods.tb_o_uvc@xdwa g_t4 WHERE g_t3.id_tps_jou = g_t2.id_jou .....
.....
Toute les tables sources OK, mais la table cible, elle est bien en local non ? c'est bien ça qui perturbe l'optimiseur....

Je t'assure que tu devrais vraiment éditer les plans d'exécution avec et sans le insert, tu sera surpris du résultat...... :roll:

10/10/2006, 16h05
aline

Citation:

Envoyé par Hugues_78

MATERIALIZE est un hint non documenté qui indique à Oracle qu'il doit créer une table temporaire avec le résultat du select.

Hello tous,
Voici un des posts les plus intéressants que j'ai pu lire depuis longtemps.
Hugues, pourrais-tu nous en dire un peut plus sur ce hint (ou bien nous donner un lien?). Ou materialise t'il le résultat (en remote ou en local)? Et peut on parametrer cela?
10/10/2006, 16h18
orafrance

Citation:

Envoyé par aline

Voici un des posts les plus intéressants que j'ai pu lire depuis longtemps.

sympa pour nous :aie:

Si j'ai bien compris, l'idée de ce hint c'est surtout de réutiliser réellement la même vue constituée par la clause WITH.

En revanche, je suis sceptique quand à son action en 8.0 :koi:
10/10/2006, 16h20
icoons

Citation:

Envoyé par Fred_D

mais il passe par le réseau quand même... spa très malin comme ETL :koi:

Je ne le formulerais pas tout à fait comme ça; Genio est un outil assez pratique, mais ce n'est pas le sujet ici; Les débats autour de Genio pourraient faire l'objet d'un forum.

En fait le moteur ETL tourne sur une machine distante ce qui explique sans doute les @xdwa, mais la table cible et les tables sources sont toutes localisées sur la même machine/instance. La requête s'execute sur la seule instance xdwa...

la trace (level 8) est en cours...
10/10/2006, 16h25
aline

Citation:

Envoyé par Fred_D

sympa pour nous :aie:

aucun sous entendu douteux dans ma remarque :fem:
Ce que ji'ai apprécié, c'est que pour un problème complexe, beaucoup de gens amènent des pistes intéressantes.

Bref, on est une vrai équipe :mrgreen:
10/10/2006, 16h35
remi4444

Citation:

Envoyé par icoons

En fait le moteur ETL tourne sur une machine distante ce qui explique sans doute les @xdwa, mais la table cible et les tables sources sont toutes localisées sur la même machine/instance. La requête s'execute sur la seule instance xdwa...

ça ne correspond pas à la requête que tu nous a donné alors... :koi:

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