Mise en RAM du tablespace temporaire d’une instance Oracle

Introduction

Depuis Oracle 9i, la gestion des tablespaces temporaires est grandement simplifiée. Certains obstacles existant jusqu’à la version 8i sont désormais levés : ainsi depuis la version 9i, les tablespaces temporaires n’ont plus de SCN (Sequence Change Number) associé, ce qui permet d’occulter les fichiers de données des tablespaces temporaires lors des sauvegardes.

La disparition du SCN pour les tablespaces temporaires permet également d’envisager une nouvelle option qui consiste à implémenter les tablespaces temporaires en RAM (/tmp), comme il est possible de mettre les bases tempdb Sybase en RAM. Cette option doit être envisagée lorsque la mise à disposition de systèmes de fichiers de tmpfs n’est pas possible.

La mise en mémoire (RAM) du tablespace temporaire permet d’accroître drastiquement la vitesse d’écriture des blocs temporaires pour les opérations de tri, les jointures par hachage, etc. opérations qui passent par le tablespace temporaire. Un exemple concret de gains de performances est mis en évidence.

La seconde partie de l’article décrit plus en avant les modifications dans les procédures d’exploitation (redémarrage, sauvegardes, restauration) en gardant bien à l’esprit que la mise en RAM du tablespace temporaire ne peut être envisagé qu’à partir des versions 9i.

Cas concret de mise en évidence des gains de performances

La mise en pratique va consister à comparer des requêtes sollicitant le tablespace temporaire :

• l’utilisateur USER1 a pour tablespace temporaire par défaut TEMP, tablespace temporaire pour lequel les fichiers de données sont sur un disque n’ayant pas des performances I/O intéressantes.
• l’utilisateur USER2 a pour tablespace temporaire par défaut TEMP2, tablespace temporaire pour lequel les fichiers de données sont en RAM.

Script de création du tablespace temporaire TEMP et de l’utilisateur USER1 :

create temporary tablespace temp
      tempfile '/oem/oracle/OEMD1ORA/TEMP_01.dbf'
      size 100M reuse autoextend off;

create user USER1 identified by USER1
default tablespace users temporary tablespace temp;

grant resource, connect to USER1;

Script de création du tablespace temporaire TEMP2 et de l’utilisateur USER2 :

create temporary tablespace temp2
      tempfile '/tmp/.oracle/OEMD1ORA/TEMP2_01.dbf'
      size 100M reuse autoextend off;

create user USER2 identified by USER2
default tablespace users temporary tablespace temp2;

grant resource, connect to USER1;

2 exemples sont proposés :

• La première requête #1 sollicite peu le tablespace temporaire et renvoie 611041 lignes au client
• La seconde requête #2 sollicite beaucoup le tablespace temporaire (tri et jointure par hachage) et retourne peu de lignes au client.

select max(version), refcon
from RISK.audit_mvt
group by refcon

select
  h.refcon,t.sicovam,t.libelle,h.quantite,
  t.type, a.datemodif,h.backoffice,
  h.infosbackoffice
from RISK.histomvts h,
     RISK.titres t,
     RISK.audit_mvt a
where h.sicovam = t.sicovam
and h.refcon = a.refcon
and a.version =
        (select max(aa.version)
               from RISK.audit_mvt aa
               where aa.refcon = a.refcon
        )
and h.opcvm in (select ident from RISK.folio
connect by prior
  ident=mgr start with mgr = 13402) order by 3,1

Les gains sont particulièrement conséquents pour la requête #2 qui sollicitent intensivement le tablespace temporaire.

Modifications des procédures d’exploitation

Normalisation

Une normalisation simple peut consister à créer les tablespaces temporaires en RAM dans les répertoires

/tmp/.oracle/<ORACLE_SID>

La nomenclature .oracle est utilisée afin de les rendre non visibles trop facilement.

Procédure de redémarrage : init_instance.ksh v 1.2

Dans le contexte d’un redémarrage de la machine, les fichiers du tablespace temporaire disparaissent intégralement puisque /tmp est vidé. Pour pallier à ce problème, on peut se baser sur le fichier généré par la commande ci-dessous (fichier généré lors de la sauvegarde)

alter database backup controlfile to trace as '${CFG}/${INST}_controlfile.txt'

($DMP est le répertoire des sauvegardes et $INST le nom de l’instance).

