Introduction
Oracle a tendance à multiplier les expressions pour parler des mêmes opérations ou objets. Par exemple, pour le mot Verrou en français, Oracle utilise les termes Lock, Latch, Mutex, Enqueue, Pin. Chacun de ces verrous a ses propres caractéristiques, il est donc pertinent d'avoir des termes précis pour les différencier et être plus efficace mais, revers de cela, ça complexifie leur approche.

Lorsqu'il s'agit de parler de la lecture de blocs de données, Oracle multiplie là-encore les termes : lecture sur disque dur, lecture en mémoire, lecture des blocs en totalité, lecture des blocs partielle ... on peut même "toucher" un bloc !

L'objectif de cet article est justement d'aborder ces différents termes que l'on rencontre dans la littérature et qui peuvent dérouter au premier abord :
          Pour les lectures sur disque dur :
                    - physical reads ou physical I/O ou P IO
                    - wait events SYSTEM I/O et USER I/O
    
          Pour les lectures en mémoire :
                    - logical reads ou logical I/O ou L IO qui se divisent en
                              - consistent gets
                              - db blocks gets
                    - touch ou accédés

          Lecture partielle ou totale :
                    - Arraysize et Fetch



Points d'attention
Aucun.


 

Base de tests
N'importe quelle base Oracle.



Exemples
============================================================================================
READ et GET : lecture physique Vs lecture logique

============================================================================================
Voyons voir cela en détail avec un SELECT en mode AUTOTRACE.    
          SQL> set autotrace traceonly statistics

          SQL> select * from HR.test_obj01 where rownum < 1000;
          Statistics
          ----------------------------------------------------------
             1344  recursive calls
                0  db block gets
             1895  consistent gets
               20  physical reads
                0  redo size
            59481  bytes sent via SQL*Net to client
             1334  bytes received via SQL*Net from client
               68  SQL*Net roundtrips to/from client
              254  sorts (memory)
                0  sorts (disk)
              999  rows processed

On voit ici que Oracle distingue nettement les lectures de blocs sur disque dur (physical reads), des lectures de bloc dans le cache en utilisant le terme gets (db block gets et consistent gets). Ce qui m'a perturbé à une époque c'est que je ne comprenais pas pourquoi Oracle n'utilisait pas les termes db block reads et consistent reads; trop simple?

Selon Tom Kytes sur Ask Tom https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4783147586098
"db block gets = blocks gotten in "current" mode, as they exist right now
consistent gets = blocks gotten in consistent read mode. This is the mode we read blocks in with a select for example
physical reads = physical IO"

En résumé, pour Oracle, le terme READ concerne les lectures de blocs sur le disque dur et le terme GET concerne les lectures en mémoire.
    

============================================================================================
TOUCH : les blocs sont accédés

============================================================================================

Les blocs peuvent aussi être touchés (accédés); c'est un terme qu'on rencontre dans la doc Oracle, lié à la lecture. La doc Oracle nous dit https://docs.oracle.com/database/121/CNCPT/memory.htm#CNCPT007
"Buffer Touch Counts
The database measures the frequency of access of buffers on the LRU list using a touch count.
This mechanism enables the database to increment a counter when a buffer is pinned instead of constantly shuffling buffers on the LRU list."

Le mot Touch ne signifie pas un type de lecture mais est utilisé par Oracle pour compter le nombre de fois qu'un bloc est lu ou accédé et déterminer la popularité de celui-ci. Cette opération conduit à incrémenter la colonne TCH de la structure X$BH (colonne non visible dans V$BH), qui indique la popularité d'un buffer donc d'un bloc de données. Quand il n'est plus assez populaire, le bloc est éjecté du buffer cache et son buffer devient libre pour y insérer un nouveau bloc, lu depuis le disque dur. Vous commencez à avoir mal à la tête? Un bloc est READ depuis le disque dur, écrit en SGA, il est GET depuis cette SGA et il est TOUCH également... oui, c'est compliqué...

Voyons voir comment ce champ TCH est incrémenté.
          SQL> alter system flush buffer_cache;
          System altered.

         
SQL> SELECT X.TS#, X.FILE#, DBABLK, X.tch from DBA_OBJECTS O, X$BH X WHERE X.OBJ = O.object_id AND O.object_name = 'TEST_OBJ01';
                 TS#    FILE#      DBABLK    TCH
          ---------- ---------- ---------- ----------
                    5       12        9281             0
                    5       12       27651            0
                    5       12        9282             0
                    5       12       27650            0

Si on relit la table de test, de nouveaux buffers apparaissent dans X$BH pour les mêmes blocs et la colonne TCH est incrémentée.
          SQL> select count(*) from HR.test_obj01;
            COUNT(*)
          ----------
              628184

          SQL> SELECT X.TS#, X.FILE#, DBABLK, X.tch from DBA_OBJECTS O, X$BH X WHERE X.OBJ = O.object_id AND O.object_name = 'TEST_OBJ01';
                 TS#    FILE#      DBABLK    TCH
          ---------- ---------- ---------- ----------
                    5       12        9281              1
                    5       12        9281              0
                    5       12       27651             1
                    5       12       27651             0
                    5       12        9282              1
                    5       12        9282              0
                    5       12       27650             1
                    5       12       27650             0
   
Encore une fois!
          SQL> select count(*) from HR.test_obj01;
            COUNT(*)
          ----------
              628184

          SQL> SELECT X.TS#, X.FILE#, DBABLK, X.tch from DBA_OBJECTS O, X$BH X WHERE X.OBJ = O.object_id AND O.object_name = 'TEST_OBJ01';
                 TS#    FILE#      DBABLK    TCH
          ---------- ---------- ---------- ----------
                    5       12        9281             2
                    5       12        9281             0
                    5       12       27651            2
                    5       12       27651            0
                    5       12        9282             2
                    5       12        9282             0
                    5       12       27650            2
                    5       12       27650            0
     

============================================================================================
WAIT EVENTS

============================================================================================

Oracle complexifie aussi le mot Read avec sa gestion des Wait Events. Une des expressions sur Oracle est que les readers ne bloquent pas les readers.

Cependant nous avons un Wait event "Read by other session" qui signifie qu'un process ne peut pas lire un bloc car celui-ci est en cours de lecture sur le disque dur par une autre session et qu'Oracle ne permet pas, pour des raisons de performances, que deux sessions lisent le même bloc sur le disque dur. La deuxième session doit donc attendre que la première l'ait lu sur disque dur puis écrit en mémoire. Une fois ce bloc écrit dans le buffer cache, N sessions pourront le lire en parallèle sans délai d'attente et sans se gêner, d'où l'expression sur les Readers.

Dans les deux cas il s'agit de lecture physique, c'est pourquoi Oracle utilise le mot READ pour la lecture sur disque dur et "read by other session" pour décrire l'attente de la deuxième session liée à cette lecture physique.
          SQL> select WAIT_CLASS, NAME from V$EVENT_NAME where  upper(NAME) like '%OTHER%' and upper(WAIT_CLASS) like '%I/O%';
          WAIT_CLASS                       NAME
          ---------------------------------------- ----------------------------------------
          User I/O                               read by other session


============================================================================================
FETCH et ARRAYSIZE : lecture partielle ou totale d'un bloc

============================================================================================

Nous allons maintenant voir qu'Oracle peut lire un bloc dans sa totalité ou partiellement via le paramètre client Arraysize; ce paramètre donne le nombre de rows lus par fetch. Doc Oracle : "Arraysize : sets the number of rows that SQL*Plus will fetch from the database at one time."
Le terme FETCH est donc encore un autre terme lié à la lecture et il décrit le nombre d'enregsitrements envoyés au client depuis un bloc lu en mémoire.
          SQL> show arraysize
          arraysize 15
    
          SQL> select * from HR.test_obj01 where rownum < 1000;
          Statistics
          ----------------------------------------------------------
                0  recursive calls
                0  db block gets
               76  consistent gets
                0  physical reads
                0  redo size
            59481  bytes sent via SQL*Net to client
             1334  bytes received via SQL*Net from client
               68  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
              999  rows processed

On modifie le arraysize de 15 à 300 : le nombre de consistent gets a chuté car on lit en un get plus de données donc potentiellement on peut lire toutes les données d'un bloc en un fetch.
          SQL> show arraysize
          arraysize 300

          SQL> select * from HR.test_obj01 where rownum < 1000;
          Statistics
          ----------------------------------------------------------
                0  recursive calls
                0  db block gets
               13  consistent gets
                0  physical reads
                0  redo size
                47259  bytes sent via SQL*Net to client
              641  bytes received via SQL*Net from client
                5  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
              999  rows processed

Je veux récupérer 999 rows dans mon SELECT :  cela va se faire en un nombre de fetch différent, lié au paramètre arraysize.
          - avec arraysize à 15 : 999/15 donne 67 fetchs en arrondissant --> on a 68 SQL*Net roundtrips to/from client : 67 + 1 appel du client vers la base
          - avec arraysize à 300 : 999/300 donne 3,33 fetchs mais 4 en arrondissant --> on a 5 SQL*Net roundtrips to/from client, soit 4 + 1 appel du client vers la base
    

============================================================================================
Conclusion

============================================================================================

En résumé : lors d'un SELECT, si le bloc n'est pas présent en mémoire, Oracle fait un PHYSICAL READ. Si le bloc est présent en mémoire, Oracle fait un GET (DB BLOCK ou CONSISTENT selon qu'on veut la toute dernière version commitée ou non du bloc). Selon la taille du ARRAYSIZE, les données du bloc sont FETCHées (envoyées) au client en une fois ou en N fois, obligeant Oracle à TouCHer (TCH) N fois le même bloc.

Une fois que vous aurez maîtrisé les termes Read, Get, Fetch et que vous jonglerez avec, ne croyez pas que le chemin soit fini, oh non, il vous faudra comprendre comment Oracle lit les blocs selon qu'ils ont été copiés en SGA ou en PGA  car ce n'est pas pareil! Je vous renvoie vers cet autre article : "ORDER BY et Consistents gets réduits par 10 : un ORDER BY ne ralentit pas toujours une requête!" http://dbaoraclesql.canalblog.com/archives/2017/10/16/35775914.html où on découvre que les consistent gets sont plus nombreux en SGA que en PGA pour lire la même quantité de données...