Automatic Big Table caching ?

On sait tous que Oracle se sert de sa mémoire pour placer les donnée et faciliter de cette manière l'accès aux données, mais on sait aussi que dans la plupart des cas c'est impossible de placer la base de données au complet en mémoire.

On sait aussi que des options keep et cache existent depuis un bon moment, mais maintenant Oracle propose une alternative intéressante et utile de faire ça d'une façon automatique.

Le but de cette fonctionnalité c'est de placer les tables assez grandes où des FULL SCAN est utilisé fréquemment. Ce "fréquemment" est appelé dans l'argot d'Oracle : Température  

Pour commencer il faudrait savoir c'est quoi une grosse table pour Oracle, pour le savoir il faudrait regarder 2 paramètres :

Le _small_table_threshold représente normalement le 2% de votre paramètre _db_block_buffers

SET PAGESIZE 500
COL KSPPINM FORMAT A40
COL KSPPSTVL FORMAT A30
SELECT KSPPINM, KSPPSTVL
  FROM X$KSPPI A, X$KSPPSV B
 WHERE A.INDX=B.INDX AND SUBSTR(KSPPINM,1,1) = '_' AND 
       KSPPINM IN ('_small_table_threshold','_db_block_buffers') ;

KSPPINM                                  KSPPSTVL
---------------------------------------- ------------------------------
_db_block_buffers                        528288
_small_table_threshold                   10565

Dans ce cas, une grosse table devra dépasser la valeur indiquée dans le _small_table_threshold pour être pris en compte par cette fonctionnalité d'Oracle.

Comme les tables seront placées en mémoire et on ne veut pas que toute notre mémoire soit utilisé pour ces objets en question, on a un paramètre à modifier afin de dire à notre base de données le maximum de mémoire -en pourcentage- à réserver pour ces objets.

SHOW PARAMETER DB_BIG_TABLE_CACHE_PERCENT_TARGET
NAME                                 TYPE        VALUE 
------------------------------------ ----------- ----- 
db_big_table_cache_percent_target    string      0     

En suite, deux vues vont nous donner des informations sur les objets placés dans cette portion de mémoire réservée : 

SET LINESIZE 400
SELECT * FROM V$BT_SCAN_CACHE;
BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP     CON_ID
-------------- --------------- ------------ ---------------- --------------- ----------
0               0            0                0            1000          0

SELECT * FROM V$BT_SCAN_OBJ_TEMPS;

aucune ligne sélectionnée

Passons à le configurer : Je vais réserver 40% de mon Buffer Cache pour placer les grosses tables qui font de FULL SCAN assez souvent.

ALTER SYSTEM SET DB_BIG_TABLE_CACHE_PERCENT_TARGET = 30;

Seulement comme exemple, je vais prendre une de mes tables pour bien valider qu'elle pourrait -en conditionnel car on ne peut pas les choisir avec cette fonctionnalité- se placer en mémoire :

SELECT BLOCKS , ROUND(BYTES/1024/1024/1024,2) GB
  FROM DBA_SEGMENTS
 WHERE SEGMENT_NAME = 'REVISIONS';
BLOCKS         GB
---------- ----------
155648       1.19


Je vais forcer -pour le test- un FULL SCAN : 


SELECT * FROM UCM.REVISIONS ;

Par la suite : 

SELECT * FROM V$BT_SCAN_CACHE;

BT_CACHE_ALLOC BT_CACHE_TARGET OBJECT_COUNT MEMORY_BUF_ALLOC MIN_CACHED_TEMP     CON_ID
-------------- --------------- ------------ ---------------- --------------- ----------
    .300028456              30            3           165308            1000          0

La colonne BT_CACHE_TARGET affiche le 30% que j'avais assigné. 

La colonne OBJECT_COUNT me dit combien d'objets profitent de cette configuration.

Après quelques minutes, on peut voir d'autres objets profiter de cette mémoire réservée : 

COL OWNER FORMAT A15 
COL OBJECT_NAME FORMAT A30
SELECT A.* , B.OWNER, B.OBJECT_NAME 
  FROM V$BT_SCAN_OBJ_TEMPS A, DBA_OBJECTS B 
 WHERE A.DATAOBJ# = B.DATA_OBJECT_ID 
 ORDER BY POLICY DESC ;
  
 TS#   DATAOBJ# SIZE_IN_BLKS TEMPERATURE POLICY    CACHED_IN_MEM     CON_ID OWNER  OBJECT_NAME
---- ---------- ------------ ----------- --------- ------------- ---------- ------ -----------
  15     216940       128685        1000 MEM_PART          23670          0 UCM    DOCMETA
  15     217093        96208        6000 MEM_ONLY          96208          0 UCM    REVCLASSES
  15     216946       155624       55380 MEM_ONLY         155624          0 UCM    REVISIONS

La colonne SIZE_IN_BLKS devrait être proche du nombre de blocks de la table en question si jamais elle est placé en mémoire.

Dans ce cas la colonne POLICY va nous dire que la table est placé complètement en mémoire, par contre on peut voir aussi que la table DOCMETA, laquelle est placé partiellement en mémoire. 

Les possibles valeurs : 

MEM_ONLY : This object will be fully cached in memory.

MEM_PART : This object will be partially cached in memory and some portion will remain on disk and will not be cached.

DISK : this object will not be cached in memory or flash for the scan at all.

INVALID : The caching policy is not valid.

Comme on peut voir, on n'a pas toujours la garantie de placer nos objets au complet en mémoire, cependant avec le 40% que j'ai mis, j'ai réussi à placer ma table REVISIONS en mémoire, bien entendu, cette fonctionnalité a plusieurs avantages cependant le fait de ne pas pouvoir choisir l'objet désiré et le placer en mémoire, peut ne pas être la meilleure solution dans quelques cas.  

Probablement dans ce cas l'option IN-MEMORY serait plus adéquate selon vos besoins.