[10g] Clause WITH dans une insertion (expressions de table communes)

[dhallennem]

Bonjour,

Je travaille sur Oracle 10i.

Je cherche à optimiser une requête d'insertion de données dans une table temporaire en vue d'une application web. Voici ma requête initiale :

INSERT INTO table_tmp
SELECT M.col1, sum(M.col2)
FROM
(SELECT col1,col2
FROM table1
WHERE cond1) M
GROUP BY M.col1;

Les données servant à alimenter cette table sont issues d'une requête de sélection (en vert) relativement coûteuse en temps d'exécution : environ 25 secondes.


J'ai réussi à optimiser nettement cette requête en utilisant une expression de table commune, autrement dit une syntaxe du type :

WITH M AS
(SELECT col1,col2
FROM table1
WHERE cond1)
SELECT col1, sum(col2)
FROM M
GROUP BY col1;

Je suis en effet arrivé à 9 secondes seulement.

Tout content, je me dis qu'il ne me reste alors qu'à rajouté le insert
into pour alimenter ma table temporaire :

Code :

1
2
3
4
5
6
7
8
INSERT INTO table_tmp
WITH M AS
(SELECT col1,col2
FROM table1
WHERE cond1)
SELECT col1, sum(col2)
FROM M
GROUP BY col1;

Et là surprise : la requête met 28 secondes !!!
Autant dire que l'utilisation de la clause WITH devient inutile !
J'ai beau cherché, je ne comprends pas pourquoi l'alimentation de la table induit un tel temps d'exécution alors que la seule sélection avec la clause WITH permet un gain de temps considérable par rapport à la requête initiale !?

Quelqu'un peut-il me donner une solution ou au moins une explication ?


PS : pour plus de renseignement sur l'utilisation de la clause WITH, vous pouvez aller voir ici : http://www.ianywhere.com/developer/p...s-5414852.html et http://www.ianywhere.com/developer/p...s-7010660.html

Je rentre bien dans les cas d'utilisation de cette clause et rien n'indique que ça induit un temps d'exécution plus long pour le cas de l'INSERT...

[orafrance]

et bien le problème vient donc de l'écriture et non de la lecture. Vérifie les attentes sur la session (v$session_wait) pour voir si ça vient des indexes, contraintes, latch, lock, redo, undo, etc...

[dhallennem]

Envoyé par Fred_D

Vérifie les attentes sur la session (v$session_wait) pour voir si ça vient des indexes, contraintes, latch, lock, redo, undo, etc...

Rien ne semble clocher... mais je ne suis pas expert

Envoyé par Fred_D

et bien le problème vient donc de l'écriture et non de la lecture.

Alors à ce moment-là pourquoi sans la clause WITH l'écriture ne rallonge-t-elle pas de la même manière le temps d'exécution ?

[orafrance]

de toute façon, le WITH ne sert à rien dans ton exemple... après, sans les waits ou les explain plan je vois mal comment on pourrait t'aider

[dhallennem]

Envoyé par Fred_D

de toute façon, le WITH ne sert à rien dans ton exemple...

Ben dans une application web, je trouve que ça sert à quelque chose d'éviter que les données mettent 30 secondes à s'afficher...
Le but est donc bien établi : optimiser le temps d'exécution !

[orafrance]

S'il y a un changement dans le temps d'exécution c'est qu'il y a un problème sur la base à mon avis... le WITH ne peut avoir un intérêt que si tu réutilises l'alias dans la requête

[dhallennem]

C'est vrai que mes exemples simplifiés ne permettent pas vraiment de visualiser le problème.

Voici donc la requête (de test) d'origine (sans WITH) :

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
 
INSERT INTO SIH_CARTE_SIG
SELECT 
'7551ba89aff707f0961011a08aac922c' SESSION_ID, '45ed4a512ff7b' NUM_COUCHE , 
M.CODE_GEOM , 
'TOTAL' CODE_METRIC , 
'TOTAL' LABEL_METRIC , 
SUM(M.MAR_QTE_CAPTUREE) VALEUR , 
0 RANG 
FROM 
( 
SELECT DISTINCT 
SE.SECT_COD CODE_GEOM, 
AM.NAVS_COD, AM.GRENG_ID, AM.ENGIN_COD, AM.TSECT_COD, AM.SECT_COD, AM.GESP_ID, AM.ESP_COD, AM.MAR_QTE_CAPTUREE 
FROM 
ASP_MAREE AM , 
DRB_SOUS_SECTEUR SS , 
DRB_SECTEUR SE 
WHERE 
1 = 1 
AND AM.mar_dated >= to_date('01/01/2004','DD/MM/YYYY') 
AND AM.mar_datef < to_date('31/05/2004','DD/MM/YYYY') 
AND SS.sect_cod = AM.sect_cod 
AND SE.sect_cod = SS.sect_cod_pere 
AND SE.tsect_cod = '13' 
) M 
GROUP BY 
M.CODE_GEOM;

