Here’s a little puzzle if you have nothing better to do between now and the new year. The following extract came from 11.2.0.3, but could have come from 10.2.0.5 or 9.2.0.8 (and many others). I’ve got a simple heap table where the last thing I (or anyone) did was “alter table t1 move” before dumping the first (data) block of the table. Looking at the resulting trace file, I see the following:
fsbo=0x56e fseo=0xf4d avsp=0x5f tosp=0x5f
If you need to have the acronyms decoded they are (according to my best guess):
fsbo – free space, beginning offset
fseo – free space, end offset
avsp – available space
tosp – total space
Doing the arithmetic, the free space starts at offset 0x56e and ends at 0xf4d, which means the free space gap is 2,527 bytes; but the total space available for use is only 0x5f bytes, i.e. 95 bytes. So what has happened to the other 2,432 ?
Remember – I dumped the block immediately after issuing “alter table t1 move”, so there are no issues of delayed block cleanout, uncommitted transactions etc. to worry about.
Footnote: the reason why you have “available space” and “total space” is to keep track of the space made available by deleted rows. The “avsp” (usually) reports the size of the gap between the row directory and the row heap; the “tosp” includes the space in the holes left in the row heap after rows have been deleted (or updated in a way that moves them up to the top of the heap, leaving a gap behind them, or updated in situ in a way that reduces the row length leaving a little hole).
Reblogged this on lava kafle kathmandu nepal.
Comment by lkafle — December 28, 2012 @ 12:13 pm UTC Dec 28,2012 |
Jonathan,
did you play around with inittrans/maxtrans to create a lot of ITLs on the block?
As I’m on a train already i have to wait until next year to do some tests, so it’s only guesswork. sorry.
Martin
Comment by Martin Berger (@martinberx) — December 28, 2012 @ 2:39 pm UTC Dec 28,2012 |
Changing initrans would only have the effect of moving fsbo further down the block.
It’s nothing to do with the ITL.
Comment by Jonathan Lewis — December 29, 2012 @ 3:05 pm UTC Dec 29,2012 |
Hi,
I still don’t know where the space went.
But I’ve noticed that these numbers don’t add up even without the “ALTER TABLE MOVE”
If I do :
CREATE TABLESPACE JLQ_TAB datafile '+data' size 5M extent management local uniform size 1M SEGMENT SPACE MANAGEMENT auto; drop table JLQ1; CREATE TABLE JLQ1 (id char(4)) PCTFREE 1 tablespace JLQ_TAB; insert into JLQ1 (select * FROM (select 'AAAA' from dba_objects where rownum < 727 order by object_id )); commit; I get 1 full block of data : select DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid),count(1) from jlq1 group by DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid); DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(1) ------------------------------------ ---------- 312 726 When I dump it I get : tsiz: 0x1f98 hsiz: 0x5be pbl: 0x7f4a335fe064 76543210 flag=-------- ntab=1 nrow=726 frre=-1 fsbo=0x5be fseo=0x8e8 avsp=0x54 tosp=0x54 So : 0x8e8 - 0x5be = 32a = 810 avsp=0x54 = 84Again space missing :(
Happy 2012+1
Pablo
Comment by Pablo — December 28, 2012 @ 3:59 pm UTC Dec 28,2012 |
Being able to model a scenario is a good step towards understanding what’s happening.
You could try using ‘AAA’, ‘AA’, and just ‘A’ to see if that gives you any more clues.
Comment by Jonathan Lewis — December 29, 2012 @ 3:07 pm UTC Dec 29,2012 |
Is that related to basic compression (per default pctfree 0) ?
Regards
GregG
Comment by goryszewskig — December 28, 2012 @ 4:17 pm UTC Dec 28,2012 |
As Pablo shows in his example, you can see the effect without compression.
Coincidentally, though, I was reminded of the effect while writing a demo about some of the features of basic compression.
Comment by Jonathan Lewis — December 29, 2012 @ 3:08 pm UTC Dec 29,2012 |
Given that the table was just moved, it must be as tightly packed as possible in the block, which would mean that fseo must be near the end of the block. This implies a 4K block size. If I understand correctly (always a dubious proposition) tosp is the amount of space available for new rows. The missing free space is suspiciously close to 60% of a 4K block. Did you specify a pctfree of 60%?
John
Comment by John Spencer — December 28, 2012 @ 7:45 pm UTC Dec 28,2012 |
Jonathan,
some ideas without a meaningful order:
1. table cluster – though you said “simple heap table”; but then I realized that COMPRESS and MOVE don’t work for table clusters (like most people I don’t use them frequently). Apart from this there is no difference between tosp and (fseo-fsbo) after object creation.
2. minimize records_per_block: has also no effect on the space measures
3. pctfree: has no effect on the space measures
4. basic compression (as suggested by GregG)
-- 11.2.0.1 -- db_block_size: 8K -- MSSM TS drop table test_free_space; create table test_free_space tablespace test_ts as select mod(rownum, 10) id , lpad('*', 10, '*') padding from dual connect by level <= 10000; alter table test_free_space compress; alter table test_free_space move; select id , dbms_rowid.rowid_relative_fno(rowid) file_no , dbms_rowid.rowid_block_number(rowid) block_no from test_free_space where rownum < 3; ID FILE_NO BLOCK_NO -- ---------- ---------- 1 5 155929 2 5 155929 alter system dump datafile 5 block min 155929 block max 155929;In the block dump I see:
fsbo=0x5d4
fseo=0x113a
avsp=0×36
tosp=0×36
0x113a – 0x5d4 = B66 => 2918 (instead of 0×36 => 54).
The fseo=0x113a corresponds with the offset of the last (or better: first) record 726
The symbol table seems to reside in the very last rows of the block (0x1f51). So I could imagine two possible explanations: a) the block header including the row directory is bigger than fsbo (1492) seems to suggest; or b) Oracle has adjusted the tosp, avsp values since the block is full (though SPARE1 in tab$ is 736 and not 726)
But perhaps that shows only the limits of my imagination…
Martin
P.S.: it’s hard, but I really seem to have nothing better to do.
Comment by Martin Preiss — December 28, 2012 @ 8:12 pm UTC Dec 28,2012 |
As I said in my reply to Greg, it was a little demo script of compression that reminded me of this little feature – but compression isn’t necessary to produce it. However, in rare cases, it is possible that “real” data with basic compression could exhibit this behaviour.
Take a closer look at your block dump and add just a little bit of imagination (as in “what if I …”).
Comment by Jonathan Lewis — December 29, 2012 @ 3:11 pm UTC Dec 29,2012 |
row chaining also seems to have an odd effect on the space information:
-- 11.2.0.1 -- db_block_size: 8K -- MSSM TS create table test_free_space tablespace test_ts as select mod(rownum, 10) col1 , lpad('*', 4000, '*') padding , lpad('*', 4000, '*') padding2 from dual connect by level <= 10000; alter table test_free_space move; fsbo=0x22 fseo=0x22 avsp=0x325 tosp=0x325In this case the row is too big for the block, but though fsbo = fseo there are still values != 0×0 for avsp and tosp. But that’s a difference in the other direction, so I guess it’s not the answer for the quiz.
Comment by Martin Preiss — December 29, 2012 @ 4:32 pm UTC Dec 29,2012 |
Well, It is just because You’ve got row length less than 9 bytes ? Oracle assumes min row length of 9 bytes so You are unable to fill those bytes below 9 ?
Regards
GregG
Comment by goryszewskig — December 30, 2012 @ 8:28 am UTC Dec 30,2012 |
Greg,
as far as I know even a row without any data consumes 11 bytes. Charles Hooper has blogged about this some years ago: http://hoopercharles.wordpress.com/2010/04/29/how-much-space-is-required-to-store-a-whole-lot-of-nothing/. In the comment section Jonathan wrote:
“The eleven bytes per row comes from the following data requirement for each row (when it’s completely null)
Row directory entry 2 bytes
Column count byte
Lock byte
Flag byte
Reserved 6 bytes for rowid that might be needed for row migration.”
When I create a table with empty rows I get similar results:
So with pctfree = 0 my 8K block contains 731 rows with NULL values (731 * 11 = 8041). But the values for fsbo (1480), fseo(1493) and avsp, tosp(13) fit together – so I don’t think the row length is the answer to our question.
Apart from this I assume that the answer is somewhere in Charles Hooper’s article (including the comments) or in Jonathan’s article on heap block compress: http://jonathanlewis.wordpress.com/2010/03/30/heap-block-compress/. But I still don’t get it …
Martin
Comment by Martin Preiss — December 30, 2012 @ 1:54 pm UTC Dec 30,2012 |
GregG (and Martin) -
The nine / eleven bytes is the answer.
In order to allow a row to migrate Oracle has to have enough space for the forward pointer, and the minimum space in a block for a row is fixed by that requirement.
The apparent contradiction between 9 and 11 comes from the fact that 2 bytes are in the row directory (towards the top of the block) and the other 9 bytes are in the row heap (at the bottom of the block). Here’s a fragment of a block dump showing what’s in the row heap when a row has migrated:
As you can see, in the heap we have the flag byte, the lob byte, the column count, and the “next rowid” (nrid) for a total of 9 bytes.
The row is in tab 1 and the bindmp details are there because this happens to be a dump from a block of a table with compression enabled.
If you looked at the equivalent dump from Pablo’s example where the row was a single column holding just ‘AAAA’, tl: value would be 8 – and the Oracle code could report (for each row) 1 extra byte that was notionally free, but wasn’t available because it was pre-reserved in case of row migration. (Change the ‘AAAA’ to ‘AAA’ and you’d get two such bytes per row.)
Comment by Jonathan Lewis — December 30, 2012 @ 2:37 pm UTC Dec 30,2012 |
Jonathan,
are you sure that you really took the block dump shown in your “Quiz Night” from an uncompressed heap table right after a ALTER TABLE MOVE?
The reason I ask is the following:
1. Assuming an uncompressed heap table I can only see Oracle storing rows smaller than 9 bytes adjacent to each other with no gap in between when conventional inserts get used. If I use direct-path inserts / CTAS, rows smaller than 9 bytes will be stored aligned to 9 bytes, with a corresponding gap in between (which seems to be reasonable to me anyway, since Oracle won’t store more rows in the block as dictated by the Hakan Factor, so why cause additional work by storing the small rows without a gap so that in case of a migration something has to be done about the now longer grown row?)
Snippet from uncompressed heap table block dump with very short rows created via conventional inserts:
.
.
.
fsbo=0x5be
fseo=0×1172
avsp=0x5c
tosp=0x5c
0xe:pti[0] nrow=726 offs=0
0×12:pri[0] offs=0x1aa5
0×14:pri[1] offs=0x1aaa
0×16:pri[2] offs=0x1aaf
0×18:pri[3] offs=0x1ab4
0x1a:pri[4] offs=0x1ab9
0x1c:pri[5] offs=0x1abe
.
.
.
Notice how the rows are stored with a offset difference of 5 bytes each (“no-gap”) and the difference between fsbo/fseo/avsp/tosp
2. If I use conventional inserts to see the “no-gap” variant just described but MOVE the table afterwards, it looks like the “direct-path” insert code path gets used to perform the MOVE, resulting in re-arranging the rows with the mentioned “gap” in between. So I wonder how you managed to get the “no-gap” variant for an uncompressed heap table right after a MOVE
Same table as above right after a MOVE:
.
.
.
fsbo=0x5b8
fseo=0x61d
avsp=0×65
tosp=0×65
0xe:pti[0] nrow=723 offs=0
0×12:pri[0] offs=0x1f83
0×14:pri[1] offs=0x1f7a
0×16:pri[2] offs=0x1f71
0×18:pri[3] offs=0x1f68
0x1a:pri[4] offs=0x1f5f
0x1c:pri[5] offs=0x1f56
.
.
.
The rows are now stored with a offset difference of 9 bytes, and the difference between fsbo/fseo/avsp/tosp is gone.
3. Compressed heap tables behave differently, as the rows are stored without gap in-between even when using direct-path inserts / CTAS, so a MOVE of such a segment doesn’t change the row layout to the variant with the “gap” in between.
Could it be that you accidentally DUMPed the “old” block of the segment that was there before the MOVE, so didn’t update the block address to reflect the new segment created by the MOVE? That’s something that happened to me at least when doing some tests.
You can see effect described in the comments above: Whereas Pablo arrived at the desired row layout (“no-gap” variant) by using conventional inserts (but apparently no MOVE) for an uncompressed heap table, Martin Preiss last comment shows an uncompressed heap table created via CTAS that also does have very short rows but apparently arrived at the “gap” variant.
Randolf
Comment by Randolf Geist — December 31, 2012 @ 5:07 pm UTC Dec 31,2012 |
I should have added that all this above applies to tables with very short rows – for tables with different sized rows things might look different, in particular the comment about the “gap” / “no-gap” variant and Hakan Factor.
Randolf
Comment by Randolf Geist — December 31, 2012 @ 5:17 pm UTC Dec 31,2012
Randolf,
Thanks for the input – for further interesting details of oddities that compression – with its resulting short rows – can achieve your blog posting about the ASSM bug and compression is worth a link: http://oracle-randolf.blogspot.co.uk/2011/05/assm-bug-reprise-part-2.html
The fragment I dumped came from a table that had been moved to compress the data: create table (with no compress); alter table compress basic; alter table move. I didn’t say that the data was uncompressed, though the possibility of short rows and odd arithmetic in the absence of compression did show up in some of the follow-up comments.
Comment by Jonathan Lewis — December 31, 2012 @ 5:37 pm UTC Dec 31,2012
Jonathan,
thanks for the clarification.
I thought to understand some of your replies to the comments here that you can see the “effect” without compression.
While it is true that the effect per se can be seen without compression, the scenario that you describe in your “Quiz Night” question – including the explicit reference to ALTER TABLE … MOVE – can only be reproduced when using compression I believe, so that’s probably why I implicitly assumed that you managed to get that result without compression according to your other replies.
Randolf
Comment by Randolf Geist — December 31, 2012 @ 7:44 pm UTC Dec 31,2012 |