DELETE dans une table : espace définitivement perdu ou non ?

**Ikebukuro** · 17/05/2016, 16h30

Bonjour,

J'ai souvent lu que suite à des Delete, l'espace occupé par les enregistrements supprimés n'était jamais réutilisé par Oracle. Néanmoins Ask Tom dit le contraire et j'ai voulu vérifier cela avec des tests.

Attention, il y a de quoi lire mais j'avoue ne pas tout comprendre (une fois de plus

), c'est pourquoi je sollicite votre aide!

Etape 1 : création d’une table avec 500 000 enregistrements.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE ZZZ_DDU01(ID NUMBER(7) NOT NULL, LIB VARCHAR2(20 CHAR));
 
DECLARE
V_NUM_MOD    NUMBER;
 
BEGIN
    FOR i in 1..500000
    LOOP
        INSERT INTO ZZZ_DDU01 VALUES(i,'LIB' || to_char(i));
        SELECT mod(i,1000) INTO V_NUM_MOD from dual;
 
-- Un commit est fait tous les 1000 insert.
        IF V_NUM_MOD = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
END;
COMMIT;
/

Etape 2 : génération des stats pour la table et lecture de ces stats

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘AFPL’, ‘ZZZ_DDU01’);

SELECT * from dba_tables where TABLE_NAME = ‘ZZZ_DDU01’;
Num_rows : 500 000
Blocks : 1378
Empty_blocks : 0
Avg_space : 0                  -- Pas d'espace perdu dans les blocs.
Avg_row_len : 15


--Taille de la table
select sum(bytes)/(1024*1024) AS "Taille Mo" from dba_segments where segment_name = 'ZZZ_DDU01';
 Taille Mo
----------
        11

--Vérification de l'espace perdu : nombre de blocs et de bytes avec utilisation de dbms_space.unused_space
SET SERVEROUTPUT ON;

declare
   v_num_tt_blk           number;
   v_num_tt_bytes         number;
   v_num_unu_blk          number;
   v_num_unu_bytes        number;
   v_num_last_ext_file_id number;
   v_num_last_ext_blk_id  number;
   v_num_last_used_blk    number;
 
 begin
   dbms_space.unused_space(
      segment_owner               => 'AFPL',
      segment_name              => 'ZZZ_DDU01',
      segment_type              => 'TABLE',
      total_blocks              => v_num_tt_blk,
      total_bytes               => v_num_tt_bytes,
      unused_blocks             => v_num_unu_blk,
      unused_bytes              => v_num_unu_bytes,
      last_used_extent_file_id  => v_num_last_ext_file_id,
      last_used_extent_block_id => v_num_last_ext_blk_id,
      last_used_block           => v_num_last_used_blk);
 
   dbms_output.put_line('Table ZZZ_DDU01' );
   dbms_output.put_line('------------------------------------------------------' );
   dbms_output.put_line('Total number of blocks = ' || TO_CHAR(v_num_tt_blk));
   dbms_output.put_line('Total unused blocks = ' || TO_CHAR(v_num_unu_blk));
   dbms_output.put_line('Total unused bytes = ' || TO_CHAR(v_num_unu_bytes));
 end;
/

Table ZZZ_DDU01
------------------------------------------------------
Total number of blocks = 1408
Total unused blocks    = 0
Total unused bytes    = 0

Etape 3 : Delete dans la table
On delete un enregistrement sur 2 (ceux pairs) pour bien générer des trous dans chaque bloc et pas uniquement dans les derniers.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT count(*) from ZZZ_DDU01;
COUNT(*)
----------
500000
 
Delete FROM ZZZ_DDU01 WHERE MOD(ID,2) = 0 ;
250000 rows deleted.
 
COMMIT;
 
SELECT count(*) FROM ZZZ_DDU01;
COUNT(*)
----------
250000

Etape 4 : Stats sur la table après les Delete et avoir régénéré les stats

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
EXEC DBMS_STATS.GATHER_TABLE_STATS('AFPL', 'ZZZ_DDU01');

--Stats sur la table : les stats ont changé  dans DBA_TABLES
SELECT * from dba_tables where TABLE_NAME = ‘ZZZ_DDU01’;
Num_rows : 250 000
Blocks : 1378
Empty_blocks : 0
Avg_space : 0 -- TRES BIZARRE car on devrait avoir perdu 50% de l'espace!
Avg_row_len : 15

--Taille de la table : pas de différence!
select sum(bytes)/(1024*1024) AS "Taille Mo" from dba_segments where segment_name = 'ZZZ_DDU01';
 Taille Mo
----------
        11 

