Oracle - Optimisation du cache de données (buffer cache) dans la SGA

SGA et buffer cache

Rappel de la structure :

Caractéristiques du buffer cache

Le buffer cache contient des copies de blocs de données des fichiers de données. Comme le buffer cache fait partie de la SGA, ces blocs peuvent être partagés par tous les utilisateurs. Le buffer cache présente les caractéristiques suivantes :

• il est dimensionné à l’aide du paramètre DB_BLOCK_BUFFERS. Ce dernier spécifie le nombre de blocs du buffer cache. Pour déterminer la taille du cache en octets, multiplier DB_BLOCK_BUFFERS par DB_BLOCK_SIZE.
• Les processus serveur lisent les données à partir des fichiers de données vers le buffer cache. Pour augmenter les performances, le processus serveur lit parfois plusieurs blocs en une seule passe.
• Le processus DBWR écrit des données du buffer cache dans les fichiers de données. Pour augmenter les performances, le DBWR écrit parfois plusieurs blocs en une seule fois.
• Chaque buffer ne contient qu’un seul bloc de données.
• A tout moment, le buffer cache peut contenir plusieurs copies d’un même bloc de données. Une seule copie courante du bloc existe mais les processus serveur peuvent avoir besoin de construire des copies cohérentes en lecture, en utilisant des informations des rollback pour répondre à leurs requêtes.
• Les blocs du buffer cache sont gérés à l’aide de deux listes :
• la liste des buffers les moins récemment accédés (LRU) est utilisée pour conserver en mémoire les blocs les plus récemment accédés. Les blocs de la liste sont classés du récemment accédé (MRU) au moins récemment accédé.
• la liste des blocs modifiés (dirty list) pointe vers les blocs du buffer cache qui ont été modifiés mais non encore écrits sur disque.
• Les blocs du buffer cache peuvent se trouver dans l’un des trois états suivants :
• les buffers libres sont des blocs qui ont la même image sur le disque qu’en mémoire. Ces blocs sont disponibles pour réutilisation.
• les blocs modifiés sont des blocs qui ont une image différente en mémoire de celle sur le disque. Ces blocs doivent être écrits sur le disque avant de pouvoir être réutilisés.
• Les buffers maintenus en mémoire sont des blocs en cours d’accès (notamment pour Parallel Server).

Objectifs et techniques d’optimisation du buffer cache

Objectifs

Dans la mesure où les E/S physiques prennent un temps considérable et accroissent la demande CPU, les performances d’Oracle peuvent être améliorées si les serveurs trouvent en mémoire la plus grande partie des blocs dont ils ont besoin. La statistique qui mesure la performance du buffer cache de base de données est le hit ratio du cache. Cette statistique représente le rapport entre le nombre de blocs trouvés en mémoire et le nombre de blocs accédés. Lorsque le buffer cache de la base de données est trop petit, le système est plus lent parce qu’il doit trop exécuter d’E/S.

Techniques d’optimisation

Le DBA surveille le buffer cache en :

• calculant le hit ratio du cache à partir des statistiques collectées par Oracle
• utilisant des utilitaires permettant d’évaluer les variations du hit ratio lorsque des buffers sont ajoutés ou supprimés.

Pour améliorer le hit ratio du cache, le DBA peut :

• accroître le paramètre DB_BLOCK_BUFFERS pour ajouter des blocs au buffer cache.
• utiliser des buffer pools multiples pour répartir les blocs en fonction des caractéristiques d’accès.
• placer certaines tables dans le cache.

Le DBA commence par déterminer l’évolution du hit ratio à mesure que des buffers sont ajoutés ou supprimés. En règle générale, il faut augmenter DB_BLOCK_BUFFERS lorsque :

• le hit ratio du cache est inférieur à 90%.
• il y a suffisamment de mémoire pour d’autres processus d’après la mesure du nombre de défauts de page
• l’augmentation antérieure de DB_BLOCK_BUFFERS a été efficace. L’augmentation de la taille du buffer cache n’accroît pas toujours les performances du fait des caractéristiques de l’application, notamment dans les systèmes décisionnels où les balayages de tables très importantes sont pénalisantes au niveau du buffer cache : dans ce cas de figure ce sont plutôt les E/S qui seront optimisés par rapport au buffer cache.

