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…
