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

[JChiappa]

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 :

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 :

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 :

==============================================
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 :

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

[PAVB]

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.

[davey23uk]

its not fragmentation. the space is re-usable

[PAVB]

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

[pando]

I call that High Water Mark and not fragmentation

[davey23uk]

fragmentation is space that cannot be re-used, that space can

[PAVB]

I call that High Water Mark and not fragmentation

You always have a HWM no matter the density of the table.

[PAVB]

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.

[Bore]

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

[JChiappa]

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