Oracle 11.2.0.4 数据泵expdp导出含CLOB字段(basicfile)的表超级慢的问题

简介

在 11.2.0.4 上 expdp导出含有CLOB字段的大表时会非常慢，而且可能会发生ora-01555错误。该错误的解决办法见：https://www.xmmup.com/oracleshujubengexpdpdaochubaoora-01555heora-22924.html#ke_neng_yuan_yin_er ，但是导出仍然会很慢，解决办法只能是重建表，然后修改存储属性STORE AS securefile，再进行导出才会解决慢的问题。

Oracle 11g专门针对LOB字段的新特性securefile，这是在11g推出的新的LOB存储模式，之前的版本都是以basicfile方式存储LOB字段，securefile于basicfile相比，对LOB的读取和写入操作的效率均有明显提高，但却需要占用更多的磁盘空间，但也多的有限，基本可以忽略不计。在11g版本，默认的LOB存储模式还是basicfile，如果使用securefile，需要在建表的时候指定或者数据库securefile相关的参数才可以，个人建议LOB都以securefile存储。

启用SecureFiles 存储

使用DB_SECUREFILE 初始化参数，数据库管理员(DBA) 可确定SecureFiles 的使用情况，其中有效值为：
• ALWAYS ：尝试将所有LOB 创建为SecureFile LOB，但是仅可将自动段空间管理(ASSM) 表空间外的任何 LOB 创建为BasicFile LOB
• FORCE：强制将所有LOB 创建为SecureFile LOB
• PERMITTED：允许创建SecureFiles （默认值）
• NEVER：禁止创建SecureFiles
• IGNORE ：禁止创建SecureFiles ，并忽略使用SecureFiles 选项强制创建BasicFiles 而导致的任何错误
如果指定了NEVER，则任何指定为SecureFiles 的LOB 均被创建为BasicFiles。如果对BasicFiles 使用任何SecureFiles 特定的存储选项和功能（如压缩、加密和取消重复），则会导致异常错误。将对任何未指定的存储选项使用BasicFiles 默认值。如果指定了ALWAYS ，则系统中创建的所有LOB 均会被创建为SecureFiles 。必须在ASSM 表空间中创建LOB ，否则会发生错误。将忽略所有指定的BasicFiles 存储选项。可以使用

ALTER SYSTEM 命令更改所有存储的SecureFiles 默认值，如幻灯片中所示。也可以通过单击“Server （服务器）”选项卡中的“Initialization Parameters （初始化参数）”链接来使用Enterprise Manager 设置参数。

修改参数：

Evaluate the following command:
SQL>ALTER SYSTEM SET db_securefile = 'IGNORE';
What is the impact of this setting on the usage of SecureFiles?
A. It forces BasicFiles to be created even if the SECUREFILE option is specified to create the LOB.
B. It forces SecureFiles to be created even if the BASICFILE option is specified to create the LOB.
C. It does not allow the creation of SecureFiles and generates an error if the SECUREFILE option is
specified to create the LOB.
D. It ignores the SECUREFILE option only if a Manual Segment Space Management tablespace is used and creates a BasicFile.
Answer: A

示例

获取表结构：

表的大小：

可见，表才15g，但是clob字段就41g。

重新建立临时表，将STORE AS BASICFILE修改为STORE AS securefile

然后导入数据：

等待导入完成后，然后继续进行导出：

可见，速度提升很快。

