Oracle Scratchpad

February 25, 2013

Free Space

Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 6:36 pm BST Feb 25,2013

Question – How can you have a single file in a single tablespace showing multiple free extents when there are no objects using any space in that file ? For example, from an 11.1.0.7 database:

SQL> select
  2          *
  3  from    user_free_space
  4  where
  5          tablespace_name = 'TEST_8K'
  6  order by
  7          file_id, block_id
  8  ;

TABLESPACE_N    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------ ---------- ---------- ---------- ---------- ------------
TEST_8K               3        128    1048576        128            3
TEST_8K               3        256    1048576        128            3
TEST_8K               3        384    1048576        128            3
TEST_8K               3        512  130023424      15872            3

4 rows selected.

The answer in this case is simple – here’s what I did just before running my query:

SQL> create table t1(n1 number);

Table created.

SQL> create table t2(n1 number);

Table created.

SQL> create table t3(n1 number);

Table created.

SQL> drop table t1;

Table dropped.

SQL> drop table t2;

Table dropped.

SQL> drop table t3;

Table dropped.

SQL>

This test is on 11.1.0.7; test_8k is my default tablespace, uses an 8KB block size (did you spot the clue) with 1MB uniform extents. The segments created immediately and I haven’t purged my recyclebin. Because I’ve dropped the tables Oracle includes their space in the “free space” views, but because I need to be able to flash them back into existence the segments can’t be coalesced into the adjacent free space, and they will also be reported in dba_segments.

Here’s a harder one – there are NO objects in this tablespace, and nothing hiding in the recyclebin:

SQL> select
  2          *
  3  from    user_free_space
  4  where
  5          tablespace_name = 'TEST_2K'
  6  order by
  7          file_id, block_id
  8  ;

TABLESPACE_N    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------ ---------- ---------- ---------- ---------- ------------
TEST_2K               7        512   58720256      28672            7
TEST_2K               7      29184   58720256      28672            7
TEST_2K               7      57856   58720256      28672            7
TEST_2K               7      86528   32505856      15872            7

And here’s the statement I executed (as SYS) just before I ran the query – so no chance that there’s anything hidden in the file:


create
        tablespace test_2k
        datafile 'C:\ORACLE\ORADATA\d11g\d11g\test_2k.dbf'
        SIZE 200M reuse
        blocksize 2k
        extent management local
        uniform size 4k
        segment space management manual
;

Update: 26th Feb

I’m sitting in Munich airport and boarding starts in 10 minutes, so just enough time to give an answer. I’ll start with a block dump of block 3 of the data file.

Block dump from disk:
buffer tsn: 22 rdba: 0x01c00003 (7/3)
scn: 0x0b86.06d63ae4 seq: 0x01 flg: 0x04 tail: 0x3ae41e01
frmt: 0x02 chkval: 0x4e8a type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0C112200 to 0x0C112A00
C112200 0000621E 01C00003 06D63AE4 04010B86  [.b.......:......]
C112210 00004E8A 00000007 00000200 00000000  [.N..............]
C112220 00000000 00003800 00000000 00000000  [.....8..........]
C112230 00000000 00000000 00000000 00000000  [................]
        Repeat 123 times
C1129F0 00000000 00000000 00000000 3AE41E01  [...............:]
File Space Bitmap Block:
BitMap Control:
RelFno: 7, BeginBlock: 512, Flag: 0, First: 0, Free: 14336
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
...
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

This is a locally managed tablespace, so the third block of the file is the first bitmap space management block for the tablespace. The tablespace uses a 2KB block size (so the amount of space in the block for the bit map is slight under 2KB) and a 4KB uniform extent size (so each bit in the map represents 2 blocks/4KB in the tablespace).

As you can see from line 15, the first bit represents the extent starting at block 512, and there are 14,336 bits available (1,792 bytes), so the bitmap in the next block would start at block 512 + 2 * 14,336 = 512 * 28,672 = 29,184. This lines up exactly with the first chunk of free space reported in dba_free_space above.

As one of the comments indicated – the code that populates x$ktfbfe is probably called once for each bitmap space management block, and it doesn’t seem to bother trying to “coalesce” two free space fragments identified by adjacent bitmap blocks.

