Oracle 10升级19C 如何提升LOB性能【SECUREFILE及BASICFILE属性对比及验证测试】

原创尚雷 2022-12-07

2217

一、概述

因生产业务需要，计划将生产一套数据量较大的核心Oracle 10g库升级到 19C，这套库相对于之前运维的数据库最大不同就是整个数据库含有较多LOB表，通过查询，这些LOB表的总体量占到整个数据库体量的一半多，所有的LOB表导出占用将近8T，而且这些LOB表有很多是业务很频繁的核心表，升级19C后，能否提升数据库性能，很大一方面取决于能否提升LOB性能。为此花了些时间去了解LOB类型的属性，并基于此做了相关的验证测试。

限于技术能力有限，难免有纰漏，还望对此熟悉的能给予指正，非常感谢。

二、理论介绍

Oracle官网文档对SECUREFILE、BASICFILE的描述是这样的：

The SecureFiles functionality is a complete redesign of the implementation of large object (LOB) storage in Oracle 11g. The original LOB storage, now known as BASICFILE,is still the default storage method, but the SECUREFILE keyword enables the new storage method, which allows encryption for security and space savings using compression and deduplication.

中文翻译过来意思就是：

SecureFiles功能是对Oracle 11g中大对象（LOB）存储实现的完全重新设计。最初的LOB存储（现在称为BASICFILE）仍然是默认的存储方法，但SECUREFILE关键字启用了新的存储方法，它允许使用压缩和重复数据消除进行加密以实现安全性和节省空间。

从Oracle 11g 开始，引入了SecureFiles 新的LOB 架构。11G 之前叫 BasicFiles。在Oracle 11G 中如果不特别指定，默认是会创建成BasicFiles LOB。但是在Oracle 12c之后，LOB 列在ASSM 管理的表空间。

默认都会创建成SecureFiles。

相较于BasicFiles，SecureFiles所带来的优势如下：

Oracle官方文档已经说明，BasicFiles在后续版本将不能使用。

CHUNK,FREELISTS,FREELIST GROUPS等参数不能在BasicFiles使用。

SecureFiles 支持加密,压缩和去重.

Oracle建议使用SecureFiles 而不是BasicFiles。

db_securefile这个参数是11G开始引入的，10G没有该参数，11G和19C默认该参数属性值是 PERMITTED，该参数可以动态修改，可修改的值可以为：ALWAYS、FORCE、PERMITTED、PREFERRED、NEVER、IGNORE, 这几个值所对应的含义分别如下：

ALWAYS：尝试将ASSM表空间上的所有LOB 创建为SecureFile LOB，但是仅可将自动段空间管理(ASSM) 表空间外的任何LOB 创建为BasicFile LOB;

FORCE：强制将所有LOB 创建为SecureFileLOB。

PERMITTED：允许创建SecureFiles（默认值）。

NEVER：禁止创建SecureFiles。

IGNORE：禁止创建SecureFiles，并忽略使用SecureFiles 选项强制创建BasicFiles 而导致的任何错误。

SQL> ALTER SYSTEM SET db_securefile = 'FORCE';
 
System altered.
 
SQL>  ALTER SYSTEM SET db_securefile = 'PERMITTED';
 
System altered.
 
SQL>
复制

三、验证测试

本次通过多个实验来对比SecureFile及BasicFile。

3.1 动态修改db_securefile测试

SQL> show parameter db_securefile
db_securefile                        string      PERMITTED
 
CREATE TABLE BASICFILE_TAB (
  id         NUMBER,
  clob_data  CLOB
)
LOB(clob_data) STORE AS BASICFILE;
 
INSERT INTO BASICFILE_TAB VALUES (1, 'My CLOB data');
COMMIT;
 
CREATE TABLE SECUREFILE_TAB (
  id         NUMBER,
  clob_data  CLOB
)
LOB(clob_data) STORE AS SECUREFILE;
 
INSERT INTO SECUREFILE_TAB VALUES (1, 'My CLOB data');
COMMIT;
 
