Comment vider des données en Shared Pool ? - How to empty data in Shared Pool ?

20 janvier 2020

Comment vider des données en Shared Pool ? - How to empty data in Shared Pool ?

Introduction
Parfois des données présentes en mémoire sur l'exécution d'une requête polluent nos tests. Par exemple on voudrait virer de la Shared Pool un plan d'éxécution pour forcer le CBO à en calculer un nouveau. Ou bien vider les données relatives à un SQL Id dans la vue V$SQL car celle_ci est une vues agrégée et pas une vue détail... Or comment faire si on veut les données de cette vue MAIS uniquement pour la dernière exécution de l'ordre SQL?

Nous allons voir trois méthodes, de la plus violente à la plus souple, pour répondre à ce besoin.

Points d'attention
La première méthode, celle avec la commande PURGE, ne DOIT PAS être utilisée en production sauf si vous êtes vraiment vraiment sur de vous.

Base de tests
Une base Oracle 18c.

Exemples
============================================================================================
Environnement de test
============================================================================================
On crée une nouvelle table avec les 20 000 premiers enregistrements de la table DBA_OBJECTS.

SQL> CREATE TABLE zztest AS SELECT * FROM dba_objects WHERE rownum < 20001;
Table created.

SQL> select count(*) from zztest;
COUNT(*)
----------
20000

On lui ajoute une colonne ID qui servira de primary key avec comme valeur la pseudo-colonne ROWNUM.
SQL> ALTER TABLE zztest ADD id number;
Table altered.

SQL> UPDATE zztest SET id = rownum;
20000 rows updated.

SQL> commit;
Commit complete.

SQL> ALTER TABLE zztest ADD CONSTRAINT pk_zztest PRIMARY KEY (id);
Table altered.

Génération des stats.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'HR' , tabname => 'ZZTEST', cascade => true);
PL/SQL procedure successfully completed.

============================================================================================
Qu'y a-t-il dans la Shared Pool?
============================================================================================
Je lance un SELECT avec un commentaire, pour facilement l'identifier, puis je récupère son SQL_ID.
SQL> select * /* TEST INDICATEUR */ from zztest;
...
20000 rows selected.

SQL> select sql_id, sql_text from v$sql where sql_text like '%INDICATEUR%';
SQL_ID SQL_TEXT
-------------------------------------
5c7a8nbrmjzcz select sql_id, sql_text from v$sql where sql_text like '%INDICATEUR%'
g29m41tavz3rn select * /* TEST INDICATEUR */ from zztest

