利用securefile技术进行空间压缩

1. 概述

某系统的二级历史库是架构在x86一体机上,上线近1年时间,转储的数据量比较多,
已经达到近21T,一体机上空间已使用了75%。
一体机架构的数据库存储扩容需要增加新的存储结点,不像传统存储这么方便,
所以需要控制历史数据增长量。
经过分析,4g历史库空间主要集中在一些CLOB字段的分区表上,这些分区表是按月分区,CLOB字段使用的是basicfile。
经过测试,CLOB字段如何使用securefile和压缩去重模式,可以降低数据量到原来的1/5左右。

2. 压缩测试过程

以下是对某张表进行的压缩测试,这些历史表是按时间分区,并且历史分区上的数据基本不变化,通过采用分区交换技术
把历史分区的clob字段全部转换成securefile压缩模式,并不影响当月分区的二级转储。

2.1 表结构

SQL> desc his_crm4g.DEP_PRV_LOC_INTF_LOG_INFO_ZSD
Name      Type         Nullable Default Comments                                        
--------- ------------ -------- ------- ----------------------------------------------- 
ID        VARCHAR2(16)                  
REQ_PARAM CLOB         Y                
RES_PARAM CLOB         Y                
CREATE_DT DATE                  SYSDATE 

2.2 转换前表大小

set linesize 160 pagesize 99
column owner format a20
column segment_name format a30
column segment_type format a30
select owner, segment_name, segment_type, sum(bytes) / 1024 / 1024
  from dba_segments
 where segment_name in
       (select segment_name
          from dba_lobs
         where owner = 'HIS_CRM4G'
           and table_name = 'DEP_PRV_LOC_INTF_LOG_INFO_ZSD')
 group by owner, segment_name, segment_type
union
select owner, segment_name, segment_type, sum(bytes) / 1024 / 1024
  from dba_segments
 where segment_name in
       (select index_name
          from dba_lobs
         where owner = 'HIS_CRM4G'
           and table_name = 'DEP_PRV_LOC_INTF_LOG_INFO_ZSD')
 group by owner, segment_name, segment_type
union
select owner, segment_name, segment_type, sum(bytes) / 1024 / 1024
  from dba_segments
 where segment_name in ('DEP_PRV_LOC_INTF_LOG_INFO_ZSD')
 group by owner, segment_name, segment_type;

压缩前,表大小为1424g

OWNER       SEGMENT_NAME                   SEGMENT_TYPE     SUM(BYTES)/1024/1024
----------- ------------------------------ ---------------- --------------------
HIS_CRM4G   DEP_PRV_LOC_INTF_LOG_INFO_ZSD  TABLE PARTITION                 87424
HIS_CRM4G   SYS_IL0000131849C00002$$       INDEX PARTITION               19.3125
HIS_CRM4G   SYS_IL0000131849C00003$$       INDEX PARTITION               19.3125
HIS_CRM4G   SYS_LOB0000131849C00002$$      LOB PARTITION              1369958.44
HIS_CRM4G   SYS_LOB0000131849C00003$$      LOB PARTITION                512.4375

2.3 检查需要转换的分区

SELECT P.table_owner, P.table_name, P.PARTITION_NAME, P.tablespace_name, 
       P.COLUMN_NAME, p.lob_name, p.lob_partition_name, S.segment_subtype, S.BYTES/1024/1024
  FROM DBA_LOB_PARTITIONS P, DBA_SEGMENTS S 
 WHERE P.table_owner = 'HIS_CRM4G' AND P.table_name = 'DEP_PRV_LOC_INTF_LOG_INFO_ZSD'
   and p.table_owner = s.owner
   and p.lob_name = s.segment_name
   AND P.lob_partition_name = S.partition_name
  order by partition_name, column_name, lob_name;

2.4 转换脚本

存储过程:

create or replace procedure recreate_his_crm4g_dpliliz_int is
begin
  execute immediate 'drop table HIS_CRM4G.DPLILIZ_INT';
  execute immediate 'create table HIS_CRM4G.DPLILIZ_INT
(
  id        VARCHAR2(16) not null,
  req_param CLOB,
  res_param CLOB,
  create_dt DATE default SYSDATE not null
)
LOB (req_param) STORE AS SECUREFILE  (
  DEDUPLICATE
  COMPRESS HIGH
  tablespace TBS_CRM4G_02
)
LOB (res_param) STORE AS SECUREFILE  (
  DEDUPLICATE
  COMPRESS HIGH
  tablespace TBS_CRM4G_02
)';
  execute immediate 'create index his_crm4g.idx_dpliliz_int on his_crm4g.dpliliz_int(id) tablespace TBS_CRM4G_02';
