Oracle Scratchpad

December 28, 2012

Quiz Night

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 11:30 am GMT Dec 28,2012

Here’s a little puzzle if you have nothing better to do between now and the new year. The following extract came from, but could have come from or (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:


If you need to have the acronyms decoded they are (according to my best guess):

  • fsbo – free space, begin 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).


  1. Reblogged this on lava kafle kathmandu nepal.

    Comment by lkafle — December 28, 2012 @ 12:13 pm GMT Dec 28,2012 | Reply

  2. 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.

    Comment by Martin Berger (@martinberx) — December 28, 2012 @ 2:39 pm GMT Dec 28,2012 | Reply

  3. 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
    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 ));
    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);
    ------------------------------------ ----------
                                     312        726
    When I dump it  I get : 
    tsiz: 0x1f98
    hsiz: 0x5be
    pbl: 0x7f4a335fe064
    So : 0x8e8 - 0x5be = 32a = 810
    avsp=0x54 = 84

    Again space missing :(

    Happy 2012+1

    Comment by Pablo — December 28, 2012 @ 3:59 pm GMT Dec 28,2012 | Reply

    • 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 GMT Dec 29,2012 | Reply

  4. Is that related to basic compression (per default pctfree 0) ?

    Comment by goryszewskig — December 28, 2012 @ 4:17 pm GMT Dec 28,2012 | Reply

    • 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 GMT Dec 29,2012 | Reply

  5. 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%?


    Comment by John Spencer — December 28, 2012 @ 7:45 pm GMT Dec 28,2012 | Reply

  6. 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)

    -- db_block_size: 8K
    -- MSSM TS
    drop table test_free_space;
    create table test_free_space tablespace test_ts
    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;
    -- ---------- ----------
     1          5     155929
     2          5     155929
    alter system dump datafile 5 block min 155929 block max 155929;

    In the block dump I see:


    0x113a – 0x5d4 = B66 => 2918 (instead of 0x36 => 54).

    The fseo=0x113a corresponds with the offset of the last (or better: first) record 726

    0x1e:pti[0]	nrow=11	offs=0
    0x22:pti[1]	nrow=716	offs=11
    0x26:pri[0]	offs=0x1f51
    0x28:pri[1]	offs=0x1f58
    0x2a:pri[2]	offs=0x1f5f
    0x2c:pri[3]	offs=0x1f66
    0x2e:pri[4]	offs=0x1f6d
    0x30:pri[5]	offs=0x1f74
    0x5d0:pri[725]	offs=0x113f
    0x5d2:pri[726]	offs=0x113a

    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…


    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 GMT Dec 28,2012 | Reply

    • 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 GMT Dec 29,2012 | Reply

      • row chaining also seems to have an odd effect on the space information:

        -- db_block_size: 8K
        -- MSSM TS
        create table test_free_space tablespace test_ts
        select mod(rownum, 10) col1
             , lpad('*', 4000, '*') padding
             , lpad('*', 4000, '*') padding2
          from dual
        connect by level <= 10000;
        alter table test_free_space move;

        In this case the row is too big for the block, but though fsbo = fseo there are still values != 0x0 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 GMT Dec 29,2012 | Reply

  7. 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 ?

    Comment by goryszewskig — December 30, 2012 @ 8:28 am GMT Dec 30,2012 | Reply

    • Greg,

      as far as I know even a row without any data consumes 11 bytes. Charles Hooper has blogged about this some years ago: 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:

      -- db_block_size: 8K
      -- MSSM TS
      drop table test_free_space;
      create table test_free_space tablespace test_ts pctfree 0
      select cast (null as number) col1
        from dual
      connect by level <= 10000;
      alter table test_free_space move;
      -- block dump

      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: But I still don’t get it …


      Comment by Martin Preiss — December 30, 2012 @ 1:54 pm GMT Dec 30,2012 | Reply

    • 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:

      tab 1, row 440, @0x866
      tl: 9 fb: --H----- lb: 0x2  cc: 0
      nrid:  0x01400103.0
      bindmp: 20 02 00 01 40 01 03 00 00

      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 GMT Dec 30,2012 | Reply

      • 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:

        0xe:pti[0] nrow=726 offs=0
        0x12:pri[0] offs=0x1aa5
        0x14:pri[1] offs=0x1aaa
        0x16:pri[2] offs=0x1aaf
        0x18: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:

        0xe:pti[0] nrow=723 offs=0
        0x12:pri[0] offs=0x1f83
        0x14:pri[1] offs=0x1f7a
        0x16:pri[2] offs=0x1f71
        0x18: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.


        Comment by Randolf Geist — December 31, 2012 @ 5:07 pm GMT Dec 31,2012 | Reply

        • 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.


          Comment by Randolf Geist — December 31, 2012 @ 5:17 pm GMT 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:

          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 GMT Dec 31,2012

  8. 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.


    Comment by Randolf Geist — December 31, 2012 @ 7:44 pm GMT Dec 31,2012 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by