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_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 |
No comments:
Post a Comment