Discussion :

[ddazou]

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]

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]

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

[ddazou]

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]

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]

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

[orafrance]

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