--ORACLE ne détecte pas les blocs perdus ou l’espace perdu (50% quand même)?
SET SERVEROUTPUT ON;
declare
   v_num_tt_blk           number;
   v_num_tt_bytes         number;
   v_num_unu_blk          number;
   v_num_unu_bytes        number;
   v_num_last_ext_file_id number;
   v_num_last_ext_blk_id  number;
   v_num_last_used_blk    number;
 
 begin
   dbms_space.unused_space(
      segment_owner               => 'AFPL',
      segment_name              => 'ZZZ_DDU01',
      segment_type              => 'TABLE',
      total_blocks              => v_num_tt_blk,
      total_bytes               => v_num_tt_bytes,
      unused_blocks             => v_num_unu_blk,
      unused_bytes              => v_num_unu_bytes,
      last_used_extent_file_id  => v_num_last_ext_file_id,
      last_used_extent_block_id => v_num_last_ext_blk_id,
      last_used_block           => v_num_last_used_blk);
 
   dbms_output.put_line('Table ZZZ_DDU02' );
   dbms_output.put_line('------------------------------------------------------' );
   dbms_output.put_line('Total number of blocks = ' || TO_CHAR(v_num_tt_blk));
   dbms_output.put_line('Total unused blocks = ' || TO_CHAR(v_num_unu_blk));
   dbms_output.put_line('Total unused bytes = ' || TO_CHAR(v_num_unu_bytes));
 end;
/

Table ZZZ_DDU02
------------------------------------------------------
Total number of blocks = 1408
Total unused blocks = 0
Total unused bytes = 0 --on ne devrait avoir 5.5 mégas?

Etape 5 : insert dans la table de 250 000 enregistrements
On insère 250000 enregistrements dans la table pour revenir à 500000 enregistrements et voir si Oracle réutilise l’espace perdu ou non. Si oui : la taille de la table ne change pas, si non, la taille doit augmenter de 50%. On insère des données avec le ID situé après les 500 000 premiers enregistrements.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select max(id) from zzz_ddu01;
   MAX(ID)
----------
    499999
 
 
DECLARE
V_NUM_MOD    NUMBER;
 
BEGIN
    FOR i in 500001..750000
    LOOP
        INSERT INTO ZZZ_DDU01 VALUES(i,'LIB' || to_char(i));
        SELECT mod(i,1000) INTO V_NUM_MOD from dual;
 
-- Un commit est fait tous les 1000 insert.
        IF V_NUM_MOD = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
END;
COMMIT;
/

Etape 6 : stats sur la table après les Insert après avoir régénéré les stats

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
EXEC DBMS_STATS.GATHER_TABLE_STATS('AFPL', 'ZZZ_DDU01');
 
--Stats sur la table : les stats ont changé  dans DBA_TABLES
SELECT * from dba_tables where TABLE_NAME = ‘ZZZ_DDU01’;
Num_rows : 500 000
Blocks : 1378 --le nombre de blocs n’a pas changé!! Ca signifie qu'Oracle a récupéré tout l'espace des rows deletés?
Empty_blocks : 0
Avg_space : 0
Avg_row_len : 15
 
--Taille de la table
select sum(bytes)/(1024*1024) AS "Taille Mo" from dba_segments where segment_name = 'ZZZ_DDU01';
 Taille Mo
----------
        11 --la taille n’a pas changé
 
--ORACLE ne détecte pas les blocs perdus?
SET SERVEROUTPUT ON;
declare
   v_num_tt_blk           number;
   v_num_tt_bytes         number;
   v_num_unu_blk          number;
   v_num_unu_bytes        number;
   v_num_last_ext_file_id number;
   v_num_last_ext_blk_id  number;
   v_num_last_used_blk    number;
 
 begin
   dbms_space.unused_space(
      segment_owner               => 'AFPL',
      segment_name              => 'ZZZ_DDU01',
      segment_type              => 'TABLE',
      total_blocks              => v_num_tt_blk,
      total_bytes               => v_num_tt_bytes,
      unused_blocks             => v_num_unu_blk,
      unused_bytes              => v_num_unu_bytes,
      last_used_extent_file_id  => v_num_last_ext_file_id,
      last_used_extent_block_id => v_num_last_ext_blk_id,
      last_used_block           => v_num_last_used_blk);
 
   dbms_output.put_line('Table ZZZ_DDU02' );
   dbms_output.put_line('------------------------------------------------------' );
   dbms_output.put_line('Total number of blocks = ' || TO_CHAR(v_num_tt_blk));
   dbms_output.put_line('Total unused blocks = ' || TO_CHAR(v_num_unu_blk));
   dbms_output.put_line('Total unused bytes = ' || TO_CHAR(v_num_unu_bytes));
 end;
/
 
Table ZZZ_DDU02
------------------------------------------------------
Total number of blocks = 1408
Total unused blocks = 0
Total unused bytes = 0

Etape 7 : shrink sur la table pour voir si on retrouve des blocs perdus ou non

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
ALTER TABLE ZZZ_DDU01 ENABLE ROW MOVEMENT ; 
Table altered.
 
alter table ZZZ_DDU01 Shrink SPACE;
Table altered.

Etape 8 : stats sur la table après les Insert après avoir régénéré les stats

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
EXEC DBMS_STATS.GATHER_TABLE_STATS('AFPL', 'ZZZ_DDU02');
 