Si les caractéristiques de l’accès aux données sont à l’origine du faible hit ratio du cache, le DBA peut l’améliorer en définissant plusieurs pools ou en mettant des tables en cache.

Outils de diagnostic

Vues associées aux outils de diagnostic

• Les vues V$SYSSTAT et V$SESSTAT contiennent les statistiques utilisées pour calculer le hit ratio du cache :

select  name, value
from    v$sysstat
where   name in ('db block gets','consistent gets','physical reads')

NAME                VALUE
--------------------------
db block gets        3245
consistent gets     11751
physical reads       1398

• V$BUFFER_POOL : décrit les différents buffer pools
• V$BH : décrit les blocs contenus dans le buffer cache.

La taille totale du buffer cache peut être simplement calculé à partir de la formule suivante :

Mesure du Hit ratio du cache

Oracle collecte des statistiques concernant l’accès aux données et les stocke dans la table dynamique des performances V$SYSSTAT. Le hit ratio du cache est mesuré à l’aide de trois statistiques système :

• db block gets : accès à l’image courante d’un bloc ou accès aux blocs non RBS (Rollback Segments)
• consistent gets : accès à une image « avant » d’un bloc ou accès aux blocs RBS (Rollback segments).
• physical reads : nombre de blocs lus à partir du disque.

Calculer le hit ratio du buffer cache à l’aide de la formule suivante :

select  1 - (phy.value / (cur.value + con.value )) "Cache Hit Ratio"
from    v$sysstat cur, v$sysstat con, v$sysstat phy
where   cur.name = 'db block gets'
and     con.name='consistent gets'
and     phy.name='physical reads';

Cache Hit Ratio
---------------
,907269833

L’addition de db_block_gets et consistent_gets donne le nombre total de lecture des données. Cette valeur inclut les requêtes satisfaites par l’accès à des buffers en mémoire et les requêtes qui provoquent une E/S physique.

Comme ces statistiques ne sont collectées qu’à partir du démarrage de l’instance, consulter ces statistiques que lors de charges de travail normales et non immédiatement après le démarrage. Dans la mesure où le buffer cache est vide au moment où l’instance est lancée, il y a plus de lectures après le démarrage.

Évaluation des objets dans le cache

Il est possible de traquer les objets qui utilisent la plus grande part du cache de données grâce à la vue V$BH

select o.owner, o.object_type, o.object_name, count(b.objd) from v$bh b, dba_objects o
where b.objd=o.object_id
group by o.owner, o.object_type, o.object_name
having count(b.objd) > (select to_number(value*0.5) from v$parameter
                                        where name='db_block_buffers')

évènements d’attente liés au database buffer cache

La vue V$SESSION_WAIT permet de déterminer efficacement les évènements d’attente liés aux caches de données :

Les évènements d’attente courants relatifs au cache de données sont :

Utilisation de plusieurs Buffer Pools

Généralités

Le DBA peut améliorer les performances du buffer cache de données en créant plusieurs buffer pools. Les objets sont affectés à un buffer pool en fonction de leur mode d’accès. Il s’agit d’une nouvelle fonctionnalité Oracle 8.

Oracle 8 possède trois buffer pools :

• KEEP : utilisé pour conserver en mémoire des objets qui sont susceptibles d’être réutilisés. La conservation de ces objets en mémoire réduit les opérations d’E/S.
• RECYCLE : utilisé pour éliminer de la mémoire les objets qui n’ont que peu de chances d’être réutilisés. En supprimant ces blocs de la mémoire plus précocement, l’espace qu’ils occuperaient peut être alloué à d’autres objets.
• DEFAULT : ce pool existe toujours. Il équivaut à un buffer cache unique.

Les buffer pools sont définis avec le paramètre d’intialisation BUFFER_POOL_name avec les attributs suivants :

• le nombre de buffers dans le buffer pool
• le nombre de latches LRU alloués au buffer pool.

Quelques mots sur le paramètre DB_BLOCK_LRU_LATCHES

Les latches sont des verrous protégeant les structures mémoires de la SGA attaquées par les transactions. Pour qu’un process puisse altérer la zone mémoire protégée par un latch, l’autre process qui détient ce latch doit le relâcher.

