La taille allouée à une table et la taille utilisée par ses données : pas la même chose! - Size allocated to table Versus data
Introduction
Cet article rejoint celui-ci "Evolution taille table : Insert, Delete, Truncate, Drop, Shrink, Move" de Mars 2017 : http://dbaoraclesql.canalblog.com/archives/2017/03/19/34974508.html. Mais cette fois je vais m'attarder sur la taille de la table versus la taille de ses données. Et ce n'est pas du tout la même chose comme nous allons le voir!
Points d'attention
NA.
Base de tests
N'importe quelle base Oracle; tests sur une 12.1.
Exemples
============================================================================================
CREATE TABLE, INSERTs et tailles
============================================================================================
Je crée une table de test toute simple, dans le tablespace USERS. Etant en mode deferred_segment_creation, le CREATE TABLE ne crée aucun segment avant le premier INSERT.
SQL> CREATE TABLE zztest_taille(id NUMBER, nom VARCHAR2(50 CHAR));
Table created.
SQL> select tablespace_name from dba_tables where table_name = 'ZZTEST_TAILLE';
TABLESPACE_NAME
------------------------------
USERS
SQL> select BYTES, BLOCKS from user_segments where segment_name = 'ZZTEST_TAILLE';
no rows selected
SQL> show parameter deferred
NAME TYPE VALUE
------------------------------------ --------------
deferred_segment_creation boolean TRUE
J'insère 10 000 enregistrements.
SQL> begin
for i in 1..10000 loop
insert into zztest_taille values(i, 'DUPOND');
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> select count(*) from zztest_taille;
COUNT(*)
----------
10000
SQL> commit;
Commit complete.
Taille avec USER_SEGMENTS
La vue USER_SEGMENTS est renseigné automatiquement, sans que l'on ait à générer des stats. La définition des colonnes que j'utilise est la suivante :
- BYTES : size, in bytes, of the segment.
- BLOCKS : size, in Oracle blocks, of the segment.
SQL> select BYTES, BLOCKS from user_segments where segment_name = 'ZZTEST_TAILLE';
BYTES BLOCKS
---------- ----------
262144 32
Attention, dans USER_SEGMENTS, on a la taille totale allouée par Oracle à une table et non pas la taille des données de cette table :-) En clair, on a seulement la taille des blocs alloués au segment mais sans indication sur la taille des données dans ces blocs; en clair on peut avoir une taille largement supérieure à la réalité des données.
Taille avec LENGTHB
Si on calcule plus précisément la taille des données avec la fonction LENGTHB (qui donne en octets la taille du contenu d'une colonne), voilà ce qu'on obtient. Le résultat est juste, ne serait-ce que pour le champ NOM car 10 000 fois DUPOND, qui fait 6 octets, donne bien 60 000 octets. Pour la colonne ID je fais le pari que c'est correct aussi :-)
Nous obtenons une taille pour les données de 98 894 octets et non pas de 262 144 octets, soit presque trois fois moins!
SQL> select sum(lengthb(id)), sum(lengthb(nom)) from zztest_taille;
SUM(LENGTHB(ID)) SUM(LENGTHB(NOM))
---------------- -----------------
38894 60000
A quoi est due cette différence? Lors des INSERT, Oracle a alloué plus de blocs qu'il n'en fallait exactement; n'oubliez pas qu'Oracle alloue des extents et non pas des blocs à l'unité. On a donc dans le segment des blocs d'un extent qui n'ont pas encore été utilisés mais qui font quand même partie du segment.
SQL> select BYTES, blocks from user_segments where segment_name = 'ZZTEST_TAILLE';
BYTES BLOCKS
---------- ----------
262144 32
SQL> select BLOCK_SIZE from user_tablespaces where tablespace_name = 'USERS';
BLOCK_SIZE
----------
8192
SQL> select 32 * 8192 from dual;
32*8192
----------
262144
Taille avec les stats
Que nous disent les stats si on fait un calcul de celles-ci sur 100% de la table? Que la table occupe 28 blocs au lieu de 32 et que la longueur moyenne des enregistrements est de 11 caractères. Sachant que la table ne contient aucun caractère sur plus de 1 octet, on peut dire que, selon les stats, la table fait 10 000 * 11 = 110 000 octets. Il s'agit d'une approximation mais on se rapproche de la réalité.
La définition des colonnes de USER_TAB_STATISTICS que j'utilise est la suivante :
- NUM_ROWS : number of rows in the object.
- BLOCKS : number of used blocks in the object.
- EMPTY_BLOCKS : number of empty blocks in the object.
- AVG_SPACE : average available free space in the object.
- AVG_ROW_LEN : average row length, including row overhead.
- SAMPLE_SIZE : sample size used in analyzing the table.
SQL> exec dbms_stats.gather_table_stats(ownname=>'HR', tabname=>'ZZTEST_TAILLE', estimate_percent=>100 );
PL/SQL procedure successfully completed.
SQL> select NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN, SAMPLE_SIZE from user_tab_statistics where table_name = 'ZZTEST_TAILLE';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE
---------- ---------- ------------ ---------- ----------- -----------
10000 28 0 0 11 10000
Taille avec DBMS_SPACE.UNUSED_SPACE
Maintenant utilisons le package DBMS_SPACE, servant justement à avoir des infos plus précises sur la gestion de l'espace dans un objet comme une table ou un index. En premier nous utiliserons la fonction DBMS_SPACE.UNUSED_SPACE.
J'exécute le code de Tim HALL trouvé ici : https://oracle-base.com/dba/script?category=monitoring&file=unused_space.sql
Contenu du fichier taille.sql
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
v_total_blocks NUMBER;
v_total_bytes NUMBER;
v_unused_blocks NUMBER;
v_unused_bytes NUMBER;
v_last_used_extent_file_id NUMBER;
v_last_used_extent_block_id NUMBER;
v_last_used_block NUMBER;
BEGIN
DBMS_SPACE.UNUSED_SPACE (segment_owner => UPPER('HR'),
segment_name => UPPER('ZZTEST_TAILLE'),
segment_type => UPPER('TABLE'),
total_blocks => v_total_blocks,
total_bytes => v_total_bytes,
unused_blocks => v_unused_blocks,
unused_bytes => v_unused_bytes,
last_used_extent_file_id => v_last_used_extent_file_id,
last_used_extent_block_id => v_last_used_extent_block_id,
last_used_block => v_last_used_block);
DBMS_OUTPUT.PUT_LINE('v_total_blocks :' || v_total_blocks);
DBMS_OUTPUT.PUT_LINE('v_total_bytes :' || v_total_bytes);
DBMS_OUTPUT.PUT_LINE('v_unused_blocks :' || v_unused_blocks);
DBMS_OUTPUT.PUT_LINE('v_unused_bytes :' || v_unused_bytes);
END;
/
On a les mêmes tailles que dans USER_SEGMENTS!
SQL> @taille
v_total_blocks :32
v_total_bytes :262144
v_unused_blocks :0
v_unused_bytes :0
PL/SQL procedure successfully completed.
Taille avec DBMS_SPACE.SPACE_USAGE
Nous allons voir maintenant le taux de remplissage des blocs avec une autre fonction du même package, DBMS_SPACE.SPACE_USAGE.
J'utilise le code de Donald BURLESON trouvé ici : http://www.dba-oracle.com/t_packages_dbms_space_usage.htm
Et là encore, nous avons des données différentes... alors qu'on utilise deux fonctions du même package...
Contenu du fichier taille02.sql
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;
begin
dbms_space.space_usage('HR',
'ZZTEST_TAILLE',
'TABLE',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);
dbms_output.put_line('Unformatted Blocks = ' || v_unformatted_blocks);
dbms_output.put_line('Unformatted Bytes = ' || v_unformatted_bytes);
dbms_output.put_line('FS1 Bytes (at least 0 to 25% free space) = ' || v_fs1_bytes);
dbms_output.put_line('FS1 Blocks(at least 0 to 25% free space) = ' || v_fs1_blocks);
dbms_output.put_line('FS2 Bytes (at least 25 to 50% free space)= ' || v_fs2_bytes);
dbms_output.put_line('FS2 Blocks(at least 25 to 50% free space)= ' || v_fs2_blocks);
dbms_output.put_line('FS3 Bytes (at least 50 to 75% free space) = ' || v_fs3_bytes);
dbms_output.put_line('FS3 Blocks(at least 50 to 75% free space) = ' || v_fs3_blocks);
dbms_output.put_line('FS4 Bytes (at least 75 to 100% free space) = ' || v_fs4_bytes);
dbms_output.put_line('FS4 Blocks(at least 75 to 100% free space)= ' || v_fs4_blocks);
dbms_output.put_line('Full Blocks in segment = ' || v_full_blocks);
dbms_output.put_line('Full Bytes in segment = ' || v_full_bytes);
end;
/
SQL> @taille02
Unformatted Blocks = 0
Unformatted Bytes = 0
FS1 Bytes (at least 0 to 25% free space) = 0
FS1 Blocks(at least 0 to 25% free space) = 0
FS2 Bytes (at least 25 to 50% free space)= 0
FS2 Blocks(at least 25 to 50% free space)= 0
FS3 Bytes (at least 50 to 75% free space) = 0
FS3 Blocks(at least 50 to 75% free space) = 0
FS4 Bytes (at least 75 to 100% free space) = 49152
FS4 Blocks(at least 75 to 100% free space)= 6
Full Blocks in segment = 22
Full Bytes in segment = 180224
PL/SQL procedure successfully completed.
Taille avec AWR et DBA_HIST_SEG_STAT
Voyons voir maintenant le contenu d'une vue AWR, la vue DBA_HIST_SEG_STAT. Elle a plus d'infos que la vue USER_SEGMENTS et, surtout, elle renferme deux notions différentes : l'espace alloué et l'espace utilisé! C'est là la clé du sujet : Oracle alloue plus de blocs qu'il n'en utilise pour stocker les données.
- SPACE_USED_TOTAL : Number of bytes used by user data.
- SPACE_USED_DELTA : Delta value for space used by user data (in bytes). A negative value indicates the number of bytes deleted in the segment.
- SPACE_ALLOCATED_TOTAL : The number of bytes that are allocated.
- SPACE_ALLOCATED_DELTA : Delta value for the space allocated (in bytes). A negative value indicates the number of bytes deallocated to the tablespace.
SQL> SELECT SEG.SNAP_ID, SNA.BEGIN_INTERVAL_TIME, SPACE_ALLOCATED_TOTAL, SEG.SPACE_USED_TOTAL
FROM DBA_HIST_SEG_STAT SEG, DBA_HIST_SEG_STAT_OBJ OBJ, DBA_HIST_SNAPSHOT SNA
WHERE SEG.OBJ# = OBJ.OBJ# AND
SEG.SNAP_ID = SNA.SNAP_ID AND
OBJ.OBJECT_NAME = upper('ZZTEST_TAILLE') AND
OBJ.OWNER = 'HR'
ORDER BY SEG.SNAP_ID;
SNAP_ID BEGIN_INTERVAL_TIME SPACE_ALLOCATED_TOTAL SPACE_USED_TOTAL
---------- ------------------------- --------------------- -----------------
70 01-NOV-18 07.00.02.687 AM 262144 146554
============================================================================================
Synthèse des tailles suite à un CREATE TABLE et des INSERTs
============================================================================================
A ce niveau nous avons les sept tailles suivantes, de la plus grande à la plus petite :
Trois résultats avec la même taille :
- USER_SEGMENTS : 262 144 octets ==> taille réservée par Oracle pour la table et non pas taille des données. Largement sur-estimée par rapport aux données MAIS donne la taille d'un objet utilisé pour un DATAPUMP, une sauvegarde RMAM, un FULL TABLE SCAN...
- DBMS_SPACE.UNUSED_SPACE : 262 144 octets ==> même taille que dans user_segments.
- DBA_HIST_SEG_STAT : colonne SPACE_ALLOCATED_TOTAL 262 144 octets ==> même taille que dans user_segments.
Quatre résultats avec des tailles diféfrentes :
- DBMS_SPACE.SPACE_USAGE : 180 224 octets
- DBA_HIST_SEG_STAT : colonne SPACE_USED_TOTAL 146 554 octets : estimation de la taille des données.
- USER_TAB_STATISTICS : 110 000 octets ==> taille des données : c'est une estimation, même si on a décidé d'analyser toute la table.
- Calcul avec la table : 98 894 octets ==> taille des données : c'est le calcul le plus précis mais aussi le plus long selon le nombre de lignes dans la table puisque cela oblige Oracle à faire un FULL TABLE SCAN.
============================================================================================
DELETE et influence sur les tailles
============================================================================================
Poursuivons nos tests en supprimant 90% des données de façon homogène dans les blocs, pour éviter d'avoir des blocs vides et d'autres pleins.
SQL> delete from zztest_taille where mod(id,10) <> 0;
9000 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from zztest_taille;
COUNT(*)
----------
1000
Et maintenant, quels sont les impacts sur les tailles allouées et utilisées?
SQL> select BYTES, BLOCKS from user_segments where segment_name = 'ZZTEST_TAILLE';
BYTES BLOCKS
---------- ----------
262144 32
SQL> select sum(lengthb(id)), sum(lengthb(nom)) from zztest_taille;
SUM(LENGTHB(ID)) SUM(LENGTHB(NOM))
---------------- -----------------
3893 6000
SQL> exec dbms_stats.gather_table_stats(ownname=>'HR', tabname=>'ZZTEST_TAILLE', estimate_percent=>100 );
PL/SQL procedure successfully completed.
SQL> select NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN, SAMPLE_SIZE from user_tab_statistics where table_name = 'ZZTEST_TAILLE';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE
---------- ---------- ------------ ------------ ----------- -----------
1000 28 0 0 11 1000
SQL> @taille
v_total_blocks :32
v_total_bytes :262144
v_unused_blocks :0
v_unused_bytes :0
PL/SQL procedure successfully completed.
Tiens, un résultat étonnant : 0 bytes sans explication :-)
SQL> @taille02
Unformatted Blocks = 0
Unformatted Bytes = 0
FS1 Bytes (at least 0 to 25% free space) = 0
FS1 Blocks(at least 0 to 25% free space) = 0
FS2 Bytes (at least 25 to 50% free space)= 0
FS2 Blocks(at least 25 to 50% free space)= 0
FS3 Bytes (at least 50 to 75% free space) = 0
FS3 Blocks(at least 50 to 75% free space) = 0
FS4 Bytes (at least 75 to 100% free space) = 229376
FS4 Blocks(at least 75 to 100% free space)= 28
Full Blocks in segment = 0
Full Bytes in segment = 0
PL/SQL procedure successfully completed.
SQL> SELECT SEG.SNAP_ID, SNA.BEGIN_INTERVAL_TIME, SPACE_ALLOCATED_TOTAL, SEG.SPACE_USED_TOTAL
FROM DBA_HIST_SEG_STAT SEG, DBA_HIST_SEG_STAT_OBJ OBJ, DBA_HIST_SNAPSHOT SNA
WHERE SEG.OBJ# = OBJ.OBJ# AND
SEG.SNAP_ID = SNA.SNAP_ID AND
OBJ.OBJECT_NAME = upper('ZZTEST_TAILLE') AND
OBJ.OWNER = 'HR'
ORDER BY SEG.SNAP_ID;
SNAP_ID BEGIN_INTERVAL_TIME SPACE_ALLOCATED_TOTAL SPACE_USED_TOTAL
---------- ------------------------- --------------------- ----------------
70 01-NOV-18 07.00.02.687 AM 262144 146554
============================================================================================
Synthèse des tailles suite à un DELETE
============================================================================================
Quelle influence a eu ce DELETE massif sur les deux tailles, allouée et utilisée?
Trois tailles identiques : taille allouée par Oracle pour le segment de la table.
- USER_SEGMENTS : 262 144 octets, la taille de la table n'a pas changé malgré le DELETE! Normal, Oracle a alloué à un instant t N blocs pour un segment, il garde ces blocs pour les futurs INSERTs ou UPDATEs; les seuls moyens de ramener la taille du segment à un niveau plus juste en rapport à la taille des données est de faire un MOVE, un SHRINK, un TRUNCATE (avec éventuellement une sauvegarde des données avant dans une table temporaire puis faire un INSERT dans la table vidée ...).
- DBMS_SPACE.UNUSED_SPACE : 262 144 octets, la taille n'a pas changé malgré le DELETE!
- DBA_HIST_SEG_STAT : j'attends d'avoir un snapshot AWR de pris :-)
Quatre tailles différentes pour l'espace alloué aux données.
- DBMS_SPACE.SPACE_USAGE : 229 376 octets.
- DBA_HIST_SEG_STAT : j'attends d'avoir un snapshot AWR de pris :-)
- USER_TAB_STATISTICS : 11 000 octets, le DELETE est bien pris en compte.
- Calcul avec la table : 9 893 octets, le DELETE là aussi est bien pris en compte.
============================================================================================
Conclusion
============================================================================================
Faites attention quand vous parlez de taille ou d'espace de stockage sous Oracle. Il y a fondamentalement deux concepts : la taille réservée à l'objet via son segment et la taille des données proprement dite dans ces objets (table, index...).
Quelle différence vous me direz? Lorsqu'on fait du Datapump, RMAN ou un FULL TABLE SCAN, Oracle va traiter le nombre de blocs sous le HWM (High Water Mark), dont certains peuvent être vides mais qui ont contenu à un moment donné des enregistrements : cela entraîne de nombreuses lectures sur le disque dur, très coûteuses en temps. En revanche, dans le cas d'un SORT ou du hachage d'une table, Oracle va devoir lire tous les blocs de la requête MAIS la taille réelle des données joue un rôle important dans ce cas : par exemple une table de 100 000 blocs mais avec seulement 50 000 rows aura un SORT (lié à un ORDER BY) qui ira bien plus vite qu'une table de 100 000 blocs mais contenant 1 000 000 de rows :-)
Dans la vraie vie, faisons une comparaison avec deux documents word de 100 pages : l'un avec les pages noircies de mots, l'autre avec juste un mot par page. Dans les deux cas, l'impression des cent pages prendra le même temps (essayez!) MAIS la lecture des pages se fera 100 fois plus vite dans le deuxième cas. Pour Oracle c'est pareil, on lit le nombre de blocs alloués mais on traite ensuite uniquement les données récupérées dans ces blocs.
Vous avez compris le problème? Une table avec 10Go de données qui est vidée par un DELETE continue d'occuper 10Go sur le disque dur alors qu'il n'y a plus un seul enregistrement dedans... Donc pensez-y quand vous regardez la taille des objets les plus volumineux dans DBA_SEGMENTS; cette info peut-être fausse!
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)