This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Feb 20, 2013 4:16 AM by user5066799 Go to original post RSS
  • 15. Re: Fragmented free space in empty LMT ASSM tablespace?
    Max Seleznev Explorer
    Currently Being Moderated
    Great explanation. Thanks you, Sir.

    Max
  • 16. Re: Fragmented free space in empty LMT ASSM tablespace?
    user5066799 Newbie
    Currently Being Moderated
    Great, this figure of 63488 you show in file dumping correlates with the result I get of fragments of 3968M. With 64K extent size, we get 3968*1024/64=63488.
    (Edited the post to avoid confusion). Please confirm my understanding (for version 11 and exact figures for 8K block size, LMT with autoallocate or uniform 64K. For other extent sizes it can be applied with adjustment per block size / extent size for uniform case):
    Internally, datafile has at the beginning:
    - file header, 1 block;
    - File Space Header Block, 1 block;
    - File Space Bitmap Blocks, 126 blocks. This part is the one important for understanding limitations of absence of free space "fragmentation" (at least for dba_free_space). Each of this blocks has a couple of hundred bytes reserved for "block headers and other control details" and the rest 7.75K (= 63488 bits) is a bitmap, each bit representing one extent, so 1 File Space Bitmap Block for 63488 extents = 507904 blocks = 3968M. Totally 126 blocks are enough for 126 * 3968M = 499968M = 488.25G. This results in two points:
    1) For datafiles containing more than 63488 extents (for autoallocate of 64K), just after datafile creation, dba_free_space will have free space fragments, number of which is number of 63488 extent units minus 1. This is due to dba_free_space basing on only 1 File Space Bitmap Block to internally "coalesce" free space fragments and isn't able to identify fragments crossing 1 File Space Bitmap Block. That's why it can't be "fixed" by coalesce command or anything else, being due to internal structures.
    ---- This does NOT result in any "hidden" space adding to datafile in between of this free extents units and to the end of datafile also, actually free space fragments go exactly one after another and previous free extent block_id + previous free extent blocks = next free extent block_id is true from the point of view of space. This does NOT result in adding up to datafile size also.
    2) For large datafiles (only for bigfile tablespaces?) of more than 7999488 extents (7999488 extents = 126 bitmap blocks * 63488 extents / block = 488.25 G for 64K extent), 126 blocks will not be enough to represent all extents, so probably Oracle will add another space management area (of 126 blocks?) after these 7999488 extents, but before next 7999488 extents.
    ---- This CAN result in adding space to datafile and increasing its size, but only 126 blocks (or maybe 1M) per each 7999488 extents. Probably, Oracle will show in dba_free_space a "break" in block_id's not belonging to either dba_free_space or dba_extents.
    When started reading your post, mixed points 1 and 2 a bit, but now seems the picture is clear. Most important, no "hidden" space added to datafile size.
    Probably, there can be some artificial experiment for autoallocate for some segment which have reached for example 8M extent size, and there is only 8M free space unit crossing the boundary between two certain free space bitmap blocks, and then Oracle is unable to allocate this extent (if the internal allocation engine has the same limitations as dba_free_space). Hopefully, rare case.

    Edited by: user5066799 on Dec 9, 2012 5:51 AM
  • 17. Re: Fragmented free space in empty LMT ASSM tablespace?
    jgarry Guru
    Currently Being Moderated
    Normally one could say "allow some slop for overhead," but it seems your procedure needs to completely account for everything. What is it trying to accomplish?
  • 18. Re: Fragmented free space in empty LMT ASSM tablespace?
    user5066799 Newbie
    Currently Being Moderated
    Great, thank you, Sir. Got more definiteness when checked for uniform size extent in LMT ASSM, the number of 63488 gets its confirmation. Need first to know when to expect such behaviour, then what implications it has. First part can be considered as amazingly solved.
    SQL> --First, let's confirm uniform extent size behaviour is the same for uniform extent size 64K as for autoallocate
    SQL> create tablespace TEST_TS extent management local segment space management auto uniform size 64K
      2  datafile '+DATA/TEST_TS.dbf' size 4161863680 /*3969M + 64K*/;
     
    Tablespace created
     
    Executed in 21,375 seconds
    SQL> select * from dba_free_space where tablespace_name='TEST_TS';
     
    TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
    ------------------------------ ---------- ---------- ---------- ---------- ------------
    TEST_TS                               129        128 4160749568     507904          129
    TEST_TS                               129     508032      65536          8          129
     
    Executed in 0,015 seconds
    SQL> select 1024 + 63488 * 128  from dual; --getting magic number of Kilobytes for 128K block, max number without additional space header (1024K = 1M normal file header at its start)
     
    1024+63488*128
    --------------
           8127488
     
    Executed in 0,015 seconds
    SQL> drop tablespace TEST_TS including contents and datafiles;
     
    Tablespace dropped
     
    Executed in 1,812 seconds
    SQL> create tablespace TEST_TS extent management local segment space management auto uniform size 128K
      2  datafile '+DATA/TEST_TS.dbf' size 8127488K;
     
    Tablespace created
     
    Executed in 42,422 seconds
    SQL> select * from dba_free_space where tablespace_name='TEST_TS';
     
    TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
    ------------------------------ ---------- ---------- ---------- ---------- ------------
    TEST_TS                               129        128 8321499136    1015808          129
     
    Executed in 0,015 seconds
    SQL> select 8127488 + 128 from dual; --getting next number of Kilobytes after which additional "space header" should appear in file (previous file size + 1 block)
     
    8127488+128
    -----------
        8127616
     
    Executed in 0,016 seconds
    SQL> drop tablespace TEST_TS including contents and datafiles;
     
    Tablespace dropped
     
    Executed in 2 seconds
    SQL> create tablespace TEST_TS extent management local segment space management auto uniform size 128K
      2  datafile '+DATA/TEST_TS.dbf' size 8127616K;
     
    Tablespace created
     
    Executed in 42,532 seconds
    SQL> select * from dba_free_space where tablespace_name='TEST_TS';
     
    TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
    ------------------------------ ---------- ---------- ---------- ---------- ------------
    TEST_TS                               129        128 8321499136    1015808          129
    TEST_TS                               129    1015936     131072         16          129
     
    Executed in 0,047 seconds
  • 19. Re: Fragmented free space in empty LMT ASSM tablespace?
    user5066799 Newbie
    Currently Being Moderated
    One more observation on free space fragmentation which can't be explained by 63488 extents per "bitmap block", as there are only 3 free extents at the file beginning.
    How could this case be explained?
    SQL> select file_id, block_id, blocks, bytes from dba_free_space where file_id=31 order by block_id;
       FILE_ID   BLOCK_ID     BLOCKS      BYTES
    ---------- ---------- ---------- ----------
            31        128          8      65536
            31        136          8      65536
            31        144          8      65536
    SQL> select file_id, block_id, blocks, bytes from dba_extents where file_id=31;
       FILE_ID   BLOCK_ID     BLOCKS      BYTES
    ---------- ---------- ---------- ----------
            31        152          8      65536
    SQL> select block_size, initial_extent, extent_management, allocation_type
      2  from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id=31);
    BLOCK_SIZE INITIAL_EXTENT EXTENT_MANAGEMENT ALLOCATION_TYPE
    ---------- -------------- ----------------- ---------------
          8192          65536 LOCAL             SYSTEM
  • 20. Re: Fragmented free space in empty LMT ASSM tablespace?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user5066799 wrote:
    One more observation on free space fragmentation which can't be explained by 63488 extents per "bitmap block", as there are only 3 free extents at the file beginning.
    How could this case be explained?
    SQL> select file_id, block_id, blocks, bytes from dba_free_space where file_id=31 order by block_id;
    FILE_ID   BLOCK_ID     BLOCKS      BYTES
    ---------- ---------- ---------- ----------
    31        128          8      65536
    31        136          8      65536
    31        144          8      65536
    You've created and dropped some small object(s) but haven't yet purged the recyclebin.

    They appear as free space because the space will be re-used if necessary, but they appear as separate extents because the object(s) can be recalled using the flashback command if necessary.

    Regards
    Jonathan Lewis
  • 21. Re: Fragmented free space in empty LMT ASSM tablespace?
    user5066799 Newbie
    Currently Being Moderated
    Yes, this time it was recyclebin. After purging free space extents joined together. Forgot about this another reason for visible "fragmentation". Thanks a lot, Jonathan!
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points