Extended cursor sharing

Jusqu'à la version 11g, le mécanisme dit de peek bind variable posait parfois des problèmes lorsque la sélectivité des différentes valeurs des bind variables était différente. Désormais, le mécanisme dit d'extended cursor sharing permet de corriger ce défaut après quelques exécutions.

Pour illustrer ce nouveau mécanisme, on crée une nouvelle table:
create table adapt_curs_share (
   idf number,
  date_ins date,
  chaine varchar2(80),
  nombre number not null )
tablespace 'nom_tablespace';

create sequence seq_adapt_curs_share
increment by 1
start with 1
;  

create index idx_adapt_curs_share_nombre on adapt_curs_share(nombre) tablespace 'nom_tablespace';

On l'alimente de la manière suivante:
begin
   for i in 1..100000
   loop
      insert into adapt_curs_share
      (idf, date_ins, chaine, nombre)
      values
      (seq_adapt_curs_share.nextval, sysdate, dbms_random.string('P', 80), abs(mod(dbms_random.random(),1000 )));
   end loop;
  
   commit;
end;

On calcule les statistiques et on nettoie le shared pool :
dbms_stats.gather_table_stats(
ownname => 'schéma',
tabname => 'adapt_curs_share',
method_opt=>'for all columns size 1',
cascade => true);

alter system flush shared pool;

Avant d'ouvrir la session sous DOS,  il est souhaitable de positionner le bon jeu de caractères:

On positionne le niveau le plus élevé pour les statistiques afin de recueillir plus d'informations au niveau des plans d'exécution:
alter session set statistics_level = all;

Une fois la configuration mise en place, on exécute sous sqlplus les instructions suivantes:
variable n number
exec :n := 999
select count(idf) from adapt_curs_share where nombre < :n; ( résultat: 99893 )
exec :n :=3
select count(idf) from adapt_curs_share where nombre < :n; ( résultat: 308 )

Le plan d'exécution de la requête est le suivant: 

Le contenu de la bind variable est 999, la valeur utilisée par la première exécution de la requête. Etant donné la faible sélectivité de la requête, un access full est effectué sur la table adapt_curs_share. La deuxième requête, bien que très sélective, va utiliser le même plan d'exécution, d'où l'idée d'améliorer le mécanisme de peek bind variable via le concept d'extended cursor sharing.

Dans la vue v$sql, les colonnes is_bind_sensitive ( utilisation du peek bind variable ), is_bind_aware ( utilisation de l'extended cursor sharing ) et is_shareable ( curseur partagé ou non ) donnent pour ce curseur le triplet (Y, N, Y).

Suite à ces deux premières exécutions, on effectue les opérations suivantes:

exec :n :=3
select count(idf) from adapt_curs_share where nombre < :n;
exec :n :=999
select count(idf) from adapt_curs_share where nombre < :n;

Pour le curseur 9fyq0vr5fh4dn, on observe désormais dans la vue v$sql trois fils au lieu d'un. Le fils 0 a exécuté les deux premières requêtes. Il utilise le mécanisme du peek bind variable, mais pas celui de l'extended cursor sharing et il n'est plus partageable.

Le fils 1 a exécuté la troisième requête ( bind variable = 3 ). Il utilise le peek bind variable, l'extended cursor sharing et il est partageable. Son plan d'exécution est le suivant;

Le fils 2 a exécuté la quatrième requête ( bind variable = 999 ). Il utilise le peek bind variable, l'extended cursor sharing et il est partageable. Son plan d'exécution est le suivant;

Si on exécute de nouveau la requête avec les deux valeurs 3 et 999, seuls les fils 1 et 2 sont utilisés.

Pour aller plus loin sur ce nouveau concept lié à la 11g, vous pouvez aussi consulter les vues v$sql_cs_statistics, v$sql_cs_selectivity et v$sql_cs_histogram.