optimisation requête Oracle 10 [Résolu]

[zorino]

Bonjour,

Soit une requête du genre

Code :

1
2
3
4
5
SELECT * 
FROM   Table1, Table2
WHERE  Table1.Cle = Table2.Cle(+)
AND    NVL(Table2.Date, Table1.Date) >= '01/01/2007'
AND    NVL(Table2.Date, Table1.Date) <  '01/02/2007'

Table1 contient 150 000 enregistrements, Table2 est vide. La restriction sur la période fait que cette requête ne retourne que 1600 enregistrements, en 1 seconde, tout va bien.

Maintenant, je voudrais appliquer un autre critère, du genre :

Code :

1
2
3
4
5
6
select * 
from   Table1, Table2
where  Table1.Cle = Table2.Cle(+)
and    NVL(Table2.Date, Table1.Date) >= '01/01/2007'
and    NVL(Table2.Date, Table1.Date) <  '01/02/2007'
and    Fonction(Table1.Champ) = 0

Or, lorsque je le fais, la requête rame terriblement. Donc j'essaye ceci :

Code :

1
2
3
4
5
6
7
8
9
SELECT * 
FROM  ( 
       SELECT * 
       FROM   Table1, Table2
       WHERE  Table1.Cle = Table2.Cle(+)
       AND    NVL(Table2.Date, Table1.Date) >= '01/01/2007'
       AND    NVL(Table2.Date, Table1.Date) <  '01/02/2007'
      )
WHERE Fonction(Table1.Champ) = 0;

après avoir pris soin de mettre une séquence dans la fonction, initialisée à zéro avant de lancer la requête.

Après exécution, la séquence vaut 90 000 et quelques, ce qui explique la lenteur, mais je ne comprends pas pourquoi on passe 90 000 fois dans cette fonction !

La question reste : comment faire pour qu'il n'exécute cette fonction que pour les 1600 enregistrements déjà sélectionnés pour la requête ?

Je précise que si je supprime la jointure avec la table vide, le résultat de la requête reste le même (normal), mais la séquence vaut bien 1600 et la requête va vite !

Merci pour toute explication et idée pour booster cette requête !

[LeoAnderson]

quel schéma....

les indexes ne sont sûrement pas utilisés avec toutes ces fonctions en clause WHERE

[zorino]

Envoyé par LeoAnderson

les indexes ne sont sûrement pas utilisés avec toutes ces fonctions en clause WHERE

et encore, j'ai bien simplifié la requête pour la publier !

Mais là n'est pas la question...

[LeoAnderson]

Si la question n'est pas d'améliorer les perfs, c'est quoi alors ?

[orafrance]

faudrait peut-être faire un index sur la fonction

[zorino]

Envoyé par LeoAnderson

Si la question n'est pas d'améliorer les perfs, c'est quoi alors ?

C'est celle-ci : comment se fait-il que la fonction soit exécutée 90 000 fois alors qu'elle ne devrait s'appliquer qu'à la requête qui renvoit 1600 lignes ?

Ensuite, car je ne doute pas qu'il y ait une bonne raison, comment faire pour qu'elle ne s'applique réellement que 1600 fois ?

[orafrance]

peut-être parce que la table à 90000 lignes non ?

Code :

comment faire pour qu'elle ne s'applique réellement que 1600 fois ?

Tu codes en PL/SQL

[orafrance]

Plutôt qu'un message qui sert à rien tu pourrais au moins donner des éléments comme le plan d'exécution et essayer l'index que j'ai proposé

j'imagine que les stats sont calculées ? Tu peux aussi donné le NUM_ROWS des tables non ?

[Michel SALAIS]

Il faut savoir que Oracle peut tenter de fusionner les vues dans la requête principale. Si tu veux empêcher la fusion alors il faut utiliser par exemple l'indicateur d'optimisation /*+ materialize */

[pifor]

On peut aussi essayer d'utiliser le hint NO_MERGE qui a l'avantage d'être documenté à la différence de MATERIALIZE.

[zorino]

Envoyé par Michel SALAIS

