Lenteur de traitement en utilisant des bind variables

Bonjour à tous,

j'ai un traitement, qui s'exécute rapidement (1 minute) dans la plupart des cas, mais dans certains cas identifier, il dure trop longtemps (2 heures).

La requête posant problème est un select utilisant des bind variables.

En prenant la requête et en l’exécutant en remplaçant les bin variable par leurs valeurs, on obtient un résultat en une minute.

Le problème vient du plan d'exécution pris dans certains cas. (il pense qu'une table lui renvoi 1000 ligne, elle lui en renvoie 2 millions)

Connaissez-vous un moyen (un hint?) pour forcer l'optimiseur à choisir le bon plan d’exécution sans forcément figer ce plan (par exemple en valorisant les bind variables avant la recherche du plan d'exec)?

Quelle version d'Oracle ?

Quand une requête utilise des variables de liaisons (bind variables) Oracle utilise un mécanisme d'introspection de la valeur appelé bind variable peeking lors du parsing hard de la requête, c-est-à-dire la première exécution de la requête. Cela veut dire qu'Oracle va regarder quelles est la valeur actuelle des variables passées avec la demande de parsing de la requête et va élaborer le plan d'exécution en fonction de ces valeurs. Ensuite les exécutions suivantes de la requête vont utiliser ce plan.
Quand vous remplacez les variables des liaisons avec des valeurs en dur vous générez par conséquence une autre requête qui est ré-parsée hard et donc qui a un autre plan d'exécution.
Il est très probable que les données d'une partie des colonnes pour lesquelles vous utilisez des variables de liaisons ont des valeurs reparties d'une manière non-uniforme ce qui génèrent potentiellement des plans d'exécution différents en fonction de la valeur actuelle de la variable, parce que des histogrammes ont été collectées automatiquement.

Plusieurs solution sont envisageables en fonction de la situation concrète, qui reste à analyser
- supprimer les histogrammes pour certaines colonnes et inhiber leur collecte automatique
- ne pas utiliser des variables de liaisons pour certaines colonnes si possible
- figer le plan d'exécution
...

Merci de m'aider.

Version Oracle 11g.

Dans cette requête, il n'est pas possible de se passer des bind variables, et je ne souhaite pas figer le plan d'exécution (j'ai toujours eu des problème de changement de volumétrie quand j'ai fait ça).

Je vais regarder du coté de la suppression des histogrammes. (à moins que vous n'ayez d'autres réponses ou pistes)

Hello,
vérifier si le plan d'exécution ne donne pas l'indication : cardinality feedback is used.
Dans ce cas il faut désactiver cette fonctionnalité qui peut provoquer des dégradatations lors de la 2nde exécution de la requête (déjà constaté à 2 reprises chez 2 clients)
Hinter la requête avec : opt_param('_optimizer_use_feedback','FALSE')

Si ce n'est qu'un simple problème de bind, oracle déconseille parfois l'usage du bind peeking (sous PeopleSoft par exemple) .
Dans le cas il faut hinter avec /*+ opt_param('_optim_peek_user_binds','FALSE') */ ou setter le paramètre au niveau DB.

D'une manière générale évitez d'utiliser des paramètres cachés ils sont réservé à l'assistance d'Oracle.
Si vraiment il y a un problème du au mécanisme de "cardinality feedback" il faut analyser les causes avant d'envisager des solutions. Mais il y a moins des chances que c'est ça d'après Oracle pour ce cas.

Citation:

---

In cardinality feedback, we limit the feedback to portions of the plan whose estimates are not affected by bind variables. For instance, if a table has a filter comparing a column to a bind value, the cardinality of that table will not be used. However, cardinality feedback can still be used for cardinalities elsewhere in the plan.

---

Inhiber le mécanisme du peek binding au niveau de la base c'est du "carpet bombing" donc à éviter!

Je n'ai pas vu de cardinality feedback sur le plan d'exécution. Pour tester, j'ai lancé le traitement en rajoutant le bind variable et cela n'a pas changé le plan.

Merci pour la piste, même si elle semble ne pas être la bonne.

Vérifiez également dans v$sql_shared_cursor les requêtes ayant use_feedback_stats égal à Y

Citation:

---

Envoyé par icsor

Connaissez-vous un moyen (un hint?) pour forcer l'optimiseur à choisir le bon plan d’exécution sans forcément figer ce plan (par exemple en valorisant les bind variables avant la recherche du plan d'exec)?

---

Vous pouvez utiliser le hint cardinality pour augmenter le volume de la table pris en compte par l'optimiseur.
Ça n'influera que sur votre requête.

L'Adaptive Cursor Sharing permet en théorie d'adresser ce genre de problématique.

Un peu de lecture qui peu convenir à votre situation, même si c'est généralement plus complexe dans la vrai vie...

How do I force a query to use bind-aware cursor sharing?

Adaptive Cursor Sharing in Oracle Database 11g Release 1

Le problème avec Adaptive Cursor Sharing est que "d'abord il faut souffrir avant de guérir". Autrement dit, il faut que la requête s'exécute pendant deux heures avant que l'optimiseur décide de la ré-parser. Vu l'écart 1 minute vs 2 heures il faut fouetter des autres chats.

Citation:

---

Envoyé par mnitu

Le problème avec Adaptive Cursor Sharing est que "d'abord il faut souffrir avant de guérir".

---

Ok, c'est pas faux.

Citation:

---

Envoyé par icsor

Dans cette requête, il n'est pas possible de se passer des bind variables

---

Pouvez-vous préciser pourquoi il est impossible de se passer de bind variable ?

Les bind variables sont particulièrement utiles (et donc généralement considéré comme une bonne pratique) car :
1/ Elles évitent de facto les injections sql
2/ Elles évitent de parser inutilement des requêtes et de pourrir le shared pool.

Mais en cas de répartition de données non uniforme elles peuvent s'avérer peu performantes pour certaines valeurs.

1/ En fonction du type de donnée il peut être extrêmement simple de se prémunir du risque d'injonction sql, et même si la colonne est de type texte il est toujours possible de valider en amont.
2/ Si la requête n'est exécuté que périodiquement, il n'est pas très problématique de reparser la requête
3/ Si le temps d'exécution de la requête est long (ici 1 minute) le temps de parse n'est que peu influant dans le temps total d'exécution.

Pour moi vous êtes potentiellement dans le cas où il est intéressante de ne pas utiliser de variable de liaison pour cette colonne spécifique.

Bien entendu leur utilisation reste nécessaire dans de très nombreux cas, mais pas obligatoirement tous.

Pouvez-vous également préciser le contexte, OLTP, batch, BI par exemple ?

Déjà merci à tous.

@Mnitu : pour ma requête, dans v$sql_shared_cursor le champs use_feedback_stats est égal à N

@Waldar : l'augmentation de la cardinalité par le hint ne change pas le problème (ou alors je le fais mal)

@skuatamad : merci de la doc, je la lirai et l'essayerai demain

@skuatamad : pour les binds variables, c'est la solution qui est parti en prod (par manque de temps et de compétence), mais j'aimerai comprendre un peu mieu Oracle et du coup, je passe de temps en temps mes midis sur des problèmes du genre ;)

Le context est une utilisation dans un repport Oracle.

Donc pas de problème de cardinality feedback. Avez-vous regarder les histogrammes ? Est-ce que les prédicats utilisés dans la requête sont vraiment indépendants ?

comment avez-vous résolu le problème ci-dessus car j'ai le même aujourd'hui :-( ?