SESSION_CACHED_CURSORS

Avant d’en savoir plus sur ce paramètre d’instance il est nécessaire de faire un rappel sur le concept de Parsing des requêtes SQL dans Oracle et sur la notion de curseur.

Le parsing est la première étape dans le process d’exécution d’une requête SQL.
Durant cette étape Oracle effectue d’abord les tâches suivantes :
- Il vérifie la syntaxe de la requête
- Il vérifie que l’utilisateur exécutant la requête a bien les droits nécessaires pour accéder aux objets référencés.
- Il vérifie si cette même requête a déjà été exécutée auparavant et si un curseur partageable existe déjà dans la shared pool.

Si durant la dernière étape Oracle trouve un curseur partageable dans la SQL Area de la shared pool, alors il s’agit d’un SOFT PARSE c'est-à-dire qu’Oracle n’a pas besoin de demander au CBO de lui calculer un plan pour cette requête car il en existe déjà un en mémoire. Par contre s’il n’existe pas de curseur partageable alors le CBO doit calculer un plan pour la requête et stocker toutes les informations du nouveau curseur dans la shared pool pour qu’il soit réutilisable par la suite. Ce type de parsing est appelé HARD PARSE et est très gourmand en CPU.

Idéalement une requête SQL est hard parsée une seule fois lors de la première exécution, et uniquement soft parsée lors des exécutions suivantes. Toutefois le SOFT PARSE bien que beaucoup moins gourmand que le HARD PARSE consomme de la CPU et est également synonyme de latch au niveau de la « library cache ».

Comment faire mieux que le SOFT PARSE ?
C’est là où la notion de « SESSION CACHED CURSORS » entre en jeu.

Ce qu’il faut savoir c’est que lorsque la requête est exécutée la première fois un curseur est ouvert dans la mémoire privée de la session. Cette mémoire est appelée UGA et se trouve dans la PGA lorsqu’on est en mode DEDICATED SERVER ou dans la SGA lorsqu’on est en mode SHARED SERVER. Ce curseur ouvert côté session correspond à un pointeur vers le curseur de la shared pool contenant toutes les informations nécessaires à l’exécution de la requête et notamment le plan d’exécution.
A la fin de l’exécution de la requête, le curseur ouvert peut être mis dans le cache des curseurs fermés (toujours côté UGA). Quand la requête est réexecutée une recherche est effectuée dans le cache des curseurs fermés. Si le curseur existe il est remis dans le cache des curseurs ouvert et le pointeur vers le curseur de la shared pool est utilisé pour exécuter la requête. On évite le Hard Parse mais on évite aussi la vérification syntaxique et sémantique du SOFT PARSE. Oracle comptabilise cette méthode comme un soft parse même s’il est plus efficace que le SOFT PARSE classique. Tom KYTE appelle ce type de parsing le « Softer Soft Parse ».

Le nombre de curseurs cachables pour chaque session dans l’UGA est défini par le paramètre d’instance SESSION_CACHED_CURSORS qui est par défaut setté à 50 en 11g.

La statistique « session cursor cache count » dans V$SESSTAT nous donne le nombre de curseurs cachés dans l’UGA pour une session donnée.

La statistique « session cursor cache hits » indique le nombre de fois où un curseur a été trouvé dans le cache de session.

La statistique « parse count (total) » indique le nombre de « parse call » (HARD PARSE, SOFT PARSE ou SOFTER SOFT PARSE).

Il faut savoir que pour qu'un curseur soit placé dans le cache des curseurs fermés, il doit avoir été exécuté 3 fois. Le but étant d'éviter de mettre en cache des curseurs qui ne seront exécutés qu'une seule fois.

En se basant sur ces statistiques il est possible de construire un petit test case permettant de comprendre le fonctionnement des curseurs en cache.

Test case :
Dans Session 1:

SQL> select sid from v$mystat where rownum=1;

       SID
----------
       136

Depuis une autre session je regarde les stats existantes pour cette session :

SQL> SELECT sn.name, ss.value
  2   FROM v$statname sn, v$sesstat ss
  3   WHERE sn.statistic# = ss.statistic#
  4   AND sn.name IN ('session cursor cache hits',
  5   'session cursor cache count',
  6   'parse count (total)')
  7   AND ss.sid =136;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                               18