Le paramètre DB_BLOCK_LRU_LATCHES renseigné dans init.ora définit le nombre de latch pour le buffer cache. Si plusieurs latches sont implémentés pour le couple LRU/DIRTY LIST, celui-ci est divisée en plusieurs listes LRU/DIRTY LIST, chacune étant protégée par un latch. Cette organisation a été implémentée afin de bénéficier des architectures physiques multi processeur et de l’emploi du parallel processing (parallel query). Dans tous les cas de figure, un nombre d’au moins 50 buffers doit être protégé par un latch.

Les sous listes LRU/Dirty List sont constituées de manière circulaire, assurant pratiquemement le même nombre de blocs par liste.

Définition des buffer pools

La définition des buffer pools est implémentée dans le fichier init.ora

...
DB_BLOCK_BUFFERS=20000
DB_BLOCK_LRU_LATCHES=6
BUFFER_POOL_KEEP=(BUFFERS:14000,LRU_LATCHES:1)
BUFFER_POOL_RECYCLE=(BUFFERS:2000,LRU_LATCHES:3)
 ...

Oracle définit alors implicitement le buffer pool default : BUFFER_POOL_DEFAULT=(BUFFERS:4000, LRU_LATCHES:2).

Le nombre minimum de buffers qui doit être alloué à chaque buffer pool est 50 fois le nombre de latches LRU. Ainsi par exemple, si un buffer pool possède trois latches LRU, il doit avoir au moins 150 buffers.

Activation des Buffer Pools

La clause BUFFER_POOL est utilisée pour définir le buffer pool par défaut d’un objet. Elle fait partie de la clause storage et est valide par les ordres CREATE et ALTER, sur les tables, clusters et les index. Les blocs provenant d’un objet sans buffer pool défini explicitement vont dans le buffer pool DEFAULT.

La syntaxe est :

CREATE / ALTER ...
STORAGE (BUFFER_POOL { KEEP | RECYCLE | DEFAULT });

Par exemple BUFFER_POOL KEEP ou BUFFER_POOL RECYCLE

Directives concernant le buffer pool KEEP

L’objectif du buffer pool KEEP est de conserver des objets en mémoire, en évitant ainsi des opérations d’E/S. La taille du buffer pool KEEP est calculée en additionnant les tailles de tous les objets dédiés à ce pool.

La commande ANALYZE... ESTIMATE STATISTICS donne la taille de chaque objet. Le High Water Mark est toujours exact même si les statistiques sont des estimations. Additionner les colonnes BLOCKS de DBA_TABLES, DBA_INDEXES et DBA_CLUSTERS pour obtenir le nombre total de blocs requis.

Le DBA doit surveiller les objets situés dans le pool KEEP dont la taille s’accroît. Un objet peut ne plus tenir dans le buffer pool KEEP et des blocs peuvent être perdus.

analyze table <tablename> estimate statistics ;
Table analyzed.
select table_name, blocks from dba_tables where owner='owner' and table_name='tablename';
TABLE_NAME                 BLOCKS
-------------------------- ------
tablename                      14

Directives concernant le buffer pool RECYCLE

L’objectif du buffer pool RECYCLE est d’éliminer les blocs de la mémoire dès qu’ils ne sont plus nécessaires. Il faut toutefois veiller à ne pas supprimer les blocs de la mémoire trop rapidement. Si le buffer pool est trop petit, il est possible qu’un bloc soit exclus du cache faute d’espace mémoire avant que la transaction ou l’ordre SQL ne soit totalement exécuté.

Le pool RECYCLE est dimensionné en utilisant les statistiques de lectures physiques ou en totalisant les blocs de buffer cache utilisés par l’objet.

La vue V$CACHE permet de surveiller le nombre de blocs de buffer pool par objet. V$CACHE est créé avec le script catparr.sql.

Pour déterminer le nombre de blocs requis pour les objets situés dans le pool RECYCLE :

• optimiser le buffer cache après avoir désactivé le pool RECYCLE
• exécuter catparr.sql pour configurer et remplir V$CACHE
• au moment des points d’activité d’exécution, caculer le nombre de blocs utilisés par chaque objet à l’aide de la requête suivante :

connect internal;
?/rdbms/admin/catparr.sql;

select  owner#, name, count(*) blocks
from    v$cache
group by owner#, name;

