tablespace fragmentation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: tablespace fragmentation

  1. #1
    Join Date
    Jan 2005
    Posts
    221

    tablespace fragmentation

    If I have a HWM on the table, I move the tabes to different tablespaces using :

    ALTER TABLE TABLE_NAME MOVE NEW_TABLESPACE;


    what if I move the table on the same tablespace like:

    ALTER TABLE TABLE_NAME MOVE;

    the question is "is it bad thing to move the table on the same tablespace??? which may cause fragmentation on the tablespace b/c of the old segment???"

    I am on 9i and using local extent

    have a great weekend to all.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ========
    what if I move the table on the same tablespace like:

    ALTER TABLE TABLE_NAME MOVE;

    the question is "is it bad thing to move the table on the same tablespace??? which may cause fragmentation on the tablespace b/c of the old segment???"

    I am on 9i and using local extent
    ========

    It is not bad to move a table within the same tablespace. In fact after the move the rows would be packed within the blocks. Of course, you have to rebuild indexes after the move.

    Tamil

  3. #3
    Join Date
    Jan 2005
    Posts
    221
    my colleagues think it's cause the tablespace fragmented and I told him I don't think so but I can't prove it.

  4. #4
    Join Date
    Mar 2002
    Posts
    534
    in case you are using LMT I would not worry about the tablespace fragmentation

  5. #5
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    This is an extract from a reply on allmost the same question

    "If you are suffering from fragmentation -- your problem is not the index
    rebuilds. It would be because you are using DICTIONARY managed tablespaces.
    Switch over to locally managed tablespaces and you'll never have fragmentation
    again. So, my suggestion -- one last index rebuild:

    o create a locally managed tablespace
    o alter index rebuild tablespace LMT_FROM_ABOVE storage ( initial 1k );

    and then forget about them. You will rebuild an index in response to IDENTIFIED
    and DEGRADED performance -- period. You will not rebulid indexes based on a
    schedule. Period."
    Able was I ere I saw Elba

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Hi hannah00,

    There are 5 types of fragmentation:
    1. ROW LEVEL Fragmentation
    2. Block Level Fragmentation
    3. Segment Level Fragmentation
    4. Tablespace Fragmentation
    5. Disk level fragmentation

    Which one are you interested? You are already using LMT, so the option 4 is ruled out.

    Tamil

  7. #7
    Join Date
    Jan 2005
    Posts
    221
    tamil,

    once you do the alter move on the same tablespace, your HWM will be removed, so what happen to the empty segment/extent??? will it be reused by some other objects???

    I am not 100% clear on this. and thanks so much for your input.

    I am on LMT and I understand that LMT will eliminate fragmentation, but I would like to understand how.

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The freed extents will be reused as and when new extents are needed for any object.

    See the example.
    PHP Code:
    SQLL
      1  select segment_name
    tablespace_name
         
    file_idextent_idblock_idblocks bytes
      2  from dba_extents where segment_name 
    'MY_TABLE'
      
    3order by file_idblock_id
    SQL
    > /

    SEGMENT_NAME TABLESPACE_NAME     FILE_ID  EXTENT_ID   BLOCK_ID     BLOCKS      BYTES
    ------------ ---------------- ---------- ---------- ---------- ---------- ----------
    MY_TABLE     SBL_MEDIUM_DATA           9          1      64009       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA           9          3      65289       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA           9          5      66569       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA           9          7      67849       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA           9          9      69129       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA           9         11      70409       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          0      60169       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          2      62729       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          4      64009       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          6      65289       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          8      66569       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10         10      67849       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10         12      69129       1280   10485760

    13 rows selected
    .

    SQLsave x1.sql repl
    Wrote file x1
    .sql
    SQL
    delete my_table where mod(object_id,3)= ;

    15588 rows deleted.

    SQLcommit;

    Commit complete.

    SQLalter table my_table move;

    Table altered.

    SQLget x1
      1  select segment_name
    tablespace_name
         
    file_idextent_idblock_idblocks bytes
      2  from dba_extents where segment_name 
    'MY_TABLE'
      
    3order by file_idblock_id
    SQL
    > /

    SEGMENT_NAME TABLESPACE_NAME     FILE_ID  EXTENT_ID   BLOCK_ID     BLOCKS      BYTES
    ------------ ---------------- ---------- ---------- ---------- ---------- ----------
    MY_TABLE     SBL_MEDIUM_DATA           9          1      71689       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA           9          3      72969       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA           9          5      74249       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA           9          7      75529       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          0      70409       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          2      71689       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          4      72969       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          6      74249       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          8      75529       1280   10485760

    9 rows selected
    .

    When you move a table with in the same tablespaceoracle would allocate new extents

    NowI move the table to a different tablespaceusers.

    SQLalter table my_table move tablespace users ;

    Table altered.

    AgainI move it back to original tablespacesbl_medium_data.

    SQLalter table my_table move tablespace sbl_medium_data ;

    Table altered.

    SQLget x1
      1  select segment_name
    tablespace_name
           
    file_idextent_idblock_idblocks bytes
      2  from dba_extents where segment_name 
    'MY_TABLE'
      
    3order by file_idblock_id
    SQL
    > /

    SEGMENT_NAME TABLESPACE_NAME     FILE_ID  EXTENT_ID   BLOCK_ID     BLOCKS      BYTES
    ------------ ---------------- ---------- ---------- ---------- ---------- ----------
    MY_TABLE     SBL_MEDIUM_DATA           9          1      64009       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA           9          3      65289       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA           9          5      66569       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA           9          7      67849       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          0      60169       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          2      62729       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          4      64009       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          6      65289       1280   10485760
    MY_TABLE     SBL_MEDIUM_DATA          10          8      66569       1280   10485760

    9 rows selected
    .

    The freed extents are reused.

    SQLspool off 
    Tamil

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by hannah00
    I am on LMT and I understand that LMT will eliminate fragmentation, but I would like to understand how.
    Only if u have uniform extent.. well when u have uniform extents how will the Q of fragmentation arrise as the extents freed due DDL will be reused.. and ther wont be any small gaps left in while formating any extent in the datafile..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width