Segments fragmentation with space management MANUAL
                                                 Last update (2008-11-28 16:29:42)
                                                                                                                   Date added (2008-10-22 12:05:13)

                                                                                                              Posted by: Eleutherios Tsekouras

Note: The method described here will only work on segments stored in tablespaces with SEGMENT_SPACE_MANAGEMENT=MANUAL.
Tablespaces with SEGMENT_SPACE_MANAGEMENT=AUTO do not manage their space via free lists, so the call to dbms_space.free_blocks will result to an error:"ORA-10618: Operation not allowed on this segment".

Summary
If a table is only subject to inserts, there will not be any fragmentation. Fragmentation comes with when we update/delete data in a table. The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reused ever at all). This leaves holes in the table which results in table fragmentation.

To understand it more clearly, we need to be clear on how oracle manages space for tables.
“High water mark” (HWM) of a segment actually defines the border line between (ever) used and unused (never) space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.

The following method will allow us to find fragmented segments having stale statistics or no statistics taken at all.
First, create a table to hold the segments we want to analyze (e.g. Schema's PERFSTAT segments):
CREATE TABLE helpdesk.segs TABLESPACE helpdesk 
AS SELECT * FROM dba_segments 
WHERE owner='PERFSTAT' 
AND tablespace_name IS NOT NULL 
AND segment_type IN ('TABLE','INDEX','CLUSTER');

We create a table to save the information we need:
CREATE TABLE helpdesk.frag_segs(OWNER VARCHAR2(30 BYTE),
SEG_NAME VARCHAR2(30 BYTE),SEG_TYPE VARCHAR2(30 BYTE), 
DT DATE,TOTAL_BLCKS NUMBER,UNUSED_BLCKS NUMBER, 
FREE_BLCKS NUMBER) 
TABLESPACE HELPDESK;

TOTAL_BLCKS: Number of blocks allocated to the segment.
UNUSED_BLCKS: Segment's number of blocks that are totally empty (above the HWM). This value is the same with the EMPTY_BLOCKS shown by USER_TABLES view. (Caution: EMPTY_BLOCKS is updated by statistics gathering). These blocks will NOT be scanned in the event of a full scan.
FREE_BLCKS: Segment's number of blocks that are in its free list. These blocks have still space left for inserts. Deletes/updates will free a fragment (or whole) of the data stored in a full block. That block will be considered by Oracle as a Free Block, that is a block that MIGHT contain data. These blocks are BELOW the HWM and WILL be scanned in the event of a full scan.
The number of blocks, that are completely filled with data is derived from:
FULL_BLCKS=TOTAL_BLCKS - UNUSED_BLCKS - FREE_BLCKS -1 (This is a block used by Oracle to store the extent map for the segment).

The goal is to have as less free blocks as possible, so the HWM will be at its optimal position and optimize the time consumed by full scans.

We run the following procedure, which uses dbms_space.unused_space and dbms_space.free_blocks procedures to populate helpdesk.frag_segs table.
DECLARE
v_tblocks number;
v_tbytes number;
v_ublocks number;
v_ubytes number;
v_lue_file_id number;
v_lue_block_id number;
v_lu_block number;
v_free_blks number;

CURSOR c1 IS 
SELECT owner,segment_name,segment_type FROM HELPDESK.SEGS;

BEGIN 
FOR line IN c1 
LOOP
   Dbms_Space.unused_space (segment_owner=>line.owner,segment_name=>line.segment_name, 
   segment_type=>line.segment_type,total_blocks=>v_tblocks,total_bytes=>v_tbytes, 
   unused_blocks=>v_ublocks,unused_bytes=>v_ubytes,last_used_extent_file_id=>v_lue_file_id, 
   last_used_extent_block_id=>v_lue_block_id,last_used_block=>v_lu_block);
   Dbms_Space.free_blocks(segment_owner=>line.owner,segment_name=>line.segment_name, 
   segment_type=>line.segment_type,freelist_group_id=>0,free_blks=>v_free_blks);
   INSERT INTO helpdesk.frag_segs(OWNER,SEG_NAME,SEG_TYPE,DT,TOTAL_BLCKS,UNUSED_BLCKS,FREE_BLCKS)
   VALUES (LINE.OWNER,LINE.SEGMENT_NAME,LINE.SEGMENT_TYPE,SYSDATE,V_TBLOCKS,V_UBLOCKS,v_free_blks);
   IF MOD(c1%rowcount,100)=0 THEN
     COMMIT;
   END IF;
END LOOP;
COMMIT;
END;
/

Finally, we run this query to derive some conclusions about which segments need rebuild or shrink (we have block size of 8192 bytes. Modify accordingly for different block size):
SELECT seg_name,seg_type, (TOTAL_BLCKS*8192)/1024/1024 "TOTAL SPACE MB",
((TOTAL_BLCKS-UNUSED_BLCKS-FREE_BLCKS-1)*8192)/1024/1024 "FULL SPACE MB",
(UNUSED_BLCKS*8192)/1024/1024 "UNUSED SPACE MB",(FREE_BLCKS*8192)/1024/1024 "FREE SPACE MB", 
ROUND((((TOTAL_BLCKS-UNUSED_BLCKS-FREE_BLCKS-1)*100)/TOTAL_BLCKS),2) "FULL SPACE %", 
ROUND(((UNUSED_BLCKS*100)/TOTAL_BLCKS),2) "UNUSED SPACE %", 
ROUND(((FREE_BLCKS*100)/TOTAL_BLCKS),2) "FREE SPACE %"
FROM helpdesk.frag_segs
WHERE TOTAL_BLCKS>2
ORDER BY "FREE SPACE MB" DESC;


An example:
After purging a few thousand statspack snapsots, PERFSTAT's segments got fragmented, eg. table STATS$SQL_SUMMARY and its index STATS$SQL_SUMMARY_PK:
SEG_NAME SEG_TYPE TOTAL SPACE MB FULL SPACE MB UNUSED SPACE MB FREE SPACE MB FULL SPACE % UNUSED SPACE % FREE SPACE %
STATS$SQL_SUMMARY TABLE 5174 1432,2421875 0,84375 3740,90625 27,68 0,02 72,3
STATS$SQL_SUMMARY_PK INDEX 4203 1174,265625 0,296875 3028,4296875 27,94 0,01 72,05



We move the table and we rebuild the index:
SQL> alter table STATS$SQL_SUMMARY move;
 
Table altered.
 
SQL> alter index STATS$SQL_SUMMARY_PK rebuild;
 
Index altered.


Now these segments are no more fragmented:
SEG_NAME SEG_TYPE TOTAL SPACE MB FULL SPACE MB UNUSED SPACE MB FREE SPACE MB FULL SPACE % UNUSED SPACE % FREE SPACE %
STATS$SQL_SUMMARY TABLE 1438 1437,265625 0,7265625 0 99,95 0,05 0
STATS$SQL_SUMMARY_PK INDEX 688 687,796875 0,1953125 0 99,97 0,03 0
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search