Monday, February 16, 2009

Segments fragmentation with space management AUTO

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

Summary
In this method we will use the procedure dbms_space.space_usage.
Its description from Oracle9i Supplied PL/SQL Packages and Types Reference states:
This procedure shows the space usage of data blocks under the segment High Water Mark. The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure. This procedure can only be used on tablespaces that are created with auto segment space management.
It is clear that system blocks used by Oracle to manage segment space allocation will not be returned from this procedure call.
So, do not expect the segment's total blocks returned by dbms_space.unused_space to be equal to the sum of full, free, unused and unformatted blocks.

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,
   TOTAL_BYTES    NUMBER,
   UNUSED_BLCKS   NUMBER,
   UNUSED_BYTES   NUMBER,
   FREE_BLCKS     NUMBER,
   FREE_BYTES     NUMBER,
   FULL_BLCKS     NUMBER,
   FULL_BYTES     NUMBER,
   UNFORM_BLCKS   NUMBER,
   UNFORM_BYTES   NUMBER
)
TABLESPACE HELPDESK;

We run the following procedure, which uses dbms_space.unused_space and dbms_space.space_usage 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_UNFORMATTED_BLOCKS   NUMBER;
   V_UNFORMATTED_BYTES    NUMBER;
   V_FS1_BLOCKS           NUMBER;
   V_FS1_BYTES            NUMBER;
   V_FS2_BLOCKS           NUMBER;
   V_FS2_BYTES            NUMBER;
   V_FS3_BLOCKS           NUMBER;
   V_FS3_BYTES            NUMBER;
   V_FS4_BLOCKS           NUMBER;
   V_FS4_BYTES            NUMBER;
   V_FULL_BLOCKS          NUMBER;
   V_FULL_BYTES           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.SPACE_USAGE (SEGMENT_OWNER        => LINE.OWNER,
                              SEGMENT_NAME         => LINE.SEGMENT_NAME,
                              SEGMENT_TYPE         => LINE.SEGMENT_TYPE,
                              UNFORMATTED_BLOCKS   => V_UNFORMATTED_BLOCKS,
                              UNFORMATTED_BYTES    => V_UNFORMATTED_BYTES,
                              FS1_BLOCKS           => V_FS1_BLOCKS,
                              FS1_BYTES            => V_FS1_BYTES,
                              FS2_BLOCKS           => V_FS2_BLOCKS,
                              FS2_BYTES            => V_FS2_BYTES,
                              FS3_BLOCKS           => V_FS3_BLOCKS,
                              FS3_BYTES            => V_FS3_BYTES,
                              FS4_BLOCKS           => V_FS4_BLOCKS,
                              FS4_BYTES            => V_FS4_BYTES,
                              FULL_BLOCKS          => V_FULL_BLOCKS,
                              FULL_BYTES           => V_FULL_BYTES);

      INSERT INTO HELPDESK.FRAG_SEGS (OWNER,
                                      SEG_NAME,
                                      SEG_TYPE,
                                      DT,
                                      TOTAL_BLCKS,
                                      TOTAL_BYTES,
                                      UNUSED_BLCKS,
                                      UNUSED_BYTES,
                                      FREE_BLCKS,
                                      FREE_BYTES,
                                      FULL_BLCKS,
                                      FULL_BYTES,
                                      UNFORM_BLCKS,
                                      UNFORM_BYTES)
           VALUES (LINE.OWNER,
                   LINE.SEGMENT_NAME,
                   LINE.SEGMENT_TYPE,
                   SYSDATE,
                   V_TBLOCKS,
                   V_TBYTES,
                   V_UBLOCKS,
                   V_UBYTES,
                   V_FS1_BLOCKS + V_FS2_BLOCKS + V_FS3_BLOCKS + V_FS4_BLOCKS,
                   V_FS1_BYTES + V_FS2_BYTES + V_FS3_BYTES + V_FS4_BYTES,
                   V_FULL_BLOCKS,
                   V_FULL_BYTES,
                   V_UNFORMATTED_BLOCKS,
                   V_UNFORMATTED_BYTES);

      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:
SELECT SEG_NAME,
         SEG_TYPE,
         ROUND (TOTAL_BYTES / 1024 / 1024, 2) "TOTAL SPACE MB",
         ROUND (FULL_BYTES / 1024 / 1024, 2) "FULL SPACE MB",
         ROUND (UNUSED_BYTES / 1024 / 1024, 2) "UNUSED SPACE MB",
         ROUND (FREE_BYTES / 1024 / 1024, 2) "FREE SPACE MB",
         ROUND (UNFORM_BYTES / 1024 / 1024, 2) "UNFORM. SPACE MB",
         ROUND ( ( (FULL_BYTES * 100) / TOTAL_BYTES), 2) "FULL SPACE %",
         ROUND ( ( (UNUSED_BYTES * 100) / TOTAL_BYTES), 2) "UNUSED SPACE %",
         ROUND ( ( (FREE_BYTES * 100) / TOTAL_BYTES), 2) "FREE SPACE %",
         ROUND ( ( (UNFORM_BYTES * 100) / TOTAL_BYTES), 2) "UNFORM. SPACE %"
    FROM HELPDESK.FRAG_SEGS
   WHERE TOTAL_BLCKS > 2
ORDER BY "FREE SPACE MB" DESC;

No comments:

Post a Comment