parse count (total)                                                      29

Le nombre de curseurs dans le cache de ma session est de 18 et aucun parse call n'a pour l'instant profité du cache des curseurs (session cursor cache hits ).

J'exécute une simple requête dans ma 1ère session:

SQL> select count(1) from t1;

  COUNT(1)
----------
       100

Je regarde les stats:

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                               18
parse count (total)                                                      31

Seul le nombre de « parse call » a bougé. J'ai toujours le même nombre de curseur dans le cache.

J'exécute une 2è fois ma requête:

SQL> select count(1) from t1;

  COUNT(1)
----------
       100

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                               18
parse count (total)                                                      32

Idem que pour la 1ère exécution.
J'exécute une 3ème fois la requête dans ma 1ère session puis je regarde les stats:

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                               18
parse count (total)                                                      33

Idem que précedemment.

J'exécute une 4ème fois la même requête:

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 1
session cursor cache count                                               24
parse count (total)                                                      34

Cette fois le nombre de curseurs dans le cache a bougé et surtout mon "session cursor cache hits" est passé à 1. Donc c'est uniquement à la 4ème exécution que le SOFT PARSE est évité au profit d'un « softer soft parse ».

Après la 5ème exécution:

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 2
session cursor cache count                                               24
parse count (total)                                                      35

Le "session cursor cache hits" est passé à 2.
En faisant le test pour 10 exécutions j'obtiens un "session cursor cache hits" de 7.
(10-7 = 3) donc 3 est bien le nombre d'exécutions nécessaires pour que le curseur soit caché dans l’UGA.

Quelle est la valeur optimale pour SESSION_CACHED_CURSORS ?
La réponse à cette question dépend de votre application.
Par défaut en 11g le paramètre est setté à 50. Si vous avez une base de donnée transactionnelle où les mêmes requêtes sont exécutées plusieurs fois, il est possible que 50 ne soit pas suffisant.
Pour en être sûr vous pouvez vérifier la valeur de la statistique « session cursor cache count » dans V$SESSTAT. Si pour certaines sessions la valeur de cette stat est de 50, c’est que surement ces sessions pourraient bénéficier d’un cache de curseur plus important.

Dans l’exemple ci-dessous je récupère pour une base (dont le paramètre SESSION_CACHED_CURSORS est à 100) les sessions dont le cache des curseurs a atteint le max :

SQL> SELECT ss.sid,ss.value
  2   FROM v$statname sn, v$sesstat ss
  3   WHERE sn.statistic# = ss.statistic#
  4   AND sn.name = 'session cursor cache count'
  5   and ss.value=(select value from v$parameter where name='session_cached_cursors');

       SID      VALUE
---------- ----------
       444        100
       451        100
       458        100
       460        100
       465        100
       466        100
       468        100
       471        100
       475        100
       493        100

10 rows selected.

SQL> select count(distinct sid) from v$sesstat;

COUNT(DISTINCTSID)
------------------
                45 

10 sessions sur 45 n’ont pu bénéficier d’un cache de curseur plus important.
On peut alors se demander si ça ne vaudrait pas le coup d’augmenter le paramètre SESSION_CACHED_CURSORS.
Le frein à tout ça c’est qu’en augmentant ce paramètre on aura besoin de plus de place dans l’UGA. Donc tout dépend des capacités en mémoire dont on dispose. Un juste équilibre doit être trouvé.


CONCLUSION :

Il est clair que le cache des curseurs permet de réduire le temps lié au parsing et donc de réduire le temps global d’exécution des requêtes SQL. De plus comme le fait de cacher les curseurs évite d’avoir à rechercher un curseur partageable dans la shared pool, cela réduit par le même effet les latchs posés au niveau de la library cache. Ca peut être très bénéfique pour les applications hyper transactionnelles en les rendant plus scalables.

Il est possible applicativement d’éviter les « parse call » et donc d’éliminer à la fois SOFT PARSE et HARD PARSE en utilisant par exemple les « prepared statements ». Mais il s’agit là d’un autre sujet.