Oracle 11g offre la possibilité, « backportée » en 10g, de supprimer un curseur de la shared pool à l’aide de la commande dbms_shared_pool.purge. Cette fonctionnalité permet, entre autre, de forcer le reparsing d’un seul ordre SQL et, ainsi, la collecte de nouvelles valeurs de bind dans V$SQL_BIND_CAPTURE.

Bien sur, ce n’est pas neutre. Vous prenez le risque que le plan régénéré change, même avec les même statistiques. Vous prenez également le risque que l’impact du reparsing ne soit pas neutre. Enfin, c’est toujours mieux que de modifier une table et ainsi d’impacter des dizaines ou centaines de curseurs…

Cet article présente l’utilisation de cette procédure

Un ordre et 2 versions de curseur

Pour notre exemple, nous allons générer 2 curseurs à partir du même ordre SQL :

create user demo
  identified by demo 
  default tablespace users 
  temporary tablespace temp;

grant connect, resource to demo;

connect demo/demo
create table T (id varchar2(10));

select /* arkzoyd */ id
  from t
 order by 1;

alter session set nls_sort=french;

select /* arkzoyd */ id
  from t
 order by 1;

connect / as sysdba

set tab off
col sql format a40
col sql_id format a15 new_value sql_id
select sql_id, substr(sql_text,1,40) sql
  from v$sql 
 where regexp_like(sql_text,'a[r]kzoyd');

SQL_ID          SQL
--------------- ----------------------------------------
fs5fn9nbr8vtt   select /* arkzoyd */ id   from t  order
fs5fn9nbr8vtt   select /* arkzoyd */ id   from t  order

Supprimer le curseur

Vous pouvez collecter l’adresse et la valeur de hash du SQL pour supprimer les curseurs associés. Vous pourrez vérifier qu’ils disparaissent de la shared pool :

col name new_value name 
select sql_id, address||','||hash_value name
  from v$sqlarea 
  where sql_id like '&sql_id';

SQL_ID          NAME
--------------- -------------------
fs5fn9nbr8vtt   4F442E14,393506617

exec dbms_shared_pool.purge('&&name','C')

col sql format a40
col sql_id format a15 new_value sql_id
select sql_id, substr(sql_text,1,40) sql
  from v$sql 
 where regexp_like(sql_text,'a[r]kzoyd');

no rows selected

Et un peu plus…

Supprimez l’utilisateur DEMO de la base de données

drop user demo cascade;

Lisez ce qui suit :

• Bug 5614566: WE NEED A FLUSH CURSOR INTERFACE
• How To Flush an Object Out The Library Cache [SGA] Using The DBMS_SHARED_POOL Package [ID 457309.1]
• DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 [ID 751876.1]
• Tom Kyte : Restoring Stats and Execution Plans », version 10.2.0.3
• Kerry Osborne : Flush A Single SQL Statement
• Jetez un regard curieux sur les 2 paramètres suivants
  • cursor_bind_capture_destination (11.2.0.2)
  • _optimizer_invalidation_period