Nouvelle colonne DEFAULT VALUE NOT NULL : espace disque, datapump - New DEFAULT VALUE NOT NULL column: disk space, datapump

16 août 2020

Nouvelle colonne DEFAULT VALUE NOT NULL : espace disque, datapump - New DEFAULT VALUE NOT NULL column: disk space, datapump

Introduction
Voici un article qui va s'intéresser à une nouvelle fonctionnalité introduite dans Oracle11 (oui, la 11, qui est encore beaucoup utilisée). Cette fonctionnalité est la suivante : si on ajoute à une table existante une nouvelle colonne, qui est NOT NULL avec une DEFAULT VALUE, alors Oracle ne va pas faire un UPDATE des N enregistrements existants, il va tout simplement gérer une métadata; et c'est tout!

L'intérêt vous me direz? Imaginez que la table a 100 millions de lignes : je crée une colonne avec la valeur par défaut 'AUCUNE INFO' soit 11 octets * 100 000 000 et donc plus de 1Go de données à créer! Pour traiter cet UPDATE Oracle va générer énormément de REDO Logs, de blocs UNDO, avec tous les problèmes de risques de saturation de tablespaces que cela implique, et, de plus, il ne rendra la main au user qu'au bout d'une heure par exemple! Ajoutez à cela qu'un lock sera posé sur la table pendant toute cette période, empêchant qui que ce soit de la manipuler, et vous comprendrez que c'est une opération extrêmement lourde qu'il fallait améliorer. Et donc, depuis la V11, Oracle gére dans ce cas une simple métadata qu'il associe à toutes les lignes : c'est magique et hyper rapide :-) Mais attention : cela ne fonctionne que si on ajoute la colonne à une table déjà existante!

Seul problème que j'ai identifié : en cas d'un export/import Datapump cette fonctionnalité saute... normal puisqu'on crée une nouvelle table lors de l'import!

Points d'attention
N/A.

Base de tests
Une base 18c multi-tenants.

Exemples

============================================================================================
L'environnement de test
============================================================================================
Création de la première table de test : on se base sur dba_objects et on crée 1 250 000 enregistrements par duplication.
SQL> create table ZZOBJ01 AS SELECT * from dba_objects;
Table created.

SQL> select count(*) from ZZOBJ01;
COUNT(*)
----------
78363

SQL> insert into ZZOBJ01 (SELECT * from ZZOBJ01);
SQL> /
SQL> /
SQL> /
...
SQL> commit;

SQL> select count(*) from ZZOBJ01;
COUNT(*)
----------
1253808

La deuxième table de test est une copie de la première.
SQL> create table ZZOBJ02 as SELECT * from zzobj01;
Table created.

SQL> select count(*) from ZZOBJ02;
COUNT(*)
----------
1253808

La taille des deux tables est identique : normal.
SQL> select SEGMENT_NAME, round(BYTES/(1024*1024),2) AS "Taille MO" from dba_segments where segment_name in ('ZZOBJ01', 'ZZOBJ02') order by 1;
SEGMENT_NAME Taille MO
-------------------- ----------
ZZOBJ01 192
ZZOBJ02 192

============================================================================================
Ajout d'une nouvelle colonne
============================================================================================
On ajoute maintenant une nouvelle colonne dans les deux tables mais de deux façon différentes.

Première table : ajout d'une colonne sans aucune contrainte d'intégrité et remplie avec une chaîne de 50 caractères via un UPDATE.
SQL> ALTER TABLE ZZOBJ01 ADD ZZTEST VARCHAR2(50);
Table altered.

SQL> update ZZOBJ01 set ZZTEST='AZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOP';
1253808 rows updated.

SQL> commit;
Commit complete.

Sur la table 2, la colonne ajoutée possède les contraintes d'intégrité DEFAULT VALUE et NOT NULL, avec la même valeur que pour la table 1.
SQL> ALTER TABLE ZZOBJ02 ADD ZZTEST VARCHAR2(50) default 'AZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOP' not null;
Table altered.

Cette fois, la taille finale est bien différente : 192Mo contre 264Mo; 40% d'écart! Cette optimisation est excellente, on a un vrai gain de place, du moins dans mon exemple avec une valeur par défaut de 50 caractères, ce qui est rare, je vous l'accorde.
SQL> select SEGMENT_NAME, round(BYTES/(1024*1024),2) AS "Taille MO" from dba_segments where segment_name in ('ZZOBJ01', 'ZZOBJ02') order by 1;
SEGMENT_NAME Taille MO
-------------------- ----------
ZZOBJ01 264
ZZOBJ02 192