DataPump Export (EXPDP) Is Taking Long time To Export Tables With BASICFILES (Doc ID 1595380.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.2 [Release 11.2 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

Datapump export is taking more than the double time after a database upgrade from 10.2.0.4 to 11.2.0.3

In our example, one table with a LOB column takes more than 18 hours. The rest of the objects are exported within 30 mins.
Here is the current size of the table with a blob column based on the output of the export..

. . exported ""."" 408.8 GB 173268 rows

CHANGES

Database was upgraded from 10.2.0.4.0 to 11.2.0.3.0

CAUSE

Testing internally made clear that a huge decrease in time could be achieved by transform of BasicFiles into SecureFiles even if the parallel option is still not usable.

The export of big table with clob (as SecureFile) doesn't still work in parallel. The confirmation is received from DEV in Bug 19345593 - EXPORT OF CLOB AD SECUREFILE NOT DONE IN PARALLEL, closed as not a bug due to: exporting a table with LOBs is still something that Data Pump does not allow to use parallel DML (PDML). It is being worked at and looking to fix it in 12.2 release. The unpublished documentation Bug 21256503 : DATAPUMP CAN'T USE PARALLEL DML FOR SECUREFILE LOBS, was created in order to correct 11gR2 Utilities documentation with this information.

SOLUTION

In our internal testing, the time for the export was reduced from 30 minutes to 6 minutes for 20 GB of data.

Basicfiles: 20.53 GB 8752 rows => 10:38:17 - 11:09:32 around 30 minutes
Securefiles: 20.53 GB 8752 rows => 14:30:51 - 14:36:41 around 6 minutes

The solution is transform the table to SecureFiles and then redo the export process

Here is the output of the DDL using SecureFiles: (example)

The idea is do an export, recreate the table using SecureFiles and then do an import.

Note: Online redefinition can be used to change a lob from BasicFile to SecureFiles as decribed in Note 728758.1 - HOW TO ONLINE CONVERT TO/FROM SECUREFILES FROM/TO BASICFILES.

REFERENCES

BUG:17518284 - EXPDP IS TAKING A LOT OF TIME AFTER UPGRADED FROM 10.2.0.4 TO 11.2.0.3
NOTE:728758.1 - HOW TO ONLINE CONVERT TO/FROM SECUREFILES FROM/TO BASICFILES

HOW TO ONLINE CONVERT TO/FROM SECUREFILES FROM/TO BASICFILES (Doc ID 728758.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.2.0.1 to 12.2.0.1 [Release 12.2]
Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.2 [Release 11.1 to 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Information in this document applies to any platform.

GOAL

This note will demonstrate how to convert BasicFiles to SecureFiles and SecureFiles to BasicFiles

SOLUTION

The following reference discusses conversion TO Securefiles FROM BasicFIles ...

Oracle Database SecureFiles and Large Objects Developer's Guide 11g Release 1 (11.1) Part Number B28393-03
4 Using Oracle SecureFiles
Example 4-1 Example of Online Redefinition

The reverese of the method may be used to convert from SecureFiles

The following example is a modified version of the example from the above reference

Convert to SecureFiles from Basicfiles

connect / as sysdba

create user pm identified by pm;

GRANT EXECUTE ON DBMS_REDEFINITION TO pm;
GRANT ALTER ANY TABLE TO pm;
GRANT DROP ANY TABLE TO pm;
GRANT LOCK ANY TABLE TO pm;
GRANT CREATE ANY TABLE TO pm;
GRANT SELECT ANY TABLE TO pm;
GRANT CREATE SESSION TO pm;
ALTER USER pm QUOTA UNLIMITED ON ;

--Privileges required to perform cloning of dependent objects.

GRANT CREATE ANY TRIGGER TO pm;
GRANT CREATE ANY INDEX TO pm;

CONNECT pm/pm

-- CREATE OUR TABLE TO BE CONVERTED TO SECUREFILE
CREATE TABLE cust (
c_id NUMBER PRIMARY KEY,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
);

-- INSERT A ROW INTO THIS TABLE

INSERT INTO cust VALUES (1, 94065, 'hhh', 'ttt');

COMMIT;

-- CREATE OUR 'INTERIM' TABLE

CREATE TABLE cust_int (
c_id NUMBER NOT NULL,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
) LOB(c_LOB) STORE AS SECUREFILE (NOCACHE FILESYSTEM_LIKE_LOGGING);

-- START THE REDEFINITION

DECLARE
col_mapping VARCHAR2(1000);
BEGIN
-- map all the columns in the interim table to the original table
col_mapping := 'c_id c_id , '|| 'c_zip c_zip , '|| 'c_name c_name, '|| 'c_lob c_lob';
DBMS_REDEFINITION.START_REDEF_TABLE('pm', 'cust', 'cust_int', col_mapping);
END;
/

-- COPY THE CONSTRAINTS FROM OUR ORIGINAL TABLE ... TO THE INTERIM TABLE

DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('pm', 'cust', 'cust_int', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

-- KEEPS THE INTERIM TABLE SYNCHRONIZED WITH THE ORIGINAL TABLE.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('pm', 'cust', 'cust_int');
END;
/

-- FINISH OUR REDEFINITION WHICH WILL SWAP THE ORIGINAL TABLE AND THE INTERIM TABLE

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('pm', 'cust', 'cust_int');

-- DROP THE INTERIM TABLE (THE FORMER ORIGINAL TABLE)

DROP TABLE cust_int;

-- TRY TO INSERT A ROW INTO OUR NEW SECUREFILE TABLE TO PROVE THAT THE PRIMARY KEY WAS PROPERLY MOVED

INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');

-- VERIFY THAT OUR NEW TABLE IS NOW USING SecureFiles

SELECT SECUREFILE FROM USER_LOBS WHERE TABLE_NAME = 'CUST';

Convert to Basicfiles from SecureFiles

-- CREATE OUR 'INTERIM' TABLE
-- NOTE: no SecureFile option and no primary key

CREATE TABLE cust_int (
c_id NUMBER,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
);

-- START THE REDEFINITION

DECLARE
col_mapping VARCHAR2(1000);
BEGIN
-- map all the columns in the interim table to the original table
col_mapping := 'c_id c_id , '|| 'c_zip c_zip , '|| 'c_name c_name, '|| 'c_lob c_lob';
DBMS_REDEFINITION.START_REDEF_TABLE('pm', 'cust', 'cust_int', col_mapping);
END;
/

-- COPY THE CONSTRAINTS FROM OUR ORIGINAL TABLE ... TO THE INTERIM TABLE

DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('pm', 'cust', 'cust_int', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

-- KEEPS THE INTERIM TABLE SYNCHRONIZED WITH THE ORIGINAL TABLE.

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('pm', 'cust', 'cust_int');
END;
/

-- FINISH OUR REDEFINITION WHICH WILL SWAP THE ORIGINAL TABLE AND THE INTERIM TABLE

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('pm', 'cust', 'cust_int');

-- DROP THE INTERIM TABLE (THE FORMER ORIGINAL TABLE)

DROP TABLE cust_int;

-- TRY TO INSERT A ROW INTO OUR NEW SECUREFILE TABLE TO PROVE THAT THE PRIMARY KEY WAS PROPERLY MOVED

INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');

-- VERIFY THAT OUR NEW TABLE IS NOW USING BasicFIles

SELECT SECUREFILE FROM USER_LOBS WHERE TABLE_NAME = 'CUST';

总结

1、若是11.2.0.4，目前只能重建表，若是表本身或CLOB字段很大，那重建的过程还需要再三斟酌是否会影响业务

参考

http://www.dbdream.com.cn/2016/10/oracle-%E4%BF%AE%E6%94%B9lob%E5%AD%97%E6%AE%B5pctversion-%E7%9A%84%E6%96%B9%E6%B3%95/

https://blog.csdn.net/ctypyb2002/article/details/64475465