À la fin de ce fichier sont consignées les créations des tablespaces temporaires :

# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/tmp/.oracle/OEMD1ORA/temp_01.dbf'
     SIZE 52428800  REUSE AUTOEXTEND OFF;
# End of tempfile additions.

Lorsque l’instance au redémarrage ne peut pas retrouver les fichiers du tablespace temporaire, les erreurs ci-dessous sont consignées dans le fichier alert de l’instance :

ORA-01157: cannot identify/lock data file 81 - see DBWR trace file
ORA-01110: data file 81: '/tmp/.oracle/OEMD1ORA/temp_01.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Pour recréer rapidement les fichiers des tablespaces temporaires en RAM :

mkdir -p /tmp/.oracle/<ORACLE_SID>

sqlplus "/ as sysdba"

alter database tempfile 'path_to_tempfile' drop;

alter tablespace <temporary tablespace> add tempfile 'path_to_tempfile'
    size <size>M reuse autoextend off;

Le script init_instance.ksh a été modifié pour prendre en compte ces spécificités.

Procédure de sauvegarde à chaud (mode archivelog) : backup.ksh v1.2

La documentation Oracle 8i - sauvegarde à chaud (mode archivelog) publiée en 2005 concernait le cas des instances Oracle 8i, versions pour lesquelles les fichiers des tablespaces temporaires apparaissaient dans la vue V$BACKUP. Depuis la version 9i, ce n’est plus le cas avec la disparition du SCN pour les tablespaces temporaires.

En conséquence, il n’est plus nécessaire de sauvegarder les fichiers des tablespaces temporaires, aussi l’union avec la vue v$tempfile peut être retirée dans la requête de récupération de fichiers à sauvegarder :

sqlplus -s "/ as sysdba" > ${TMP_FILELIST}

set feedback off;
set heading off;
set linesize 1000;
set pagesize 10000;
select name from v\$datafile
union
select name from v\$tempfile;
EOF

sqlplus -s "/ as sysdba" > ${TMP_FILELIST}

set feedback off;
set heading off;
set linesize 1000;
set pagesize 10000;
select name from v\$datafile;
EOF

Procédure de restauration (mode archivelog) : getbackup.ksh v1.6

La documentation Oracle 8i - copie d’une base de données à partir d’une sauvegarde à chaud publiée en 2005 concernait le cas des instances Oracle 8i. Pour les versions 8i, les fichiers des tablespaces temporaires font partie de la commande CREATE CONTROLFILE :

CREATE CONTROLFILE REUSE DATABASE "OEM" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXDATAFILES 254
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 (
      '/cgcdb/oracle/OEMD1ORA/redo1_01.log',
      '/cgcdb/oracle/OEMD1ORA/redo1_02.log') SIZE 1M,
GROUP 2 (
      '/cgcdb/oracle/OEMD1ORA/redo2_01.log',
      '/cgcdb/oracle/OEMD1ORA/redo2_02.log') SIZE 1M,
GROUP 3 (
      '/cgcdb/oracle/OEMD1ORA/redo3_01.log',
      '/cgcdb/oracle/OEMD1ORA/redo3_02.log') SIZE 1M
DATAFILE
      '/cgcdb/oracle/OEMD1ORA/SYSTEM_01.dbf',
      '/cgcdb/oracle/OEMD1ORA/RBS_01.dbf',
      '/cgcdb/oracle/OEMD1ORA/USERS_01.dbf',
      '/cgcdb/oracle/OEMD1ORA/TEMP_01.dbf',
      '/cgcdb/oracle/OEMD1ORA/USERS_02.dbf',
      '/cgcdb/oracle/OEMD1ORA/INDX_01.dbf'
CHARACTER SET WE8ISO8859P1;

À partir de la version Oracle 9i, les fichiers de données des tablespaces temporaires ne sont plus dans la commande CREATE CONTROLFILE, ils sont dans un paragraphe à la fin du fichier généré par la commande alter database backup controlfile

alter database backup controlfile to trace as '${DMP}/${INST}_controlfile.txt'

...
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/tmp/.oracle/OEMD1ORA/temp_01.dbf'
     SIZE 52428800  REUSE AUTOEXTEND OFF;
# End of tempfile additions.
#

La procédure getbackup.ksh v1.6 prend en compte ces spécificités pour regénérer dynamiquement la création des tablespaces temporaires d’une instance vers une autre.