nvl et temps de reponse

Bonjour,

je n'ai rien trouvé dans la fonction recherche alors je pose ma question...

j'ai une requete (somme toute assez simple), quelques jointures etc.. rien de mechant..
une des clauses du where est :

Code:

---

AND DE.TYP_EMP = NVL(p_typ_emp, DE.TYP_EMP)

---

où p_typ_emp est un parametre passé au package ou se trouve la requete (un curseur)...
si le parametre n'est pas null.. le resultat est immediat...
si il est null.. là.. c'est la cata !! 10 secondes pour 20 lignes trouvées !!

du coup j'ai essayé ca :

Code:

---

AND DE.TYP_EMP = decode(p_typ_emp,NULL,de.typ_emp, p_typ_emp)

---

et là que ce soit null ou pas.. .c'est instantanée (pour le meme resultat qu'avec le nvl...)

quelqu'un aurait une idée ??
la base est une 10g...
merci

Ta requête avec :

Code:

---

AND DE.TYP_EMP = NVL(p_typ_emp, DE.TYP_EMP)

---

est ce qu'il y a de pire pour l'optimiseur. Il s'attend à avoir toujours une valeur pour de.typ_emp... Il va donc effectuer un parcours suivant un index (vérifie ton plan d'exécution).
Quand tu lui passe NULL, il va parcourir l'index en filtrant des choses qui sont toujours identique.

Un index est un arbre récursif. La complexité de parcours d'un index est donc en N ln N (croissance forte en fonction de la taille) auquel il faut bien sur ajouter le temps de récupération des données dans la table. On n'utilise donc le parcours suivant un index que si l'interrogation ramène peu de lignes.

Pour avoir quelques chose de performant dans tous les cas, il est préférable de mettre la requête dans une variable (varchar2), puis d'ouvrir un curseur sur cette variable.

Tu va avoir un truc du genre :

Code:

---

1 2 3 4 5 6 7 8 9 10

