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

Version imprimable

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:

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:

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

exécute cette requête :

Code:

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 :aie: tu passes par des curseurs pour faire des updates unitaires...

28/10/2008, 12h08
orafrance

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

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

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

28/10/2008, 15h00
orafrance

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 ?
28/10/2008, 15h16
ddazou

1 pièce(s) jointe(s)

Veuillez trouver en fichier joint le résultat d'explain plan sous taod
28/10/2008, 15h32
orafrance

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

ça donne quoi ça :

Code:

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:

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.

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

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

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:

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

Citation:

Envoyé par ddazou

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

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

8O 8O 8O

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.

28/10/2008, 16h12
orafrance

Citation:

Envoyé par ddazou

le code de la fonction

je comprends mieux :aie:

déjà le UNION fait un DISTINCT donc

Code:

SELECT DISTINCT... UNION SELECT DISTINCT

ça gaze pas.
28/10/2008, 16h15
ddazou

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
28/10/2008, 16h20
orafrance

8O 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 :

Citation:

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.
28/10/2008, 16h21
ddazou

Citation:

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.

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

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

28/10/2008, 16h30
orafrance

et si je ne m'abuse SUM(nbref) = nbref non ?
28/10/2008, 16h39
muad'dib

Citation:

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.
28/10/2008, 21h59
lallio

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.