Le stockage des données dans une base Oracle 11g

Cet article va aborder de manière simple les fondamentaux du stockage dans une base de données Oracle 11g. Les éléments qui vont être traités sont :

  • TABLESPACE
  • DATAFILE (fichiers de données)
  • SEGMENT
  • EXTENT
  • BLOC


Cet ordre est important car, pour rappel :

  • Un TABLESPACE (notion logique) va être composé d’un ou plusieurs fichiers (notion physique) appelés DATAFILE (fichiers de données)
  • Un TABLESPACE va contenir des SEGMENTs de données (par exemple).
  • Un SEGMENT va être constitué d’EXTENTs.
  • Un EXTENT va être constitué de BLOCS.

Exemple  :  On va créer une table

create table example ( a varchar2(1000) )

Si rien n’est précisé, cette table va être crée dans le TABLESPACE par défaut de l’utilisateur ou de la base ( sinon on doit préciser le TS dans lequel on veut la créer )
Exemple  :

create table example ( a varchar2(1000) )   tablespace EXAMPLE

Par défaut :
En 10g, Un segment composé de 1 Extent (de 8 blocs ) est créé à la création de la table.
En 11g,  Ça dépend du paramètre DEFERRED_SEGMENT_CREATION (par défaut = Yes )
Cela signifie que la création du segment de données va être différée au moment de l’insertion de ligne dans la table.
A la création de la table, le segment sera seulement référencé dans le dictionnaire, mais aucun segment de données ne sera créé  (pas de ligne dans la table USER_SEGMENTS).
Au premier INSERT dans la table , le segment de données va être créé.
La question maintenant à se poser est : Quand on créé un segment de données, de combien d’extents est il composé et quelle est la taille des extents ?
Cela dépend du paramètre que l’on a mis lors de la création du TABLESPACE au niveau de l’onglet STORAGE pour la propriété Extent Allocation .
1 – Si l’on précise : Extent Allocation = UNIFORM avec une taille de 100k ( par exemple )
Chaque extent qui va être alloué aura une taille de 100K

  • Le 1er Extent aura une taille de 100K
  • Le 2eme  Extent aura une taille de 100K
  • Le 3eme … etc

Cela ressemble un peu à ce que l’on avait dans les vieilles versions d’Oracle où l’on pouvait préciser à la création du TABLESPACE ou à la création de la table, la taille du 1er extent (INITIAL) , la taille des extents suivants (NEXT) et éventuellement le % d’accroissement (PCTINCREASE).
A l’époque,  il fallait savoir à terme la taille de la table et on taillait les paramètres INITIAL et NEXT en conséquence.
Si on mettait un paramètre INITIAL trop petit et que la table se mettait à grandir, on se retrouvait avec un très grand nombre d’extents ( attention on était limité en nombre d’extents).
Si on mettait un paramètre INITIAL trop grand  , par exemple 1G , et que la table était très petite , on perdait quasiment 1G de place.
2 – Si on précise  Extent Allocation = AUTOMATIC
Oracle va allouer des extents de manière automatique avec l’algorithme suivant :

  • Le 1er extent aura une taille de 8 blocs ( 1 block = 8K , 8 x 8 = 64K ) cf paramètre DB_BLOCK_SIZE=8K par défaut
  • Le 2ème extent aura une taille de 64K
  • Le 3ème extent aura une taille de 64K

…  et cela jusqu’au 16ème extents …

  • Le 16eme extents aura une taille de 64K

A partir du 17eme extents la taille de l’extent passe à 128 blocks ( 128 * 8 = 1024K=1Mb )

  • Le 18eme extent aura une taille de 1Mb

…   et cela jusqu’au 79ème extents …

  • Le 79ème extent aura une taille de 1Mb

A partir du 80ème extents la taille de l’extent passe a 1024 blocks ( 1024 * 8 = 8Mb)

  • Le 81ème extent aura une taille de 8Mb

Et ainsi de suite ….
On se rend compte dans ce cas (et c’est le cas par défaut),

  • que  les petites tables auront n extents de 64k  ( n <= 16 )
  • que les moyennes tables auront 16 extents de 64k puis n extents de 1M ( n<= 63 )
  • que les grosses tables auront 16 extents de 64k puis 63 extents de 1M puis n extents de 8M

Pour savoir comment est organisé le segment, il faut passer par un ordre select :

SELECT  SEGMENT_NAME   ,
        SEGMENT_TYPE  ,
        TABLESPACE_NAME ,
        BLOCKS ,
        EXTENTS ,
        BYTES
FROM   USER_EXTENTS
WHERE  SEGMENT_NAME = ‘EXAMPLE’

(nom et type du segment , nom du tablespace dans lequel est stocké le segment , nombre de blocks et nombre d’extents pour ce segment , taille du segment )
On peut aussi visualiser les extents graphiquement avec la console Oracle Entreprise Manager 11g : Onglet Server > Lien Tablespaces > Cocher le radio bouton en face du tablespace Users
Puis Sélectionner « Show Tablespaces Content » dans la liste déroutante puis Appuyer sur le bouton « Go »
On peut voir que le segment Table HR.EXAMPLE comporte 88 extents (par exemple) pour une taille totale du segment de 138K

  • 16 extents de 64k
  • 63 extents de 1M
  • 9 extents de  8M

Voilà pourquoi est il important de savoir comment sont stockées les données. Lorsque le segment évolue ( INSERT, UPDATE et surtout DELETE), il peut alors devenir nécessaire de réorganiser les segments ( EXPORT/IMPORT , REORGANIZE, SHRINK) pour diminuer le nombre d’extents par segment et donc gagner de la place. Cela fera sûrement l’objet d’un autre article !

3 réflexions sur “Le stockage des données dans une base Oracle 11g”

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *