Accueil Nos publications Blog Les Binds Variables Oracle

Les Binds Variables Oracle

Mots clés bind variablesL’expression “il n’est pas nécessaire de réinventer la roue” est bien connue du monde informatique et Oracle suit également cette vieille maxime avec l’introduction des Bind Variable. Ce sont des variables dont le but est de permettre au développeur de pouvoir écrire des requêtes SQL qui partagent leur propriété. Cet article va vous exposer le principe de cette notion et son utilité dans l’écriture d’applications OLTP.

Soft Parse & Hard Parse

Avant de comprendre l’utilisation des binds variables, il faut déjà comprendre le fonctionnement du moteur Oracle face à la requête d’un utilisateur.

Schéma d'exécution d'une requête

Lorsque qu’un utilisateur exécute une requête, le moteur Oracle va la parser (la lire dans le langage oracle) avant de l’exécuter et de renvoyer son résultat.

On distingue alors deux types de parse (lecture).

Le hard parse qui s’effectue lorsque le moteur Oracle fait face à une requête SQL dont le code SQL n’est pas connue de sa zone de mémoire partagée, la shared pool.  Il consiste à l’enchainement des tâches suivantes :

  • Vérification syntaxique de la requête

  • Vérification que l’utilisateur qui lance la requête a bien tous les droits requis

  • Et enfin, l’allocation dans la shared pool d’un espace appelé curseur afin d’y stocker, entre autre, le code SQL

A l’issue des ces étapes, le moteur Oracle va rechercher quelles sont les opérations à effectuer afin d’accéder aux données souhaitées ; c’est le plan d’exécution. Pour cela, il utilise toutes les informations qu’il a sur les données : les indexes disponibles, les partitions, les statistiques etc…

Ce plan d’exécution sera écrit dans le curseur (en shared pool) avec le code SQL.

Le hard parse est très coûteux en terme de consommation CPU et d’acquisition des ressources de la zone de mémoire partagée.

Le soft parse est le second mode de lecture qui s’effectue lorsqu’Oracle fait face à une requête dont le code est connu de la shared pool.  Il partage avec le hard parse les étapes de vérifications syntaxique et de droit sur les objets requêtés mais il est bien moins consommateur que celui-ci car il va rechercher directement les informations déjà stockées dans le curseur de la shared pool.

Pour qu’une requête soit connue de la zone de mémoire partagée il faut une mémoire suffisamment grande afin de pouvoir conserver un assez grand nombre de requêtes mais également que la base n’ait pas été redémarrée.  En effet, chaque arrêt/relance de l’instance vide la mémoire de celle-ci.  Il faut également que ladite requête soit exactement identique à sa consœur qui l’a précédée.

Exemple :


select * from consultants;
SELECT * FROM CONSULTANTS;
select * from consultants where prenom='Olivier';
select * from consultants where prenom='Christophe';

Toutes ses requêtes ne sont pas les mêmes du point de vue du moteur Oracle.

Pour que deux requêtes soient identiques il faut donc :

  • qu’elles aient exactement la même syntaxe au caractère près

  • que les noms des objets (tables, vues, etc..) utilisés correspondent bien aux mêmes objets d’un même schéma

  • que les variables d’environnement NLS (la “localisation” : pays, langue, format de date etc…) soient les mêmes

  • que le type des binds variables soit le même

Remarque : Ce sont vos DBA préférés qui décident en général de la taille de l’espace mémoire, la SGA, qui contient la shared pool.

Les bind variables

Les binds variables sont donc des variables Oracle permettant d’écrire des requêtes dont le code SQL et le plan d’éxécution sont réutilisables avec des valeurs de variables différentes. Pour une utiliser une bind variable il faut d’abord la déclarer afin de préciser son type et son nom, puis lui donner une valeur et enfin l’appeler en précèdant son nom par le caractère “:”.

Exemple :

En reprenant l’exemple précèdent sur la recherche des informations d’un consultant sur la table CONSULTANTS, le code SQL devient le suivant :


variable prenom varchar2;
execute :prenom := "Olivier";
select * from consultants where prenom = :prenom;
execute :prenom := "Christophe";
select * from consultants where prenom = :prenom;

Ainsi quelque soit la valeur de la variable, pour peu qu’elle respecte son type, la requête sera connue de la shared pool tant que celle-ci n’est pas vidée et le moteur Oracle réutilisera le plan d’éxécution contenue dans la shared pool permettant ainsi des gains de performances.

Les bind variables et le bind peeking en 10G

Il existe de plus, à partir de la version 10G d’Oracle, le mécanisme de Bind Peeking consistant à aller chercher la valeur de la bind variable d’une requête à la première occurrence de celle-ci (après un redémarrage de la base par exemple) et à créer le plan de d’exécution dessus. Ce mécanisme implique donc que le plan d’exécution qui sera attribué à la requête SQL avec la valeur de cette variable sera partagé pour toutes les autres occurrences de cette requête même avec des valeurs différentes de la variable.

L’utilisation des binds variables nécessite donc de bien connaître le fonctionnel mais également la distribution des valeurs d’une colonne.

En effet, disons que dans notre table CONSULTANTS, la colonne PRENOM soit constitué à 75% de Olivier et à 25% de Christophe et qu’il existe un index sur cette colonne.

Une requête comme celle de notre précèdent exemple peut avoir deux plans d’exécution différents selon le prénom. Sans rentrer trop dans les détails, Oracle préférera logiquement passer par l’index pour rechercher les informations sur les consultants se prénommant Christophe et s’en passer pour ceux se prénommant Olivier, ceux-ci constituant les 3/4 de la table.

Si Lundi, après démarrage de l’instance, on exécute en premier la requête avec la variable à ‘Christophe’ voici ce qu’il se passe :
– Oracle lit la requête, il ne la connaît pas (hard parse), lit la valeur de la variable (bind peeking), et choisit le plan d’exécution par l’index. Très bien, la requête est rapide ; le plan d’exécution est optimal.
– Malheureusement, toute la journée de lundi, on exécute principalement la même requête mais cette fois avec la variable à ‘Olivier’. Oracle lit la requête, il la connaît (soft parse) et réutilise le plan calculé. Échec, il va utiliser un plan peu efficace en passant par l’index.

Nous voyons donc bien avec cet exemple que l’utilisation des binds variables avec ce mécanisme peut dégrader les performances d’une base et il est conseillé en général d’étudier avec vos DBA préférés si ce mécanisme de bind peeking doit être conservé pour votre application.

Remarque : Afin de palier au problème évoqué que l’on peut rencontrer avec le bind peeking, Oracle a introduit en 11G l’adaptative cursor sharing qui est une fonctionnalité permettant de conserver différents plans d’une requête utilisant des binds variables.

Remarque 2 : Il est possible de forcer le “bindage” des requêtes pour des applications n’utilisant pas de binds variables dans leur requête SQL en positionnant le paramètre CURSOR_SHARING à SIMILAR ou FORCE. C’est un paramètre de base délicat que seul votre DBA peut modifier. Il doit être modifié qu’en dernier recours car il a beaucoup d’impact sur les performances de votre base.

Conclusion

Avec le grand nombre actuel de framework de persistence de données tels que Hibernate ou Entity Framework, il sera aisé pour un développeur de ne plus toucher au code SQL de son application. Mais nous venons de voir une bonne pratique d’écriture de requête compatible avec ceux-ci et permettant d’éviter les problèmes de performances d’une application. L’utilisation des binds variables est une bonne pratique à utiliser sans parcimonie pour les applications OLTP.