Tailles de fichiers et tailles de LOB

Quelle est la taille des segments qui stockent des fichiers sous le forme de LOB ? La réponse à cette question dépend de nombreux paramètres :

  • le type de stockage : BasicFile ou SecureFile
  • le type de LOB : BLOB, CLOB, NCLOB ou objets stockés en LOB
  • la taille moyenne des fichiers : proche du paramètre CHUNK, de la taille de bloc
  • l’endroit où ils sont stockés : la table ou le segment LOB
  • le nombre de fichiers
  • la nature des fichiers : images, XML, texte, …
  • la vie de ces fichiers : le nombre de fois qu’ils sont modifiés et la manière dont ils sont modifiés
  • d’une multitude d’autres paramètres comme la rétention, compression déduplication pour n’en citer que quelques-uns
  • sans doute de la version d’Oracle et de ses bugs 😉

S’il est utile de comprendre la stratégie d’allocation des fichiers dans les LOB et le fonctionnement des paramètres associés, il est également utile de confronter la théorie à la réalité. Pour cela, vous réaliserez des tests.

Cet article présente les résultats d’un test simple de mise en oeuvre de BLOB stockés dans des SecureFiles. Il présente ces résultats et la méthode mise en oeuvre pour obtenir ces résultats.

Présentation du test

Le test qui suit compare la taille des différents segments associés à un BLOB et le volume des fichiers qui y sont stockés. La base de données utilisée est une base de données 11.2.0.3 sur Linux avec une taille de bloc de 8Ko. Les tests sont réalisés avec différentes tailles de fichiers de 1Ko, 10Ko, 100Ko et 1000Ko. La structure de stockage du BLOB des SecureFile est sans rétention ni aucune option de quelque sorte que ce soit. Celle des Basicfile est laissée par défaut.

Les résultats

Les résultats sont disponibles ci-dessous :

Stockage Fichiers Nb Taille (Ko) Total (Ko) Espace (Table) Espace (LOB) Espace (Index) Espace (Total) Ratio
Basicfile f1k.txt 1000 1 1000 1456 0 8 1464 146%
Basicfile f10k.txt 100 10 1000 40 1600 8 1648 165%
Basicfile f100k.txt 10 100 1000 40 1040 8 1088 109%
Basicfile f1000k.txt 1 1000 1000 40 1008 8 1056 106%
Basicfile f1k.txt 10000 1 10000 11536 0 8 11544 115%
Basicfile f10k.txt 1000 10 10000 104 16000 8 16112 161%
Basicfile f100k.txt 100 100 10000 40 10400 8 10448 104%
Basicfile f1000k.txt 10 1000 10000 40 10080 8 10128 101%
Basicfile f1k.txt 100000 1 100000 115464 0 8 115472 115%
Basicfile f10k.txt 10000 10 100000 584 160000 8 160592 161%
Basicfile f100k.txt 1000 100 100000 104 104000 8 104112 104%
Basicfile f1000k.txt 100 1000 100000 40 100800 8 100848 101%
Secufile f1k.txt 1000 1 1000 1456 40 8 1504 150%
Secufile f10k.txt 100 10 1000 40 1968 8 2016 202%
Secufile f100k.txt 10 100 1000 40 1968 8 2016 202%
Secufile f1000k.txt 1 1000 1000 40 2000 8 2048 205%
Secufile f1k.txt 10000 1 10000 11536 40 8 11584 116%
Secufile f10k.txt 1000 10 10000 104 18224 8 18336 183%
Secufile f100k.txt 100 100 10000 40 12128 8 12176 122%
Secufile f1000k.txt 10 1000 10000 40 12128 8 12176 122%
Secufile f1k.txt 100000 1 100000 115464 40 8 115512 116%
Secufile f10k.txt 10000 10 100000 520 177328 8 177856 178%
Secufile f100k.txt 1000 100 100000 104 122152 8 122264 122%
Secufile f1000k.txt 100 1000 100000 40 113968 8 114016 114%

Remis en forme de manière plus explicite, cela donne le graphique ci-dessous; on y constate, comme on peut s’y attendre que le ratio est mauvais lorsque (1) il y a peu de fichier et (2) lorsque la taille des fichiers est proche de celle des blocs/chunk. Si c’est votre cas, il faudra sans doute réfléchir à des optimisations possibles :

