Cache Buffer Chain

SQL> SELECT sid,event, p1text, p1 p1raw, p2, p3  FROM v$session_wait w 
  2  WHERE sid IN (17) ; 

       SID EVENT                P1TEXT          P1RAW                     P2         P3
---------- -------------------- ----------        ----------                      ---------- ----------
        34 latch free  file#      address       C0000001053EC720          66      0

Lorsqu'on observe dans la vue v$session_wait un évènement de type latch free on peut collecter un certain nombre d'informations très importantes.

Tout d'abord dans la colonne P2 donne le numéro de latch, le nom du latch peut être retrouvé ainsi :

SQL> SELECT * FROM  v$latchname  
  2  WHERE latch#=66 ;
 
    LATCH# NAME
---------- ----------------------------------------------------------------
        66 cache buffers chains

Ensuite la colonne P1raw va nous permettre d'isoler la ressource qui est sujette à ce bloc chaud (Hot bloc).
Qu'est-ce que le P1RAW : c'est une valeur hexadécimale spécifiant l'adresse du latch. Nous allons nous servir de cette valeur pour requêter sur la vue sys.x$bh.

Pour information P3 est un compteur qui va comptabiliser le nombre de fois que le latch a été demandé et n'a pas été obtenu.
En effet sous SYS :

SQL> SELECT FILE#, dbablk, class, state, tch FROM x$bh WHERE hladdr='C0000001053EC720' ORDER BY tch
 ;
 
     FILE#     DBABLK      CLASS      STATE        TCH
---------- ---------- ---------- ---------- ----------
 
(………. 
voir la dernière ligne)
 
        40      41359          1          1         94

Et grâce à la valeur de file# et de DBABLK (block) nous allons pouvoir retrouver le segment (table ou index) qui est sujet à contention.Plus précisément la colonne FILE# est le numéro de fichier et le DBABLK est le numéro de bloc.
toujours sous le compte sys ou internal (version > 9i) :

SQL> SELECT * FROM dba_extents WHERE 41359 BETWEEN dba_extents.block_id AND dba_extents.block_id+blo
cks-1   AND dba_extents.file_id = 40 ;
 
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
PSHR83
PS_GP_RTO_PRC_WRK
                               INDEX              PSINDEX
         0         40      41357      40960          5           40

Nous venons de nous intéresser à une vue micro (sur une requête à un instant T), maintenant nous allons nous intéresser à une vue macro, à savoir tous les évènements d'attente depuis le démarrage de la base, pour ce faire la vue latch_children et la requête suivante vont nous aider.
Cette démarche est plus dans une optique d'optimisation de l'instance que d'une requête ou d'un traitement.

SQL> SELECT addr, latch#, gets, misses, sleeps, ROUND (((misses*100)/gets),6) AS ratio 
  2      FROM v$latch_children
  3     WHERE sleeps>0
  4       AND latch# = 66
  5     ORDER BY sleeps DESC ; 
 
ADDR                 LATCH#       GETS     MISSES     SLEEPS      RATIO
---------------- ---------- ---------- ---------- ---------- ----------
C0000001053EC720         66   70591975    1976853       2877   2,800393

La colonne RATIO est une colonne issue d'un calcul, déterminant le ratio qui existe entre misses et gets.
Détaillons cette requête,chaque fois qu'un verrou est demandé sur un block de données, si oracle nous l'accorde de suite la colonne GET est incrémentée, sinon MISSES est incrémentée.
Donc le ratio revient à poser la question suivante :
combien de fois un latch a été demandé et combien de fois il a été octroyé tout de suite ?
Oracle recommande d'avoir un ratio inférieur à 1 %, ici nous voyons que le ratio avoisine les 3 %…
Cependant comme tout ratio, cela doit être pondéré par le contexte de l'instance.
Avec la colonne ADDR et en reprenant le même principe que vu précédemment, on peut retrouver le segment concerné
À part les conseils classiques de tuning concernant le code, je vous déconseille fortement de « tuner » les paramètres non documentés et cachés d'oracle :_db_block_hash_buckets et
_db_block_hash_latches  sans le support Oracle.
Pour une table, on conseille en règle générale le partitioning et pour un index, « le reverse index » peut obtenir de bons résultats, mais attention, si cet index est utilisé ailleurs en range scan, l'optimiseur COST peut dans ce cas choisir le FTS (Full Table Scan). D'où la nécessité de construire des histogrammes pour les colonnes indexées.
Lorsqu'on met en place un reverse index, deux méthodes permettent de diminuer les probabilités que l'optimiseur COST ou CHOOSE préfère un FTS au lieu d'un accès indexé :
1-Construire des histogrammes : la commande suivante permet de le faire :

analyze table schéma.table compute statistics for all indexed columns

Cependant pour les versions 8i ou supérieures préférez le paquetage : DBMS_STATS

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME => 'DVP', CASCADE => FALSE, METHOD_OPT => 'FOR ALL INDEXED COLUMNS');

Suite à « la mort » du process, c'est PMON (process monitor) qui libère le latch. Notamment lors d'un « SNAPSHOT TOO OLD ».
Cependant, la construction des histogrammes ne renseigne que sur la distributivité des données, donc a un effet limité.
Remarque : attention le latch free n'est pas en corrélation avec un cache hit ratio faible. Il ne sous-entend nullement une augmentation du cache size.