DECLARE req VARCHAR2(10000); BEGIN ... req :='select ... debut de la requête'; IF p_typ_emp IS NOT NULL THEN req := req || ' AND de.typ_emp = '''||p_typ_emp||'''; END IF; req := req||' fin de la requête'

---

(attention aux espaces dans la construction)

Regarde la documentation PL/SQL fourni par Oracle, chapitre native dynamic sql. Tu auras tout ce qui va bien sur execute immediate ou open-for qui te permettront d'exécuter le code qui est dans la variable req

que donne ceci ?

Code:

---

AND (DE.TYP_EMP = p_typ_emp OR p_typ_emp IS NULL)

---

C'est effectivement une autre solution, plus rapide à mettre en oeuvre. Mais je crains un peu coté plan d'exécution (utilisation d'un éventuel index ?)

La version initiale me rappele le code d'un développeur il y a quelques années : en powerbuilder, il construisait la requête pour intérroger notre plus grosses tables... Toutes les possibilités d'interogation était écrite sous cette forme (numéro de client ou libellé de client ou date d'achat ou ...).

Lors des tests de performances avec un peu de volumétrie : full table scan systématique...
Pour aller vite dans la correction, on a tenté de patcher de la manière que tu proposes, mais l'optimiseur avait du mal à gérer la complexité de la requête obtenue (trop de or... mais c'était la version 8i à l'époque)

A tester... Ce serait bien si yrogerg_d (à tes souhaits;) ) nous donnait les plans d'exécutions des différentes possibilités (nvl / decode / or / remplacement de chaîne de caractères)

Citation:

---

Envoyé par yrogerg_d

...
du coup j'ai essayé ca :

Code:

---

AND DE.TYP_EMP = decode(p_typ_emp,NULL,de.typ_emp, p_typ_emp)

---

et là que ce soit null ou pas.. .c'est instantanée (pour le meme resultat qu'avec le nvl...)

quelqu'un aurait une idée ??
la base est une 10g...
merci

---

8O
Que ce soit rapide avec le décode m'etonne vraiment... ça voudrais dire que le CBO de 10g traite de manière spéciale les nvl ou les décode ??? :koi:

Effectivement il faudrait voir s'il y a une différence de plan...

Salut

desole pour le pseudo... c un truc que traine depuis un moment...

bon.. alors pour le plan d'execution...
avec le nvl ou le decode, c'est strictement le meme plan (??!)

j'ai aussi essayé le (de.typ_emp = p_typ_emp or p_typ_emp is null)
en temps de reponse c'est identique au decode...

pas essayé de réécrire avec un ref cursor... trop de boulot je pense...
enfin si ca marche pas mieux on y pensera...

pour info j'ai aussi essayé COLAESCE.. ca fait comme le nvl.. ca rame !!

Citation:

---

Envoyé par remi4444

8O
Que ce soit rapide avec le décode m'etonne vraiment... ça voudrais dire que le CBO de 10g traite de manière spéciale les nvl ou les décode ??? :koi:

---

DECODE = OR = UNION ALL alors que NVL je ne sais pas trop comment il le gére :koi:

Hugues_78 :arrow: en général, il vaut mieux utiliser la solution que tu préconises. Mais ce qui est assez facile dans les AGL (Forms, Visual, Powerbuilder, etc...) l'est moins en SQL. Ceci étant, j'ai mis OR pour gagner du temps, mais l'idéal aurait été de le transformer en UNION ALL ;)

Citation:

---

Envoyé par Fred_D

DECODE = OR = UNION ALL alors que NVL je ne sais pas trop comment il le gére :koi:

---

Ben ce qui m'étonne c'est qu'il aille faire ce genre d'équivalence justement... jusqu'à présent que ce soit nvl, décode ou autre, c'est une fonction et pi c'est tout...

Si c'est le meme plan d'exéctution, il se peut qu'il y ait une histoire de cache, que ce passe-t-il si on enchaine 2 exéctutions avec nvl, puis 2 exectutions avec décode mais avec d'autres paramètres...

Autre doute qui me vient, dans l'exemple il est fait référence à une variable, donc je suppose que le chronométrage est fait au niveau de l'appel à une procédure, est-ce que ce serait pas à un autre endroit de la proc que ça coince.... (locks, calcul du curseur passé en paramètre etc...).

Pour analyser la requête, il faut l'exécutée toute seule avec une constante à la place de p_typ_emp.

S'il y a un réel problème de performances, une solution rapide à mettre en oeuvre serait d'avoir 2 requêtes :
si p_emp est null on exécute la première sinon on exécute la seconde
(pas cool en terme de maintenance, mais ça se fait en 5mn, à la différence de la ré-écriture dans des chaînes de caractères)

Pour ton info, pl/sql accepte très bien les chaînes de caractères multilignes :

Code:

---

1 2 3 4 5 6

ma_variable := ' select * from user_tables where table_name like ''%TOTO%'' order by table_name';

---

J'ai bcp de proc qui fonctionne suivant ce principe. Une fois que ma requête est testée, je double tous les guillemets (' devient '') et je colle ça dans une variable avant de faire un execute immediate.

8O mais c'est génial ça :ccool:

Sauf qu'avec le execute immediate y'a un hard parse à chaque fois, non ?
Le moteur sql doit revérifier la chaine de caractère, les bounds variables, etc...

Sauf erreur de ma part, mais Oracle définit le plan d'exécution de la requête à chaque exécution, entre autre pour tenir compte des différences de statistiques, surtout depuis l'introduction des histogrammes.

Peut etre que l'optimiseur stocke une partie du parsing de la requête à la compilation, mais ce n'est pas ça qui va être le plus consomateur (analytise syntaxique puis sémantique... assez basique comme traitement et temps en complexité linéaire)
mais plutôt la définition du plan d'exécution (permutation des différentes tables / possibilités... complexité probablement en N^2 ou au mieux en N ln N).

De plus, les plans d'exécutions des dernières requêtes sont stockées dans la SGA, donc ce n'est que la première fois qu'UN utilisateur va lancer la requête que ça va être parsé.

un NVL(ma_variable,ma_colonne) va donner quelques chose de pas trés cool coté optimiseur... Je suis curieux de savoir combien il fait de permutation pour le déterminer... (au fait, quelqu'un sais comment le mesurer ?)

Pour éviter le re-parsing et re-calcul du plan, il faut faire attention d'utiliser des variables BIND et non des constante en dur dans la chaine.

En ce qui concerne le NVL, à mon avis, c'est le contraire (mais là j'ai un doute avec la 10g). Le fait d'avoir une fonction comme nvl simplifie la tache du CBO puisque un champ accédé par une fonction discalifie tout index d'autorité donc ça va faire beaucoup moins de permutations.

De toutes façons, y'a toute une cuisine interne pour optimiser l'optimiseur, il a des règles (secrètes...) pour éliminer hypothèses dés le départ. Il ne calcule tout que lorsqu'il n'y a très peu de tables dans la requête.

Mais avant d'entrer dans ces considérations, il faut savoir sur quelle échelle on se situe. Si on est à quelques dizaines de requêtes longues, alors pas la peine de se soucier plus que ça du parsing et du calcul de plan. Si on est à plusieurs milliers de requêtes courtes, là oui il faut s'en préocuper.

PS: moi aussi j'aimerais bien savoir comme tracer le nombre de permutations effectuées par l'optimiseur. Le problème c'est que c'est un peu la boite noire ce qui se passe à l'intérieur. Sur sybase il y a un mode de trace de l'optimiseur ou on voit tous les détails de calcul qui l'ont amené à effectuer le choix de son plan d'exécution, c'est vraiment le top. Je ne sais pas si un tel mode de trace existe sur oracle...

J'ai vu une fois (et ça m'a fait tout drole :aie: ) l'optimiseur partir en vrille sur une requête complexe, et mettre 5 minutes rien que pour le calcul du plan. J'avais contourné le problème en baissant à 1000 le paramètre session OPTIMIZER_MAX_PERMUTATION juste avant la requête.

Citation:

---

Envoyé par remi4444

Pour éviter le re-parsing et re-calcul du plan, il faut faire attention d'utiliser des variables BIND et non des constante en dur dans la chaine.

---

ça dépend de la valeur du paramètre cursor_sharing. Il me semble bien qu'en SIMILAR il remplace les valeurs en dur par des binds ;)

Citation:

---

Envoyé par Fred_D

ça dépend de la valeur du paramètre cursor_sharing. Il me semble bien qu'en SIMILAR il remplace les valeurs en dur par des binds ;)

---

Oui, ou meme 'FORCE' qui est encore plus radical (SIMILAR, il choisi si c'est pertinant ou pas...)

Effectivement avec ce paramètre oracle reconstitue préalablement une requête bind à partir d'une requête avec des constantes en dur. Cependant en activant ces paramètre, on solicite un peu de CPU supplémentaire à chaque fois. Tant qu'a faire, si on a la main sur ses requêtes autant faire du BIND et laisser ce paramètre à 'FALSE'

Effectivement, le parsing de la requête est un peu hors sujet au vu du problème...

attention : les variables de bind implique pas d'histogrammes !!!

Sinon, avec les execute immediate et les open..., on peut aussi utiliser le mot clé using pour mettre une variable

Bonjour

c'est vrai que depuis que nous avons migré la prod en 10g, (dev en 10g deja)... on a des trucs bizarre dans le genre de ce NVL qui est beaucoup plus long que le decode (mais pas dans toutes les requetes...)
c'est la 2eme fois que ca arrive..

enfin... pour l'instant on a mis le decode... ca marche et c'est rapide..


mais je trouve etrange que des fonctions de base du sql comme nvl et decode soient si differente...
surtout que le plan d'execution est identique.

attention au plan d'exécution, il peut être très différent pendant l'exécution par rapport au plan calculé "hors contexte" notamment à cause d'une éventuelle parallélisation de la session. Ca m'est arrivé il y a peu de temps ;)

Très bonne idée ;)

Tu peux forcer ou inhiber le parallélisme avec alter session

Code:

---

alter session force parallel query

---

ou

Code:

---

alter session disable parallel query

---

. Il y a bien sur les variantes pour le DML / DDL / ...

Attention : le force parallel query ne sera effectif que s'il y a suffisement de process parallel disponible au moment de l'exécution.

Dans le cas d'une requête paralléle, l'optimiseur sera beaucoup plus tenté de faire des full table scan (lecture de la table en totalité) plutôt que de passer par un index.