heap block compress

In a recent note showing how an index could become much larger than the underlying table because of the different ways that Oracle handles deletion from table and index blocks, I pointed out that Oracle would have to pick a moment to replace rows marked for deletion with a stub, or place-holder, showing where the row had been but reserving the rowid in case a process rolled back the delete. (This tied back to a note I had written showing that the row directory in a table block could become much larger than you might think possible.)

My closing question asked when Oracle would do this replacement, and how you could monitor it. This note is a partial answer to that question.

When a session inserts a row into a block (or updates a row in a way that increases its size) it has to check whether the row will fit into the available space. It’s possibe that there is enough space, but not at the top of the free space heap in the block. If this is the case, Oracle packs the block downwards to coalesce all the free space into a single chunk between the rows (stored at the end of the block) and the row directory. Since this is an expensive operation, Oracle only does it when absolutely necessary.

It may be easiest to see this with a block dump after a few cycles of inserting and deleting, so here are two partial dumps of the table block, the first just after a single row has been inserted and deleted, the second after four rows have been inserted and deleted:

nrow=1
frre=-1
fsbo=0x14
fseo=0x1e0a
avsp=0x1df6
tosp=0x1f8c
0xe:pti[0]	nrow=1	offs=0
0x12:pri[0]	offs=0x1e0a
block_row_dump:
tab 0, row 0, @0x1e0a
tl: 2 fb: --HDFL-- lb: 0x1

nrow=4
frre=-1
fsbo=0x1a
fseo=0x1948
avsp=0x1dea
tosp=0x1f86
0xe:pti[0]	nrow=4	offs=0
0x12:pri[0]	offs=0x1e0a
0x14:pri[1]	offs=0x1c74
0x16:pri[2]	offs=0x1ade
0x18:pri[3]	offs=0x1948
block_row_dump:
tab 0, row 0, @0x1e0a
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 1, @0x1c74
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 2, @0x1ade
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 3, @0x1948
tl: 2 fb: --HDFL-- lb: 0x1

Notice how row zero (pri[0]) is at offset 0x1e0a in the block – somewhere near the bottom – and rows 1, 2, and 3 are at offsets that decrease by 0x196: the rows are “stacked” on top of each other in the block, and are still holding space even though they have been deleted (note the D in the flag string “–HDFL–“) and have a total actual length of 2 (tl: 2).

Now take a look at the block just after I’ve inserted the 20th row:

ntab=1
nrow=20
frre=-1
fsbo=0x3a
fseo=0x1de4
avsp=0x1daa
tosp=0x1dd0
0xe:pti[0]	nrow=20	offs=0
0x12:pri[0]	offs=0x1f9e
0x14:pri[1]	offs=0x1f9c
0x16:pri[2]	offs=0x1f9a
0x18:pri[3]	offs=0x1f98
0x1a:pri[4]	offs=0x1f96
0x1c:pri[5]	offs=0x1f94
0x1e:pri[6]	offs=0x1f92
0x20:pri[7]	offs=0x1f90
0x22:pri[8]	offs=0x1f8e
0x24:pri[9]	offs=0x1f8c
0x26:pri[10]	offs=0x1f8a
0x28:pri[11]	offs=0x1f88
0x2a:pri[12]	offs=0x1f86
0x2c:pri[13]	offs=0x1f84
0x2e:pri[14]	offs=0x1f82
0x30:pri[15]	offs=0x1f80
0x32:pri[16]	offs=0x1f7e
0x34:pri[17]	offs=0x1f7c
0x36:pri[18]	offs=0x1f7a
0x38:pri[19]	offs=0x1de4
block_row_dump:
tab 0, row 0, @0x1f9e
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 1, @0x1f9c
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 2, @0x1f9a
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 3, @0x1f98
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 4, @0x1f96
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 5, @0x1f94
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 6, @0x1f92
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 7, @0x1f90
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 8, @0x1f8e
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 9, @0x1f8c
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 10, @0x1f8a
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 11, @0x1f88
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 12, @0x1f86
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 13, @0x1f84
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 14, @0x1f82
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 15, @0x1f80
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 16, @0x1f7e
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 17, @0x1f7c
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 18, @0x1f7a
tl: 2 fb: --HDFL-- lb: 0x1
tab 0, row 19, @0x1de4
tl: 406 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [400]

It was at this point that Oracle discovered that there was enough space in the block to insert a row (tosp – total space), but that the gap (which usually equates to avsp – available space) between the beginning of free space (fsbo) and the end of free space (fseo) was too small and the free space was scattered through the block. So, to insert the row, Oracle has rewritten the entire set of rows in the block, pushing them down to the bottom of the block. Notice how the row offsets now increase by just two at a time – until the last one that jumps from 0x1f7a to 0x1de4 for the row that I’ve just inserted.