Regardons la vue V$SQL pour avoir des infos sur son exécution.
SQL> select to_char(LAST_ACTIVE_TIME, 'DD/MM/YYYY HH24:MI:SS'), EXECUTIONS, ROWS_PROCESSED, CHILD_NUMBER, CPU_TIME, ELAPSED_TIME, BUFFER_GETS from V$SQL where SQL_ID = 'g29m41tavz3rn';
TO_CHAR(LAST_ACTIVE EXECUTIONS ROWS_PROCESSED CHILD_NUMBER CPU_TIME ELAPSED_TIME BUFFER_GETS
------------------- ---------- -------------- ------------ ---------- ------------ -----------
18/01/2020 12:05:46 1 20000 0 39989 119405 2703

Si on réexécute le même ordre : aïe, cette vue est une vue agrégée et pas une vue détail… Ce qui implique qu'il y a un cumul des données relatives à cet ordre.
TO_CHAR(LAST_ACTIVE EXECUTIONS ROWS_PROCESSED CHILD_NUMBER CPU_TIME ELAPSED_TIME BUFFER_GETS
------------------- ---------- -------------- ------------ ---------- ------------ -----------
18/01/2020 12:10:32 2 40000 0 42701 136901 4375

============================================================================================
Comment vider des données en Shared Pool ?
============================================================================================
Dans le cas où on a besoin de relancer cet ordre SQL et de voir les données relatives à la dernière exécution, on fait comment? Il existe certainement des vues détail MAIS si je veux absolument utiliser V$SQL car cette vue a exactement les colonnes que je recherche. Réponse : il faut vider les données des exécutions précédentes dans la Shared Pool, tout simplement!

Méthode 1 : purger COMPLETEMENT la Shared Pool avec la commande FLUSH
La première méthode consiste à utiliser la commande FLUSH sur la Shared Pool.

Avantages :
- on est sur de purger TOUTE la Shared Pool de tous les éléments relatifs à l'exécution d'un ordre SQL
- très facile à exécuter

Inconvénients :
- méthode brutale qui va ralentir la base car, tous les curseurs ayant été virés, tous les ordres SQL à venir devront repasser par l'étape lourde du parsing et du recalcul de N plans d'exécution. Donc à ne SURTOUT pas faire en production!
- pas assez précise dans le cas où seul un ordre SQL est concerné

SQL> alter system flush shared_pool;
System altered.

SQL> select to_char(LAST_ACTIVE_TIME, 'DD/MM/YYYY HH24:MI:SS'), EXECUTIONS, ROWS_PROCESSED, CHILD_NUMBER, CPU_TIME, ELAPSED_TIME, BUFFER_GETS from V$SQL where SQL_ID = 'g29m41tavz3rn';
no rows selected

Méthode 2 : utiliser un ordre DDL
Une autre façon de faire est d'exécuter un ordre DDL sur une des tables du FROM de notre SELECT.

Avantages :
- évite de vider complètement la Shared Pool
- très facile à exécuter

Inconvénients :
- TOUS les ordres SQL sur cet objet vont voir leurs données virées de la Shared_Pool, indépendamment du SQL Id. Il faudra donc recalculer les plans d'exécution de tous les SQL Id à venir sur cet objet. On peut effectivement avoir dix ordres différents sur une même table, donc dix SQL Id différents.

Exécutons deux ordres : l'un avec * et l'autre avec la colonne id. Il y aura deux SQL Id générés.
SQL> select * /* TEST INDICATEUR */ from zztest;
…
20000 rows selected.

SQL> select id /* TEST INDICATEUR */ from zztest;
…
20000 rows selected.

SQL> select sql_id, sql_text from v$sql where sql_text like '%INDICATEUR%';
SQL_ID SQL_TEXT
---------------------------------------------------------------------------------
5c7a8nbrmjzcz select sql_id, sql_text from v$sql where sql_text like '%INDICATEUR%'
g29m41tavz3rn select * /* TEST INDICATEUR */ from zztest
63hrwtzy4bhau select id /* TEST INDICATEUR */ from zztest

Comme ordre DDL on va faire un truc tout bête : un GRANT READ sur la table concernée à l'utilisateur SYSTEM. C'est indolore, on ne modifie pas la table, on ne fait pas d'ALTER TABLE, on ne touche pas aux données, c'est super rapide… que demander de plus?
SQL> GRANT READ ON hr.zztest TO SYSTEM;
Grant succeeded.

Bingo, les curseurs précédents ont été virés avec cet ordre DDL.
SQL> select sql_id, sql_text from v$sql where sql_text like '%INDICATEUR%';
SQL_ID SQL_TEXT
--------------------------------------------------------------------------
5c7a8nbrmjzcz select sql_id, sql_text from v$sql where sql_text like '%INDICATEUR%'

Méthode 3 : utiliser DBMS_SHARED_POOL.PURGE
Les deux méthodes précédentes sont quand même assez violentes, l'idéal est de faire de la micro-chirurgie : on ne veut remettre à zéro que les données d'un SQL Id et un seul. Solution : la fonction DBMS_SHARED_POOL.PURGE.

Avantages :
- seul l'ordre SQL concerné est impacté

Inconvénients :
- méthode un peu plus complexe

On relance l'ordre.
SQL> select * /* TEST INDICATEUR */ from zztest;
...
20000 rows selected.

Il faut d'abord récupérer les adresses mémoire du curseur de l'ordre SQL via les champs ADRESS et HASH_VALUE dans V$SQLAREA, puis utiliser DBMS_SHARED_POOL.PURGE.

SQL> select ADDRESS, HASH_VALUE, to_char(LAST_ACTIVE_TIME, 'DD/MM/YYYY HH24:MI:SS'), EXECUTIONS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS from V$SQLAREA where SQL_ID = 'g29m41tavz3rn';
ADDRESS HASH_VALUE TO_CHAR(LAST_ACTIVE EXECUTIONS ROWS_PROCESSED CPU_TIME ELAPSED_TIME BUFFER_GETS
---------------- ---------- ------------------- ---------- -------------- ---------- ------------ -----------
000000007B2AD260 1438617332 18/01/2020 12:35:59 1 20000 47270 122314 2978

SQL> exec sys.dbms_shared_pool.purge('000000007B2AD260, 1438617332','c');
PL/SQL procedure successfully completed.

Et voilà, succès total :-)
SQL> select ADDRESS, HASH_VALUE, to_char(LAST_ACTIVE_TIME, 'DD/MM/YYYY HH24:MI:SS'), EXECUTIONS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS from V$SQLAREA where SQL_ID = 'g29m41tavz3rn';
no rows selected