Le stockage des LOB est souvent négligé lors de la création des tables. Hors les choix de stockage ont un impact sur la performance d’accès aux données. Nous allons voir rapidement dans cet article l’avantage à utiliser le stockage SecureFiles proposé par Oracle à partir de la version 11gR1.

Arrivé avec la version 8i d’Oracle les Long Object Binary (LOB) sont aujourd’hui le standard de stockage pour les objets composites (semi-structured) ou purement binaire (Unstructured). La documentation de notre produit préféré décrit longuement pourquoi et quand utiliser ce type LOB qui se décline en ( BLOB, CLOB, NCLOB , BFILE). Cette documentation peut-être consultée sous les références suivantes :

“SecureFiles and Large Objects Developer’s Guide”

11g Release 2 (11.2) 11g Release 1 (11.1)
E18294-01 B28393-03

 

C’est d’ailleurs avec la version 11.1 qu’est apparue l’option de stockage SecureFiles.

Depuis la version d’Oracle 7 nous avions la possibilité de stocker des objets binaires de grande dimension dans la base mais avec des restrictions, la principale était l’impossibilité d’avoir plus d’une colonne LONG ou LONG RAW par table. Avec la version 8i ces restrictions ont disparu.

Description des Tables de Test

Nous pouvons donc facilement et simplement créer des tables avec les LOB dont nous avons besoins, voici un exemple :

CREATE TABLE emp_resume (
empno number(4) not null,
pdfname varchar2(100),
pdf BLOB,
memo CLOB,
picture BFILE);

ALTER TABLE emp_resume ADD CONSTRAINT emp_resume_pk PRIMARY KEY (empno);
Dans cet exemple nous utilisons toutes les valeurs par défaut. Utilisons SQL Developer pour les faire apparaître :

CREATE TABLE “SCOTT”.”EMP_RESUME”
(
“EMPNO” NUMBER(4,0) NOT NULL ENABLE,
“PDFNAME” VARCHAR2(100 CHAR),
“PDF” BLOB,
“MEMO” CLOB,
“PICTURE” BFILE,
CONSTRAINT “EMP_RESUME_PK” PRIMARY KEY (“EMPNO”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
NOCOMPRESS LOGGING
TABLESPACE “USERS” ENABLE
)

SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE “USERS”
LOB (“PDF”) STORE AS BASICFILE
( TABLESPACE “USERS” ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING )
LOB (“MEMO”) STORE AS BASICFILE ( TABLESPACE “USERS” ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING );

Focalisons-nous à présent uniquement sur le stockage de la colonne pdf et faisons varier ces paramètres dans l’objectif de comprendre quels sont les effets sur le stockage et la performance. Dans ce but cinq tables sont créées (emp_lobX) :

paramètre

emp_lob0

emp_lob1

emp_lob2

emp_lob3

emp_lob4

emp_lob5

FileType

BasicFile

BasicFile

SecureFile

SecureFile

SecureFile

SecureFile

Inrow

enable

disable

disable

disable

disable

disable

Cache

NO

YES

YES

YES

YES

YES

Tablespace

Users

lobstore

lobstore

lob16kstore

lob16kstore

lob16kstore

Compress

NA

NA

NO

NO

YES

YES

deduplicate

NA

NA

NO

NO

NO

YES

Nota : La taille du cache pour les blocks de 16k est de 256MB

Description des options :

FileType

C’est le type de stockage utilisé BasicFile | SecureFile

Inrow

Le fait que le stockage en row soit actif ou pas

Cache

Utilisation du Cache ou pas

Tablespace

Nom du tablespace de création du LOBSEGMENT. Trois cas testés dans le tablespace de la table, dans un tablespace différent de la table, dans un tablespace avec une taille de bloc différente.

Compress

Activation ou non de la compression, notons que le stockage en BasicFile ne permet pas l’utilisation de la compression.

deduplicate

Activation ou non de la déduplication, notons que le stockage en BasicFile ne permet pas l’utilisation de la compression

 Protocole de test

Dans le cadre de ce test l’utilisation d’un programme en python a été faite afin de charger les données dans les différentes tables :

Python version: 2.6.5
cx_Oracle version: 5.0.4
Oracle client: (11.1.0.7.0)
Oracle DB version: 11.2.0.1.0
Oracle client encoding: UTF-8

L’application python est hébergée sur un portable connecté à une base de données Oracle via une connexion Ethernet 100Mb/s. Les données chargées proviennent de la documentation Oracle, il s’agit des fichiers PDF des répertoires appdev.112, 39 fichiers pour 175MB et appdev.111 35 fichiers pour 186 MB donc un volume total de 371 MB.

Six tests ont été réalisés:

1. Chargement des 39 fichiers pdf dans chacune des tables.
2. Chargement simultané des 74 fichiers pdf dans chacune des tables.
3. Lecture des données des tables suivant 3 protocoles.
4. Lecture table lob0 …… lob5 puis lob0 ….. lob5
5. Lecture deux fois de suite de la même table.
6. Lecture simultanée de la même table.

Résultats : chargement

Le temps de chargement est exprimé en seconde et comprends à la fois la lecture sur disque le transfert via le réseau et l’écriture dans l’instance.

Tables

Chargement 1

Chargement simultané (Deux chargements en parallèle)

emp_lob0

46

52

emp_lob1

41

58

emp_lob2

34

44

emp_lob3

29

44

emp_lob4

35

44

emp_lob5

38

45

Sur la table emp_lob5 afin de voir l’effet de la déduplication le chargement des 39 fichiers est relancé, cela crée ainsi 39 entrées supplémentaire dans la table, ce temps de chargement et de 36 secondes.

 

Résultats : stockage

Sur le plan du stockage dans la base quel est l’espace utilisé ?

Tables

Taille de la table.

LOBindex

LOGSEGMENT

emp_lob0

64 KB

512KB

376MB

emp_lob1

128KB

512kB

376MB

emp_lob2

64 KB

64KB

419MB

emp_lob3

64 KB

32MB

425MB

emp_lob4

64 KB

32MB

229MB

emp_lob5

64 KB

32MB

229MB

Résultats : lecture

Les temps en seconde de ce test inclus l’écriture sur le disque local.

Tables.

Test Lecture 1

Test Lecture 2

Test Lecture 3

emp_lob0

47

43

66

emp_lob1

64

70

75

emp_lob2

37

36

63

emp_lob3

41

36

67

emp_lob4

41

38

65

emp_lob5

37

34

65

En conclusion

Les temps de chargement montrent des gains significatifs de l’ordre de 20% entre les résultats avec usage du stockage SecureFiles et ceux avec usage du BasicFiles.

Lors des tests de lecture nous observons un gain d’environ 15%, l’observation des performance réseau (10MB) fait apparaitre qu’il est le point de contention.

Le troisième point observé est lié à l’espace utilisé, le gain d’espace obtenu sur les données chargées est de l’ordre de 50%. Il faut cependant relativiser ce résultat car la compression est fortement dépendante du type de données. La décision d’activation doit être faite après une série de tests représentatifs de l’utilisation effective. Elle est a priori inutile sur des images en jpeg.

Au vu de ces tests, l’usage des SecureFiles me semble recommandé du fait des gains mesurés, de la possible utilisation de la compression et de la déduplication qu’offre cette fonctionnalité.