Temps d'exécution très long : jointure

**ddazou** · 28/10/2008, 11h47

Bonjour,
Je cherche à optiser une requête dans ma procédure parce que j'ai un temps d'exécution extrêmement long et je n'ai pas de résultat.

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
 
....
INSERT INTO MA_TABLE_1 (VENDEUR, NOM, TITRE, REGION, SOUSREGION, NBREFTOTAL)
SELECT 	A.VENDEUR, NOM, TITRE, LIBREGION, LIBSOUSREGION, SUM(NBREF)
FROM  MA_TABLE_2,
      LIBREGION,
      LIBSOUSREGION,
      ( SELECT MA_TABLE_TEMP.VENDEUR VENDEUR,
		       MA_TABLE_TEMP.TITRE TITRE,
		       MA_TABLE_TEMP.CIP CIP,
		       FONCTION_CALCUL (MA_TABLE_TEMP.COM, date_debut, date_fin, MA_TABLE_TEMP.REFERENCE) NBREF
	   FROM MA_TABLE_TEMP
	  ) A
WHERE 	A.VENDEUR = MA_TABLE_2.VENDEUR
	AND MA_TABLE_2.REGION = LIBREGION.REGION
	AND MA_TABLE_2.SOUS_REGION = LIBSOUSREGION.SOUSREGION
GROUP BY A.VENDEUR, NOM, TITRE, LIBREGION, LIBSOUSREGION ;
....

Le problème se pose à ce niveau parce que sans cette requête j'ai quand même un résultat d'éxécution en si peu de temps.

Situation :
Oracle 10
Pas d'index sur MA_TABLE_1, pas de clé
MA_TABLE_2 : clé primaire (VENDEUR), création d'index sur les colonnes REGION, SOUSREGION
LIBREGION : clé primaire (REGION)
LIBSOUSREGION : clé primaire (SOUSREGION)
MA_TABLE_TEMP : pas de clé, pas d'index
MA_TABLE_1,MA_TABLE_2,MA_TABLE_TEMP environ neuf cent mille (900 000) lignes chacune.

Ma fonction FONCTION_CALCUL renvoie un number
Select count(distinct champ) from ( select distinct p from A union select ....) T where ...;

select FONCTION_CALCUL(...) from dual; aucun problème

Solution 1:
Je l'ai éclatée en 3 requêtes avec une nouvelle table temporaire
1)Insertion dans MA_TABLE_1 sans le champ calcul : VENDEUR, NOM, TITRE, REGION, SOUSREGION
2)Insertion dans une table temporaire avec le champ : VENDEUR, résultat de la fonction FONCTION_CALCUL
3)Insertion dans MA_TABLE_1 en faisant le sum sur la colonne résultat

Je n'ai pas eu de résultat

Solution 2 :
1)Insertion dans MA_TABLE_1 sans le champ calcul : VENDEUR, NOM, TITRE, REGION, SOUSREGION
2)Curseur pour le calcul
...

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
for enreg in (select vendeur,titre from MA_TABLE_1) loop 
	for rec in (select vendeur, com, reference 
	            from MA_TABLE_TEMP 
				where vendeur = enreg.vendeur
				and titre = enreg.titre ) loop 
	   nbref := nbref + F_CALCUL_NB_REF_MERE_PERIODE (rec.COM, date_debut, date_fin, rec.REFERENCE);
	end loop;
 
	UPDATE MA_TABLE_1
	SET NB_REF = nbref
	WHERE MA_TABLE_1.VENDEUR = enreg.VENDEUR
	and MA_TABLE_1.titre = enreg.titre;
 
	nbref := 0;
 
end loop;

...
Idem pas de résultat

Dans MA_TABLE_TEMP beaucoup de colonnes identiques ou vides, pas judicieux d'avoir un index.

Problème, comment optimiser cette requête pour le temps d'exécution et espérer avoir un résultat quand je lancerai la procédure.

Merci

**orafrance** · 28/10/2008, 12h05

exécute cette 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
14
SELECT 	A.VENDEUR, NOM, TITRE, LIBREGION, LIBSOUSREGION, SUM(NBREF)
FROM  MA_TABLE_2,
      LIBREGION,
      LIBSOUSREGION,
      ( SELECT MA_TABLE_TEMP.VENDEUR VENDEUR,
		       MA_TABLE_TEMP.TITRE TITRE,
		       MA_TABLE_TEMP.CIP CIP,
		       FONCTION_CALCUL (MA_TABLE_TEMP.COM, date_debut, date_fin, MA_TABLE_TEMP.REFERENCE) NBREF
	   FROM MA_TABLE_TEMP
	  ) A