La méthode

Cette section présente la méthode utilisée. Vous y trouverez en particulier les scripts utilisés pour obtenir les résultats ci-dessus. Vous pourrez simplement les adapter à vos besoins plus précis et à des tests plus complets.

Générer des fichiers

N’utilisant pas la compression ni la déduplication, nous allons utiliser un unique fichier par taille que nous remplirons du caractère 0. Le script ci-dessous génère les-dits fichiers :

cd /home/oracle
dd if=/dev/zero of=f1k.txt bs=1k count=1
dd if=/dev/zero of=f10k.txt bs=1k count=10
dd if=/dev/zero of=f100k.txt bs=1k count=100
dd if=/dev/zero of=f1000k.txt bs=1k count=1000

ls -lh f1*
-rw-r--r-- 1 oracle oinstall 1000K 2012-04-20 08:49 f1000k.txt
-rw-r--r-- 1 oracle oinstall 100K 2012-04-20 08:49 f100k.txt
-rw-r--r-- 1 oracle oinstall 10K 2012-04-20 08:49 f10k.txt
-rw-r--r-- 1 oracle oinstall 1.0K 2012-04-20 08:49 f1k.txt

Créer et charger Table et LOB

Le script ci-dessous crée ensuite une table X dans le schéma DEMO
contenant une seule colonne de type BLOB :

create directory oracle_dir as '/home/oracle';
grant read on directory ORACLE_DIR to demo;

connect demo/demo
drop table X purge;

create table X(f blob)
lob(f) store as securefile f_lob
(tablespace users retention none);

On peut ensuite charger les fichiers dans la table et controler son contenu:

declare
v_x blob;
v_f bfile:=bfilename('ORACLE_DIR','f10k.txt');
begin
dbms_lob.open(v_f, DBMS_LOB.LOB_READONLY);
for i in 1..10000 loop
INSERT INTO X VALUES (EMPTY_BLOB()) RETURNING f INTO v_x;
dbms_lob.loadfromfile (v_x, v_f, dbms_lob.LOBMAXSIZE);
end loop;
dbms_lob.close(v_f);
commit;
end;
/

select count(*), max(length(f)), sum(length(f))
from x;

Libellés et tailles des SEGMENTS

Nous allons créer une requête qui récupère le nom du segment LOB, de l’index associé en fonction de la table :

set lines 90
col table_name format a5 new_value tabname
col column_name format a5
col segment_name format a25 new_value segname
col index_name format a25 new_value indname
select table_name, column_name, segment_name, index_name
from user_lobs
where table_name='X'
and column_name='F';

On peut ensuite utiliser DBMS_SPACE.SPACE_USAGE pour visualiser le détail de l’espace occupé dans le segment de table :

set serveroutput on

declare
v_unformatted_blocks NUMBER;
v_unformatted_bytes NUMBER;
v_fs1_blocks NUMBER;
v_fs1_bytes NUMBER;
v_fs2_blocks NUMBER;
v_fs2_bytes NUMBER;
v_fs3_blocks NUMBER;
v_fs3_bytes NUMBER;
v_fs4_blocks NUMBER;
v_fs4_bytes NUMBER;
v_full_blocks NUMBER;
v_full_bytes NUMBER;
v_total_blocks NUMBER;
v_total_bytes NUMBER;
v_used_blocks NUMBER;
v_used_bytes NUMBER;
begin
dbms_space.space_usage(
segment_owner => user,
segment_name => '&&tabname',
segment_type => 'TABLE',
unformatted_blocks => v_unformatted_blocks,
unformatted_bytes => v_unformatted_bytes,
fs1_blocks => v_fs1_blocks,
fs1_bytes => v_fs1_bytes,
fs2_blocks => v_fs2_blocks,
fs2_bytes => v_fs2_bytes,
fs3_blocks => v_fs3_blocks,
fs3_bytes => v_fs3_bytes,
fs4_blocks => v_fs4_blocks,
fs4_bytes => v_fs4_bytes,
full_blocks => v_full_blocks,
full_bytes => v_full_bytes);

v_total_blocks:=v_unformatted_blocks+
v_fs1_blocks+
v_fs2_blocks+
v_fs3_blocks+
v_fs4_blocks+
v_full_blocks;