--Stats sur la table
SELECT * from dba_tables where TABLE_NAME = ‘ZZZ_DDU02’;
Num_rows : 500 000
Blocks : 1372
Empty_blocks : 0
Avg_space : 0
Avg_row_len : 15
Row_movement : Enabled
 
--Taille de la table : 
select sum(bytes)/(1024*1024) AS "Taille Mo" from dba_segments where segment_name = 'ZZZ_DDU02';
 Taille Mo
----------
        11
 
--ORACLE détecte des blocs perdus MAIS pourquoi n’ont-ils pas été éliminés lors du Shrink ?
SET SERVEROUTPUT ON;
declare
   v_num_tt_blk           number;
   v_num_tt_bytes         number;
   v_num_unu_blk          number;
   v_num_unu_bytes        number;
   v_num_last_ext_file_id number;
   v_num_last_ext_blk_id  number;
   v_num_last_used_blk    number;
 
 begin
   dbms_space.unused_space(
      segment_owner               => 'AFPL',
      segment_name              => 'ZZZ_DDU02',
      segment_type              => 'TABLE',
      total_blocks              => v_num_tt_blk,
      total_bytes               => v_num_tt_bytes,
      unused_blocks             => v_num_unu_blk,
      unused_bytes              => v_num_unu_bytes,
      last_used_extent_file_id  => v_num_last_ext_file_id,
      last_used_extent_block_id => v_num_last_ext_blk_id,
      last_used_block           => v_num_last_used_blk);
 
   dbms_output.put_line('Table ZZZ_DDU02' );
   dbms_output.put_line('------------------------------------------------------' );
   dbms_output.put_line('Total number of blocks = ' || TO_CHAR(v_num_tt_blk));
   dbms_output.put_line('Total unused blocks = ' || TO_CHAR(v_num_unu_blk));
   dbms_output.put_line('Total unused bytes = ' || TO_CHAR(v_num_unu_bytes));
 end;
/
 
Table ZZZ_DDU01
------------------------------------------------------
Total number of blocks = 1408
Total unused blocks = 6
Total unused bytes = 49152

Si je fais quelques calculs :
1408*8 = 11264 Ko pour la table.
6*8 = 48 Ko de libre soit 49152 octets soit (6/1408) * 100 = 0.42 % d’espace libre dans la table.
La table a un PCTFREE de 10 soit 10% de la table doit au minimum rester libre pour les updates. Nous avons 0.42%...

Je constate avec plaisir que Oracle arrive à récupérer l'espace des Delete mais les stats ne varient pas après les Delete et là je suis paumé.

Vous avez des idées sur le pourquoi du comment de tout ça?

**pachot** · 17/05/2016, 20h18

Bonjour,
L'espace libéré dans les blocs après les delete n'est pas désalloué (donc les stats montrent le même BLOCKS) mais il est réutilisable pour de nouveaux inserts.
Chaque segment garde un bitmap des blocs qui ont de l'espace libre.
Pour voir cet espace, il faut utiliser dbms_space.
Cordialement,
Franck.

**Oracle4Ever** · 17/05/2016, 22h15

Bonsoir,

Je rajoute à la réponse de Pachot que c’est normal que les stats Oracle après le delete donnent 1408 blocks utilisés (comme avant le delete) car effectivement ils sont à moitié vides, mais toujours alloués aux différents segments de la table. Et cela est important pour l’optimiseur de savoir que la table fait 1408 blocks car un un full scan table lui coutera 1048 block à lire que la table soit pleine, à moitié vide ou vide et il doit le savoir pour ne pas se lancer inutilement dans un full scan, il doit en tenir compte.

Oracle4Ever

**Ikebukuro** · 18/05/2016, 09h02

Je vous remercie tous les deux pour vos réponses.

Néanmoins je suis perplexe sur les points suivants :
Etape 2 : pourquoi Avg_space vaut 0? Ca signifie que la table n'a aucun byte d'espace libre alors qu'elle a un PCTFREE de 10? Doc Oracle : average amount of free space, in bytes, in a data block allocated to the table.
SELECT * from dba_tables where TABLE_NAME = ‘ZZZ_DDU01’;
Avg_space : 0

Etape 4 : on a encore Avg_space à 0 alors que j'ai supprimé 50% de mes enregistrements

Etape 8 : si je ne me suis pas trompé dans mes calculs, je me retrouve avec une table ayant 0.42% d'espace libre alors à quoi ça sert d'avoir un PCTFREE à 10% ?

**mnitu** · 18/05/2016, 11h41

Envoyé par Ikebukuro

...
Etape 2 : pourquoi Avg_space vaut 0? Ca signifie que la table n'a aucun byte d'espace libre alors qu'elle a un PCTFREE de 10? Doc Oracle : average amount of free space, in bytes, in a data block allocated to the table.
SELECT * from dba_tables where TABLE_NAME = ‘ZZZ_DDU01’;
Avg_space : 0

Etape 4 : on a encore Avg_space à 0 alors que j'ai supprimé 50% de mes enregistrements

...

Il me semble que avg_space n'est calculé que si vous utilisez l'ancienne commande: analyze table.