mardi 19 mars 2013

un select qui génère du redo?

On sait tous que les REDO records sont générés normalement dès qu'une mise à jour est effectuée au niveau de la base pour pouvoir être rejoués lors d'un recovery mais surement pas lors d'un  SELECT n'est-ce pas? C'est peut-être ce que certains d'entre vous doivent penser. Et pourtant si. Un SELECT tout simple sans clause FOR UPDATE peut générer du REDO.

Avant d'expliquer la raison de ce phénomène voyons d'abord la preuve avec un exemple:
zizou-SQL>create table T1 as select * from dba_objects;

Table created.

zizou-SQL>insert into t1 select * from t1;

69269 rows created.

zizou-SQL>/

138538 rows created.

zizou-SQL>/

277076 rows created.

zizou-SQL>/

554152 rows created.

zizou-SQL>create index idx1 on t1(owner);

Index created.

zizou-SQL>exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

zizou-SQL>select COMPONENT,CURRENT_SIZE/1024/1024,MAX_SIZE/1024/1024 from v$sga_dynamic_components;

COMPONENT          CURRENT_SIZE/1024/1024 MAX_SIZE/1024/1024
------------------------- ---------------------- ------------------
shared pool                     184        184
large pool                       4          4
java pool                       4          4
streams pool                       0          0
DEFAULT buffer cache                 340        340
KEEP buffer cache                   0          0
RECYCLE buffer cache                   0          0
DEFAULT 2K buffer cache                0          0
DEFAULT 4K buffer cache                0          0
DEFAULT 8K buffer cache                0          0
DEFAULT 16K buffer cache               0          0
DEFAULT 32K buffer cache               0          0
Shared IO Pool                       0          0
ASM Buffer Cache                   0          0

-- Nb de blocks dans le segment table
zizou-SQL>select blocks from dba_tables where table_name='T1';

    BLOCKS
----------
     15729

J'ai crée dans ma base ZIZOU une table T1 contenant un peu plus de 1 million de lignes. Mon buffer cache fait à peine 340MB et ma table contient 15 729 blocks.
Ma base a une taille de block de 8K.

Pour vérifier que chaque requête génère ou pas du redo je vais utiliser la commande AUTOTRACE.
J'effectue d'abord un select de ma table T1:
zizou-SQL>set autot trace stat 
zizou-SQL>select * from t1 where owner='SYSTEM';

8384 rows selected.

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
       1502  consistent gets
      0  physical reads
      0  redo size
     918591  bytes sent via SQL*Net to client
       6557  bytes received via SQL*Net from client
    560  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
       8384  rows processed
Le premier SELECT sur ma table n'a pas généré de redo (la stat redo size = 0).
Maintenant j'effectue un update massif de ma table T1 et j'exécute de nouveau le même select:
zizou-SQL>update t1 set last_ddl_time=SYSDATE;

1108304 rows updated.

Elapsed: 00:00:50.73
zizou-SQL>commit;

Commit complete.

zizou-SQL>select * from t1 where owner='SYSTEM';

8384 rows selected.

Statistics
----------------------------------------------------------
 1 recursive calls
 0 db block gets
 1767 consistent gets
 23 physical reads
 19124 redo size
 918591 bytes sent via SQL*Net to client
 6557 bytes received via SQL*Net from client
 560 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 8384 rows processed
Je constate cette fois que le SELECT a bien généré du redo.
Je relance une deuxième fois la requête:
zizou-SQL>select * from t1 where owner='SYSTEM';

8384 rows selected.

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
       1502  consistent gets
      0  physical reads
      0  redo size
     918591  bytes sent via SQL*Net to client
       6557  bytes received via SQL*Net from client
    560  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
       8384  rows processed
Là plus rien. La requête ne génère plus de redo.
Pour comprendre ce phénomène il faut se rappeler les concepts sur la gestion des transactions dans Oracle.
Quand une session modifie une ligne dans une table Oracle crée un lock au niveau row en ajoutant une entrée dans l'ITL (Interested Transaction List) dans l'entête du bloc de données contenant la ligne modifiée. Cette entrée ITL contient notamment l'identifiant de la transaction (permettant d'accéder à la table des transactions situées dans le bloc en-tête de l'undo segment) et l'adresse du dernier undo record généré par cette transaction. Si une deuxième session tente de lire cette ligne modifiée (pas encore committée) il verra en checkant l'entrée ITL qu'une transaction active existe pour cette ligne et utilisera les informations dans l'undo segment pour restituer l'image d'avant la modification (consistent read). Quand la transaction est committée, la table des transactions est modifiée dans l'entête du rollback segment pour indiquer que la transaction est committée. Oracle tente également de retrouver le data block modifié par la transaction afin de modifier l'entrée ITL correspondant à la transaction et y indiquer que les changements apportées au bloc par la transaction ont été committés. On dit alors que le "nettoyage" au niveau du bloc de donnée a été effectué lors du commit c'est ce qu'on appelle le commit cleanout. Cependant, si le bloc n'est plus en mémoire au moment du commit (le process DBWR peut avoir écrit le bloc dans le datafile suite à un checkpoint par exemple) ou si le nombre de blocs modifiés par la transaction est trop important, alors Oracle ne va pas s'autoriser à modifier tous les en-têtes des data blocs modifiées et va juste modifier le slot au niveau de la table des transaction dans le bloc en-tête de l'undo segment. La prochaine session qui lira les blocs se rendra compte que ce qu'on a dans l'entrée ITL (transaction non committée) n'est pas cohérent avec ce qu'on a dans la table des transactions de l'undo segment (transaction committée avec indication du numéro SCN) et va faire le travail de nettoyage du bloc lu c'est à dire qu'il va indiquer dans l'entrée ITL que la transaction est en fait bien commitée. Comme le nettoyage de l'en-tête du bloc se fait bien après que le commit ait eu lieu on appelle ce mécanisme le Delayed Block Cleanout.

Puisque le cleanout consiste à modifier l'entrée ITL et donc à modifier le bloc, du redo est forcément généré.
C'est ce qui s'est passé dans mon exemple précédent: le premier SELECT n'a pas généré de redo puisque les blocs étaient "cleans" ensuite lorsque j'ai modifié massivement plus d'un million de lignes le commit n'a pas fait le travail de nettoyage des blocs (Oracle n'a pas pu se permettre de revisiter tous les blocs pour modifier les entrées ITL et puis vu la petite taille de mon cache les blocs étaient pour la plus part déjà ecrits sur disc). Lors du select qui a suivi mon update le process server s'est rendu compte que les entrées ITL n'avaient pas été cleanés et a donc fait le travail de block cleanout pour chaque bloc récupérés par ma requête.

Si j'avais modifié un faible nombre de lignes lors de l'update il y' a de forte chances que le cleanout des blocks ait été fait au moment du commit.
Faisons le test pour s'en assurer:
zizou-SQL>update t1 set last_ddl_time=SYSDATE where owner='APPQOSSYS';

48 rows updated.

zizou-SQL>commit;

Commit complete.

zizou-SQL>select * from t1 where owner='APPQOSSYS';

48 rows selected.

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
     27  consistent gets
      0  physical reads
      0  redo size
       6457  bytes sent via SQL*Net to client
    452  bytes received via SQL*Net from client
      5  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     48  rows processed
Effectivement on constate que cette fois le SELECT n'a pas généré de redo c'est donc que le cleanout a bien été fait au moment du COMMIT.