v_total_bytes:=v_unformatted_bytes+
v_fs1_bytes+
v_fs2_bytes+
v_fs3_bytes+
v_fs4_bytes+
v_full_bytes;

v_used_blocks:= v_fs1_blocks+
v_fs2_blocks+
v_fs3_blocks+
v_fs4_blocks+
v_full_blocks;

v_used_bytes:= v_fs1_bytes+
v_fs2_bytes+
v_fs3_bytes+
v_fs4_bytes+
v_full_bytes;

dbms_output.put_line('Unformatted Space (Blocks/KB):'||
lpad(to_char(v_unformatted_blocks),7)||
'/'||
lpad(to_char(round(v_unformatted_bytes/1024)),7)||
'KB');
dbms_output.put_line('Free Space 1-25% (Blocks/KB):'||
lpad(to_char(v_fs1_blocks),7)||
'/'||
lpad(to_char(round(v_fs1_bytes/1024)),7)||
'KB');
dbms_output.put_line('Free Space 26-50% (Blocks/KB):'||
lpad(to_char(v_fs2_blocks),7)||
'/'||
lpad(to_char(round(v_fs2_bytes/1024)),7)||
'KB');
dbms_output.put_line('Free Space 51-75% (Blocks/KB):'||
lpad(to_char(v_fs3_blocks),7)||
'/'||
lpad(to_char(round(v_fs3_bytes/1024)),7)||
'KB');
dbms_output.put_line('Free Space 76-100% (Blocks/KB):'||
lpad(to_char(v_fs4_blocks),7)||
'/'||
lpad(to_char(round(v_fs4_bytes/1024)),7)||
'KB');
dbms_output.put_line('Full Space (Blocks/KB):'||
lpad(to_char(v_full_blocks),7)||
'/'||
lpad(to_char(round(v_full_bytes/1024)),7)||
'KB');
dbms_output.put_line('Total Space (Blocks/KB):'||
lpad(to_char(v_total_blocks),7)||
'/'||
lpad(to_char(round(v_total_bytes/1024)),7)||
'KB');
dbms_output.put_line('Used Space (Blocks/KB):'||
lpad(to_char(v_used_blocks),7)||
'/'||
lpad(to_char(round(v_used_bytes/1024)),7)||
'KB');
end;
/

On peut utiliser la 2nd forme de DBMS_SPACE.SPACE_USAGE pour visualiser le détail de l’espace occupé dans le cas où le segment LOB est un SecureFile :

set serveroutput on

declare
v_segment_size_blocks NUMBER;
v_segment_size_bytes NUMBER;
v_used_blocks NUMBER;
v_used_bytes NUMBER;
v_expired_blocks NUMBER;
v_expired_bytes NUMBER;
v_unexpired_blocks NUMBER;
v_unexpired_bytes NUMBER;
v_alloc_blocks NUMBER;
v_alloc_bytes NUMBER;
begin
DBMS_SPACE.SPACE_USAGE(
segment_owner => user,
segment_name => '&&segname',
segment_type => 'LOB',
segment_size_blocks => v_segment_size_blocks,
segment_size_bytes => v_segment_size_bytes,
used_blocks => v_used_blocks,
used_bytes => v_used_bytes,
expired_blocks => v_expired_blocks,
expired_bytes => v_expired_bytes,
unexpired_blocks => v_unexpired_blocks,
unexpired_bytes => v_unexpired_bytes);

v_alloc_blocks:=v_used_blocks+v_expired_blocks+v_unexpired_blocks;
v_alloc_bytes:=v_used_bytes+v_expired_bytes+v_unexpired_bytes;

dbms_output.put_line('Segment Size (Blocks/KB):'||
lpad(to_char(v_segment_size_blocks),7)||
'/'||
lpad(to_char(round(v_segment_size_bytes/1024)),7)||
'KB');
dbms_output.put_line('Used Space (Blocks/KB):'||
lpad(to_char(v_used_blocks),7)||
'/'||
lpad(to_char(round(v_used_bytes/1024)),7)||
'KB');
dbms_output.put_line('Expired Space (Blocks/KB):'||
lpad(to_char(v_expired_blocks),7)||
'/'||
lpad(to_char(round(v_expired_bytes/1024)),7)||
'KB');
dbms_output.put_line('Unexpired Space (Blocks/KB):'||
lpad(to_char(v_unexpired_blocks),7)||
'/'||
lpad(to_char(round(v_unexpired_bytes/1024)),7)||
'KB');
dbms_output.put_line('Alloc. Space (Blocks/KB):'||
lpad(to_char(v_alloc_blocks),7)||
'/'||
lpad(to_char(round(v_alloc_bytes/1024)),7)||
'KB');