15 Comments »

  1. Hi Jonathan,
    that is because of free space management, right?
    Few blocks contains bitmaps for space management.
    Regards
    GregG

    Comment by goryszewskig — February 25, 2013 @ 6:59 pm BST Feb 25,2013 | Reply

    • GregG,

      There’s something in what you say, but:

      512 + 28672 = 29184
      29184 + 28672 = 57856
      57856 + 28672 = 86528

      So I don’t seem to have left any gaps for space management blocks.

      Comment by Jonathan Lewis — February 25, 2013 @ 7:22 pm BST Feb 25,2013 | Reply

  2. You are right, no gaps but somehow Oracle did not decide to show 1 row only .
    I think that small block size and extend size plays important role here.
    Instead of guessing I’ll just wait for Your explanations :).
    Regards
    GregG

    Comment by goryszewskig — February 25, 2013 @ 8:12 pm BST Feb 25,2013 | Reply

  3. GregG,

    The phenomenon happens for all block sizes – but at larger block sizes you need a larger file to see it.

    Comment by Jonathan Lewis — February 25, 2013 @ 8:20 pm BST Feb 25,2013 | Reply

  4. Maybe the small block size limits the maximum free space size that Oracle can store in some headers ?

    Comment by Pierre — February 25, 2013 @ 9:54 pm BST Feb 25,2013 | Reply

  5. It seems depend on size of File Space Bitmap Block and uniform size, in your case 28672 block = 14336 extents and require 14336 / 8 = 1792 bytes per File Space Bitmap Block.

    Comment by Valentin Nikotin — February 25, 2013 @ 10:16 pm BST Feb 25,2013 | Reply

  6. Hmm, in trace i see queries to recyclebin$. What does it means? Some objects was created and dropped? Or was initialized recyclebin for this tablespace?

    select nvl(sum(space),0) 
    from
     recyclebin$ where ts# = :1
    
    select file#, block# 
    from
     recyclebin$ where ts# = :1     and file# != 0 and block# != 0 and space = 0
    

    Comment by Sayan Malakshinov — February 25, 2013 @ 10:23 pm BST Feb 25,2013 | Reply

  7. Since we are using locally managed tablespace, It is storing the information about the space available in a file in the bitmaps in the file header. One unit of information contains information about one extent. If the information can fit in the space allocated in the file header, all the free space appears as contiguous. As the size of the file increases and extent size decreases , the units of the information to be stored increases and more space is allocated beyond the file header. Until any objects are created in the tablespace, the bits corresponding to all the extents are not set and appear as free space. As objects are created in the tablespace, corresponding bits are set and free space available decreases (Starting block_id rises).

    This is the conclusion I have reached after trying with block/extent/file sizes.
    I don’t know if it is correct.

    Comment by Anju Garg — February 26, 2013 @ 6:44 am BST Feb 26,2013 | Reply

  8. As I understand, free space for each datafile calculated by each freespace bmb separately, because each freespace bmb contains a header and bitmap, so bitmaps in these blocks are not continuous(although located in a row). Number of bmb depends on datafile size.

    Comment by Sayan Malakshinov — February 26, 2013 @ 7:20 am BST Feb 26,2013 | Reply

  9. Hi Jonathan,

    I can´t understand the above operation:

    512 + 2 * 14,336

    If an extent consists of two blocks, and there is only a bitmap control header for each extent, it means there is 14336 bits in the first block of the extent but the second block should be full available (except its own control information, of course). I mean:

    Block 1, extent Nth Block 2, extent Nth
    ——————————————————– + ——————————————————-
    bitmap header: 256 B | 14336 bits=1792B + Full 2048 B – block header(22B for example)
    ———————————————————+——————————————————–

    So, If it is correct, the next bit map should be at: 1792 B + 2048B, isn´t?.

    Best regards.

    Comment by Richard. — March 4, 2013 @ 11:27 am BST Mar 4,2013 | Reply

    • Richard,
      I’m not quite sure I understand your comments about, but I think you are mixing up the bitmaps for the extents and the bitmaps for the tablespace as you work through the numbers.

      Each block in the bitmap for the tablespace allows us to map 14,336 extents.
      In this version of Oracle, the first extent of the tablespace starts after a 1MB file header section (which, of course, includes the tablespace bitmaps).

      Comment by Jonathan Lewis — March 10, 2013 @ 11:55 am BST Mar 10,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 Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,453 other followers