Il nous est tous arrivé d’écrire des requêtes qui font appel à des fonctions utilisateurs pour sortir des statistiques ou des rapports divers. Quand celles-ci sont exécutées plusieurs fois avec les mêmes paramètres, on peut se demander  : « Mais pourquoi Oracle ne met pas en cache le résultat de la fonction ? »

A partir de la version 11g, Oracle est capable de cet exploit. C’est ce qu’illustre cet article à travers un exemple simple qui va un peu plus loin que la démonstration…

Un schéma exemple

Pour illustrer cette fonctionnalité, on prendra l’exemple d’une étude sur plusieurs dizaines de milliers de notes collectées dans des villes de France, Allemagne et des US ; des notes que nous rammenons à [0-20]. Nous allons créer un schéma et un jeu de données à l’aide du script ci-dessous :

create table notes (
   id_eleve number, 
   id_ville number,
   num_note number,
   note     number);

create unique index idx_note_eleve 
           on notes(id_eleve, num_note);

create table eleves (
   id_eleve number, 
   id_ville number);

create table villes (
  id_ville number,
  ville varchar2(20),
  id_pays number);

create table Pays(
  id_pays number, 
  codePays varchar2(2));

Alimentons maintenant les tables des élèves, des villes et des pays :

insert into pays values (1,'FR');
insert into pays values (2,'US');
insert into pays values (3,'DE');
insert into villes values (1,'PARIS',1);
insert into villes values (2,'MARSEILLE',1);
insert into villes values (3,'LYON',1);
insert into villes values (4,'BREME',3);
insert into villes values (5,'MUNICH',3);
insert into villes values (6,'NEW-YORK',2);
insert into villes values (7,'CHICAGO',2);
insert into villes values (8,'BOSTON',2);
insert into villes values (9,'VEGAS',2);
insert into eleves 
   (select (v.id_ville-1)*25000+x.n,id_ville
      from (select rownum n 
              from dual 
           connect by level <=25000) x,
           villes v);
commit;

Ainsi que la table des notes :

insert into notes 
(select (v.id_ville-1)*25000+x.n, 
        v.id_ville,
        y.n,
  case when x.n between 1 and 5000 then 
         round(dbms_random.value(4,8))
       when x.n between 5001 and 15000 then
         round(dbms_random.value(8,12))
       when x.n between 15001 and 23000 then
         round(dbms_random.value(12,16))
       else
         round(dbms_random.value(16,20))
       end note 
  from (select rownum n 
          from dual 
       connect by level <=25000) x, 
       (select rownum n
          from dual
        connect by level <= 10) y,
       villes v);

commit;

Pour terminer on collectera les statistisques sur les différents objets:

exec dbms_stats.gather_table_stats(user,'PAYS',cascade=>TRUE);
exec dbms_stats.gather_table_stats(user,'VILLES',cascade=>TRUE);
exec dbms_stats.gather_table_stats(user,'ELEVES',cascade=>TRUE);
exec dbms_stats.gather_table_stats(user,'NOTES',cascade=>TRUE);

Une fonction pour le (mauvais) exemple

Nous voulons maintenant créer une fonction pour l’exemple. Il s’agit ici d’évaluer la moyenne des notes d’un élève en interrogeant la table NOTES. Evidemment, il ne s’agit que d’un exemple ; j’ai conscience que ce qui est fait est très mal… Est-ce que ça veut dire que vous ne rencontrerez pas ce type de cas dans votre base de données ?! Vous allez comprendre où je veux en venir.

Créons donc une fonction qui ramène la moyenne des notes pour un élève donné.

create or replace function f_moyenne_eleve (v_eleve number) 
                                return number is
   v_moyenne number;
begin
   select sum(note)/count(note) 
     into v_moyenne 
     from notes
    where id_eleve=v_eleve;
   return round(v_moyenne,2);
end;

Essayons maintenant de connaitre le nombre d’élèves américain ayant eu une note supérieure à 10.

