Oracle 压缩LOB数据会增加表大小

askTom 2018-09-10

1070

问题描述

你好，

我认为这是一个更具概念性的问题:

在我们的核心生产数据库系统中，我们有一个相当大的表，重量为15，5tb。其中，大约14.4tb是存储在LOB列中的XML数据。

我们决定使用高级lob压缩和联机表重新定义来减小数据库的大小。

我们已经在QA enviroment中运行了一些测试，该测试的副本较小。

在质量检查中，尺寸为:

表段172536889344B 160，69gb
Lob索引6553600B 0,01gb
Lob段2284452839424B 2127，56gb

在使用中等lob压缩重新定义表之后，我们得到了以下结果:

表段511839305728B 476，69BG
Lob索引6553600B 0,01gb
Lob段474550894592B 0,21gb

如您所见，lob段被压缩为原始大小的20，8%，但另一方面，表段几乎增加了其大小的3倍。

我们运行另一个测试应用lob压缩和重复数据删除和高级表压缩，结果甚至更糟，表段不仅没有减小它的大小，它增加甚至比没有表压缩 (501gb) 更多。

最后，我们运行了传统测试，结果是相同的: LOB减少，表增加了它的大小。

我们无法在任何地方找到此行为记录，这就是为什么我们在这里:

这是在所有场景中预期的吗？
表大小增加的原因可能是什么，
最后，如果表压缩不起作用，我们是否应该假设无法避免它 (增加)？

提前感谢您的意见

问候，

杆。

编辑:

这是原始表dll:

CREATE TABLE SC.TAB
(
  C01                    CHAR(36 CHAR),
  C02                    CHAR(36 CHAR),
  C03                    CHAR(2 CHAR),
  C04                    CHAR(2 CHAR),
  C05                    TIMESTAMP(6),
  C06                    TIMESTAMP(6),
  C07           TIMESTAMP(6),
  C08              VARCHAR2(150 CHAR),
  C09               CHAR(36 CHAR),
  C10                    CHAR(36 CHAR),
  C11                    CLOB,
  C12                    NUMBER(10),
  C13                    CHAR(2 CHAR),
  C14                    VARCHAR2(2 CHAR),
  C15                    TIMESTAMP(6),
  C16                    TIMESTAMP(6),
  C17                    TIMESTAMP(6),
  C18                    CHAR(36 CHAR),
  C19                    VARCHAR2(2 CHAR),
  C20                    CHAR(36 CHAR)
)
LOB (C11) STORE AS SECUREFILE (
  TABLESPACE  SC_DATA
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  NOCACHE
  LOGGING)
NOCOMPRESS 
TABLESPACE SC_DATA
PCTUSED    0
PCTFREE    40
INITRANS   200
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
PARTITION BY HASH (C01)
(  
  PARTITION N01
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N02
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N03
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N04
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N05
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N06
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N07
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N08
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N09
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N10
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA)  

)
NOCACHE
MONITORING;
复制

这是我们用来压缩lob段的步骤:

1.创建启用lob压缩的aux表:

CREATE TABLE SC.TAB_INT
(
  C01                    CHAR(36 CHAR),
  C02                    CHAR(36 CHAR),
  C03                    CHAR(2 CHAR),
  C04                    CHAR(2 CHAR),
  C05                    TIMESTAMP(6),
  C06                    TIMESTAMP(6),
  C07           TIMESTAMP(6),
  C08              VARCHAR2(150 CHAR),
  C09               CHAR(36 CHAR),
  C10                    CHAR(36 CHAR),
  C11                    CLOB,
  C12                    NUMBER(10),
  C13                    CHAR(2 CHAR),
  C14                    VARCHAR2(2 CHAR),
  C15                    TIMESTAMP(6),
  C16                    TIMESTAMP(6),
  C17                    TIMESTAMP(6),
  C18                    CHAR(36 CHAR),
  C19                    VARCHAR2(2 CHAR),
  C20                    CHAR(36 CHAR)
)
LOB (C11) STORE AS SECUREFILE (
  TABLESPACE  SC_DATA
  ENABLE      STORAGE IN ROW
  CHUNK          8192
  COMPRESS  
  NOCACHE
  LOGGING)
NOCOMPRESS 
TABLESPACE SC_DATA
PCTUSED    0
PCTFREE    40
INITRANS   200
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
PARTITION BY HASH (C01)
(  
  PARTITION N01
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N02
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N03
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N04
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N05
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N06
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N07
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N08
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N09
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA),  
  PARTITION N10
    TABLESPACE SC_DATA
    LOB (C11) STORE AS SECUREFILE (TABLESPACE SC_DATA)
)
NOCACHE
MONITORING;

复制

2.开始在线重新定义:

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(uname => 'SC',orig_table => 'TAB',int_table => 'TAB_INT',col_mapping => NULL,options_flag => 2);
END;
复制

3.复制表对象

