Oracle : Comment réaliser un FLUSH d'une requète SQL en cours d'éxécution

Il nous est tous arrivé d'avoir à faire un kill de session ou encore un FLUSH de la SHARED_POOL sur une base Oracle en production après avoir identifié (par exemple) une charge anormale, laquelle serait provoquée par l'exécution d'un requête consommatrice, mais il est également possible d'être moins violent (même si certaines fois cela soulage) en réalisant un FLUSH de la requête en cours d'exécution, en agissant sur le contenu de la SHARED POOL.

L'objectif est d'éviter d’avoir à vider tout le contenu du cache, car l'impact peut être extrêmement important sur une base en activité. En effet, toutes les nouvelles requêtes vont devoir être re-parsées, et donc les plans d'exécution régénérés, ce qui pourrait provoquer une baisse des performances générale de la base de données.

C'est donc un des intérêts du package DBMS_SHARED_POOL, lequel permet (entre autre via la procédure PURGE) de réaliser le Flush d'un SQL.

Détails de la procédure PURGE de ce package :

DBMS_SHARED_POOL.PURGE(
name varchar2,
flag char default 'P',
heaps number default 1)

Le premier paramètre "NAME" correspond à l’ADRESS et à la HASH VALUE du curseur SQL, que l'on peut identifier en interrogeant gv$sqlarea, comme par exemple :

SQL> select address, hash_value from gv$sqlarea where sql_id ='d1gbxdavz12qu';

ADDRESS          HASH_VALUE
---------------- ----------
00000000F6A39F78 3085994714

Quant au second paramètre "FLAG", les valeurs possibles sont :

P Pour un package / procédure / fonction (valeur DEFAULT si non spécifié)
T Pour un type,
R Pour un trigger,
et Q pour une séquence.

On remarque cependant qu'il n'y a pas de paramètre concernant les curseurs SQL, mais pas d'inquiétude, car pour que cela fonctionne il faut positionner une valeur qui soit différente des valeurs indiquées ci-dessus. En d'autres termes, on peut positionner la valeur "C" pour curseur, ou "S" à la convenance de chacun.

Le troisième et dernier paramètre HEAPS permet de définir la position de "l'objet" à purger au sein de la mémoire (en format hexadécimal). Dans notre cas, on laisse la valeur défaut (soit 1) ce qui signifie l'intégralité de l'objet sera purgé (ce paramètre n'est donc pas à postionner pour flusher un SQL).

Voyons voir ce que cela donne, toujours pour mon SQL ID "d1gbxdavz12qu", en reprenant les informations identifiées plus haut via gv$sqlarea :

SQL> exec dbms_shared_pool.purge('00000000F6A39F78, 3085994714','C');

PL/SQL procedure successfully completed.

Ok, vérifions ensuite si notre requête a bien été purgée du cache :

SQL> select address, hash_value from gv$sqlarea where sql_id ='d1gbxdavz12qu';

no rows selected

Et voilà comment purger un simple SQL du cache sans pour autant avoir à killer la session responsable, ou encore vider l'intégralité de la shared_pool ;-)

Documentation officielle pour ce la définition et utilisation de ce package DBMS_SHARED_POOL ici

Enjoy ;-)