WHERE 	A.VENDEUR = MA_TABLE_2.VENDEUR
	AND MA_TABLE_2.REGION = LIBREGION.REGION
	AND MA_TABLE_2.SOUS_REGION = LIBSOUSREGION.SOUSREGION
GROUP BY A.VENDEUR, NOM, TITRE, LIBREGION, LIBSOUSREGION ;

en activant la trace niveau 8 et joins nous le tkprof STP

PS : la 2eme solution sera même plus longue

tu passes par des curseurs pour faire des updates unitaires...

**orafrance** · 28/10/2008, 12h08

quel est le code de FONCTION_CALCUL ? et combien de lignes sont dans MA_TABLE_TEMP ?

**ddazou** · 28/10/2008, 14h56

Dans MA_TABLE_TEMP, j'ai presque 900 000 lignes
Fichier résultat de l'exécution du tkprof : reqresultat.prf

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
TKPROF: Release 8.1.7.0.0 - Production on Ma Oct 28 14:22:09 2008
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
Trace file: c:\reqtest.sql
Sort options: default
 
********************************************************************************
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
    0  statements EXPLAINed in this session.
********************************************************************************
Trace file: c:\reqtest.sql
Trace file compatibility: 8.00.04
Sort options: default
 
       0  session in tracefile.
       0  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       0  SQL statements in trace file.
       0  unique SQL statements in trace file.
      15  lines in trace file.

Mon fichier reqtest.sql contient mon select et la commande exécutée sous dos : tkprof c:\reqtest.sql c:\reqresultat explain=user/user@user table=user.plan_table
Ai-je saisi la bonne syntaxe?

**orafrance** · 28/10/2008, 15h00

t'as pas arrêté la trace avant de faire le tkprof je pense

A mon avis ça vient de ta fonction de toute façon, 900 000 appels à ta fonction c'est trop... selon ce qu'elle fait ça peut couter TRES cher.

Non, ta commande n'est pas bonne

trace : http://orafrance.developpez.com/dbahelp/#L3.1
tkprof sur le fichier trace : http://orafrance.developpez.com/dbahelp/#L3.3

PS : et le code de la fonction ?

**ddazou** · 28/10/2008, 15h16

Veuillez trouver en fichier joint le résultat d'explain plan sous taod

**orafrance** · 28/10/2008, 15h32

vu la requête faut rien attendre de ce coté... j'attends en revanche toujours les réponses à mes demandes

**orafrance** · 28/10/2008, 15h35

ça donne quoi ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 	A.VENDEUR, NOM, TITRE, LIBREGION, LIBSOUSREGION, NBREF
FROM  MA_TABLE_2,
      LIBREGION,
      LIBSOUSREGION,
      ( SELECT MA_TABLE_TEMP.VENDEUR VENDEUR,
		       MA_TABLE_TEMP.TITRE TITRE,
		       SUM(FONCTION_CALCUL (MA_TABLE_TEMP.COM, date_debut, date_fin, MA_TABLE_TEMP.REFERENCE)) NBREF
	   FROM MA_TABLE_TEMP
          GROUP BY VENDEUR, TITRE
	  ) A
WHERE 	A.VENDEUR = MA_TABLE_2.VENDEUR
	AND MA_TABLE_2.REGION = LIBREGION.REGION
	AND MA_TABLE_2.SOUS_REGION = LIBSOUSREGION.SOUSREGION ;

et ça :

Code :

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

1
2
3
4
5
SELECT MA_TABLE_TEMP.VENDEUR VENDEUR,
		       MA_TABLE_TEMP.TITRE TITRE,
		       SUM(FONCTION_CALCUL (MA_TABLE_TEMP.COM, date_debut, date_fin, MA_TABLE_TEMP.REFERENCE)) NBREF
	   FROM MA_TABLE_TEMP
          GROUP BY VENDEUR, TITRE

les temps de traitement ne devrait pas être très éloigné mais il y a une chance que ce soit mieux.

**ddazou** · 28/10/2008, 15h52

