This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Feb 20, 2013 4:16 AM by user5066799 RSS

Fragmented free space in empty LMT ASSM tablespace?

user5066799 Newbie
Currently Being Moderated
select count(*) from dba_extents where file_id=127;
COUNT(*)
0
select count(*) from dba_free_space where file_id=127;
COUNT(*)
2
select block_id, blocks from dba_free_space where file_id=127;
 
  BLOCK_ID     BLOCKS
---------- ----------
       128     507904
    508032     139616
select extent_management, segment_space_management, allocation_type
from dba_tablespaces 
where tablespace_name=(select tablespace_name from dba_data_files where file_id=127);
EXTENT_MANAGEMENT     SEGMENT_SPACE_MANAGEMENT     ALLOCATION_TYPE
LOCAL     AUTO     SYSTEM
alter tablespace ... coalesce;
select count(*) from dba_free_space where file_id=127;
COUNT(*)
2
select version from product_component_version where product like 'Oracle%';
VERSION
11.2.0.2.0

How is that possible?
  • 1. Re: Fragmented free space in empty LMT ASSM tablespace?
    Max Seleznev Explorer
    Currently Being Moderated
    And the answer is....

    RECYCLEBIN!
    SQL> select * from dba_extents where tablespace_name='TEST1';
    
    no rows selected
    
    Elapsed: 00:00:00.01
    SQL> select * from dba_free_space where tablespace_name='TEST1';
    
    TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
    ------------------------------ ---------- ---------- ---------- ---------- ------------
    TEST1                                1166        152  103612416      12648          143
    TEST1                                1166        128      65536          8          143
    TEST1                                1166        144      65536          8          143
    TEST1                                1166        136      65536          8          143
    
    Elapsed: 00:00:00.03
    
    SQL> select original_name from recyclebin where ts_name='TEST1';
    
    ORIGINAL_NAME
    --------------------------------
    TEST_T1
    TEST_T3
    TEST_T2
    
    Elapsed: 00:00:00.01
    SQL> purge tablespace test1;
    
    Tablespace purged.
    
    Elapsed: 00:00:00.15
    SQL> select * from dba_free_space where tablespace_name='TEST1';
    
    TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
    ------------------------------ ---------- ---------- ---------- ---------- ------------
    TEST1                                1166        128  103809024      12672          143
    
    Elapsed: 00:00:00.01
    SQL>
    Hope it helps.
  • 2. Re: Fragmented free space in empty LMT ASSM tablespace?
    user5066799 Newbie
    Currently Being Moderated
    Thanks, interesting effect, but not my case.
    select (select count(*) from recyclebin r where r.ts_name = ts.ts) cnt_objs_recyclebin,
           (select count(*) from dba_extents e where e.tablespace_name = ts.ts) cnt_extents,
           (select count(*) from dba_free_space f where f.tablespace_name = ts.ts) cnt_free_fragments
    from (select tablespace_name ts from dba_data_files where file_id=127) ts;
    
    CNT_OBJS_RECYCLEBIN CNT_EXTENTS CNT_FREE_FRAGMENTS
    ------------------- ----------- ------------------
                      0           0                  2
  • 3. Re: Fragmented free space in empty LMT ASSM tablespace?
    sb92075 Guru
    Currently Being Moderated
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_3002.htm#SQLRF01002

    "COALESCE

    For each data file in the tablespace, this clause combines all contiguous free extents into larger contiguous extents."

    it worked as documented.
    COALESCE is NOT designed to result in single contiguous free extent
  • 4. Re: Fragmented free space in empty LMT ASSM tablespace?
    Max Seleznev Explorer
    Currently Being Moderated
    You're looking at your own recyclebin. The objects could have belonged to another user.
    SQL> select original_name from recyclebin where ts_name='TEST1';
    
    no rows selected
    
    Elapsed: 00:00:00.07
    SQL> select original_name from dba_recyclebin where ts_name='TEST1';
    
    ORIGINAL_NAME
    --------------------------------
    TEST_T1
    
    Elapsed: 00:00:00.03
  • 5. Re: Fragmented free space in empty LMT ASSM tablespace?
    user5066799 Newbie
    Currently Being Moderated
    But 128 + 507904 = 508032 ! Does not that say my two free extents are contiguous?
    See the results of my query to dba_free_space.
    128 = block_id of the first free extent
    507904 = blocks of the first extent
    508032 = block_id of the second extent

    Edited by: user5066799 on Dec 7, 2012 7:57 AM
  • 6. Re: Fragmented free space in empty LMT ASSM tablespace?
    user5066799 Newbie
    Currently Being Moderated
    No objects of other users also... Tablespace has been actually just created, should not have been "touched" yet.
    SQL> select (select count(*) from dba_recyclebin r where r.ts_name = ts.ts) cnt_objs_recyclebin,
      2         (select count(*) from dba_extents e where e.tablespace_name = ts.ts) cnt_extents,
      3         (select count(*) from dba_free_space f where f.tablespace_name = ts.ts) cnt_free_fragments
      4  from (select tablespace_name ts from dba_data_files where file_id=127) ts;
     
    CNT_OBJS_RECYCLEBIN CNT_EXTENTS CNT_FREE_FRAGMENTS
    ------------------- ----------- ------------------
                      0           0                  2
     
    Executed in 0,297 seconds
  • 7. Re: Fragmented free space in empty LMT ASSM tablespace?
    Max Seleznev Explorer
    Currently Being Moderated
    Are you saying there were never any objects created in that tablespace? Are you absolutely confident?

    Could you run
    select table_name from dba_tables where tablespace_name in
    (select tablespace_name ts from dba_data_files where file_id=127);
    Not sure but maybe it has something to do with deferred segments. Just a thought.
  • 8. Re: Fragmented free space in empty LMT ASSM tablespace?
    user5066799 Newbie
    Currently Being Moderated
    Tried to find objects of any type to be located in tablespace, probably without segments due to deferred segment creation. But found nothing.
    SQL> with ts as (select tablespace_name ts from dba_data_files where file_id=127)
      2  select sum(cnt) total_objects_cnt from (
      3          select count(*) cnt from dba_clusters, ts where tablespace_name = ts.ts union all
      4          select count(*) cnt from dba_indexes, ts where tablespace_name = ts.ts union all
      5          select count(*) cnt from dba_ind_partitions, ts where tablespace_name = ts.ts union all
      6          select count(*) cnt from dba_ind_subpartitions, ts where tablespace_name = ts.ts union all
      7          select count(*) cnt from dba_lobs, ts where tablespace_name = ts.ts union all
      8          select count(*) cnt from dba_lob_partitions, ts where tablespace_name = ts.ts union all
      9          select count(*) cnt from dba_lob_subpartitions, ts where tablespace_name = ts.ts union all
     10          select count(*) cnt from dba_tables, ts where tablespace_name = ts.ts union all
     11          select count(*) cnt from dba_tab_partitions, ts where tablespace_name = ts.ts union all
     12          select count(*) cnt from dba_tab_subpartitions, ts where tablespace_name = ts.ts
     13  )
     14  ;
     
    TOTAL_OBJECTS_CNT
    -----------------
                    0
    Executed in 2,125 seconds
  • 9. Re: Fragmented free space in empty LMT ASSM tablespace?
    Max Seleznev Explorer
    Currently Being Moderated
    How about running DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY?
  • 10. Re: Fragmented free space in empty LMT ASSM tablespace?
    user5066799 Newbie
    Currently Being Moderated
    SQL> declare
      2      TSName varchar2(30);
      3  begin
      4    select tablespace_name into TSName from dba_data_files where file_id=127;
      5      DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY(tablespace_name => TSName,
      6        ts_option => DBMS_SPACE_ADMIN.TS_VERIFY_DEEP, segment_option => DBMS_SPACE_ADMIN.SEGMENT_VERIFY_DEEP);
      7  end;
      8  /
     
    PL/SQL procedure successfully completed
     
    Executed in 0,015 seconds
    But no trace file in user_dump_dest, which as I understand is expected without any segments in tablespace...
  • 11. Re: Fragmented free space in empty LMT ASSM tablespace?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user5066799 wrote:
    select count(*) from dba_extents where file_id=127;
    COUNT(*)
    0
    select count(*) from dba_free_space where file_id=127;
    COUNT(*)
    2
    select block_id, blocks from dba_free_space where file_id=127;
    
    BLOCK_ID     BLOCKS
    ---------- ----------
    128     507904
    508032     139616
    select extent_management, segment_space_management, allocation_type
    from dba_tablespaces 
    where tablespace_name=(select tablespace_name from dba_data_files where file_id=127);
    EXTENT_MANAGEMENT     SEGMENT_SPACE_MANAGEMENT     ALLOCATION_TYPE
    LOCAL     AUTO     SYSTEM
    alter tablespace ... coalesce;
    select count(*) from dba_free_space where file_id=127;
    COUNT(*)
    2
    select version from product_component_version where product like 'Oracle%';
    VERSION
    11.2.0.2.0

    How is that possible?
    This might be due to the limitation of how many AUTO extents can be mapped in the first 128 blocks of a file. It will take a few minutes to check, but my first guess would be that the last block reported in your free space is a little way from the end of the file and that there are a few more blocks past that which will turn out to be file space management blocks.

    Regards
    Jonathan Lewis
  • 12. Re: Fragmented free space in empty LMT ASSM tablespace?
    user5066799 Newbie
    Currently Being Moderated
    Interesting, who knows why the number of 3968M is a magical one? Looks like Oracle creates such continuous free fragments just after creating LMT ASSM tablespace. Have anybody seen anything like this? I'm writing some procedure which should identify free continuous fragment at the end of datafile, but this "feature" forces me to implement some artificial procedural workaround for this.
    SQL> create tablespace TEST_TS extent management local segment space management auto autoallocate
      2  datafile '+DATA/TEST_TS.dbf' size 3969M /*3968M + 1M for datafile header*/;
     
    Tablespace created
     
    Executed in 21,453 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
     
    Executed in 0,032 seconds
    SQL> select 4160749568/1024/1024 from dual;
     
    4160749568/1024/1024
    --------------------
                    3968
     
    Executed in 0,016 seconds
    SQL> drop tablespace TEST_TS including contents and datafiles;
     
    Tablespace dropped
     
    Executed in 1,844 seconds
    SQL> create tablespace TEST_TS extent management local segment space management auto autoallocate
      2  datafile '+DATA/TEST_TS.dbf' size 4161863680 /*3969M + 64K*/;
     
    Tablespace created
     
    Executed in 21,438 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,016 seconds
    SQL> drop tablespace TEST_TS including contents and datafiles;
     
    Tablespace dropped
     
    Executed in 1,782 seconds
    SQL> select 3968 * 4 + 1 from dual;
     
      3968*4+1
    ----------
         15873
     
    Executed in 0,016 seconds
    SQL> create tablespace TEST_TS extent management local segment space management auto autoallocate
      2  datafile '+DATA/TEST_TS.dbf' size 15873M;
     
    Tablespace created
     
    Executed in 85,141 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 4160749568     507904          129
    TEST_TS                               129    1015936 4160749568     507904          129
    TEST_TS                               129    1523840 4160749568     507904          129
     
    Executed in 0,063 seconds
  • 13. Re: Fragmented free space in empty LMT ASSM tablespace?
    user5066799 Newbie
    Currently Being Moderated
    Thanks Jonathan, but, sorry - have not got - what do you mean? Do you mean that space management blocks occupy first 128 blocks of datafile or do they occupy blocks after last free extent block? The only management blocks I expect to find are these first 128 blocks (1M) which as I understand are datafile header (was 64K in earlier versions, but in 11.2 is 1M, with 8K results in 128 blocks).
    Or do you mean the fact of using autoallocate extent size influences this matter? Should we consider "autoallocate start size" of 64K in relation to something?
  • 14. Re: Fragmented free space in empty LMT ASSM tablespace?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user5066799 wrote:
    Thanks Jonathan, but, sorry - have not got - what do you mean? Do you mean that space management blocks occupy first 128 blocks of datafile or do they occupy blocks after last free extent block? The only management blocks I expect to find are these first 128 blocks (1M) which as I understand are datafile header (was 64K in earlier versions, but in 11.2 is 1M, with 8K results in 128 blocks).
    Or do you mean the fact of using autoallocate extent size influences this matter? Should we consider "autoallocate start size" of 64K in relation to something?
    Your interpretation of what I was saying is correct - but I got the scale wrong, there's a different explanation of what you're seeing.
    If you dump the first few blocks of the file, in your case using something like:
    alter system dump datafile 129 block min 2 block max 6;
    you will see that block 2 will be the "file space header block" and the next blocks will be file space bitmap blocks.
    Each file space bitmap block (in an 8KB block) loses a couple of hundred bytes to block headers and other control details, then has one bit for each 64KB of the data file, where the 64KB unit is dictated by the autoallocate option

    Sample dump:
    buffer tsn: 14 rdba: 0x01c00002 (7/2)
    scn: 0x0b86.40279c3f seq: 0x01 flg: 0x04 tail: 0x9c3f1d01
    frmt: 0x02 chkval: 0x3100 type: 0x1d=KTFB Bitmapped File Space Header
    ...
    File Space Header Block: 
    Header Control: 
    RelFno: 7, Unit: 8, Size: 655360, Flag: 1 
    AutoExtend: NO, Increment: 0, MaxSize: 0 
    Initial Area: 126, Tail: 655359, First: 0, Free: 81904 
    
    
    buffer tsn: 14 rdba: 0x01c00003 (7/3)
    scn: 0x0b86.40279b45 seq: 0x01 flg: 0x04 tail: 0x9b451e01
    frmt: 0x02 chkval: 0x0afb type: 0x1e=KTFB Bitmapped File Space Bitmap
    ...
    File Space Bitmap Block: 
    BitMap Control: 
    RelFno: 7, BeginBlock: 128, Flag: 0, First: 0, Free: 63488 
    
    buffer tsn: 14 rdba: 0x01c00004 (7/4)
    scn: 0x0b86.40279b47 seq: 0x01 flg: 0x04 tail: 0x9b471e01
    frmt: 0x02 chkval: 0xcafb type: 0x1e=KTFB Bitmapped File Space Bitmap
    ...
    File Space Bitmap Block: 
    BitMap Control: 
    RelFno: 7, BeginBlock: 508032, Flag: 0, First: 0, Free: 63488 
    Note particularly the "Free: 63488" - which is bits, each bit represents 8 blocks for a total of 507904. Allowing for the first 128 blocks in the file this is why the map in block 7/4 has a BeginBlock of 508032.
    A single extent in dba_free_extents is, apparently, not allowed to cross a space management block.

    Regards
    Jonathan Lewis


    P.S. Since there are 126 available file space bitmap blocks, and 507904 blocks allowed per bitmap block, then when the file hits 63,995,904 blocks (488.25 GB) Oracle will have to add a secondary space management area. (At least, that's if you grow the file to that size; if you pre-create the tablespace at that something above that size then perhaps Oracle will allocate 2M of file header.)

    P.P.S Here's a quote from my errata pages for Practical Oracle 8i: "Allowing for a little overhead, a single 8K block can hold information about 63,488 extents,..." so not a lot has changed in the interim, apart from the fact that the header is now 1MB rather than 64KB - possibly to cater for bigfile tablespaces, possibly to help ASM and Exadata align their extents and AUs.

    Edited by: Jonathan Lewis on Dec 7, 2012 6:30 PM
1 2 Previous Next

Legend

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