Result caching

Dernièrement, j'ai testé cette fonctionnalité sur une instance 11.2.0.2. Il s'agit d'un cache supplémentaire de données situé dans le shared pool; il est partageable par toutes les sessions.

Lorsqu'une requête contient le hint result_cache ou depuis la 11.2, si on utilise l'instruction alter table nom_table  result_cache ( mode force ), le résultat des requêtes est placé dans ce cache.  Si une requête est bindée, il existe une entrée pour chaque n-uplet de bind variables. Pour la deuxième option ( alter table ), il peut être utile d'augmenter la taille initiale du cache: alter system set result_cache_max_size = x m scope=both.

A chaque fois qu'une table référencée dans une requête est modifiée, l'entrée correspondante dans le cache est invalidée et la requête s'exécute alors classiquement avant de créer une nouvelle entrée dans le cache avec les données mises à jour ( colonne build_time ). Afin d'optimiser le contenu du cache, la vue v$result_cache_objects permet de répérer les requêtes fréquemment invalidées ansi que celles bénéficiant d'un hit intéressant ( colonne scan_count ).

La vue v$result_cache_dependency permet d'identifier les tables utilisées par une entrée du cache. Puis, on peut trouver la dernière modification de type DML de la table via cette requête: select scn_to_timestamp(max(ora_rowscn) from nom_table. D'autres méthodes sont disponibles: utilisation de la fonction d'audit, alter table monitoring, v$sql, ...

Sur quelques exemples testés, le gain en performance est appréciable. Cependant, on peut regretter qu'il soit difficilement évaluable lorsqu'on décide d'associer une table au cache. En effet, la vue v$resultat_cache_objects stocke l'identifiant du cache ( colonne cache_id ) apparaissant dans le plan d'exécution et non l'identifiant de requête. Et la vue ne conserve que les 128 premiers caractères de chaque requête ...

La vue v$result_cache_statistics permet d'avoir une vue générale du fonctionnement du cache ( nombre d'entrées invalidées, nombre de hits, ... ) et de s'assurer que la taille du cache est correcte ( colonne Delete Count Valid ). On peut aussi obtenir un rapport de la mémoire du cache via la procédure dbms_result_cache.memory_report.

Pour finir, il existe le même type de fonctionnalité pour les résultats des fonctions PL/SQL.