最近資料庫升級遷移過程中，遇到一個非常棘手的問題，源庫環境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匯出都非常慢。

ORACLE expdp在表空間較多的情況下執行非常緩慢

相關文章