Flusher un curseur de la shared pool avec DBMS_SHARED_POOL

Tout le monde connait la commande qui permet de vider la shared pool:
ALTER SYSTEM FLUSH SHARED_POOL;

Mais comment faire lorsqu'on veut que le CBO recalcule un nouveau plan et donc qu'il ne prenne pas en compte la plan déjà en cache? La solution consiste à vider le curseur de la shared pool en utilisant la nouvelle procédure PURGE du package DBMS_SHARED_POOL apparu avec la 10.2.0.4.

TEST CASE:

Créons une table contenant 1000 lignes avec une PK sur la colonne ID:

ALTER SYSTEM FLUSH SHARED_POOL;

DROP TABLE t;

CREATE TABLE t 
AS 
SELECT rownum AS id, rpad('*',100,'*') AS pad 
FROM dual
CONNECT BY level <= 1000;

ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

BEGIN
  dbms_stats.gather_table_stats(
    ownname          => user, 
    tabname          => 't', 
    estimate_percent => 100, 
    method_opt       => 'for all columns size 1'
  );
END;
/

Le plan de la requête suivante correspond à un FULL TABLE SCAN (FTS). Logique car on retourne 99% de la table:

VARIABLE id NUMBER

EXECUTE :id := 990;

SELECT count(pad) FROM t WHERE id < :id;

SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

Par contre le plan de la requête suivante va également me retourner un FTS même si on ne veut ici que 1% de la table. Un hard parse ayant déjà été effectué pour un même SQL_ID, le plan en cache est réutilisé pour cette requête même s'il n'est pas approprié:

EXECUTE :id := 10;

SELECT count(pad) FROM t WHERE id < :id;

SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic'));

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT AGGREGATE    |      |
|   2 |   TABLE ACCESS FULL| T    |
-----------------------------------

Pour flusher ce curseur de la shared pool sans avoir à flusher toute la shared pool on peut utiliser le package DBMS_SHARED_POOL:

SQL> @?/rdbms/admin/dbmspool

Package créé.

Autorisation de privilèges (GRANT) acceptée.

SQL> select address, hash_value from v$sql where sql_text = 'SELECT count(pad) FROM t WHERE id < :id';

ADDRESS  HASH_VALUE
-------- ----------
3C639C80 1107655156

SQL> exec sys.dbms_shared_pool.purge('3C639C80,1107655156','c');

Procédure PL/SQL terminée avec succès.

Le package DBMS_SHARED_POOL n'étant pas installé par défaut vous devez le faire en appelant le script DBMSPOOL qui se trouve dans %ORACLE_HOME%\RDBMS\ADMIN. Je vérifie maintenant que le curseur n'existe plus en mémoire:

SQL> select address, hash_value from v$sql where sql_text = 'SELECT count(pad) FROM t WHERE id < :id';

aucune ligne sélectionnée

Si je réexecute ma requête, le HARD PARSE a cette fois bien lieu et un plan prenant en compte mon index est utilisé:

SELECT count(pad) FROM t WHERE id < :id;

---------------------------------------------
| Id  | Operation                    | Name |
---------------------------------------------
|   0 | SELECT STATEMENT             |      |
|   1 |  SORT AGGREGATE              |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |
|   3 |    INDEX RANGE SCAN          | T_PK |
---------------------------------------------