Et bien sur Oracle sait quelle est la valeur dans la colonne contenant la métadata et son traitement est transparent pour les utilisateurs.
SQL> SELECT 'zzobj01' AS "TABLE", count(*) AS "Nb" from zzobj01 where zztest = 'AZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOP'
UNION
SELECT 'zzobj02', count(*) from zzobj02 where zztest = 'AZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOP';
TABLE Nb
------- ----------
zzobj01 1253808
zzobj02 1253808

============================================================================================
Datapump
============================================================================================
On va voir maintenant que cette fonctionnalité saute avec un import Datapump; c'est important de le savoir pour estimer l'espace disque nécessaire sur la base cible.

Estimation de la taille des tables à exporter
On génère les stats sur les deux tables pour faire un export datapump avec une méthode d'estimation de la taille basée sur le nombre de blocs, d'où l'intérêt des stats.
SQL> exec dbms_stats.gather_table_stats('HR', 'ZZOBJ01');
PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('HR', 'ZZOBJ02');
PL/SQL procedure successfully completed.

Pour cet export Datapump, pas besoin d'indiquer un directory ni un fichier dump puisqu'on ne génère aucun fichier, même pas un log. Tiens, on a bien la même taille que le SELECT ci-dessus :-)
[oracle@vbgeneric ~]$ expdp HR ESTIMATE_ONLY=YES NOLOGFILE=YES TABLES=HR.ZZOBJ01, HR.ZZOBJ02
Export: Release 12.2.0.1.0 - Production on Sat Aug 8 12:00:36 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "HR"."SYS_EXPORT_TABLE_01": HR/******** ESTIMATE_ONLY=YES NOLOGFILE=YES TABLES=HR.ZZOBJ01, HR.ZZOBJ02
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "HR"."ZZOBJ01" 264 MB
. estimated "HR"."ZZOBJ02" 192 MB
Total estimation using BLOCKS method: 456 MB
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 8 12:00:57 2020 elapsed 0 00:00:09

Export/Import Datapump
Maintenant j'exporte réellement les deux tables. Tiens, bizarre : les deux tables ont la même taille, mais aucune ne correspond à sa vraie taille en base source, pas même la première... étrange, surtout que Datapump avait trouvé 264Mo pour la première, celle créée de façon très basique et là on a 10% de moins.
[oracle@vbgeneric ~]$ expdp HR DIRECTORY=DATA_PUMP_DIR DUMPFILE=ZZOBJ0102_EXP.dump LOGFILE=ZZOBJ0102_EXP.log TABLES=HR.ZZOBJ01, HR.ZZOBJ02
Export: Release 12.2.0.1.0 - Production on Sat Aug 8 12:05:20 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "HR"."SYS_EXPORT_TABLE_01": HR/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=ZZOBJ0102_EXP.dump LOGFILE=ZZOBJ0102_EXP.log TABLES=HR.ZZOBJ01, HR.ZZOBJ02
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "HR"."ZZOBJ02" 230.4 MB 1253808 rows
. . exported "HR"."ZZOBJ01" 230.4 MB 1253808 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/orcl12c/dpdump/49BFF8A6BB912582E0530100007F8BE4/ZZOBJ0102_EXP.dump
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sat Aug 8 12:06:03 2020 elapsed 0 00:00:37

La taille du fichier datapump généré est de 461Mo : donc les tailles données apr l'export sont correctes.
SQL> ! ls -l /u01/app/oracle/admin/orcl12c/dpdump/49BFF8A6BB912582E0530100007F8BE4/ZZOBJ0102_EXP.dump
-rw-r----- 1 oracle oinstall 483565568 Aug 8 12:06 /u01/app/oracle/admin/orcl12c/dpdump/49BFF8A6BB912582E0530100007F8BE4/ZZOBJ0102_EXP.dump

SQL> select round(483565568/(1024*1024),2) AS "Taille" from dual;
Taille
-------
461.16

Et maintenant si j'importe? Est-ce que la table ZZOBJ02 aura la même taille que dans la base source? Pour mon test, j'importe dans la même base (c'est plus simple) mais en renommant les tables pour les recréer. On voit que pour l'import la taille est identique à celle des tables dans l'export.
[oracle@vbgeneric ~]$ impdp HR DIRECTORY=DATA_PUMP_DIR DUMPFILE=ZZOBJ0102_EXP.dump LOGFILE=ZZOBJ0102_IMP.log TABLES=HR.ZZOBJ01, HR.ZZOBJ02 REMAP_TABLE=HR.ZZOBJ01:ZZOBJ01_IMPDP, HR.ZZOBJ02:ZZOBJ02_IMPDP
REMAP_TABLE=HR.ZZOBJ01:ZZOBJ01_IMPDP, HR.ZZOBJ02:ZZOBJ02_IMPDP
Import: Release 12.2.0.1.0 - Production on Sun Aug 9 05:14:57 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "HR"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01": HR/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=ZZOBJ0102_EXP.dump LOGFILE=ZZOBJ0102_IMP.log TABLES=HR.ZZOBJ01, HR.ZZOBJ02 REMAP_TABLE=HR.ZZOBJ01:ZZOBJ01_IMPDP, HR.ZZOBJ02:ZZOBJ02_IMPDP
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."ZZOBJ02_IMPDP" 230.4 MB 1253808 rows
. . imported "HR"."ZZOBJ01_IMPDP" 230.4 MB 1253808 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "HR"."SYS_IMPORT_TABLE_01" successfully completed at Sun Aug 9 05:16:01 2020 elapsed 0 00:00:59

Alors, que voit-on? Que les deux tables importées ont la même taille, 272Mo; c'est plus que les 230Mo affichés lors de l'import mais cela doit être lié à la création d'extents qui ne sont pas remplis : Oracle a besoin de 230Mo mais il crée des extents contenant N blocs dont certains sont vides mais ils sont quand même comptabilisés par Oracle. Pour la table 1, dont la taille passe de 264 à 272Mo, c'est juste 8 Mo de plus mais, si on compte en blocs de 8Ko, ce sont quand même 1 000 blocs et là j'ai du mal à voir des extents créés pour un total de 1 000 blocs...
SQL> select SEGMENT_NAME, round(BYTES/(1024*1024),2) AS "Taille MO" from dba_segments where segment_name LIKE ('ZZOBJ%') order by 1;
SEGMENT_NAME Taille MO
------------------------------
ZZOBJ01 264
ZZOBJ01_IMPDP 272
ZZOBJ02 192
ZZOBJ02_IMPDP 272

============================================================================================
Ordre DDL des tables
============================================================================================
Regardons les caractéristiques des tables via leur ordre DDL de création pour voir s'il y a des différences dans la création des tables; pour cela je lance la commande "ddl" sous SQLCl, le petit successeur parait-il de SQL*Plus.

Pour la table 1, pas de différence bien sur.
SQL> ddl zzobj01
CREATE TABLE "HR"."ZZOBJ01"
( "OWNER" VARCHAR2(128),
...
"ZZTEST" VARCHAR2(50)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

SQL> ddl zzobj01_IMPDP
CREATE TABLE "HR"."ZZOBJ01_IMPDP"
( "OWNER" VARCHAR2(128),
...
"ZZTEST" VARCHAR2(50)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

Idem pour la table 2, même ordre SQL : c'est normal, Datapump ne doit pas modifier la structure d'une table sauf si on le lui demande (par exemple en forçant la création de la table dans un tablespace avec un block size différent de celui de la base source). MAIS, comme la taille est passée de 192 à 272 Mo, cela veut dire que les enregistrements pour cette colonne contiennent la valeur 'AZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOP' et non pas la métadata. Cela est logique car la fonctionnalité liée à la métadata ne joue que si on ajoute une colonne dans une table DEJA existante; là ce n'est pas le cas, on crée une table avec cette donnée par défaut donc on a pas le gain de place lié à cette métadata.
SQL> ddl zzobj02 CREATE TABLE "HR"."ZZOBJ02"
( "OWNER" VARCHAR2(128),
...
"ZZTEST" VARCHAR2(50) DEFAULT 'AZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOP' NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

SQL> ddl zzobj02_IMPDP
CREATE TABLE "HR"."ZZOBJ02_IMPDP"
( "OWNER" VARCHAR2(128),
...
"ZZTEST" VARCHAR2(50) DEFAULT 'AZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOPAZERTYUIOP' NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

Conclusion
Voilà, nous avons démontré d'un point de vue espace disque l'intérêt de la nouvelle fonctionnalité implémentée en Oracle 11. En revanche cette fonctionnalité saute quand on fait un export import Datapump d'une table avec une colonne DEFAULT VALUE NOT NULL. C'est important quand on crée une nouvelle table de ne pas seulement se baser sur sa taille dans la base source; récupérer son code DDL avec dbms_metadata.get_ddll est important car on peut comprendre comment celle-ci va être recréée; à cela s'ajoute peut-être des problèmes de fragmentation, d'un SHRINK à faire mais c'est un autre sujet :-)

Alors, comment résoudre ce problème du Datapump? Je n'ai pas testé mais une solution serait de créer une table 1 SANS les colonnes NOT NULL DEFAULT VALUE, via un CTAS et un DBLink par exemple, puis faire un ALTER TABLE ADD colonne de ces colonnes NOT NULL DEFAULT VALUE; de la sorte, on aura à nouveau l'optimisation sur l'espace disque.

Posté par David DBA à 17:45 - Commentaires […] - Permalien [#]

Tags: Administration