et son EXPLAIN PLAN :

Code :

1
2
3
4
5
6
7
8
9
10
 
INSERT STATEMENT	CHOOSE	31515	389	7391					
SORT(GROUP BY)		31515	389	7391					
VIEW		31515	2294409	43593771					
SORT(UNIQUE)		31515	2294409	162903039					
HASH JOIN		5124	2294409	162903039					
HASH JOIN		31	29324	615804					
TABLE ACCESS(FULL) SIHDBA.DRB_SECTEUR	ANALYZED	11	389	3501					
TABLE ACCESS(FULL) SIHDBA.DRB_SOUS_SECTEUR	ANALYZED	19	39396	472752					
TABLE ACCESS(FULL) SIHDBA.ASP_MAREE	ANALYZED	4486	662102	33105100

et la requête (de test toujours) avec le WITH :

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
 
INSERT INTO SIH_CARTE_SIG
WITH M AS( 
SELECT DISTINCT 
SE.SECT_COD CODE_GEOM, 
AM.NAVS_COD, AM.GRENG_ID, AM.ENGIN_COD, AM.TSECT_COD, AM.SECT_COD, AM.GESP_ID, AM.ESP_COD, AM.MAR_QTE_CAPTUREE 
FROM 
ASP_MAREE AM , 
DRB_SOUS_SECTEUR SS , 
DRB_SECTEUR SE 
WHERE 
1 = 1 
AND AM.mar_dated >= to_date('01/01/2004','DD/MM/YYYY') 
AND AM.mar_datef < to_date('31/05/2004','DD/MM/YYYY') 
AND SS.sect_cod = AM.sect_cod 
AND SE.sect_cod = SS.sect_cod_pere 
AND SE.tsect_cod = '13' 
)
SELECT 
'7551ba89aff707f0961011a08aac922c' SESSION_ID, '45ed4a512ff7b' NUM_COUCHE , 
M.CODE_GEOM , 
'TOTAL' CODE_METRIC , 
'TOTAL' LABEL_METRIC , 
SUM(M.MAR_QTE_CAPTUREE) VALEUR , 
0 RANG 
FROM M
GROUP BY 
M.CODE_GEOM;

Et son EXPLAIN PLAN :

Code :

1
2
3
4
5
6
7
8
9
10
 
INSERT STATEMENT	CHOOSE	31515	389	7391					
SORT(GROUP BY)		31515	389	7391					
VIEW		31515	2294409	43593771					
SORT(UNIQUE)		31515	2294409	162903039					
HASH JOIN		5124	2294409	162903039					
HASH JOIN		31	29324	615804					
TABLE ACCESS(FULL) SIHDBA.DRB_SECTEUR	ANALYZED	11	389	3501					
TABLE ACCESS(FULL) SIHDBA.DRB_SOUS_SECTEUR	ANALYZED	19	39396	472752					
TABLE ACCESS(FULL) SIHDBA.ASP_MAREE	ANALYZED	4486	662102	33105100

Comme je l'ai déjà dit, je suis pas expert Oracle... donc la lecture de l'explain plan ne m'apprend pas grand chose

Si ça vous permet de m'éclairer merci d'avance !

[dhallennem]

Pour la requête de sélection avec le WITH (donc juste sans INSERT INTO SIH_CARTE_SIG), voici l'EXPLAIN PLAN :

Code :

1
2
3
4
5
6
7
8
9
10
 
SELECT STATEMENT	CHOOSE	31515	389	8169					
SORT(GROUP BY)		31515	389	8169					
VIEW		31515	2294409	48182589					
SORT(UNIQUE)		31515	2294409	162903039					
HASH JOIN		5124	2294409	162903039					
HASH JOIN		31	29324	615804					
TABLE ACCESS(FULL) SIHDBA.DRB_SECTEUR	ANALYZED	11	389	3501					
TABLE ACCESS(FULL) SIHDBA.DRB_SOUS_SECTEUR	ANALYZED	19	39396	472752					
TABLE ACCESS(FULL) SIHDBA.ASP_MAREE	ANALYZED	4486	662102	33105100

[orafrance]

donc tu vois bien que le plan d'exécution ne change pas... le problème vient donc surement des waits

[dhallennem]

Ok.


Et comment faire pour voir ça ? Ou est-ce qu'on peut voir comment sont positionnés les wait ?

[orafrance]

v$session_wait pendant l'exécution ou une trace