Discussion :

[Zidmann]

Bonjour,

Je souhaiterais savoir si lorsque j'exécute un script ou appelle une fonction (ou une procédure) en PL/SQL, selon quelles conditions l'instance binde automatiquement les variables utilisées dans mes requêtes ?

Je précise :
-J'utilise une variable PL/SQL dans certains exemples appelée SQL_TXT pour stocker les requêtes.
-Si j'écris directement dans la chaîne SQL_TXT les valeurs explicites des variables que j'utilise en réalisant de simple concaténation (||ma_variable||), je sais que les variables ne sont pas bindées quand je fais un EXECUTE IMMEDIATE SQL_TXT;
-En revanche si dans mon buffer SQL_TXT, je place des ':' devant mes variables de ma requête, en réalisant un EXECUTE IMMEDIATE SQL_TXT USING <liste de variables de ma fonction ou procédure> alors mes variables sont automatiquement bindées ce qui évitera à l'instance de la base de données de redéterminer le plan d'exécution.

La question que je me pose et que si dans ma fonction j'ai une requête qui est appelée directement, c'est-à-dire sans utiliser de EXECUTE IMMEDIATE, est-ce que lorsque la requête utilise des variables de ma fonction pour vérifier les conditions sur certaines colonnes, l'instance binde automatiquement les variables ?

J'ai une procédure P1 qui utilise un curseur C1 définit sous cette forme :
CURSOR C1(var1, var2, ..., varN) IS
SELECT <liste_de_champ> FROM <Mes tables> WHERE Tab1.Champ1=var1 AND ...;
Est-ce que mes variables sont bindées automatiquement dès le premier appel de cette requête ?

Lorsque en revanche j'utilise là requête suivante ci-dessous pour faire des mises à jour dans une table est-ce que les variables sont aussi bindées ?
J'ai des variables var1, var2, ..., varN définit dans ma fonction et dans le code source j'aurais :
UPDATE Tab1 SET Tab1.Champ1=var1, Tab1.Champ2=var2, ... WHERE Tab1.ChampN=varN;

Lorsque j'ai affiché les rapports AWR j'ai pu voir le SELECT de mon curseur et l'UPDATE de la fonction. Quand je demandais d'afficher le texte qui allait avec le SQL_ID, Oracle avait mis des ':' devant les variables varX. Mais est-ce que celà veut dire que les variables ont bien été bindées dès le premier appel des requêtes ?

Merci d'avance de vos réponses,

Zidmann

[pachot]

Bonjour,
Oui, c'est le moteur PL/SQL qui utilise des binds variables lorsqu'il exécute une requête. Donc pas de soucis: tout ce qui est variable pl/sql est passé en bind variable.
Cordialement,
Franck.

[Mohamed.Houri]

Lorsque vous utilisez du PL/SQL statique il y a un "binding" automatique. C'est la nature même du PL/SQL lorsqu'il est statique. Concrètement, dans votre procédure P1 (qui semble ne pas avoir de paramètre) telle que vous l'avez définie bénéficiera de cet "auto binding". Il faut, par contre, faire attention à l'appel de ce genre de procédure (ok, ici vous n'avez pas de paramètre) mais lorsque vous utilisez des procédures avec des paramètres il faut veiller à ce que le programme qui appelle cette procédure passe les paramètres de cette procédure comme "bind variables". Autrement, votre SGA-Library cache sera rempli d'appel à votre procédure.

Par contre, lorsque vous utilisez du SQL dynamique vous perdez cet avantage. Vous devez vous même veiller à la bonne utilisation des variables de liaisons à l'intérieur du code de votre procédure P1. Vous y ajoutez des concaténations qui rendent votre application vulnérable devant une attaque du type "SQL injection". Évitez donc d'utiliser le SQL dynamique lorsque cela est possible.

Vous avez un peu de lecture à ce sujet ici et ici

En passant, les variables de liaison ont pour principal but de faire en sorte que des instructions SQL identiques mais qui diffèrent uniquement par les variables, paraissent à l'optimisateur comme une instruction identique permettant le partage des ressources. Il se trouve que parmi ces ressources partagées existe le plan d'exécution (child_cursor). Le but principal des variables de liaison n'est donc pas d'éviter de ré-optimiser la requête; c'est même un désavantage qu'Oracle essaie de corriger par le "bind variable peeking" qui a lieu au moment du "hard parse" (quoi que j'ai appris hier que le bind variable peeking a lieu même en l'absence d'un "hard parse") et de "Adaptive Cursor Sharing" qui lui, a l'avantage de ne pas partager le même plan d'exécution, mais a l'inconvénient de générer plusieurs child_cursor.

[mnitu]

L’utilisation des variables des liaisons, le mécanisme d’introspection des valeurs des variables de liaison (peek variable binding) et l’adaptive cursor sharing ne sont que l’évolution des solutions imparfaites d’Oracle dans le traitement d’une unique problématique : comment faire pour trouver le « meilleur » plan d’exécution d’une requête en consumant le moins possible des ressources système.

Pour mieux comprendre regardez l’historique :

• Oracle 6 : introduction des variables des liaisons, optimiseur basée sur des règles
• Oracle (7) 8 : basculement progressif vers l'optimiseur basé sur le coût
• Oracle 9 : peek variable binding
• Oracle 11 : adaptive cursor sharing (ne fonctionne pas en PL/SQL)
• Oracle 12 : Adaptive Execution Plans, etc