OWNER#      NAME                          BLOCKS
---------- ------------------------------ ----------
0          ACCESS$                                11
0          ARGUMENT$                               1
0          ATTRCOL$                              440
0          AUDIT$                                  1
0          CCOL$                                  21
0          CDEF$                                  21

Additionner les blocs de tous les objets qui seront utilisés dans le buffer pool RECYCLE et diviser par 4 ce chiffre pour obtenir la taille du pool RECYCLE. Il faut effectuer cette division par 4 parce que l’on fait l’hypothèse qu’un quart des blocs destinés au pool RECYCLE sont actifs. Les trois quarts restants attendent d’être exclus du cache.

Suivi des lectures physiques

La table dynamique de performances V$SESS_IO fournit les statistiques d’E/S par session.

select  io.block_gets,
        io.consistent_gets,
        io.physical_reads
from    v$sess_io io,
        v$session s
where   s.audsid = userenv('SESSIONID')
and     io.sid = s.sid;

BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS
---------- --------------- --------------
140                    767            316

Vues sur les buffer pools

La vue V$BUFFER_POOL_STATISTICS permet de calculer les hit ratio dans les différents buffer pools.

select  name,
        (1 - physical_reads / (db_block_gets + consistent_gets )) "HIT RATIO"
from    v$buffer_pool_statistics
where   db_block_gets + consistent_gets > 0

La vue V$BUFFER_POOL décrit les buffer pool alloués. Les colonnes de la vue V$BUFFER_POOL indiquent :

• le nombre et la portée des latches LRU alloués au buffer (ou ensemble de buffers)
• le nombre et la portée de blocs alloués au buffer

select * from v$buffer_pool where id <> 0;

ID         NAME                 LO_SETID   HI_SETID   SET_COUNT  BUFFERS    LO_BNUM    HI_BNUM
---------- -------------------- ---------- ---------- ---------- ---------- ---------- ----------
3          DEFAULT              1          1          1          5547       0           0

SET COUNT : liste(s) LRU

Autres indicateurs de performances sur les buffer pools : V$SYSTEM_EVENT et V$SYSSTAT

Les vues V$SYSSTAT et V$SYSTEM_EVENT sont des indicateurs de performance importants pour les buffer pools.

Statistiques de contention

Il est envisageable d’augmenter le buffer cache si les valeurs de la statistique système « free buffer inspected » sont élevées ou croissantes. Cette statistique représente le nombre de buffers examinés avant de trouver un buffer libre. Les buffers sont examinés parce qu’ils ont été modifiés ou maintenus en mémoire.

select  name, value
from    v$sysstat
where   name='free buffer inspected';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
free buffer inspected                                                     0

évènements de contention

Il est possible de déterminer les contentions de buffers à partir des vues V$SYSTEM_EVENT et V$SESSION_WAIT :

• le premier évènement à recherche est « buffer busy waits » qui signale qu’un processus a attendu qu’un buffer soit rendu disponible
• le second évènement est « free buffer waits » qui se produit lorsqu’un serveur ne peut pas trouver de buffer libre ou lorsque la file des buffers modifiés est complète.

Il n’y a plus de contention si l’évènement ne se produit pas.

select  event, total_waits
from    v$system_event
where   event in ('free buffer waits','buffer busy waits');

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
buffer busy waits                                                         14

Mise en cache de tables

Principe de mise en cache des tables

Lorsque le serveur retrouve des blocs en procédant à un balayage complet de table, ces blocs sont placés dans la partie des blocs les moins récemment utilisés de la liste LRU. Il est possible de choisir de mettre en cache des tables complètes dans la partie des blocs les plus récemment utilisés (MRU) de la liste.

Il est possible de modifier le comportement en effectuant les opérations suivantes :

• créer une table à l’aide de la clause CACHE
• modifier une table à l’aide de la clause CACHE
• coder la clause hint CACHE dans une requête

Limitation des blocs mis en cache : cache_size_threshold

Le paramètre CACHE_SIZE_THRESHOLD fixe une limite au nombre de blocs mis en cache pour chaque table. Ainsi par exemple, si CACHE_SIZE_THRESHOLD est fixé sur 5 et que la table occupe 10 blocs, seuls les cinq premiers blocs sont mis en cache. Ce paramètre est réglé par défaut sur un dixième de DB_BLOCK_BUFFERS.