set timing on
set autotrace on statistics
select count(*) 
  from eleves e, villes v, pays p
 where e.id_ville=v.id_ville
   and v.id_pays=p.id_pays
   and p.codepays='US' 
   and f_moyenne_eleve(e.id_eleve) > 10;

Le résultat met un peu plus d’une vingtaine de secondes sur ma machine :

COUNT(*)
----------
     57991

Elapsed: 00:00:26.43

Statistics
----------------------------------------------------------
     225000  recursive calls
   1  db block gets
     957646  consistent gets
   0  physical reads
   0  redo size
 528  bytes sent via SQL*Net to client
 523  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   1  sorts (memory)
   0  sorts (disk)
   1  rows processed

Le grand nombre d’appels récursifs n’est ni plus ni moins qu’en très grande majorité  les appels à la fonction f_moyenne_eleve.

Fonction Result Cache ?

25 secondes pour un résultat, c’est peu, ou pas ! Si votre utilisateur attend devant son écran, ça peut être beaucoup… Et nous pourrions avoir besoin d’afficher le nombre d’élèves ayant une moyenne supérieure à 11, 12, etc.

Evidemment, il faudrait écrire la requête correctement, mais le temps, la nécessité de reprendre le fonctionnel, le budget ne le permettent pas toujours… C’est pour ça que je vous propose de voir si la notion de fonction « Result Cache » d’Oracle 11g peut nous servir. Pas besoin de chercher longtemps pour comprendre que l’objectif d’Oracle est de réutiliser le résultat créé précédemment… Cela sous réserve que les données sous-jascentes à notre fonction n’aient pas été modifiées. La mise en place est très simple. Il suffit de recompiler la fonction en ayant pris soin d’ajouter le mot clé result_cache comme ci-dessous :

create or replace function f_moyenne_eleve (v_eleve number) 
                                return number result_cache is
   v_moyenne number;
begin
   select sum(note)/count(note) 
     into v_moyenne 
     from notes
    where id_eleve=v_eleve;
   return round(v_moyenne,2);
end;
/

Si on s’arrête là, la surprise peut être de taille ; non seulement la première exécution est bien plus lente :

set timing on
set autotrace on statistics
select count(*) 
  from eleves e, villes v, pays p
 where e.id_ville=v.id_ville
   and v.id_pays=p.id_pays
   and p.codepays='US' 
   and f_moyenne_eleve(e.id_eleve) > 10;

  COUNT(*)
----------
     57991

Elapsed: 00:01:19.12

Statistics
----------------------------------------------------------
     225040  recursive calls
          1  db block gets
     957650  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Mais la seconde exécution que nous attendions bien plus rapide puisque les ré
sultats sont sensés être en « cache » ne montre aucune amélioration, ni en temps de réponse, ni en nombre d’appels récursifs :

/
  COUNT(*)
----------
     57991

Elapsed: 00:01:19.12

Statistics
----------------------------------------------------------
     225040  recursive calls
   1  db block gets
     957650  consistent gets
   0  physical reads
   0  redo size
 528  bytes sent via SQL*Net to client
 523  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   1  sorts (memory)
   0  sorts (disk)
   1  rows processed

Ne Concluez Pas Trop Tôt !

Bof… Pas convaincant le cache de résultats. Pourtant, mon environnement de test est plutôt bien taillé par rapport à la taille de ma base. Je ne devrais donc pas avoir de problème de mémoire.

NAME                                TYPE        VALUE
----------------------------------- ----------- ------------------------------
sga_target                          big integer 5G
pga_aggregate_target                big integer 2G

Mais alors ou est le problème ? Interrogeons donc la vue v$result_cache_statistics :

set line 300
set pages 1000
select name,value
  from v$result_cache_statistics;

NAME                          VALUE
---------------------         ------
Block Size (Bytes)            1024
Block Count Maximum           3072
Block Count Current           3072
Result Size Maximum (Blocks)  153

Un début d’explication semble t-il : la partie du cache dédiée au cache de résultats peut contenir au maximum 3072 blocks de 1024 bytes soit 3 Mo ! Cela n’est pas suffisant pour stocker les quelques 225000 appels possibles de la fonctions.

