如何评估数据膨胀情况

今天主要想分享一下数据膨胀情况评估脚本，即估算表存在多少空洞。那么问题来了，什么是数据膨胀？为什么会数据膨胀？

1、什么是数据膨胀？

数据膨胀指实际存储占用远大于有效数据量，导致空间浪费；另外查询变慢，因为数据库在做全表扫描时候会扫描到“曾经写过”的最后一个数据页的位置，哪怕中间有大量空洞，此时就会扫描大量不必要的数据页，所以查询变慢。而这个”曾经“的最后数据页的位置有个专业的名词叫”高水位线“。

高水位线详解

高水位线(Hign-Water Mark，简称HWW) 是Oracle数据库数据文件或者段(segment) 使用空间和未使用空间之间的边界。
第一次插入数据时，Oracle会为其分配Segment和block

随着数据的插入，高水位线随之增长

数据被删除也无法降低高水位线

2、为什么会发生数据膨胀？

数据库为了性能，通常只标记“可以复用“，不主动回收已分配的磁盘空间。以下几大操作引起数据膨胀。
（1） delete操作：删除数据时，行被标记为删除，空间变成可以重用状态，但不会降低文件大小。即高水位不下降。
（2） update操作：行变长，原位置放不下，数据会被用到新位置，原空间留下碎片（页分裂）。即空间碎片化，膨胀严重。
（3）乱序insert：非自增主键或随机uuid插入时，经常发生页分裂，导致页填充率下降。大量页半满，空间虚高。
（4）索引维护：索引页同样分裂，碎片化，二级索引膨胀比主键索引严重。

3、影响

（1）磁盘空间浪费：原本几十m的有效数据可能占用几十G。
（2）全表扫变慢：扫描到高水位线，中间存在大量空洞数据。
（3）备份体积增大。
（4）缓冲池命中率下降：产生大量不必要的数据页污染内存池。

4、如何解决

重建表，重组碎片，让高水位线重新和有效数据对齐。那么问题来了？哪些表出现数据膨胀呢？

5、数据膨胀情况评估

目前在项目中的达梦数据库上自定义一个脚本，用来估算索引组织表数据膨胀情况。核心思路：（索引组织表大小权重-表数据量/每个数据页上的行数（获取理论上表占用多少个数据页））/索引组织表大小权重，求出来的膨胀占比。
（1）求每个数据页上的行数

select 'select round(lengthb(trxid))+round(lengthb(rowid))+round(avg('||replace(replace(wm_concat('round(nvl(lengthb("'||column_name||'"),0))'),',','+'),'+0',',0')||'))'  into v_sql from dba_tab_cols 
 where data_type not in ('CLOB','BLOB','TEXT') and table_name=V_table_name and owner=v_owner;

--计算平均行长
  v_sql:=v_sql||' from (select * from  "'||v_owner||'"."'||v_table_name||'"
   where rownum<=50000) AA;';
   EXECUTE IMMEDIATE v_sql into v_avg_len;
   --根据平均行长计算每页存的行数
   execute immediate 'select round(page/'||v_avg_len||',0) from dual;' into v_avg_cnt_perpage_len;

利用dba_tab_cols表拼接列，排除大字段，然后随机抽取5万行求其数据的平均行长，然后计算数据页上每页存多少行数据。最终根据表总行数/每页存多少行数据，就获得理论上表占用多少数据页。

--理论值大小
   select round(v_cnt/v_avg_cnt_perpage_len) into v_base_size from dual ;

（2）求这个表实际占用多少数据页

--获取表对应聚集索引
  select index_name into v_indexname from dba_indexes where table_name=v_table_name and owner=v_owner and index_type='CLUSTER';
 --获取index_used_pages大小
    select index_used_pages(v_owner,v_indexname) into v_index_size from dual ;

索引组织表实际上是个索引，所以获取该聚集索引，就能获取其大小。
理论值一般都会偏大，因为数据页并非全部用来填充数据，它还包含页头页尾。如下图

因此需要权重，在实际测试环境中获得的权重0.88，所以脚本采用该权重来衡量，减少误差。

脚本

