ORACLE expdp在表空间较多的情况下执行非常缓慢

      最近数据库升级迁移过程中，遇到一个非常棘手的问题，源库环境11.2.0.4拥有3700+个表空间，每个表空间只有数据文件，大小不超过10MB，在对该数据库全库expdp导出压缩体积不超过300MB时，expdp全库导出需要10多个小时。

     在数据泵expdp全库导出时，查看数据库的内部等待事件是control file sequce read:

SQL> l
  1  select event,username,count(*) from gv$session gs
  2* where wait_class<>'Idle' group by event,username
SQL> /

EVENT                                                            USERNAME     COUNT(*)
---------------------------------------------------------------- ---------- ----------
SQL*Net message to client                                        SYS                 1
control file sequential read                                     SYS                 3

SQL>

      查看等待事件对应的会话信息：

new   1: select sid,username,sql_id,event,module,machine,program from gv$session where event='control file sequential read' and sql_id is not null

       SID USERNAME   SQL_ID        EVENT                                                            MODULE                         MACHINE         PROGRAM
---------- ---------- ------------- ---------------------------------------------------------------- ------------------------------ --------------- ------------------------------------------------
      1894 SYS        d3gfd5bdzb56n control file sequential read                                     Data Pump Worker               orcl01       oracle@orcl01 (DW02)
SQL>

可以明确看到control file sequential read对应的操作对象就是Data Pump Worker，即数据泵。

      查看sql语句d3gfd5bdzb56n对应的具体内容：

SQL>select sql_fulltext from v$sqlarea where sql_id='d3gfd5bdzb56n'
SELECT /*+all_rows*/ 
SYS_XMLGEN(VALUE(KU$),
XMLFORMAT.createFormat2('TABLESPACE_T', '7')), 
KU$.TS_NUM,
KU$.NAME,
KU$.NAME,
'TABLESPACE'
FROM SYS.KU$_TABLESPACE_VIEW KU$
WHERE NOT EXISTS (
  SELECT 1 
  FROM  SYS.KU$NOEXP_TAB A WHERE
  A.NAME=KU$.NAME 
  AND A.OBJ_TYPE='TABLESPACE') 
  AND NOT (not(ku$.name not in ('SYSTEM','SYSAUX') 
    and dbms_metadata.in_tsnum(1,ts_num)=0)) 
    AND NOT (KU$.BITMAPPED>0 AND KU$.STATUS=2);

手工执行该sql语句时也十分缓慢，但是，去除XMLFORMAT.createFormat2('TABLESPACE_T', '7'))时，sql语句执行非常快。针对这种情况查询oracle mos，找到一篇文档：Bug 14794472 - expdp/impdp is slow with many tablespaces (Doc ID 14794472.8)。文档的workaround建议执行如下update：

Workaround
 Update sys table entry used for datapump as follows.
  update metaview$ set properties=properties+1024-bitand(properties,1024) where viewname='KU$_TABLESPACE_VIEW';
  commit;

14794472.8文档的workaround是要修改数据库的CBO执行模式，但是执行完update后，expdp执行依然很慢。

      后续，将该数据库的表空间和数据文件对应关系转储为sql文件，在19c的目标数据库中创建源库的表空间和数据文件，执行expdp导出发现19c数据库也一样十分慢。因此，oracle数据库不分版本，在大量数据库表空间和数据文件的情况下，expdp导出都非常慢。