This operation is known as a “heap block compress”, and from 10g gets reported in v$sysstat and v$sesstat so that we can see how often it happens. (This can be worth knowing in extreme cases, as it is a relatively slow, CPU intensive, operation, and can increase the impact of “buffer busy waits”). As a simple way of observing an example of this action in near real-time, you could modify my original pl/sql loop as follows:

rem
rem     Script:         index_too_big.sql
rem     Dated:          March 2010
rem     Author:         Jonathan Lewis
rem

declare
        last_comp       number := 0;
        heap_comp       number := 0;
        heap_comp1      number := 0;
        heap_comp2      number := 0;
begin

        select value
        into    heap_comp
        from    v$mystat ms, v$statname sn
        where   sn.name = 'heap block compress'
        and     ms.statistic# = sn.statistic#
        ;

        dbms_output.put_line(
                lpad('Iteration',10)  ||
                lpad('Since last',11) ||
                lpad('Ins HSC ct',11) ||
                lpad('Del HSC ct',11)
        );

        dbms_output.put_line(
                lpad('---------',10)  ||
                lpad('----------',11) ||
                lpad('----------',11) ||
                lpad('----------',11)
        );

        for i in 1..1000 loop
                insert into t1 values (lpad(i,400));

                select value
                into    heap_comp1
                from    v$mystat ms, v$statname sn
                where   sn.name = 'heap block compress'
                and     ms.statistic# = sn.statistic#
                ;

                delete from t1;

                select value
                into    heap_comp2
                from    v$mystat ms, v$statname sn
                where   sn.name = 'heap block compress'
                and     ms.statistic# = sn.statistic#
                ;

                if (   heap_comp1 != heap_comp
                    or heap_comp2 != heap_comp
                ) then
                        dbms_output.put_line(
                                lpad(to_char(i,'9999'),10)             ||
                                lpad(to_char(i - last_comp,'9999'),11) ||
                                lpad(to_char(heap_comp1,'999'),11)     ||
                                lpad(to_char(heap_comp2,'999'),11)
                        );
                        heap_comp := heap_comp2;
                        last_comp := i;
                end if;

        end loop;
        rollback;
--      commit;
end;
/

The code gets a little long, but it’s not doing anything particularly clever – just checking the “heap block compress” statistic after each insert and delete. Reporting changes in this value (along with the row where the change happened, and the number of rows since the last change) gives you a fairly helpful insight into how smart the mechanism is. Here’s the output I got:

 Iteration Since last Ins HSC ct Del HSC ct
 --------- ---------- ---------- ----------
        20         20          1          1
        39         19          2          2
        58         19          3          3
        77         19          4          4
        96         19          5          5
       114         18          6          6
       132         18          7          7
       150         18          8          8
       168         18          9          9
       186         18         10         10
       203         17         11         11
       220         17         12         12
       237         17         13         13
       254         17         14         14
       271         17         15         15
       288         17         16         16
       304         16         17         17
       320         16         18         18
       336         16         19         19
       352         16         20         20
       368         16         21         21
       384         16         22         22
       400         16         23         23
       415         15         24         24
       430         15         25         25
       445         15         26         26
       460         15         27         27
       475         15         28         28
       490         15         29         29
       504         14         30         30
       518         14         31         31
       532         14         32         32
       546         14         33         33
       560         14         34         34
       574         14         35         35
       588         14         36         36
       602         14         37         37
       615         13         38         38
       628         13         39         39
       641         13         40         40
       654         13         41         41
       667         13         42         42
       680         13         43         43
       693         13         44         44
       705         12         45         45
       717         12         46         46
       729         12         47         47
       741         12         48         48
       753         12         49         49
       765         12         50         50
       777         12         51         51
       789         12         52         52
       801         12         53         53
       812         11         54         54
       823         11         55         55
       834         11         56         56
       845         11         57         57
       856         11         58         58
       867         11         59         59
       878         11         60         60
       889         11         61         61
       900         11         62         62
       910         10         63         63
       920         10         64         64
       930         10         65         65
       940         10         66         66
       950         10         67         67
       960         10         68         68
       970         10         69         69
       980         10         70         70
       990         10         71         71
      1000         10         72         72

The first column shows us how many times we’ve been round the loop. The second shows us how many times we’ve been round the loop since the last heap block compress. The third and fourth columns show us the current value of the heap block compress statistic as it changes and demonstrate that compression occurs only on inserts.

The first compress took place on the 20th row, then for a while every 19th row, then every 18th row. As time passes and the number of row stubs increases – gradually filling up the bottom of the block – the number of rows needed to fill the remaining space decreases, so the number of rows between calls to compress also decreases.

Footnote: If you want to know how 11g handles “OLTP table compression” (not the hybrid columnar compression of Exadata, or “read-only” table compression of earlier versions of Oracle) the “heap block compress” is very important. Every time Oracle inserts a row into a table that has been defined with “online compression” it checks for “block full” and if the block appears to be full the code attempts to recompress it – so it’s an interesting trade-off between concurrency, CPU usage, and maximum degree of block compression.