Monday, February 16, 2009

Segments fragmentation with space management MANUAL

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".
Check this post for SEGMENT_SPACE_MANAGEMENT=AUTO.

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_NAMESEG_TYPETOTAL SPACE MBFULL SPACE MBUNUSED SPACE MBFREE SPACE MBFULL SPACE %UNUSED SPACE %FREE SPACE %
STATS$SQL_SUMMARYTABLE51741432.24218750.843753740.9062527.680.0272.3
STATS$SQL_SUMMARY_PKINDEX42031174.2656250.2968753028.429687527.940.0172.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_NAMESEG_TYPETOTAL SPACE MBFULL SPACE MBUNUSED SPACE MBFREE SPACE MBFULL SPACE %UNUSED SPACE %FREE SPACE %
STATS$SQL_SUMMARYTABLE14381437.2656250.7265625099.950.050
STATS$SQL_SUMMARY_PKINDEX688687.7968750.1953125099.970.030

No comments:

Post a Comment