Pour augmenter cette capacité, positionnez le paramètre result_cache_max_size. Sa valeur par défaut est un dérivé des valeurs de shared_pool_size, sga_target et memory_target. Toujours est-il qu’à la création mon instance avait un peu d’un 1Go d’alloué et que je me retrouve avec ce paramètre positionné à 3 Mo.

show parameter result_cache_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_size                big integer 3M

Modifions donc ce paramètre :

alter system 
   set result_cache_max_size=300M
   scope=both;

Relançons maintenant notre requête…L’impact du chargement du cache n’est toujours pas négligeable mais elle a été réduit :

set timing on
set autotrace on statistics
select count(*) 
  from eleves e, villes v, pays p
 where e.id_ville=v.id_ville
   and v.id_pays=p.id_pays
   and p.codepays='US' 
   and f_moyenne_eleve(e.id_eleve) > 10;

   COUNT(*)
----------
     57991

Elapsed: 00:00:42.99

Statistics
----------------------------------------------------------
     229058  recursive calls
   1  db block gets
     957437  consistent gets
       3288  physical reads
   0  redo size
 528  bytes sent via SQL*Net to client
 523  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
       2990  sorts (memory)
   0  sorts (disk)
   1  rows processed

Relançons une requête différente qui utilise la même fonction ; cette fois pour trouver le nombre d’élèves dont la moyenne est supérieure à 16 :

set timing on
set autotrace on statistics
select count(*) 
  from eleves e, villes v, pays p
 where e.id_ville=v.id_ville
   and v.id_pays=p.id_pays
   and p.codepays='US' 
   and f_moyenne_eleve(e.id_eleve) > 16;

COUNT(*)
----------
      8000

Elapsed: 00:00:02.99

Statistics
----------------------------------------------------------
        123  recursive calls
          1  db block gets
        574  consistent gets
          3  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
          1  rows processed

Magie du cache ! Plus aucun appel récursif  quasi plus d’I/O logiques. Le tout pour un résultat inférieur à 3 secondes. Et cette fois-ci, si je consulte la vue v$result_cache_statistics nous constatons bien que nous n’utilisons pas l’ensemble de la mémoire réservée au « result_cache »

set tab off
set autotrace off
set timing off
set lines 300
set pages 1000
col name format a35
col value format a15
select name,value 
  from v$result_cache_statistics;

NAME                                VALUE
----------------------------------- -------
Block Size (Bytes)                  1024
Block Count Maximum                 307200
Block Count Current                 225024
Result Size Maximum (Blocks)        15360
Create Count Success                670394

Pour Conclure

Avant de conclure, vous noterez quelques remarques à propos des fonctions et du cache de résultat :

• D’abord la première exécution est impactée du fait de l’utilisation du cache de résultat. Il ne vous a pas échappé que cette exécution passe de 25 à plus de 40 secondes. Cela est dû évidemment à la gestion du cache : je vous invite à prendre comme point d’entrée cet article d’Alex Fatkulin pour comprendre de quoi il s’agit. Une autre raison importante est dû au fait qu’en 11.2, Oracle trace l’ensemble des data sources utilisées par la fonction comme décrit dans la documentation. Attention donc à ne pas baser vos fonctions sur des sources de données volatiles au risque d’obtenir l’inverse du résultat escompté.
• La zone mémoire allouée au cache de résultat n’est pas propre aux fonctions mais sert également à conserver les données issue d’une requête avec le hint /*+ result_cache */. Le sizing est essentiel comme vous l’aurez compris à travers cet exemple
• La performance a un coût… Les fonctions « result cache » ne fonctionnent qu’en version Enterprise Edition.

Autrement dit, si le cache de résultat peut être un outil précieux, il convient toujours de tester et évaluer le gain avant de recompiler toutes les fonctions avec la clause « result_cache ». Et pour conclure, supprimez le schéma exemple :

drop table notes purge;
drop table eleves purge;
drop table villes purge;
drop table pays purge;