create or replace function p_hwn_reocrd_page_by_index (v_owner varchar2(200),v_table_name varchar2(200))
return number as 
v_cnt bigint;
v_sql varchar2;
v_cnt2 bigint;
v_indexname varchar2(200);
v_index_size number(20,2);
v_base_size number(20,2);
v_avg_cnt_perpage_len bigint;
v_per number(20,2);
v_avg_len bigint;

begin
  execute immediate 'select count(1) from "'||v_owner||'"."'||v_table_name||'";' into v_cnt;
  if v_cnt>0 then
  --拼接计算平均行长的语句
  select 'select round(lengthb(trxid))+round(lengthb(rowid))+round(avg('||replace(replace(wm_concat('round(nvl(lengthb("'||column_name||'"),0))'),',','+'),'+0',',0')||'))'  into v_sql from dba_tab_cols 
  where data_type not in ('CLOB','BLOB','TEXT') and table_name=V_table_name and owner=v_owner;
 --获取表对应聚集索引
  select index_name into v_indexname from dba_indexes where table_name=v_table_name and owner=v_owner and index_type='CLUSTER';

 --获取index_used_pages大小
    select index_used_pages(v_owner,v_indexname) into v_index_size from dual ;
  
  --计算平均行长
  v_sql:=v_sql||' from (select * from  "'||v_owner||'"."'||v_table_name||'"
   where rownum<=50000) AA;';
   EXECUTE IMMEDIATE v_sql into v_avg_len;
   --根据平均行长计算每页存的行数
   execute immediate 'select round(page/'||v_avg_len||',0) from dual;' into v_avg_cnt_perpage_len;
   --理论值大小
   select round(v_cnt/v_avg_cnt_perpage_len) into v_base_size from dual ;
   --理论值-当下估算的值统计出来的占比
   select round((v_index_size*0.88-v_base_size)/v_index_size*0.88,2) into v_per from dual ;
   if v_per<=0 then
   v_per:=0;
   end if;
  else
   v_per:=0;
   end if;
  return v_per;
end;

--使用：
p_hwn_reocrd_page_by_index('用户名','表名')；

目前还封装了统计一个模式所有表的存储过程，如下：

CREATE TABLE "TAB_HWN_RECORD_PAGE_BY_INDEX"
(
"OWNER" VARCHAR2(200),
"TABLE_NAME" VARCHAR2(200),
"CNT" NUMBER,
"TAB_SIZE" NUMBER(8,2),
"HWN_PER" NUMBER(8,2),
"HWN_FLAG" number default 0,
"UPDATE_TIME" TIMESTAMP(6)
);



--执行存储过程
create or replace procedure pro_tab_hwn_record_BY_INDEX (v_owner varchar2(200))
as
v_time timestamp ;
v_cnt bigint;
v_size bigint;
v_per number(20,2);
v_min_rowid bigint;
v_max_rowid bigint;
v_page bigint;
v_pagesize bigint;
begin
select sysdate into v_time from dual;
select /*+OPTIMIZER_OR_NBEXP(2)*/count(1) into v_cnt  
from sysobjects where type$='SCHOBJ' AND SUBTYPE$ IN ('UTAB') and schid in (select id from sysobjects where name=v_owner)
and NAME not like '%BAK%' and NAME not like 'DMHS%' and NAME not like '%_HIS%' and name not like '%TEMP%' and name not like '%TMP%'
AND name not like 'BK%' 
 and pid='-1'
and INFO3 & 0X40 <=0 ;
execute immediate 'select /*+OPTIMIZER_OR_NBEXP(2)*/sf_get_real_rowid(max(rowid)),sf_get_real_rowid(min(rowid)) from sysobjects where 
type$=''SCHOBJ'' AND SUBTYPE$ IN (''UTAB'') and schid in (select id from sysobjects where name='''||v_owner||''')
and NAME not like ''%BAK%'' and NAME not like ''DMHS%'' and NAME not like ''%_HIS%'' and name not like ''%TEMP%'' and name not like ''%TMP%''
AND name not like ''BK%'' 
 and pid=''-1''
