1、统计分析脚本:
在数据库创建函数REAL_SIZE,用以分析统计归档源表的实际大小,即除去HWM下空洞后的真实大小,函数创建脚本如下:

CREATE OR REPLACE FUNCTION REAL_SIZE(P_SEGNAME IN VARCHAR2,
                                     P_OWNER   IN VARCHAR2 DEFAULT USER,
                                     P_TYPE    IN VARCHAR2 DEFAULT 'TABLE')
  RETURN NUMBER AUTHID CURRENT_USER AS
  L_TOTAL_BLOCKS       NUMBER;
  L_TOTAL_BYTES        NUMBER;
  L_UNUSED_BLOCKS      NUMBER;
  L_UNUSED_BYTES       NUMBER;
  L_LASTUSEDEXTFILEID  NUMBER;
  L_LASTUSEDEXTBLOCKID NUMBER;
  L_LAST_USED_BLOCK    NUMBER;
  L_UNFORMATTED_BLOCKS NUMBER;
  L_UNFORMATTED_BYTES  NUMBER;
  L_FS1_BLOCKS         NUMBER;
  L_FS1_BYTES          NUMBER;
  L_FS2_BLOCKS         NUMBER;
  L_FS2_BYTES          NUMBER;
  L_FS3_BLOCKS         NUMBER;
  L_FS3_BYTES          NUMBER;
  L_FS4_BLOCKS         NUMBER;
  L_FS4_BYTES          NUMBER;
  L_FULL_BLOCKS        NUMBER;
  L_FULL_BYTES         NUMBER;
  T_TOTAL_BYTES        NUMBER;
  T_FS_BYTES           NUMBER;
  IS_PART              VARCHAR2(3);
  P_PART_NAME          VARCHAR2(30);
  SQLCUR               VARCHAR2(200);
  TYPE VC_SQL IS REF CURSOR;
  CUR_PART VC_SQL;