11:49:18 SQL> select dbms_metadata.get_ddl('TABLE','BASICFILE_TAB') FROM DUAL;
 
  CREATE TABLE "ORCL"."BASICFILE_TAB"
   (    "ID" NUMBER,
        "CLOB_DATA" CLOB
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "T_ORCL"
 LOB ("CLOB_DATA") STORE AS BASICFILE (
  TABLESPACE "T_ORCL" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
 
11:49:46 SQL> select dbms_metadata.get_ddl('TABLE','SECUREFILE_TAB') from dual;
 
  CREATE TABLE "ORCL"."SECUREFILE_TAB"
   (    "ID" NUMBER,
        "CLOB_DATA" CLOB
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULTCELL_FLASH_CACHE DEFAULT)
  TABLESPACE "T_ORCL"
 LOB ("CLOB_DATA") STORE AS SECUREFILE (
  TABLESPACE "T_ORCL" ENABLE STORAGE IN ROW CHUNK
8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
复制

db_securefile在默认为PERMITTED时，在19C上创建相应LOB表时，如果建表语句中默认指定了BASICFILE，创建的LOB表存储类型就仍会是BASICFILE属性。

接下来，我们再测试将db_securefile修改为FORCE,其中一张表建表语句中仍指定BASICFILE属性，看看会有什么结果。

SQL> show parameter db_securefile
db_securefile                        string      PERMITTED
SQL> ALTER SYSTEM SET db_securefile = 'FORCE';
System altered.
SQL> show parameter db_securefile
db_securefile                        string      FORCE
 
CREATE TABLE BASICFILE_TAB_NEW (
  id         NUMBER,
  clob_data  CLOB
)
LOB(clob_data) STORE AS BASICFILE;
 
INSERT INTO BASICFILE_TAB_NEW VALUES (1, 'My CLOB data');
COMMIT;
 
CREATE TABLE SECUREFILE_TAB_NEW (
  id         NUMBER,
  clob_data  CLOB
)
LOB(clob_data) STORE AS SECUREFILE;
 
INSERT INTO SECUREFILE_TAB_NEW VALUES (1, 'My CLOB data');
COMMIT;
 
SQL> select dbms_metadata.get_ddl('TABLE','BASICFILE_TAB_NEW') from dual;
 
  CREATE TABLE "ORCL"."BASICFILE_TAB_NEW"
   (    "ID" NUMBER,
        "CLOB_DATA" CLOB
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "T_ORCL"
 LOB ("CLOB_DATA") STORE AS SECUREFILE (
  TABLESPACE "T_ORCL" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
 
 
SQL> select dbms_metadata.get_ddl('TABLE','SECUREFILE_TAB_NEW') from dual;
 
  CREATE TABLE "ORCL"."SECUREFILE_TAB_NEW"
   (    "ID" NUMBER,
        "CLOB_DATA" CLOB
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
 DEFAULT)
  TABLESPACE "T_ORCL"
 LOB ("CLOB_DATA") STORE AS SECUREFILE (
  TABLESPACE "T_ORCL" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
复制

可以看到当db_securefile修改为FORCE，哪怕建表语句中指定BASICFILE，创建后的表也会将其强制转成SECUREFILE。

3.2 LOB字段消重测试

测试19C SECUREFILE 关于LOB字段的消重压缩特性。

SQL> show parameter db_sec
db_securefile                        string      PERMITTED
SQL> CREATE TABLE keep_duplicates_tab (
  2    id         NUMBER,
  3    clob_data  CLOB
  4  )
  5  LOB(clob_data) STORE AS SECUREFILE keepdup_lob(
  6    KEEP_DUPLICATES
  7  );
 
Table created.
 
SQL> CREATE TABLE deduplicate_tab (
  2    id         NUMBER,
  3    clob_data  CLOB
  4  )
  5  LOB(clob_data) STORE AS SECUREFILE dedup_lob (
  6    DEDUPLICATE
  7  );
 
Table created.
 
SQL> DECLARE
  2    l_clob CLOB := RPAD('X', 10000, 'X');
  3  BEGIN
  4    FOR i IN 1 .. 1000 LOOP
  5      INSERT INTO keep_duplicates_tab VALUES (i, l_clob);
  6    END LOOP;
  7    COMMIT;
  8 
  9    FOR i IN 1 .. 1000 LOOP
 10      INSERT INTO deduplicate_tab VALUES (i, l_clob);
 11    END LOOP;
 12    COMMIT;
 13  END;
 14  /
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'keep_duplicates_tab');
 
PL/SQL procedure successfully completed.
 
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'deduplicate_tab');
 
 
PL/SQL procedure successfully completed.
 
SQL> SQL> COLUMN segment_name FORMAT A30
SQL> SELECT segment_name, bytes
  2  FROM   user_segments
  3  WHERE  segment_name IN ('KEEPDUP_LOB', 'DEDUP_LOB');
DEDUP_LOB                         1245184
KEEPDUP_LOB                      33685504
 
SQL> ALTER TABLE deduplicate_tab MODIFY LOB(clob_data) (
  2    KEEP_DUPLICATES
  3  );
 
Table altered.
 
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'deduplicate_tab');
 
PL/SQL procedure successfully completed.
 
SQL> COLUMN segment_name FORMAT A30
SQL> SELECT segment_name, bytes
  2  FROM   user_segments
  3  WHERE  segment_name IN ('KEEPDUP_LOB', 'DEDUP_LOB');
DEDUP_LOB                        25362432
KEEPDUP_LOB                      33685504
复制

通过上述实验，可以看到含有LOB字段表在使用了KEEP_DUPLICATES相较于DUPLICATE，没有去重，数据占用更大。

动态修改LOB字段DEDUPLICATE类型测试

SQL> CREATE TABLE TB_1 (ID NUMBER, DOC CLOB ENCRYPT)
  2  LOB(DOC) STORE AS SECUREFILE;
 
Table created.
 
SQL> select dbms_metadata.get_ddl('TABLE','TB_1') from dual;
 
  CREATE TABLE "ORCL"."TB_1"
   (    "ID" NUMBER,
        "DOC" CLOB ENCRYPT USING 'AES192' 'SHA-1'
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  NOCOMPRESS LOGGING
  TABLESPACE "T_ORCL"
  LOB ("DOC") STORE AS SECUREFILE (
  TABLESPACE "T_ORCL" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  ENCRYPT USING 'AES192' 'SHA-1' NOCOMPRESS  KEEP_DUPLICATES )
 
 
SQL> ALTER TABLE TB_1 MODIFY LOB(DOC)(DEDUPLICATE LOB);
 
Table altered.
 
SQL> select dbms_metadata.get_ddl('TABLE','TB_1') from dual;
 
  CREATE TABLE "ORCL"."TB_1"
   (    "ID" NUMBER,
        "DOC" CLOB ENCRYPT USING 'AES192' 'SHA-1'
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  NOCOMPRESS LOGGING
  TABLESPACE "T_ORCL"
  LOB ("DOC") STORE AS SECUREFILE (
  TABLESPACE "T_ORCL" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  ENCRYPT USING 'AES192' 'SHA-1' NOCOMPRESS  DEDUPLICATE LOB )
 
 
SQL> ALTER TABLE TB_1 MODIFY LOB(DOC)(KEEP_DUPLICATES);
 
Table altered.
 
SQL> select dbms_metadata.get_ddl('TABLE','TB_1') from dual;
 
  CREATE TABLE "ORCL"."TB_1"
   (    "ID" NUMBER,
        "DOC" CLOB ENCRYPT USING 'AES192' 'SHA-1'
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  NOCOMPRESS LOGGING
  TABLESPACE "T_ORCL"
  LOB ("DOC") STORE AS SECUREFILE (
  TABLESPACE "T_ORCL" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  ENCRYPT USING 'AES192' 'SHA-1' NOCOMPRESS  KEEP_DUPLICATES )
复制

通过上述测试可以发现，19C对于创建的LOB表默认采用KEEP_DUPLICATES特性，并且可以动态修改LOB字段的DUPLICATE属性值。

3.3 LOB字段数据压缩测试

Oracle官方对于LOB字段的压缩是如此描述的：

The COMPRESS option of SecureFiles enables compression of LOB contents at table or partition level. The level of compression is indicated using the optional MEDIUM and HIGH keywords. If no compression level is specified, MEDIUM is used. There is an overhead associated with compressing the LOB contents, so using a high level of compression may be counterproductive to system performance. The compression in SecureFiles does not affect table compression, and vice versa. The example below compares the space usage of a regular and a compressed SecureFile LOB.

翻译过来就是：
SecureFiles的COMPRESS选项支持在表或分区级别压缩LOB内容。压缩级别使用可选的中高关键字表示。如果未指定压缩级别，则使用介质。压缩LOB内容会带来开销，因此使用高级别的压缩可能会对系统性能产生负面影响。SecureFiles中的压缩不会影响表压缩，反之亦然。

CREATE TABLE NOCOMPRESS_TAB (
  id         NUMBER,
  clob_data  CLOB
)
LOB(clob_data) STORE AS SECUREFILE nocompress_lob(
  NOCOMPRESS
);
 
CREATE TABLE COMPRESS_TAB (
  id         NUMBER,
  clob_data  CLOB
)
LOB(clob_data) STORE AS SECUREFILE compress_lob (
  COMPRESS HIGH
);
 
DECLARE
  l_clob CLOB := RPAD('X', 10000, 'X');
BEGIN
  FOR i IN 1 .. 1000 LOOP
    INSERT INTO NOCOMPRESS_TAB VALUES (i, l_clob);
  END LOOP;
  COMMIT;
 
  FOR i IN 1 .. 1000 LOOP
    INSERT INTO COMPRESS_TAB VALUES (i, l_clob);
  END LOOP;
  COMMIT;
END;
/
 
EXEC DBMS_STATS.gather_table_stats(USER, 'NOCOMPRESS_TAB');
EXEC DBMS_STATS.gather_table_stats(USER, 'COMPRESS_TAB');
 
COLUMN segment_name FORMAT A30
SELECT segment_name, bytes
FROM   user_segments
WHERE  segment_name IN ('COMPRESS_LOB', 'NOCOMPRESS_LOB');
 
SEGMENT_NAME                        BYTES
------------------------------ ----------
COMPRESS_LOB                       131072
NOCOMPRESS_LOB                   33751040
 
SQL> ALTER TABLE COMPRESS_TAB MODIFY LOB(clob_data) (
  2    NOCOMPRESS
  3  );
 
Table altered.
 
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'COMPRESS_TAB');
 
 
PL/SQL procedure successfully completed.
 
SQL> SQL> COLUMN segment_name FORMAT A30
SQL> SELECT segment_name, bytes
  2  FROM   user_segments
  3  WHERE  segment_name IN ('COMPRESS_LOB', 'NOCOMPRESS_LOB');
COMPRESS_LOB                     25296896
NOCOMPRESS_LOB                   33751040
复制