Merci pour les liens, j'essaie de comprendre davantage pour tkprof
le code de la fonction :

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
CREATE OR REPLACE FUNCTION FONCTION_CALCUL (p_nocli VARCHAR2, p_date_debut DATE, p_date_fin DATE, p_produit varchar2) RETURN NUMBER IS
v_nb_ref_mere		NUMBER;
BEGIN
select count(distinct refmere)
INTO v_nb_ref_mere
FROM (
select distinct p.refmere
from detfac d, produit p, strprod s, (select reference from (
SELECT p_produit REFERENCE FROM DUAL
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.SOUSFAMILLE = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.FAMILLE = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.GAMME = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.GAMMECOM = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.SUPERGAMME = p_produit
 )) REFERENCE
where d.datefac >= p_date_debut
and d.datefac <= p_date_fin
and d.com = p_nocli
AND d.reference = s.reference
AND s.fgkit = 'N'
and s.REFERENCE = P.reference
AND d.reference = reference.reference
AND p.refmere not like '%999'
AND d.typfac = 'FV'
union
select distinct p.refmere
from detfac d, produit p, strprod comp, strprod kit, (select reference from (
SELECT p_produit REFERENCE FROM DUAL
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.SOUSFAMILLE = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.FAMILLE = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.GAMME = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.GAMMECOM = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.SUPERGAMME = p_produit
  )) REFERENCE
where d.datefac >= p_date_debut
and d.datefac <= p_date_fin
and d.com = p_nocli
AND d.reference = kit.reference
AND kit.fgkit = 'Y'
AND comp.fgcompkit = 'Y'
AND comp.refkit = kit.reference
and comp.REFERENCE = P.reference
AND comp.reference = reference.reference
AND p.refmere not like '%999'
AND d.typfac = 'FV'
);
RETURN v_nb_ref_mere;
EXCEPTION
WHEN OTHERS THEN
	RETURN 0;
END;
/

**Waldar** · 28/10/2008, 16h03

Oui il faut essayer d'appeller le moins possible votre fonction qui à l'air coûteuse.

Dans votre première requête, vous exécutez la fonction sur tous les enregistrements de MA_TABLE_TEMP, peut-être ce n'est pas nécessaire.

Que donne le code suivant :

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
SELECT
    mt.VENDEUR,
    mt.NOM,
    tt.TITRE,
    rg.LIBREGION,
    sr.LIBSOUSREGION,
    SUM(FONCTION_CALCUL(tt.COM, tt.date_debut, tt.date_fin, tt.REFERENCE)) nb_ref
FROM
    MA_TABLE_2 mt,
    LIBREGION rg,
    LIBSOUSREGION sr,
    MA_TABLE_TEMP tt
WHERE
    tt.VENDEUR = mt.VENDEUR
AND rg.REGION = mt.REGION 
AND sr.SOUSREGION = mt.SOUS_REGION
GROUP BY
    mt.VENDEUR,
    mt.NOM,
    tt.TITRE,
    rg.LIBREGION,
    sr.LIBSOUSREGION

**muad'dib** · 28/10/2008, 16h05

Envoyé par ddazou

Merci pour les liens, j'essaie de comprendre davantage pour tkprof
le code de la fonction :

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
CREATE OR REPLACE FUNCTION FONCTION_CALCUL (p_nocli VARCHAR2, p_date_debut DATE, p_date_fin DATE, p_produit varchar2) RETURN NUMBER IS
v_nb_ref_mere		NUMBER;
BEGIN
select count(distinct refmere)
INTO v_nb_ref_mere
FROM (
select distinct p.refmere
from detfac d, produit p, strprod s, (select reference from (
SELECT p_produit REFERENCE FROM DUAL
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.SOUSFAMILLE = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.FAMILLE = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.GAMME = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.GAMMECOM = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.SUPERGAMME = p_produit
 )) REFERENCE
where d.datefac >= p_date_debut
and d.datefac <= p_date_fin
and d.com = p_nocli
AND d.reference = s.reference
AND s.fgkit = 'N'
and s.REFERENCE = P.reference
AND d.reference = reference.reference
AND p.refmere not like '%999'
AND d.typfac = 'FV'
union
select distinct p.refmere
from detfac d, produit p, strprod comp, strprod kit, (select reference from (
SELECT p_produit REFERENCE FROM DUAL
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.SOUSFAMILLE = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.FAMILLE = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.GAMME = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.GAMMECOM = p_produit
 UNION
 SELECT STRPROD.REFERENCE REFERENCE FROM STRPROD
 WHERE STRPROD.SUPERGAMME = p_produit
  )) REFERENCE
where d.datefac >= p_date_debut
and d.datefac <= p_date_fin
and d.com = p_nocli
AND d.reference = kit.reference
AND kit.fgkit = 'Y'
AND comp.fgcompkit = 'Y'
AND comp.refkit = kit.reference
and comp.REFERENCE = P.reference
AND comp.reference = reference.reference
AND p.refmere not like '%999'
AND d.typfac = 'FV'
);
RETURN v_nb_ref_mere;
EXCEPTION
WHEN OTHERS THEN
	RETURN 0;
END;
/

Une fonction aussi longue avec autant de jointures sur une table à 900 000 lignes!!!! C'est de la folie, il y a clairement un problème de conception. Faudrait voir à stocker certains résultats de calculs dans des tables internédiaires afin d'économiser du processeur. Par ex., tu définis un trigger qui exécute la requête et en stocke le contenu dans une table à part. Ensuite, lors de ta requête tu n'as qu'à aller chercher ce qui a été calculé auparavant.

**orafrance** · 28/10/2008, 16h12

