Optimisation des paramètres des tablespaces d’UNDO (Oracle 9i)

Introduction

À partir d’Oracle 9i, les segments d’annulation (rollback segments) peuvent être remplacés par un tablespace d’UNDO (undo logs).

Traditionnellement, les informations d’annulation d’une transaction étaient stockées dans les segments d’annulation (rollback segments) jusqu’à la commande de validation (commit) ou la commande d’annulation (rollback).

Le tablespace d’UNDO d’Oracle 9i permet de spécifier combien de temps les informations d’annulation (rollback segments) peuvent être retenues dans ce tablespace après la commande COMMIT, empêchant ainsi les erreurs « snapshot too old » de se produire pour les requêtes longues.

Le paramètre d’initialisation permettant de gouverner le temps de rétention dans un tablespace d’UNDO est la paramètre undo_retention. Par défaut, ce paramètre vaut 900 secondes (5 minutes) et il peut être étendu.

Cet article se propose de présenter :

• un rappel sur les tablespaces d’UNDO Oracle 9i et une présentation de la vue statistique V$UNDOSTAT
• le tuning de la taille du tablespace d’UNDO
• le tuning du paramètre undo_retention

Généralités sur les tablespaces d’UNDO

Les tablespaces d’UNDO d’Oracle 9i permettent d’automatiser et de simplifier la gestion des segments d’annulation (rollback segments). L’accent est mis plus en avant sur l’espace disque et l’exigence de rétention des informations avec les tablespaces d’UNDO, la création, la maintenance et le suivi des segments d’annulation (rollback segments) étant désormais laissés à la discrétion d’Oracle.

Le mode classique de gestion des segments d’annulation est toutefois maintenu pour des raison de compatibilité, et par ailleurs le tablespace SYSTEM dispose toujours de ses propres segments d’annulation indépendamment du mode d’undo adopté.

Création d’un tablespace d’UNDO : create undo tablespace

La commande CREATE UNDO TABLESPACE est dédiée à la création d’un tablespace d’UNDO :

create undo tablespace undts
    datafile 'filname' size nM
    [ autoextend on [ next nM [ maxsize nM ]]]

Paramètres undo_tablespace, undo_management

Le paramètre statique undo_management (non modifiable par la commande ALTER SYSTEM SET) permet d’indiquer à une instance Oracle si un tablespace peut être géré en mode UNDO automatiquement ou manuellement :

Dans le fichier d’initialisation de l’instance :

init<INSTANCE>.ora

undo_management=<manual | auto>

Une fois choisi, le mode de management des rollback ne peut être modifié dans l’instance.

Le paramètre dynamique undo_tablespace permet de spécifier le tablespace d’UNDO de l’instance et par conséquent de basculer d’un tablespace d’UNDO à un autre :

alter system set undo_tablespace = undots;

Dans le fichier d’initialisation de l’instance :

init<INSTANCE>.ora

undo_tablespace=undots

Quelques considérations sont importantes dans la combinaison des paramètres undo_management et undo_tablespace :

• si undo_management=manual et undo_tablespace=tsname, le paramètre undo_tablespace exige undo_management=auto.
• si undo_management=auto et que le paramètre undo_tablespace est omis, Oracle cherche un tablespace d’UNDO et si il existe au moins un tablespace d’UNDO, il est adopté par Oracle comme tablespace d’UNDO. La combinaison undo_management = auto et rollback_segments=(rbsx) est autorisée.

Paramètre undo_retention

L’exigence de rétention d’images que l’on estimait avant en fonction du nombre d’extents des rollbacks et de l’activité générale est désormais précisée avec les tablespaces d’UNDO directement en secondes avec le paramètre dynamique undo_retention=nbsec.

alter system set undo_retention = 1800;

Dans le fichier d’initialisation de l’instance :

init<INSTANCE>.ora

undo_retention=1800

Paramètre undo_suppress_errors

En étant en mode undo dans la gestion des segments d’annulation, des commandes set transaction use rollback segment "rbs_name" peuvent subsister, ce qui provoque des messages d’erreur que l’on peut demander à ne pas voir grâce au paramètre dynamique undo_suppress_errors :

alter system set undo_suppress_errors = <TRUE | FALSE>;

Dans le fichier d’initialisation de l’instance :

init<INSTANCE>.ora

undo_suppress_errors=true

La vue V$UNDOSTAT

La vue statistique V$UNDOSTAT est utilisable que le tablespace d’UNDO soit en mode manuel ou en mode automatique, toutefois les valeurs dans cette vue sont nulles lorsque le mode manuel est appliqué.