end recreate_his_crm4g_dpliliz_int;
/

执行脚本:

set timing on
set serveroutput on
declare
  v_sql varchar2(500);
  v_src_tab constant varchar2(30) := 'DEP_PRV_LOC_INTF_LOG_INFO_ZSD';
  v_int_tab constant varchar2(30) := 'DPLILIZ_INT'; 
begin
  for red in (select p.partition_name
                from dba_tab_partitions p
               where P.table_owner = 'HIS_CRM4G'
                 and P.table_name = 'DEP_PRV_LOC_INTF_LOG_INFO_ZSD'
                 and p.partition_name >= 'PT_201503'
                 and p.partition_name <  'PT_201604'
               order by p.partition_name) loop
    recreate_his_crm4g_dpliliz_int;
    v_sql := 'insert into HIS_CRM4G.' || v_int_tab || ' select * from HIS_CRM4G.' || v_src_tab || ' partition(' || red.partition_name || ')';
    execute immediate v_sql;
    execute immediate 'commit';
    v_sql := 'alter table HIS_CRM4G.' || v_src_tab || ' exchange partition ' || red.partition_name || ' with table HIS_CRM4G.' || v_int_tab || ' including indexes without validation';
    execute immediate v_sql;
  end loop;
exception
  when others then
     DBMS_OUTPUT.PUT_LINE(sqlcode || '---' || sqlerrm);
end;

2.5 收益

压缩后,当前表大小为263g。

OWNER                SEGMENT_NAME                   SEGMENT_TYPE                   SUM(BYTES)/1024/1024
-------------------- ------------------------------ ------------------------------ --------------------
HIS_CRM4G            DEP_PRV_LOC_INTF_LOG_INFO_ZSD  TABLE PARTITION                               79888
HIS_CRM4G            SYS_IL0000131849C00002$$       INDEX PARTITION                            2921.625
HIS_CRM4G            SYS_IL0000131849C00003$$       INDEX PARTITION                             19.3125
HIS_CRM4G            SYS_LOB0000131849C00002$$      LOB PARTITION                            266970.625
HIS_CRM4G            SYS_LOB0000131849C00003$$      LOB PARTITION                               127.625

但当月数据还未压缩,当月差不多是50g的分区,按1比5,差不多可以压缩40g左右,整个表的空间估计可以压缩到220g左右

SQL> SELECT P.table_owner, P.table_name, P.PARTITION_NAME, P.tablespace_name,
  2         P.COLUMN_NAME, p.lob_name, p.lob_partition_name, S.segment_subtype, S.BYTES/1024/1024
  3    FROM DBA_LOB_PARTITIONS P, DBA_SEGMENTS S
  4   WHERE P.table_owner = 'HIS_CRM4G' AND P.table_name = 'DEP_PRV_LOC_INTF_LOG_INFO_ZSD'
  5     and p.table_owner = s.owner
  6     and p.lob_name = s.segment_name
  7     AND P.lob_partition_name = S.partition_name
  8     and s.segment_subtype <> 'SECUREFILE'
  9    order by partition_name, column_name, lob_name;
TABLE_OWNER  TABLE_NAME                     PARTITION_NAME TABLESPACE_NAME COLUMN_NAME LOB_NAME                  LOB_PARTITION_NAME SEGMENT_SUBTYPE S.BYTES/1024/1024
------------ ------------------------------ -------------- --------------- ----------- ------------------------- ------------------ --------------- -----------------
HIS_CRM4G    DEP_PRV_LOC_INTF_LOG_INFO_ZSD  PT_201604      TBS_CRM4G_02    REQ_PARAM   SYS_LOB0000131849C00002$$ SYS_LOB_P36549     ASSM                        50881
HIS_CRM4G    DEP_PRV_LOC_INTF_LOG_INFO_ZSD  PT_201604      TBS_CRM4G_02    RES_PARAM   SYS_LOB0000131849C00003$$ SYS_LOB_P36781     ASSM                           27

整个压缩结果是:从1424g压缩到220g。
由于二级库中存在多个CLOB字段的表,预计可以压缩节省出10T空间左右,空间优化效果非常明显。

关于紫砂壶

感悟技术人生
此条目发表在Oracle基础知识分类目录,贴了标签。将固定链接加入收藏夹。