|
Posted by: Eleutherios Tsekouras
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".
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.
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 (we have block size of 8192 bytes. Modify accordingly for different block size):
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(UNIFORM_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
|