BEGIN
  T_FS_BYTES    := 0;
  T_TOTAL_BYTES := 0;
  SELECT PARTITIONED
    INTO IS_PART
    FROM DBA_TABLES
   WHERE OWNER = P_OWNER
     AND TABLE_NAME = P_SEGNAME;
  IF IS_PART = 'YES' THEN
    IF P_TYPE = 'TABLE' THEN
      SQLCUR := 'SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER = ''' ||
                P_OWNER || ''' AND TABLE_NAME = ''' || P_SEGNAME || '''';
    END IF;
    IF P_TYPE = 'INDEX' THEN
      SQLCUR := 'SELECT PARTITION_NAME FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER = ''' ||
                P_OWNER || ''' AND INDEX_NAME = ''' || P_SEGNAME || '''';
    END IF;
    --PT_TYPE := P_TYPE || ' PARTITION';
    OPEN CUR_PART FOR SQLCUR;
    LOOP
      FETCH CUR_PART
        INTO P_PART_NAME;
      EXIT WHEN CUR_PART%NOTFOUND;
      DBMS_SPACE.SPACE_USAGE(P_OWNER,
                             P_SEGNAME,
                             P_TYPE || ' PARTITION',
                             L_UNFORMATTED_BLOCKS,
                             L_UNFORMATTED_BYTES,
                             L_FS1_BLOCKS,
                             L_FS1_BYTES,
                             L_FS2_BLOCKS,
                             L_FS2_BYTES,
                             L_FS3_BLOCKS,
                             L_FS3_BYTES,
                             L_FS4_BLOCKS,
                             L_FS4_BYTES,
                             L_FULL_BLOCKS,
                             L_FULL_BYTES,
                             P_PART_NAME);
      DBMS_SPACE.UNUSED_SPACE(P_OWNER,
                              P_SEGNAME,
                              P_TYPE || ' PARTITION',
                              L_TOTAL_BLOCKS,
                              L_TOTAL_BYTES,
                              L_UNUSED_BLOCKS,
                              L_UNUSED_BYTES,
                              L_LASTUSEDEXTFILEID,
                              L_LASTUSEDEXTBLOCKID,
                              L_LAST_USED_BLOCK,
                              P_PART_NAME);
      T_FS_BYTES    := T_FS_BYTES + L_FS1_BYTES * 0.25 / 2 +
                       L_FS2_BYTES * (0.5 + 0.25) / 2 +
                       L_FS3_BYTES * (0.75 + 0.5) / 2 +
                       L_FS4_BYTES * (1 + 0.75) / 2 + L_UNUSED_BYTES;
      T_TOTAL_BYTES := T_TOTAL_BYTES + L_TOTAL_BYTES;
    END LOOP;
    CLOSE CUR_PART;
  END IF;
  IF IS_PART = 'NO' THEN
    DBMS_SPACE.SPACE_USAGE(P_OWNER,
                           P_SEGNAME,
                           P_TYPE,
                           L_UNFORMATTED_BLOCKS,
                           L_UNFORMATTED_BYTES,
                           L_FS1_BLOCKS,
                           L_FS1_BYTES,
                           L_FS2_BLOCKS,
                           L_FS2_BYTES,
                           L_FS3_BLOCKS,
                           L_FS3_BYTES,
                           L_FS4_BLOCKS,
                           L_FS4_BYTES,
                           L_FULL_BLOCKS,
                           L_FULL_BYTES,
                           P_PART_NAME);
    DBMS_SPACE.UNUSED_SPACE(P_OWNER,
                            P_SEGNAME,
                            P_TYPE,
                            L_TOTAL_BLOCKS,
                            L_TOTAL_BYTES,
                            L_UNUSED_BLOCKS,
                            L_UNUSED_BYTES,
                            L_LASTUSEDEXTFILEID,
                            L_LASTUSEDEXTBLOCKID,
                            L_LAST_USED_BLOCK,
                            P_PART_NAME);
    T_FS_BYTES    := L_FS1_BYTES * 0.25 / 2 +
                     L_FS2_BYTES * (0.5 + 0.25) / 2 +
                     L_FS3_BYTES * (0.75 + 0.5) / 2 +
                     L_FS4_BYTES * (1 + 0.75) / 2 + L_UNUSED_BYTES;
    T_TOTAL_BYTES := L_TOTAL_BYTES;
  END IF;
  RETURN T_TOTAL_BYTES - T_FS_BYTES;
EXCEPTION
  WHEN OTHERS THEN
    RETURN - 1;
END;
/

2、REAL_SIZE计算方法:
(1)使用DBMS_SPACE.SPACE_USAGE分析目标表,获取BLOCK空闲度分布结构:

空闲度	  BLOCK数	 加权空闲BLOCK数
--------  -------  ------------------
0-25%	    a	       a*(0+25%)/2
25-50%	  b	       b*(25%+50%)/2
50-75%	  c	       c*(50%+75%)/2
75-100%	  d	       d*(75%+100%)/2

(2)使用DBMS_SPACE.UNUSED_SPACE分析目标表,获取完全空闲的BLOCK数:

空闲度	BLOCK数	  加权空闲BLOCK数
------  -------   ----------------
100%	  e	        e*100%

(3)加权计算结果汇总:
段的真实大小 = DBA_SEGMENTS的段大小 – (以上黄色标注的加和) * 数据库block大小
如果判断是分区表,则按以上方法逐个扫描所有分区。

3、REAL_SIZE计算测试结果:

表大小(MB)	    行数	delete行数	shrink后大小(MB)	空洞率	真实大小(MB)	真实大小偏差率
----------  --------  ----------  ---------------- -------  ------------  ----------------
12	          102082	  6750	                 12	   0.00%	       11.11	         7.42%
104	          918747	  60750	                100	   3.85%	       98.62	         1.38%
368	         3266656	  113920	              359	   2.45%	      357.10	         0.53%
1194	      10633309	  1733488	             1016	  14.91%	      984.32	         3.12%
2457	      21966573	  364219	             2434	   0.94%	     2426.29	         0.32%
2457	      21966573	  200000	             2457	   0.00%	     2456.20	         0.03%
2457	      21966573	  400000	             2455	   0.08%	     2453.31	         0.07%
2457	      21966573	  1000000	             2417	   1.63%	     2389.98	         1.12%
2457	      21966573	  1500000	             2348	   4.44%	     2294.08	         2.30%
Trackback

no comment untill now

Add your comment now

切换到手机版