Il faut savoir que Oracle peut tenter de fusionner les vues dans la requête principale. Si tu veux empêcher la fusion alors il faut utiliser par exemple l'indicateur d'optimisation /*+ materialize */

Merci pour ton aide, mais pourrais-tu me dire ce que ça signifierait pour ma requête ?
Pour être franc, j'ai essayé et ça ne change pas grand chose, mais je ne sais pas trop où placer ce hint...

[zorino]

Envoyé par pifor

On peut aussi essayer d'utiliser le hint NO_MERGE qui a l'avantage d'être documenté à la différence de MATERIALIZE.

merci pour ton aide, ça a effectivement l'air de correspondre à ce que je cherche, je vais fouiner de ce côté...
Mais, du coup, je me demande quel est le moyen "recommandé" de tourner une requête de ce genre !

Par exemple, admettons que je veuille appliquer une fonction assez coûteuse en temps seulement sur les filles.
La requête de base serait

Code :

1
2
3
4
5
 
SELECT * 
FROM   TABLE
WHERE  sexe='F'
AND   fonction(TABLE.cle) = 'OK'

C'est ce que je faisais, mais je me suis aperçu que la fonction n'était pas exécutée QUE sur les filles. Je voudrais donc qu'il y ait une préséance de la clause "WHERE SEXE = 'F'" sur celle AND FONCTION(CLE) = 'OK'

Comment tournerais-tu cette requête pour utiliser ce hint NO_MERGE ?

[LeoAnderson]

j'en profite pour élargir le débat : l'utilisation des hints est très efficace et parfaitement normale d'utilisation mais il faut bien être conscient de ce que l'on fait !

C'est à dire que l'on fige "ad vitam eternam" l'application et donc le schéma.
Ce qui est assez gênant dans bien des contextes.

Je préfère utiliser des outline qui permettent de modifier le plan d'exécution mais sans que l'on ne touche aux requêtes générées par l'appli...
http://download.oracle.com/docs/cd/B...htm#sthref3533

[Michel SALAIS]

Envoyé par zorino

Merci pour ton aide, mais pourrais-tu me dire ce que ça signifierait pour ma requête ?
Pour être franc, j'ai essayé et ça ne change pas grand chose, mais je ne sais pas trop où placer ce hint...

En fait materialize doit se positionner dans la requête interne et pas sur la requête externe mais comme l'a signalé pitfor no_merge est une autre possibilité

[Michel SALAIS]

Envoyé par LeoAnderson

j'en profite pour élargir le débat : l'utilisation des hints est très efficace et parfaitement normale d'utilisation mais il faut bien être conscient de ce que l'on fait !

C'est à dire que l'on fige "ad vitam eternam" l'application et donc le schéma.
Ce qui est assez gênant dans bien des contextes.

Je préfère utiliser des outline qui permettent de modifier le plan d'exécution mais sans que l'on ne touche aux requêtes générées par l'appli...
http://download.oracle.com/docs/cd/B...htm#sthref3533

J'y ajoute que l'utilisation des hints doit être d'une certaine manière le dernier recours. Normalement, nous ne devons pas travailler à la place de l'optimiseur!

Pour aller encore plus loin, il peut être utile de collecter les stats système ce qui activera la prise en considération du cpu par l'optimiseur.

Une solution qui éviterai le calcul coûteux de la fonction est d'utiliser un index basé sur cette fonction

[zorino]

Envoyé par Michel SALAIS

J'y ajoute que l'utilisation des hints doit être d'une certaine manière le dernier recours. Normalement, nous ne devons pas travailler à la place de l'optimiseur!

je suis bien d'accord, d'autant que les perfs d'Oracle sont vraiment bluffantes... sauf quelquefois ! Et dans ces cas-là, comment faire ?

Envoyé par Michel SALAIS

Une solution qui éviterai le calcul coûteux de la fonction est d'utiliser un index basé sur cette fonction

