Discussion :

[Snipah]

Bonjour,

J'ai un problème de performance dans une requête, manifestement lié à l'utilisation d'un type.
Je dois faire un grand nombre de tests dans une requete (j'utilise CASE WHEN ... THEN ... ).
Je travaille sur une base Oracle 10g.

Pour faire mes tests, je compare les champs de la requete à des valeurs d'un type 'Constantes' déclaré comme suit :

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
 
CREATE OR REPLACE
Type      Constantes AS object(
 
-- Statuts svg/communs
OK						VARCHAR2(16),
KO						VARCHAR2(16),
 
CONSTRUCTOR FUNCTION Constantes RETURN SELF AS RESULT
)
/
 
CREATE OR REPLACE
Type BODY      Constantes AS
  CONSTRUCTOR FUNCTION Constantes RETURN SELF AS RESULT IS
  BEGIN
 
	self.OK						:= 'OK';
	self.KO						:= 'KO';
 
        RETURN ;
  END;
END;
/

Je n'en mets qu'une partie, mais j'ai une trentaine de constantes définies dans ce type.

Lorsque j'augmente le nombre de " WHEN MonChamps = Constantes().Valeur " dans ma requête, les performances sont en chute libre.
Pour une quarantaine de tests, l'exécution passe à une dizaine de secondes, tandis qu'elle met 200ms si je remplace les Constantes().Valeur par sa valeur en dur dans la requête.
Je précise que la requete ne ramène qu'une ligne.

Autre détail : avec un shared_pool_size à 128M, la requete utilisant les types plante si je lui demande le plan d'execution (ORA-04031: impossible d'affecter 808 octets de mémoire partagée).
En augmentant le shared_pool_size à 256, l'affichage du plan d'execution se fait, mais prends une trentaine de secondes.
Tout est instantané avec la requete qui n'utilise pas les types.
Je travaille sur une base de dev, donc rien d'autre ne tourne.
Je serai tenté d'en conclure que l'utilisation que je fais des types est bien trop gourmande en mémoire.

Je vous mets un ou deux tests pour la route, mettre la totalité ne servirait à rien :

Code :

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

1
2
3
4
5
 
WHEN StatutEtape1 = Constantes().OK
     THEN Constantes().OK
WHEN StatutEtape1 = Constantes().KO
     THEN Constantes().KO

Qui équivaut à :

Code :

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

1
2
3
4
5
 
WHEN StatutEtape1 = 'OK'
     THEN 'OK'
WHEN StatutEtape1 = 'KO'
     THEN 'KO'

J'utilise un type pour pouvoir modifier à tout moment la valeur testée sans modifier toutes mes requetes. Pour prendre un exemple bidon, si un jour l'utilisateur décide de me renvoyer 'KOTechnique' au lieu de 'KO', j'aimerai n'avoir à changer que mon type.

Donc, je me pose deux questions :
- Est-ce que j'utilise correctement les types, ou est-ce que je fais quelque chose de travers qui expliquerait ces mauvaises performances?
- Sinon, voyez vous une autre solution plus efficace que l'utilisation de types pour déclarer mes constantes?

Merci d'avance!

[mnitu]

Peux tu nous donner un petit exemple de la requête qui les utilisent ?
Sinon très probablement que tu peux améliorer les performances en utilisant un package à la place des types.

[Snipah]

Voila pour l'exemple, en version très light :

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
SELECT
		StatutParEtape.pk_serv,
		StatutParEtape.groupe,
		CASE
			WHEN StatutParEtape.StatutEtape2 = Constantes().KO
			 AND StatutParEtape.StatutEtape1 = Constantes().KO
				 THEN Constantes().KO
			WHEN StatutParEtape.StatutEtape2 = Constantes().OK
			 AND StatutParEtape.StatutEtape1 = Constantes().OK
				 THEN Constantes().OK
			ELSE Constantes().INCO
		END AS StatutGroupe