La vue V$UNDOSTAT collecte les données statistiques sur un tablespace d’UNDO. Chaque ligne de la vue V$UNDOSTAT correspond aux statistiques d’un intervalle de temps de 10 minutes (BEGIN_TIME, END_TIME) et sont triées par ordre décroissant sur la colonne BEGIN_TIME. La vue V$UNDOSTAT contient au maximum 1008 lignes et donne les statistiques des tablespaces d’UNDO sur 7 jours glissants.

Les statistiques disponibles dans la vue V$UNDOSTAT permettent de dimensionner au mieux les tablespaces d’UNDO (taille, paramètre UNDO_RETENTION, etc.) car les statistiques disponibles incluent

• la consommation d’espace (undoblks).
• la concurrence des transactions.
• le temps en secondes des requêtes les plus lentes.

Tuning du paramètre UNDO_RETENTION pour un tablespace d’UNDO

Dans ce contexte, la taille du tablespace d’UNDO est fixe et ne peut être augmentée faute d’espace disque, aussi le paramètre UNDO_RETENTION va être redimensionné à une valeur optimale en fonction de l’activité de la base de données et la taille du tablespace d’UNDO. La formule ci-dessous permet de calculer approximativement la valeur optimale du paramètre UNDO_RETENTION :

Cette formule se base sur la vue V$UNDOSTAT (UNDO_BLOCK_PER_SEC), aussi cette valeur optimale doit être calculée après une durée d’activité significative de la base de données.

Pour retrouver la taille actuelle du tablespace d’UNDO (ACTUAL UNDO SIZE) :

SELECT SUM(a.bytes) "UNDO_SIZE"
  FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
  WHERE c.contents = 'UNDO'
  AND   c.status = 'ONLINE'
  AND   b.name = c.tablespace_name
  AND   a.ts# = b.ts#;

UNDO_SIZE
----------
209715200

Pour retrouver la statistique UNDO_BLOCK_PER_SEC :

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;


UNDO_BLOCK_PER_SEC
------------------
3.12166667

Pour retrouver le paramètre DB_BLOCK_SIZE :

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';

DB_BLOCK_SIZE [KByte]
---------------------
4096

Les tables dérivées permettent de tout obtenir en une seule requête (ACTUAL UNDO SIZE, UNDO RETENTION, OPTIMAL UNDO RETENTION) :

SELECT  d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
        SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
        ROUND((d.undo_size / (to_number(f.value) *
            g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM  (
        SELECT SUM(a.bytes) undo_size
        FROM    v$datafile a,
                v$tablespace b,
                dba_tablespaces c
        WHERE  c.contents = 'UNDO'
        AND    c.status = 'ONLINE'
        AND    b.name = c.tablespace_name
        AND    a.ts# = b.ts#
      ) d,
      v$parameter e,
      v$parameter f,
      (
        SELECT  MAX(undoblks/((end_time-begin_time)*3600*24))
                undo_block_per_sec
        FROM     v$undostat
      ) g
WHERE  e.name = 'undo_retention'
AND    f.name = 'db_block_size'

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
------------------------ -------------------- ----------------------------
                     200                10800                        16401

Tuning de la taille du tablespace d’UNDO pour une base de données

Si la taille de l’espace disque n’est pas le facteur limitant, la taille nécessaire pour le tablespace d’UNDO peut être calculée en fonction du paramètre UNDO_RETENTION avec l’approximation ci-dessous :

Comme précédemment, cette formule se base sur la vue V$UNDOSTAT (UNDO_BLOCK_PER_SEC), aussi cette valeur optimale doit être calculée après une durée d’activité significative de la base de données.

La requête ci-dessous avec l’utilisation des tables dérivées permet d’obtenir un rapide coup d’œil sur la taille nécessaire du tablespace d’UNDO pour une base de données en cours d’activité :

SELECT  d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
        SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
        (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
            g.undo_block_per_sec) / (1024*1024)  "NEEDED UNDO SIZE [MByte]"
FROM (
        SELECT SUM(a.bytes) undo_size
        FROM   v$datafile a,
               v$tablespace b,
               dba_tablespaces c
        WHERE c.contents = 'UNDO'
        AND   c.status = 'ONLINE'
        AND   b.name = c.tablespace_name
        AND   a.ts# = b.ts#
      ) d,
      v$parameter e,
      v$parameter f,
      (
        SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
        FROM   v$undostat
      ) g

WHERE e.name = 'undo_retention'
AND   f.name = 'db_block_size'/

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
------------------------ -------------------- ------------------------
                     200                10800               131.695313

Dans le cas pratique ci-dessus, la requête retourne une taille utile pour le tablespace d’UNDO (NEEDED UNDO SIZE) qui est inférieure à la taille actuelle du tablespace d’UNDO (ACTUAL UNDO SIZE). Si c’est le cas, de l’espace est gaspillé et on peut opter pour :

• réduire la taille du tablespace d’UNDO
• ou augmenter le paramètre UNDO_RETENTION pour utiliser de l’espace additionnel.