end;
/

Dans le cas où le BLOB est stocké dans un Basicfile, on utilisera DBMS_SPACE.SPACE_USAGE avec la même forme que pour la table :

declare 
v_unform atted_blocks NUMBER;
v_unformatted_bytes NUMBER;
v_fs1_blocks NUMBER;
v_fs1_bytes NUMBER;
v_fs2_blocks NUMBER;
v_fs2_bytes NUMBER;
v_fs3_blocks NUMBER;
v_fs3_bytes NUMBER;
v_fs4_blocks NUMBER;
v_fs4_bytes NUMBER;
v_full_blocks NUMBER;
v_full_bytes NUMBER;
v_total_blocks NUMBER;
v_total_bytes NUMBER;
v_used_blocks NUMBER;
v_used_bytes NUMBER;
begin
dbms_space.space_usage(
segment_owner => user,
segment_name => '&&segname',
segment_type => 'LOB',
unformatted_blocks => v_unformatted_blocks,
unformatted_bytes => v_unformatted_bytes,
fs1_blocks => v_fs1_blocks,
fs1_bytes => v_fs1_bytes,
fs2_blocks => v_fs2_blocks,
fs2_bytes => v_fs2_bytes,
fs3_blocks => v_fs3_blocks,
fs3_bytes => v_fs3_bytes,
fs4_blocks => v_fs4_blocks,
fs4_bytes => v_fs4_bytes,
full_blocks => v_full_blocks,
full_bytes => v_full_bytes);

v_total_blocks:=v_unformatted_blocks+
v_fs1_blocks+
v_fs2_blocks+
v_fs3_blocks+
v_fs4_blocks+
v_full_blocks;

v_total_bytes:=v_unformatted_bytes+
v_fs1_bytes+
v_fs2_bytes+
v_fs3_bytes+
v_fs4_bytes+
v_full_bytes;

v_used_blocks:= v_fs1_blocks+
v_fs2_blocks+
v_fs3_blocks+
v_fs4_blocks+
v_full_blocks;

v_used_bytes:= v_fs1_bytes+
v_fs2_bytes+
v_fs3_bytes+
v_fs4_bytes+
v_full_bytes;

dbms_output.put_line('Unformatted Space (Blocks/KB):'||
lpad(to_char(v_unformatted_blocks),7)||
'/'||
lpad(to_char(round(v_unformatted_bytes/1024)),7)||
'KB');
dbms_output.put_line('Free Space 1-25% (Blocks/KB):'||
lpad(to_char(v_fs1_blocks),7)||
'/'||
lpad(to_char(round(v_fs1_bytes/1024)),7)||
'KB');
dbms_output.put_line('Free Space 26-50% (Blocks/KB):'||
lpad(to_char(v_fs2_blocks),7)||
'/'||
lpad(to_char(round(v_fs2_bytes/1024)),7)||
'KB');
dbms_output.put_line('Free Space 51-75% (Blocks/KB):'||
lpad(to_char(v_fs3_blocks),7)||
'/'||
lpad(to_char(round(v_fs3_bytes/1024)),7)||
'KB');
dbms_output.put_line('Free Space 76-100% (Blocks/KB):'||
lpad(to_char(v_fs4_blocks),7)||
'/'||
lpad(to_char(round(v_fs4_bytes/1024)),7)||
'KB');
dbms_output.put_line('Full Space (Blocks/KB):'||
lpad(to_char(v_full_blocks),7)||
'/'||
lpad(to_char(round(v_full_bytes/1024)),7)||
'KB');
dbms_output.put_line('Total Space (Blocks/KB):'||
lpad(to_char(v_total_blocks),7)||
'/'||
lpad(to_char(round(v_total_bytes/1024)),7)||
'KB');
dbms_output.put_line('Used Space (Blocks/KB):'||
lpad(to_char(v_used_blocks),7)||
'/'||
lpad(to_char(round(v_used_bytes/1024)),7)||
'KB');
end;
/