irréalisable dans mon cas, j'ai évidemment simplifié la requête à l'extrême mais la fonction en question fait entre autres un solde d'écritures, aucun intérêt d'y mettre un index... La seule optimisation possible est celle que tu ferais si je te demandais de le faire à la main, du genre : prends une classe et fais-moi la moyenne de l'année des filles.
Tu isolerais d'abord les filles, puis tu ferais leur moyenne.
Oracle fait toutes les moyennes (enfin, pas toutes d'ailleurs, il en fait 90000 sur 150000, alors qu'il n'y a que 10000 filles) et cherche à rapprocher la liste des moyennes de la liste des filles.

Le seul moyen que j'aie trouvé pour faire ce que je veux, c'est une fonction CAST qui, en gros, parcourt un curseur SELECT * FROM TABLE WHERE SEXE='F' et, pour chaque ligne trouvée, exécute la fonction et renvoie une ligne... Là, ça booste pile-poil et c'est normal, Oracle fait ce qu'on lui demande et c'est tout simple. Le code, pour info :

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
 
CREATE OR REPLACE FUNCTION "LISTE_NUMFAC" (dDateD DATE, dDateF DATE)
 RETURN TABLE_RETOUR_NUMFAC
IS
  RETVAL TABLE_RETOUR_NUMFAC := TABLE_RETOUR_NUMFAC();
  NB INTEGER;
  CURSOR cur (ssexe) IS
 	   SELECT Cle
 	   FROM MaTable
	   WHERE SEXE = ssexe;
  PROCEDURE EXPAND_COLLECTION (ENREG_A_RETOURNER IN RETOUR_NUMFAC)
  IS
  BEGIN
      RETVAL.EXTEND;
      RETVAL(retval.LAST) := ENREG_A_RETOURNER;
  END;
BEGIN
    FOR x IN cur('F')
    LOOP
      SELECT MaFonction(x.Cle) "nb"
	  INTO   nb
      FROM   XXX
      WHERE  bla-bla;
      IF (nb > 0) THEN
	      EXPAND_COLLECTION(RETOUR_NUMFAC(x.Cle));
      END IF;
    END LOOP;
    RETURN RETVAL;
END;

Le problème, c'est que je ne sais pas faire ça dans une requête, ou plutôt que dans la vraie requête Oracle se perd alors que le principe reste simple et que je ne sais pas comment lui dire de faire d'abord le test 1 avant de tester la condition 2...

[Michel SALAIS]

Bon,
Que dis-tu de ça ?

Pour aller encore plus loin, il peut être utile de collecter les stats système ce qui activera la prise en considération du cpu par l'optimiseur.

L'as-tu essayé?

[LeoAnderson]

sans compter, qu'il faut peut-être un peu faire confiance au CBO....

quelle est la différence de temps constaté entre sans rien et avec des hints ?

[zorino]

Envoyé par Michel SALAIS

Bon,
Que dis-tu de ça ?

je ne pense que du bien de tes propositions
Mais bon, celle-là, faut être franc, je n'y comprends rien !
Je suis quand même le débutant de base avec Oracle alors faut être patient...
Mais de toutes façons, pour ce cas, s'il faut toucher au paramétrage d'Oracle, tu peux laisser tomber parce que je n'y ai pas accès chez le client.

Je ne touche pas non plus au prog, il faut juste que la requête booste et c'est tout !

[zorino]

Envoyé par LeoAnderson

sans compter, qu'il faut peut-être un peu faire confiance au CBO....

Houlà ! C'est quoi ça ?

quelle est la différence de temps constaté entre sans rien et avec des hints ?

aucune significative...
Une requête qui dure 40 secondes va peut-être passer à 38, mais rien ne me dit que c'est grâce aux hints car 2 minutes après elle prendra 42...
La comparaison de référence se fait avec l'appel de la foncton CAST : environ 2 secondes, ce qui est tout à fait correct et conforme à ce qu'on peut attendre d'une requête qui fait un truc un peu compliqué sur 2500 enregistrements (parmi 150 000) environ.
Le problème c'est que, même avec index, si Oracle exécute la fonction sur 90 000 enregistrements (d'où les sort-il ?), c'est normal que ça lui prenne du temps. Or, c'est idiot de l'exécuter sur 90 000 puisqu'au final, seuls 2500 enreg. sont concernés (euh... je me répète, là , non ? )