Database Export slow while exporting triggers - XDB installed (Doc ID 727411.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.6 - Release: 9.2 to 11.1
Information in this document applies to any platform.

Symptoms

EXPORT LOG 
----------
...
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers <== Export seems to hang.
SQL> SELECT SUBSTR(comp_id,1,15) comp_id, status,  
            SUBSTR(version,1,10) version,      
            SUBSTR(comp_name,1,30) comp_name      
       FROM dba_registry ORDER BY 1;
       
COMP_ID              STATUS VERSION    COMP_NAME
--------------- ----------- ---------- ------------------------------
... 
XDB                   VALID 10.1.0.5.0 Oracle XML Database 
XML                   VALID 10.1.0.5.0 Oracle XDK 

ANALYSIS

To diagnose this in more detail, repeat the export, open a separate sqlplus session to create several errorstack dumps.

-- Determine SPID of export process 
select distinct(p.spid),s.sid,s.serial#
from v$process p, v$session s, v$mystat m
where s.PADDR = p.ADDR
and lower(s.program) like '%exp%';

 

-- connect to the process as sysdba 
connect / as sysdba
oradebug setospid xxxx <-- use SPID here
oradebug unlimit
-- Create process state dumps 

oradebug dump errorstack 3
... wait 30 seconds
oradebug dump errorstack 3
... wait 30 seconds
oradebug dump errorstack 3


TRACE FILE

Look at the "Current SQL statement for this session". This will confirm package body XDB.DBMS_XDBUTIL_INT is executed:

ksedmp: internal or fatal error 
Current SQL statement for this session:
SELECT COUNT(*) FROM ALL_POLICIES V WHERE V.OBJECT_OWNER = :B2 AND V.OBJECT_NAME = :B1 AND (POLICY_NAME LIKE '%xdbrls%' OR POLICY_NAME LIKE '%$xd_%')
----- PL/SQL Call Stack -----
object line object
handle number name
c000000257c950c8 465 package body XDB.DBMS_XDBZ0
c0000002479c2048 65 package body XDB.DBMS_XDBUTIL_INT <==
c0000002479c3080 1 anonymous block

 

Now, look at 'psql' field in the session state dump information. This will point to a library cache handle.




(session) trans: 0000000000000000, creator: c00000025d2fd8f0, flag: (10000041) USR/- BSY/-/-/-/-/-
...
oct: 3, prv: 0, sql: c000000257c7d0b8, psql: c00000024a58e988, user: 5/SYSTEM
O/S info: user: oracle, term: pts/ta, ospid: 9260, machine: vir-hp7
program: exp@vir-hp7 (TNS V1-V3)
application name: exp@vir-hp7 (TNS V1-V3), hash value=0
last wait for 'SQL*Net message from client' blocking sess=0x0000000000000000 seq=10615 wait_time=58
driver id=62657100, #bytes=1, =0

Check the previous sql statement:

 

Here, 'psql' points to handle 0xc00000024a58e988 which shows a query against ALL_TRIGGERS.





SO: c00000025a3d07d8, type: 54, owner: c00000025e3ad198, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=c00000025a3d07d8 handle=c00000024a58e988 mode=N
call pin=c0000002511a07b0 session pin=0000000000000000 hpc=0000 hlc=0000
htl=c00000025a3d0848[c00000025a316140,c00000025a312a20] htb=c00000025bd6df68
user=c00000025e3ad198 session=c00000025e3ad198 count=1 flags=[0000] savepoint=7419397
LIBRARY OBJECT HANDLE: handle=c00000024a58e988
name=SELECT TABLE_NAME FROM ALL_TRIGGERS WHERE TRIGGER_NAME = :B2 AND TABLE_OWNER = :B1

Have a look at the explain plan

 


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