Nouveauté 11g R2: Segment Deferred Creation

Si vous travaillez avec des bases 11g R2 il est possible que vous tombiez sur l’erreur suivante lors d’un export:

EXP-00003: no storage definition found for segment(0, 0)
. . exporting table                  T1

Le descriptif de l’erreur est plutôt clair : il indique qu’il n’existe pas de segment associé à la table T1.
En effet, si on requête la vue USER_SEGMENTS pour récupérer le segment associé à la table T1 on constate que ce segment n’existe pas :

SQL> select count(*) from user_segments where segment_name='T1';
 
  COUNT(*)
----------
         0

SQL> select count(*) from user_tables where table_name='T1';
 
  COUNT(*)
----------
         1

Ceci est en fait dû à une nouvelle fonctionnalité de la 11g R2 appelée « Segment Deferred Creation ». En effet, jusqu’à la 11g R1 inclus lorsque qu’on créait une table vide, Oracle allouait automatiquement de l’espace (un extent). On avait donc un segment associé à cette table. Avec la 11g R2, Oracle par défaut ne crée plus de segment pour une table tant qu’on n’a pas inséré de lignes dedans ou qu’on n’ait pas demandé explicitement à Oracle de la faire via la commande ALLOCATE EXTENT :

SQL> alter table t1 allocate extent;
 
Table altered
 
SQL> select count(*) from user_segments where segment_name='T1';
 
  COUNT(*)
----------
         1

Ce comportement par défaut d’Oracle en 11g R2 peut-être changé en touchant au paramètre d’instance DEFERRED_SEGMENT_CREATION. Par défaut celui-ci est à TRUE ce qui veut dire que la création du segment lors du CREATE TABLE n’est pas immédiate. Ce paramètre peut être modifiable au niveau de la session :

SQL> drop table t1 purge;
 
Table dropped
 
SQL> create table t1 (c1 NUMBER, C2 varchar2(50));
 
Table created
 
SQL> select count(*) from user_segments where segment_name='T1';
 
  COUNT(*)
----------
         0
 
SQL> drop table t1 purge;
 
Table dropped
 
SQL> alter session set deferred_segment_creation=false;
 
Session altered
 
SQL> create table t1 (c1 NUMBER, C2 varchar2(50));
 
Table created
 
SQL> select count(*) from user_segments where segment_name='T1';
 
  COUNT(*)
----------
         1

Dans cet exemple on voit que lorsque le paramètre DEFERRED_SEGMENT_CREATION est à TRUE le segment n’est pas crée automatiquement lors de la commande DDL. Par contre lorsque le paramètre est à FALSE on retrouve le comportement d’Oracle des versions précédentes c’est à dire que le segment est crée immédiatement.

Il est également possible de spécifier cette option directement au niveau de la commande DDL via la clause SEGMENT CREATION.
Par défaut la clause est SEGMENT CREATION DEFERRED mais on peut spécifier à la place SEGMENT CREATION IMMEDIATE :

SQL> drop table t1 purge;
 
Table dropped
 
SQL> alter session set deferred_segment_creation=TRUE;
 
Session altered
 
SQL> create table t1 (c1 NUMBER, C2 varchar2(50)) SEGMENT CREATION DEFERRED;
 
Table created
 
SQL> select count(*) from user_segments where segment_name='T1';
 
  COUNT(*)
----------
         0
 
SQL> drop table t1 purge;
 
Table dropped
 
SQL> create table t1 (c1 NUMBER, C2 varchar2(50)) SEGMENT CREATION IMMEDIATE;
 
Table created
 
SQL> select count(*) from user_segments where segment_name='T1';
 
  COUNT(*)
----------
         1

Si vous effectuez ces tests sur votre base et que vous obtenez l’erreur « ORA-14223: Deferred segment creation is not supported for this table », c’est surement que vous utilisez le user SYS ou SYSTEM. Or cette fonctionnalité ne s'applique pas sur ces schémas là.

Dans la vue USER_TABLES existe en 11g R2 une nouvelle colonne SEGMENT_CREATED qui indique si oui ou non le segment associé à la table est crée :

SQL> drop table t1 purge;
 
Table dropped

SQL> create table t1 (c1 NUMBER, C2 varchar2(50)) ;

Table created.

SQL> select count(*) from user_segments where segment_name='T1';

  COUNT(*)
----------
         0

SQL> select SEGMENT_CREATED  from user_tables where table_name='T1';

SEG
---
NO

SQL> alter table T1 allocate extent;

Table altered.

SQL> select SEGMENT_CREATED  from user_tables where table_name='T1';

SEG
---
YES

Sachez aussi qu’en 11.2.0.1 la fonctionnalite Segment Deferred Creation ne peut pas être appliqué aux tables partitionnées, mais qu’en 11.2.0.2 c’est désormais possible.

Si un jour en analysant les statistiques d’exécution d’un plan vous constatez que le nombre de lectures logiques pour un Full Table Scan est de 0 c’est surement que la table n’a pas de segment associé :

SQL> create table t1 (c1 NUMBER, C2 varchar2(50)) ;

Table created.

SQL> select * from t1;

no rows selected

SQL> set autotrace traceonly statistics
SQL> select * from t1;

no rows selected


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        338  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> set autot off

Aucune lecture logique ni lecture physique n’a été effectué pour cette requête.
Maintenant si j’alloue de l’espace à la table T1 des lectures logiques seront effectuées (même si la table ne contient pas de lignes) :

SQL> alter table t1 allocate extent;

Table altered.

SQL> select * from t1;

no rows selected

SQL> set autotrace traceonly statistics
SQL> select * from t1;

no rows selected


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        338  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> set autot off

Pour ceux qui auront remarqué que j’ai exécuté 2 fois la requête pour les 2 cas, c’est en fait juste pour ne prendre en compte que les I/O liés à l’exécution de la requête elle-même et non pas les éventuels I/O effectués par Oracle lors du parsing de la requête.