Example create table buffer pools

Fiche express
Type	Exemple DDL Oracle
Contexte	Affectation d'une table à un buffer pool (KEEP / RECYCLE)
Voir aussi	Example create table · Example create tablespace

Oracle gère par défaut un seul cache mémoire pour les blocs de données (le database buffer cache), régi par un algorithme LRU (Least Recently Used). Il est possible de réserver une partie de ce cache pour deux pools dédiés, afin d'influencer la durée de vie des blocs d'une table donnée en mémoire :

• KEEP — conserve en mémoire des blocs qu'on ne veut pas voir sortir

 du cache par vieillissement naturel : petites tables de référence très consultées (tables
 de correspondance, paramétrage), lues en full scan de façon répétée.

• RECYCLE — au contraire, pour des blocs qu'on ne s'attend pas à

 revoir bientôt : gros objets parcourus de façon dispersée, où laisser ces blocs polluer le
 cache par défaut ferait sortir prématurément des blocs plus utiles.

Sous-dimensionner un pool KEEP revient à annuler son intérêt (les blocs finissent par sortir comme dans le pool par défaut) ; sous-dimensionner un pool RECYCLE provoque des relectures disque inutiles (un bloc sort du pool avant que la transaction en cours ait fini de l'utiliser). Sur-dimensionner l'un ou l'autre gaspille simplement de la mémoire qui manquera au pool par défaut.

Configuration côté instance

Depuis Oracle9i, les anciens paramètres DB_BLOCK_BUFFERS, BUFFER_POOL_KEEP et BUFFER_POOL_RECYCLE (conservés pour compatibilité descendante) sont remplacés par des paramètres dynamiques dédiés — ne pas les combiner dans le même fichier de paramètres, Oracle lève une erreur :

ALTER SYSTEM SET db_cache_size        = 200M;
ALTER SYSTEM SET db_keep_cache_size   = 32M;
ALTER SYSTEM SET db_recycle_cache_size = 32M;

La mémoire allouée aux pools KEEP/RECYCLE est prise sur celle du cache par défaut, pas ajoutée en plus.

Script

/* --------------------------------------------------------
 * CREATE TABLE (EMP) — BUFFER_POOL KEEP
 * -------------------------------------------------------- */

PROMPT Dropping Table (emp)...
DROP TABLE emp CASCADE CONSTRAINTS
/

PROMPT Creating Table (emp)...
CREATE TABLE emp (
    empno     NUMBER(4)
  , ename     VARCHAR2(10)
  , job       VARCHAR2(9)
  , mgr       NUMBER(4)
  , hiredate  DATE
  , sal       NUMBER(7,2)
  , comm      NUMBER(7,2)
  , deptno    NUMBER(2)
)
TABLESPACE users
STORAGE (
  INITIAL     128K
  NEXT        128K
  MINEXTENTS  1
  MAXEXTENTS  121
  PCTINCREASE 0
  BUFFER_POOL KEEP
)
/

/* --------------------------------------------------------
 * CREATE TABLE (DEPT) — BUFFER_POOL RECYCLE
 * -------------------------------------------------------- */

PROMPT Dropping Table (dept)...
DROP TABLE dept CASCADE CONSTRAINTS
/

PROMPT Creating Table (dept)...
CREATE TABLE dept (
    deptno  NUMBER(2)
  , dname   VARCHAR2(14)
  , loc     VARCHAR2(13)
)
TABLESPACE users
STORAGE (
  INITIAL     128K
  NEXT        128K
  MINEXTENTS  1
  MAXEXTENTS  121
  PCTINCREASE 0
  BUFFER_POOL RECYCLE
)
/

L'exemple ci-dessus n'est qu'illustratif : en pratique DEPT (petite table de référence) serait plutôt un candidat pour KEEP, pas RECYCLE — le choix dépend du schéma d'accès réel, pas de la taille seule.

Voir aussi

• Example create table — création de table sans affectation de buffer pool spécifique
• Example create tablespace — création du tablespace USERS utilisé ici