and INFO3 & 0X40 <=0 ;'into v_max_rowid,v_min_rowid;
v_page:=100000;
select ceil((v_max_rowid-v_min_rowid)/v_page) into v_pagesize from dual ;
if v_cnt>100000 then
for i in 1..v_pagesize loop
merge /*+OPTIMIZER_OR_NBEXP(2) NO_USE_CVT_VAR*/into TAB_HWN_RECORD_PAGE_BY_INDEX t using (
select v_owner as owner,name,table_rowcount(v_owner,NAME) as table_cnt,round((table_used_pages(v_owner,name)*page)/1024/1024/1024,2) table_size,v_time update_time 
from sysobjects where type$='SCHOBJ' AND SUBTYPE$ IN ('UTAB') and schid in (select id from sysobjects where name=v_owner)
and NAME not like '%BAK%' and NAME not like 'DMHS%' and NAME not like '%_HIS%' and name not like '%TEMP%' and name not like '%TMP%'
AND name not like 'BK%' 
and pid='-1'
and INFO3 & 0X40 <=0 and rowid>=(i-1)*v_page+v_min_rowid and rowid<=(i+1)*v_page+v_min_rowid
) a
on t.owner=a.owner and t.table_name=a.name
when matched then update set t.cnt=a.table_cnt,t.tab_size=a.table_size,update_time=a.update_time
when not matched then insert (t.owner,t.table_name,t.cnt,t.tab_size,t.update_time)
values(a.owner,a.name,a.table_cnt,a.table_size,a.update_time);
commit;
end loop;
else
merge /*+OPTIMIZER_OR_NBEXP(2) NO_USE_CVT_VAR*/into TAB_HWN_RECORD_PAGE_BY_INDEX t using (
select v_owner as owner,name,table_rowcount(v_owner,NAME) as table_cnt,round((table_used_pages(v_owner,name)*page)/1024/1024/1024,2) table_size,v_time update_time 
from sysobjects where type$='SCHOBJ' AND SUBTYPE$ IN ('UTAB') and schid in (select id from sysobjects where name=v_owner)
and NAME not like '%BAK%' and NAME not like 'DMHS%' and NAME not like '%_HIS%' and name not like '%TEMP%' and name not like '%TMP%'
AND name not like 'BK%' 
and pid='-1'
and INFO3 & 0X40 <=0 
) a
on t.owner=a.owner and t.table_name=a.name
when matched then update set t.cnt=a.table_cnt,t.tab_size=a.table_size,update_time=a.update_time
when not matched then insert (t.owner,t.table_name,t.cnt,t.tab_size,t.update_time)
values(a.owner,a.name,a.table_cnt,a.table_size,a.update_time);
commit;
end if;
select count(1) into v_cnt from TAB_HWN_RECORD_PAGE_BY_INDEX where owner=v_owner and update_time=v_time;
v_size:=0;
--update膨胀情况
-- 计算百万数据的表数据膨胀情况，且table_size>10G
if v_cnt>0 then 
for i in (select * from TAB_HWN_RECORD_PAGE_BY_INDEX where owner=v_owner and update_time=v_time and CNT>=1000000 and tab_size>10)loop
select  p_hwn_reocrd_page_by_index(i.owner,i.table_name) into v_per from dual;
if v_per>0.5 then
update TAB_HWN_RECORD_PAGE_BY_INDEX a set a.HWN_PER=v_per,a.hwn_flag=1 
where a.owner=i.owner and a.table_name=i.table_name and a.update_time=v_time;
else
update TAB_HWN_RECORD_PAGE_BY_INDEX a set a.HWN_PER=v_per 
where a.owner=i.owner and a.table_name=i.table_name and a.update_time=v_time;
end if;
v_size:=v_size+1;
if mod(v_size,1000)=0 then
commit;
end if;
commit;
end loop;
end if;
end;

call pro_tab_hwn_record_BY_INDEX('用户‘);

--统计的结果查询
select * from TAB_HWN_RECORD_PAGE_BY_INDEX where hwn_flag=1

该脚本统计的是大于10g以上表的膨胀情况，此脚本的规则是膨胀率高达50%以上需要做重建表处理（hwn_flag=1），该规则可视实际情况调整。