Envoyé par ddazou

le code de la fonction

je comprends mieux

déjà le UNION fait un DISTINCT donc

Code :

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

SELECT DISTINCT... UNION SELECT DISTINCT

ça gaze pas.

**ddazou** · 28/10/2008, 16h15

J'ai déjà testé votre première requête à la seule différence que j'avais gardé la colonne CIP et un group by sur les 3 : vendeur,titre et cip. J'avais lancé la procédure et 7h après je n'avais toujours pas de réponse. J'ai arrêté pour d'autres modifications.
Je viens de relancer les deux requêtes dans deux fenêtres dos avec un set time on pour comparer.
Merci

**orafrance** · 28/10/2008, 16h20

il n'y a pas un bléme dans ta fonction ? Tu joins 2 requêtes quasi identique dans la 2eme tu ajoutes juste strprod une deuxième fois :

SELECT ...
FROM detfac d, produit p, strprod s...
UNION
SELECT ...
FROM detfac d, produit p, strprod comp, strprod kit

Après bah cherche pas plus, la requête de ta fonction, il faut la sortir pour la mettre dans ta requête... éventuellement crée une vue au moins.

**ddazou** · 28/10/2008, 16h21

Une fonction aussi longue avec autant de jointures sur une table à 900 000 lignes!!!! C'est de la folie, il y a clairement un problème de conception. Faudrait voir à stocker certains résultats de calculs dans des tables internédiaires afin d'économiser du processeur. Par ex., tu définis un trigger qui exécute la requête et en stocke le contenu dans une table à part. Ensuite, lors de ta requête tu n'as qu'à aller chercher ce qui a été calculé auparavant.

Cela revient à la première solution soulignée dès le debut et testée sur une table temporaire avec deux champs (vendeur et resultat du calcul de la fonction) sauf qu'après plusieurs heures d'exécution je n'avais pas de résultat

Merci, OK je me replonge sur ma fonction.

**orafrance** · 28/10/2008, 16h25

remplace la requête de ta fonction par ceci pour voir :

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
SELECT count(DISTINCT refmere)
INTO v_nb_ref_mere
FROM (
SELECT p.refmere
FROM detfac d, produit p, strprod s, (
SELECT p_produit REFERENCE FROM DUAL
 UNION ALL
 SELECT STRPROD.REFERENCE  FROM STRPROD
 WHERE STRPROD.SOUSFAMILLE = p_produit
 UNION ALL
 SELECT STRPROD.REFERENCE  FROM STRPROD
 WHERE STRPROD.FAMILLE = p_produit
 UNION ALL
 SELECT STRPROD.REFERENCE  FROM STRPROD
 WHERE STRPROD.GAMME = p_produit
 UNION ALL
 SELECT STRPROD.REFERENCE  FROM STRPROD
 WHERE STRPROD.GAMMECOM = p_produit
 UNION ALL
 SELECT STRPROD.REFERENCE  FROM STRPROD
 WHERE STRPROD.SUPERGAMME = p_produit
 ) REFERENCE
WHERE d.datefac >= p_date_debut
AND d.datefac <= p_date_fin
AND d.com = p_nocli
AND d.reference = s.reference
AND s.REFERENCE = P.reference
AND d.reference = reference.reference
AND p.refmere NOT LIKE '%999'
AND d.typfac = 'FV'
AND (
     s.fgkit = 'N'
	 OR (
	         s.fgkit = 'Y'
	     AND s.reference IN ( SELECT refkit 
		                        FROM strprod 
							   WHERE fgcompkit = 'Y'
							     AND REFERENCE = p.reference
								 AND reference = reference.reference )
		)
	)

**orafrance** · 28/10/2008, 16h30

et si je ne m'abuse SUM(nbref) = nbref non ?

**muad'dib** · 28/10/2008, 16h39

Envoyé par ddazou

Cela revient à la première solution soulignée dès le debut et testée sur une table temporaire avec deux champs (vendeur et resultat du calcul de la fonction) sauf qu'après plusieurs heures d'exécution je n'avais pas de résultat

Merci, OK je me replonge sur ma fonction.

En fait je pensais plus à stocker les résultat de la fonction FONCTION_CALCUL afin que tu n'aies plus besoin de l'appeler lors de ta requête, je ne suis pas sur que c'est ce que tu as essayé de faire.

**lallio** · 28/10/2008, 21h59

Bonsoir,

-1/ combien de lignes dans les tables detfac d, produit p, strprod s
-2/ Y a t'il des indexes sur ces tables
-3/ Les stats sont-elles à jours.

Entre les union, les from dual, les like '%....' c'est presque normal que ca marche pas.

Laurent.

Temps d'exécution très long : jointure

SQL Oracle

Discussions similaires

Partager

Partager