White space (space once used, but reserved) in tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: White space (space once used, but reserved) in tables

  1. #1
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350

    White space (space once used, but reserved) in tables

    Hi people : in a 10Gr1 EE dw-like database, I have the following non-partitioned, non-IOT (heap only) table, residing in a non-ASSM uniform-sized LMT tablespace :

    Code:
    chiappa@DWBAN:SQL>@desc TB_ORGAN_RECEIVE ;
     Nome                                      Nulo?    TYPE
     ----------------------------------------- -------- ----------------------------
     TYPE_ORGAN_REC                            NOT NULL VARCHAR2(1)
     DESC_TYPE_ORGAN_REC                                VARCHAR2(30)
     COD_ORGAN                                 NOT NULL NUMBER
     DESC_ORGAN_REC                                     VARCHAR2(35)
    The table have few rows :

    chiappa@DWBAN:SQL>select count(*) from TB_ORGAN_RECEIVE ;

    COUNT(*)
    ------------------
    4283

    But it is occupying a lot of physical space :

    Code:
    chiappa@DWBAN:SQL>select * from user_segments where segment_name='TB_ORGAN_RECEIVE';
    
    SEGMENT_NAME                        PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                             BYTES             BLOCKS            EXTENTS     INITIAL_EXTENT        NEXT_EXTENT        MIN_EXTENTS        MAX_EXTENTS       PCT_INCREASE          FREELISTS    FREELIST_GROUPS BUFFER_
    ----------------------------------- ------------------------------ ------------------ ------------------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ -------
    TB_ORGAN_RECEIVE                                                    TABLE              DW_INTERFACE_DAT                       1845493760             225280                440            4194304            4194304                  1         2147483645                  0                                       DEFAULT
    I queried the unused space (via DBMS_SPACE), and there is no un-allocated space :

    Code:
    ==============================================
    Segment_name                 = TB_ORGAN_RECEIVE
    Total Blocks                 = 225280
    Total Bytes                  = 1845493760
    Unused (Free) Blocks         = 0
    Unused (Free) Bytes          = 0
    Used Blocks                  = 225280
    Used Bytes                   = 1845493760
    Last used extents file id    = 5
    Last used extents block id   = 3831816
    Last used block              = 512
    =============================================
    And nope. SO, I guessed a white-space question (ie, the table someday was loaded with a lot of data, much of the data was deleted (not truncated), as normal the DELETE keep the now non-used space reserved to the segment, AND after that APPEND-mode loaded data above this. In this line of thought, I made a move :

    chiappa@DWBAN:SQL>alter table TB_ORGAN_RECEIVE move;

    Tabela alterada.


    and yes, this was the point :


    Code:
    chiappa@DWBAN:SQL>select * from user_segments where segment_name='TB_ORGAN_RECEIVE';
    
    SEGMENT_NAME                        PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                             BYTES             BLOCKS            EXTENTS     INITIAL_EXTENT        NEXT_EXTENT        MIN_EXTENTS        MAX_EXTENTS       PCT_INCREASE          FREELISTS    FREELIST_GROUPS BUFFER_
    ----------------------------------- ------------------------------ ------------------ ------------------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ -------
    TB_ORGAN_RECEIVE                                                    TABLE              DW_INTERFACE_DAT                          4194304                512                  1            4194304            4194304                  1         2147483645                  0                                       DEFAULT
    So, my question is : how I can find/detect this type of situation ? As showed, I made a compare between NUM_ROWS and BYTES...

    Regards,

    Chiappa

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Welcome to the wonderful world of fragmentation.

    You have to compare (num_rows * avg_row_lenght) weighted by pct_used against sum of segment bytes.

    Plenty of scripts out there in the web.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    its not fragmentation. the space is re-usable

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    That's correct, space is re-usable but still Table Fragmentation.

    "Table fragmentation will result in longer query times when a full table scan is performed. Since data is not as evenly packed in the data blocks, many blocks may have to be read during a scan to satisfy the query. These blocks may be distributed on various extents. In this case, Oracle must issue recursive calls to locate the address of the next extent in the table to scan." (1)

    (1) SAP; Monitoring Table and Index Fragmentation; http://help.sap.com/saphelp_bw30b/he...bd/content.htm
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I call that High Water Mark and not fragmentation

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    fragmentation is space that cannot be re-used, that space can

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by pando
    I call that High Water Mark and not fragmentation
    You always have a HWM no matter the density of the table.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by davey23uk
    fragmentation is space that cannot be re-used, that space can
    Since all space at any level -table, index, tablespace- can be re-used your definition implies there is no chance of having fragmentation in the known universe. Fragmentation is dead, long live to Fragmentation.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    In 10g R2 there is a beast called Segment adviser (maybe it's present in 10gR1 as well)
    That's your guy. You run it on table/schema/tablespace and it let's you know the segments needing reorganization\
    In 10g R2 it runs automatically during the maintanence window, analyzing
    1) The tablespaces with the warning threshold reacged
    2) The frequently full table scanned tables

  10. #10
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    PAVB, I called the thing "white space" because I accept and use TK´s definition of fragmentation (showed in http://asktom.oracle.com/pls/asktom/...19043139891434 ) : 'space IMPOSSIBLE to reuse in normal DML operations', ie, extents with different sizes (the db cannot "break" extents in smaller parts), this kind of space yes, NEVER will be reused. My case is different, eventual DMLs ** will ** use my now-empty space normally.no fragmentation properly saying here...
    Bore, I will try the Segment Advisor, but according to the manual (OracleŽ Database Administrator's Guide, chapter 14 "Managing Space for Schema" :

    "The Segment Advisor generates the following types of advice:

    If the Segment Advisor determines that an object has a significant amount of free space, it recommends online segment shrink. If the object is a table that is not eligible for shrinking, as in the case of a table in a tablespace without automatic segment space management, the Segment Advisor recommends online table redefinition.

    If the Segment Advisor encounters a table with row chaining above a certain threshold, it records that fact that the table has an excess of chained rows
    "

    row chaining, of course, don´t matters in the context what we are talking, my doubt is : what is "a significant amount" ? What is the algorithm ? Is it based in extent size, or it does the same as me (ie, compares number of rows with number of blocks) or what ? It covers my situation, in a word ?

    Regards,

    Chiappa

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