Cela faisait quelque temps que je cherchais à améliorer les temps de réponse d'une application connectée à une base Oracle 11g R2.

L'advisor d'Oracle Enterprise Manager indiquait que l'application utilisait des littéraux dans ses requêtes et que l'usage de variables "bind" à la place de ces littéraux aurait pu diminuer la consommation de CPU et le temps d'exécution des requêtes. Seulement je n'avait pas accès au code de l'application en question et donc je ne pouvais rien y changer.

Pourquoi les variables bind permettent de gagner du temps ?

Parce qu'Oracle considère qu'une requête qui lui est soumise deux fois de suite avec des valeurs (littéraux) différentes revient à deux requêtes différentes. Il va donc passer du temps d'analyse (parsing) sur la deuxième, pour déterminer le plan d'exécution, alors qu'il a déjà fait le travail pour la première. De plus il va stocker en mémoire deux plans d'exécution très probablement identiques.
Avec des variables bind, la requête ne serait analysée qu'une fois et il n'y aurait qu'un seul plan d'exécution.

J'ai tenté d'utiliser le paramètre cursor_sharing en lui donnant la valeur "force", cela permet justement de forcer le remplacement des littéraux par des variables, malheureusement cela a provoqué des erreurs dans l'application et j'ai du revenir en arrière (cursor_sharing=exact).

Je pensais être bloqué jusqu'à ce qu'on me parle du paramètre "sessions_cached_cursor". Ce paramètre indique combien de requête Oracle conserve en mémoire par session. Les informations conservées en mémoire sont le texte de la requête et le plan d'exécution, cela ne concerne pas les données. Son augmentation (valeur par défaut 50, passé à 1000) à nettement améliorer les temps de réponse de l'application. Je n'ai pas noté d'augmentation significative de la consommation de sga ou de pga.

Remarque : Cette modification n'améliore les temps d’exécution des requêtes qu'à partir de la quatrième exécution, au lancement, l'application reste donc lente puis au fur et à mesure de l'utilisation, les temps s'améliorent. Donc c'est valable pour une application utilisée toute la journée. Pour une utilisation ponctuelle de quelques minutes cela risque d'être inutile.

Pour effectuer la modification :

alter system set sessions_cached_cursor=1000 scope=spfile;

Puis redémarrage de l'instance.