Datapump Import May Slow Down Due To "RAC: constraint DDL lock" Waits (Doc ID 2056924.1)

Last updated on APRIL 24, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 12.1.0.2 [Release 11.2 to 12.1]
Information in this document applies to any platform.

Symptoms

Customer may see performance issues with the datapump import, when importing schemas with large database schemes with several database constraints.

Trace files indicate that the worker processes spent long time on "RAC: constraint DDL lock" wait.


Chain 7:
------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 2 (ps9qa.ps9qa2)
                   os id: 60170
              process id: 181, oracle@ex02db02.saic.com (DW00)
              session id: 433
        session serial #: 59775
    }
    is waiting for 'RAC: constraint DDL lock' with wait info:
    {
                      p1: 'maximum attempts'=0x78
                      p2: 'attempt count'=0x1
            time in wait: 0.212016 sec
           timeout after: 0.787984 sec
                 wait id: 1331503
                blocking: 0 sessions
             current sql: CREATE TABLE "SYSADM"."PS_ENG_COMM_TMP28"
("PROCESS_INSTANCE" NUMBER(10,0) NOT NULL ENABLE, "SA_ID_TYPE" VARCHAR2(1
BYTE) NOT NULL ENABLE, "COMMON_ID" VARCHAR2(11 BYTE) NOT NULL ENABLE,
"INSTITUTION" VARCHAR2(5 BYTE) NOT NULL ENABLE, "EVENT_3CS_ID" VARCHAR2(10
BYTE) NOT NULL ENABLE, "COMM_KEY" VARCHAR2(8 BYTE) NOT NUL
             short stack:
ksedsts()+244<-ksdxfstk()+58<-ksdxcb()+918<-sspuser()+224<-__sighandler()<-__p
oll()+24<-ipcgxp_selectex()+423<-ipclw_wait()+2429<-ksxpwait_ipclw()+3696<-ksx
pwait()+19359<-ksliwat()+9356<-kslwaitctx()+161<-kslwait()+140<-kkdcnco()+1850
<-kkdcico()+284<-ctcdrv()+30477<-opiexe()+19591<-opiosq0()+4554<-opipls()+1181
0<-opiodr()+1165<-rpidrus()+206<-skgmstack()+144<-rpiswu2()+723<-rpidrv()+1570
<-psddr0()+478<-psdnal()+636<-pevm_EXIM()+260<-pfrinstr_EXIM()+52<-pfrrun_no_t
ool()+60<-pfrrun()+1155<-plsql_run()+708<-peicnt()+

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms