-
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
-
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.
-
its not fragmentation. the space is re-usable
-
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.
-
I call that High Water Mark and not fragmentation
-
fragmentation is space that cannot be re-used, that space can
-
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.
-
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.
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|