Enfin, on utilise à nouveau DBMS_SPACE.SPACE_USAGE pour visualiser le détail de l’espace occupé dans l’index associé au LOB :

set serveroutput on

declare
v_unformatted_blocks NUMBER;
v_unformatted_bytes NUMBER;
v_fs1_blocks NUMBER;
v_fs1_bytes NUMBER;
v_fs2_blocks NUMBER;
v_fs2_bytes NUMBER;
v_fs3_blocks NUMBER;
v_fs3_bytes NUMBER;
v_fs4_blocks NUMBER;
v_fs4_bytes NUMBER;
v_full_blocks NUMBER;
v_full_bytes NUMBER;
v_total_blocks NUMBER;
v_total_bytes NUMBER;
v_used_blocks NUMBER;
v_used_bytes NUMBER;
begin
dbms_space.space_usage(
segment_owner => user,
segment_name => '&&indname',
segment_type => 'INDEX',
unformatted_blocks => v_unformatted_blocks,
unformatted_bytes => v_unformatted_bytes,
fs1_blocks => v_fs1_blocks,
fs1_bytes => v_fs1_bytes,
fs2_blocks => v_fs2_blocks,
fs2_bytes => v_fs2_bytes,
fs3_blocks => v_fs3_blocks,
fs3_bytes => v_fs3_bytes,
fs4_blocks => v_fs4_blocks,
fs4_bytes => v_fs4_bytes,
full_blocks => v_full_blocks,
full_bytes => v_full_bytes);

v_total_blocks:=v_unformatted_blocks+
v_fs1_blocks+
v_fs2_blocks+
v_fs3_blocks+
v_fs4_blocks+
v_full_blocks;

v_total_bytes:=v_unformatted_bytes+
v_fs1_bytes+
v_fs2_bytes+
v_fs3_bytes+
v_fs4_bytes+
v_full_bytes;

v_used_blocks:= v_fs1_blocks+
v_fs2_blocks+
v_fs3_blocks+
v_fs4_blocks+
v_full_blocks;

v_used_bytes:= v_fs1_bytes+
v_fs2_bytes+
v_fs3_bytes+
v_fs4_bytes+
v_full_bytes;

dbms_output.put_line('Unformatted Space (Blocks/KB):'||
lpad(to_char(v_unformatted_blocks),7)||
'/'||
lpad(to_char(round(v_unformatted_bytes/1024)),7)||
'KB');
dbms_output.put_line('Free Space 1-25% (Blocks/KB):'||
lpad(to_char(v_fs1_blocks),7)||
'/'||
lpad(to_char(round(v_fs1_bytes/1024)),7)||
'KB');
dbms_output.put_line('Free Space 26-50% (Blocks/KB):'||
lpad(to_char(v_fs2_blocks),7)||
'/'||
lpad(to_char(round(v_fs2_bytes/1024)),7)||
'KB');
dbms_output.put_line('Free Space 51-75% (Blocks/KB):'||
lpad(to_char(v_fs3_blocks),7)||
'/'||
lpad(to_char(round(v_fs3_bytes/1024)),7)||
'KB');
dbms_output.put_line('Free Space 76-100% (Blocks/KB):'||
lpad(to_char(v_fs4_blocks),7)||
'/'||
lpad(to_char(round(v_fs4_bytes/1024)),7)||
'KB');
dbms_output.put_line('Full Space (Blocks/KB):'||
lpad(to_char(v_full_blocks),7)||
'/'||
lpad(to_char(round(v_full_bytes/1024)),7)||
'KB');
dbms_output.put_line('Total Space (Blocks/KB):'||
lpad(to_char(v_total_blocks),7)||
'/'||
lpad(to_char(round(v_total_bytes/1024)),7)||
'KB');

dbms_output.put_line('Used Space (Blocks/KB):'||
lpad(to_char(v_used_blocks),7)||
'/'||
lpad(to_char(round(v_used_bytes/1024)),7)||
'KB');

end;
/

Conclusion

Vous ne manquerez sans doute pas de réaliser vos propres tests pour mesurer comment cet espace évolue en fonction de différents scénarios d’utilisation. Et n’oubliez pas les autres critères d’utilisation et notamment les performances ou la gestion de la concurrence des accès…