Oracle Scratchpad

March 30, 2010

heap block compress

Filed under: Infrastructure,trace files — Jonathan Lewis @ 7:24 pm GMT Mar 30,2010

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.

7 Comments »

  1. […] how often, in my test case does Oracle remove the actual rows and replace them with stubs ? (See “Heap Block Compress” for […]

    Pingback by Index too big « Oracle Scratchpad — March 31, 2010 @ 10:17 pm GMT Mar 31,2010 | Reply

  2. Jonathan,
    What lead you to believe that a DELETE may bump the “heap block compress” statistic? In other words, what’s the purpose of the heap_comp2 variable? My guess is, you just wanted to confirm this does not, in fact, happen – but is there a non-obvious reason to suspect a DELETE might trigger a block compact operation? Maybe if it was coming from another transaction thus requiring a new ITL slot, but your test was single-session…

    Thanks,
    Flado

    Comment by Flado — April 1, 2010 @ 7:32 am GMT Apr 1,2010 | Reply

    • Flado,

      I’ve just discovered that I didn’t reply to your question.

      Your guess was correct – I wasn’t expecting to see any change, but I thought I’d put in the code to check anyway since it was just a couple more lines.

      Your thought about a second transaction is interesting, though. It would need to start with two active transactions on the block (INITRANS=2 by default), but it’s possible that a third transaction would be able to see free space in the block and need to compress the block because it had to push down the row directory to allow space to create a third ITL entry before it could delete a row.

      Comment by Jonathan Lewis — July 24, 2010 @ 11:31 am GMT Jul 24,2010 | Reply

  3. Hello Jonathan,
    great blog article.

    In your proof the “heap block compression” occurs on a block where only the last row is still present (row 19 in your case).
    If oracle “rewrites” the whole block in such cases, what would happen to rows that still exist in the middle (let’s say that row 10 would also exist).

    As the ROWID also contains the “row number” – will such a compression also change the rowid or do the rows always keep their “row number”.

    Regards
    Stefan

    Comment by Stefan — April 22, 2010 @ 12:47 pm GMT Apr 22,2010 | Reply

    • Stefan,

      Rows will keep their rowid which, in other words, means that a row cannot change the row directory entry that it’s stored in. This does have a notable side effect. In my article on the row directory I showed a block with 2017 entries in the row directory, of which the first 2016 pointed to row stubs that had been marked for deletion. When the session commits the space from the delete rows can be reclaimed in a (future) heap block compress. Similarly space from the row directory can be reclaimed – within limits.

      If row 2016 had not been deleted, then the heap block compress would not be able to reclaim any space from the row directory. If it was just row 1,000 that had not been deleted then the heap block compress could reclaim the row directory entries from 1001 to 2016. There’s a type of “high watermark” effect – a row has to stay in the same row directory entry so you can only reclaim row directory space for row entries larger than the largest remaining row.

      Comment by Jonathan Lewis — May 2, 2010 @ 10:48 am GMT May 2,2010 | Reply

  4. […] 4-How does heap block compress statistics increase and what happens when there is space but not at the top of the free space heap? Jonathan Lewis– Heap Block Compress […]

    Pingback by Blogroll Report 26/03 /2010 – 02/04/2010 « Coskan’s Approach to Oracle — May 23, 2010 @ 10:21 pm GMT May 23,2010 | Reply

  5. […] If you delete a few rows from a block, or update a few rows so that they become longer and have to be moved into the free space gap, you leave holes in the row heap. If Oracle needs to do something that requires more space than is currently in the free space gap it can re-arrange the contents of the block, moving the rows downwards to the end of the block (adjusting the row directory as it does so) so that all the holes “bubble up” into the free space gap. This is the action recorded as a “heap block compress”. This also explain why a block dump shows two measures of free space, the “tosp” (total space free in block) and the “avsp” (available space in the free space gap) – ignoring a couple of anomalies the tosp is the avsp plus the sum of all the little holes. (see also: jonathanlewis.wordpress.com/2010/03/30/heap-block-compress) […]

    Pingback by Compression in Oracle – Part 3: OLTP Compression – All Things Oracle — January 31, 2013 @ 1:53 pm GMT Jan 31,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Powered by WordPress.com.