SET SERVEROUTPUT ON
DECLARE
  err_cnt PLS_INTEGER;
BEGIN
 
 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (uname => 'SC',
 orig_table => 'TAB',
 int_table => 'TAB_INT',
 copy_indexes => dbms_redefinition.cons_orig_params,
 copy_triggers => TRUE,
 copy_constraints => TRUE,
 copy_privileges => TRUE,
 ignore_errors => TRUE,
 num_errors => err_cnt);
 
DBMS_OUTPUT.PUT_LINE ('Errors Count := ' || TO_CHAR (err_cnt));
 
END;
复制

4.同步表

BEGIN
DBMS_REDEFINITION.sync_interim_table('SC', 'TAB', 'TAB_INT'); 
END;复制

5.完成在线表重新定义:

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE (uname => 'SC', orig_table => 'TAB', int_table => 'TAB_INT');
END;
复制

这些步骤给出了我们前面提到的结果:

以前
表段172536889344B 160，69gb
Lob索引6553600B 0,01gb
Lob段2284452839424B 2127，56gb

之后
表段511839305728B 476，69BG
Lob索引6553600B 0,01gb
Lob段474550894592B 0,21gb

谢谢你的评论

专家解答

它最有可能是您在行中的启用存储。如果lob低于一定大小 (约4k)，则它将与表行内联存储，否则将存储在lob段中。如果压缩使那些曾经在该阈值下过大的lob，则它们将成为表的一部分，而不是lob段，例如

SQL> CREATE TABLE t1
  2  (
  3    x int,
  4    C11                    CLOB
  5  )
  6  LOB (C11) STORE AS SECUREFILE (
  7    ENABLE      STORAGE IN ROW
  8    CHUNK          8192
  9    NOCOMPRESS
 10    NOCACHE
 11    LOGGING);

Table created.

SQL>
SQL> CREATE TABLE t2
  2  (
  3    x int,
  4    C11                    CLOB
  5  )
  6  LOB (C11) STORE AS SECUREFILE (
  7    ENABLE      STORAGE IN ROW
  8    CHUNK          8192
  9    COMPRESS
 10    NOCACHE
 11    LOGGING);

Table created.

SQL>
SQL> insert into t1
  2  select rownum, rpad(chr(65+mod(rownum,20)),6000,65+mod(rownum,20))
  3  from   dual
  4  connect by level <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, bytes from user_segments
  2  where segment_name = 'T1' or
  3  segment_name = ( select segment_name from user_lobs
  4                   where table_name = 'T1' );

SEGMENT_NAME                        BYTES
------------------------------ ----------
T1                                 655360
SYS_LOB0000265363C00002$$       184745984

SQL>
SQL>
SQL> insert into t2
  2  select * from t1;

10000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, bytes from user_segments
  2  where segment_name = 'T2' or
  3  segment_name = ( select segment_name from user_lobs
  4                   where table_name = 'T2' );

SEGMENT_NAME                        BYTES
------------------------------ ----------
T2                                2097152
SYS_LOB0000265366C00002$$          131072
复制

如果我在 “禁用存储行” 中重复此操作，您会看到表没有太大变化，但是由于压缩，lob大小会缩小

SQL> CREATE TABLE t1
  2  (
  3    x int,
  4    C11                    CLOB
  5  )
  6  LOB (C11) STORE AS SECUREFILE (
  7    DISABLE      STORAGE IN ROW
  8    CHUNK          8192
  9    NOCOMPRESS
 10    NOCACHE
 11    LOGGING);

Table created.

SQL>
SQL> CREATE TABLE t2
  2  (
  3    x int,
  4    C11                    CLOB
  5  )
  6  LOB (C11) STORE AS SECUREFILE (
  7    DISABLE      STORAGE IN ROW
  8    CHUNK          8192
  9    COMPRESS
 10    NOCACHE
 11    LOGGING);

Table created.

SQL>
SQL> insert into t1
  2  select rownum, rpad(chr(65+mod(rownum,20)),6000,65+mod(rownum,20))
  3  from   dual
  4  connect by level <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, bytes from user_segments
  2  where segment_name = 'T1' or
  3  segment_name = ( select segment_name from user_lobs
  4                   where table_name = 'T1' );

SEGMENT_NAME                        BYTES
------------------------------ ----------
T1                                 655360
SYS_LOB0000265370C00002$$       184745984

2 rows selected.

SQL>
SQL>
SQL> insert into t2
  2  select * from t1;

10000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, bytes from user_segments
  2  where segment_name = 'T2' or
  3  segment_name = ( select segment_name from user_lobs
  4                   where table_name = 'T2' );

SEGMENT_NAME                        BYTES
------------------------------ ----------
T2                                 720896
SYS_LOB0000265373C00002$$        92471296

2 rows selected.

复制

oracle asktom

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 压缩LOB数据会增加表大小

问题描述

专家解答

评论

相关阅读