FROM
		(SELECT DISTINCT
			   serv.pk_service,
			   grp.groupe,
			   CASE
			   		WHEN SUM(DECODE(etape2.statut,Constantes().OK,1)) > 0
						 THEN Constantes().OK
					WHEN SUM(DECODE(etape2.statut,Constantes().KO,1)) > 0
					 	 THEN Constantes().KO
					ELSE Constantes().INCO
			   END as StatutEtape2,
			   CASE
			   		WHEN SUM(DECODE(etape1.statut,Constantes().OK,1)) > 0
						 THEN Constantes().OK
					WHEN SUM(DECODE(etape1.statut,Constantes().KO,1)) > 0
					 	 THEN Constantes().KO
					ELSE Constantes().INCO
			   END as StatutEtape1
		FROM rea_serv serv
			 left join rea_etape2 etape2
				ON fin.fk_serv = serv.pk_serv
				   left join rea_etape1 etape1
				   ON grp.fk_serv = serv.pk_serv
		GROUP BY serv.pk_serv, grp.groupe
		) StatutParEtape
where pk_serv = 83871

Pour donner un ordre d'idée, avec tous les différents tests et différentes étapes, la requete fait environ 300 lignes.

En quoi l'utilisation d'un package plutot qu'un type sera meilleurs pour les perfs?

Merci pour ta réponse!

[mnitu]

A chaque référence de l’objet le constructeur de la fonction est exécuté (ajout un dbms_output dans le constructeur). Avec un package le code d’initialisation est exécuté une seule fois. Bon, tu va te taper quand même les changements de contexte SQL PL/SQL.
A partir d’Oracle 11 il y a la possibilité de mettre le résultat de la fonction dans un cache ce qui permettra des meilleurs temps de réponses.

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
73
74
75
76
77
78
79
80
81
82
83
 
create or replace package pk_test Is
  OK            VARCHAR2(16);
  KO            VARCHAR2(16);
  function f_ok return varchar2;
  pragma restrict_references (f_ok,WNDS,RNDS);
end;
/
create or replace package body pk_test Is
  procedure init Is
  begin
    OK            := 'OK';
    KO            := 'KO';
   dbms_output.put_line('init');
  End;
  function f_ok return varchar2 Is
  begin
    dbms_output.put_line('f_ok');
    return ok;
  end;
Begin
  init;
end;
/
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0 
Connected as mni
 
SQL> set serveroutput on
SQL> 
SQL> Select case when  'OK' = Constantes().OK Then 1 Else 0 end a,
  2         case when 'KO' =  Constantes().OK Then 1 Else 0 end B
  3  from dual
  4  /
 
         A          B
---------- ----------
         1          0
 
constructor
constructor
 
SQL> 
SQL> Select case when 'OK' = pk_test.f_OK Then 1 Else 0 end a,
  2         case when 'KO' = pk_test.f_OK Then 1 Else 0 end B
  3  from dual
  4  /
 
         A          B
---------- ----------
         1          0
 
init
f_ok
f_ok
 
SQL> 
SQL> Select case when  'OK' = Constantes().OK Then 1 Else 0 end a,
  2         case when 'KO' =  Constantes().OK Then 1 Else 0 end B
  3  from dual
  4  /
 
         A          B
---------- ----------
         1          0
 
constructor
constructor
 
SQL> 
SQL> Select case when 'OK' = pk_test.f_OK Then 1 Else 0 end a,
  2         case when 'KO' = pk_test.f_OK Then 1 Else 0 end B
  3  from dual
  4  /
 
         A          B
---------- ----------
         1          0
 
f_ok
f_ok
 
SQL>

[Snipah]

Comme tu me l'as conseillé, j'ai remplacé mon type Constantes par un package pkg_constantes, puis adapté ma requête.

La toute première exécution a pris 8 secondes.
La seconde exécution a pris 60ms, soit l'équivalent de la requête avec les valeurs codées en dur lorsqu'elle est déjà en cache.

Lorsque la requête n'est pas en cache :
900ms pour la requête utilisant le package.
300ms pour la requête avec valeurs codées en dur.

Lorsque les requêtes sont en cache, 60ms pour les deux versions.

La solution du package est donc de très loin plus performante que celle du type dans mon cas (10 fois plus rapide, 900ms contre 9secondes).

Merci beaucoup pour ton aide mnitu!

Une dernière question avant de marquer le sujet résolu : l'affichage de l'explain plan est toujours très long pour la requete utilisant le package (une vingtaine de secondes), tandis qu'il est instantané pour celles dont les valeurs sont codées en dur.
Quelqu'un aurait une explication à ça?

Merci encore.

[Snipah]

Bon, j'ai rien changé, mais j'ai plus le problème de lenteur pour l'explain plan.
Je marque le sujet comme résolu