Oracle Scratchpad

March 30, 2010

heap block compress

Filed under: Infrastructure,trace files — Jonathan Lewis @ 7:24 pm BST 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 0×196: 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:


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#
	;

	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(
				to_char(i,'9999') ||
				to_char(i - last_comp,'9999') ||
				to_char(heap_comp1,'999') ||
				to_char(heap_comp2,'999')
			);
			heap_comp := heap_comp2;
			last_comp := i;
		end if;

	end loop;
	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:


   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
  505   15  30  30
  519   14  31  31
  533   14  32  32
  547   14  33  33
  561   14  34  34
  575   14  35  35
  589   14  36  36
  603   14  37  37
  616   13  38  38
  629   13  39  39
  642   13  40  40
  655   13  41  41
  668   13  42  42
  681   13  43  43
  694   13  44  44
  707   13  45  45
  719   12  46  46
  731   12  47  47
  743   12  48  48
  755   12  49  49
  767   12  50  50
  779   12  51  51
  791   12  52  52
  803   12  53  53
  814   11  54  54
  825   11  55  55
  836   11  56  56
  847   11  57  57
  858   11  58  58
  869   11  59  59
  880   11  60  60
  891   11  61  61
  902   11  62  62
  912   10  63  63
  922   10  64  64
  932   10  65  65
  942   10  66  66
  952   10  67  67
  962   10  68  68
  972   10  69  69
  982   10  70  70
  992   10  71  71

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 definded 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 BST 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 BST 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 BST 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 BST 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 BST 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 BST 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 BST 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

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,528 other followers