Re: expdp is painfully slow
From: DG problem <skatefree_at_gmail.com>
Date: Mon, 5 Apr 2010 23:53:28 -0700 (PDT)
Message-ID: <4214aa1f-96e2-41a7-84e0-8608ff5f58b2_at_y14g2000yqm.googlegroups.com>
On Apr 5, 5:01 am, zigzagdna <zigzag..._at_yahoo.com> wrote:
> I am using Oracle 11.1.0.7.1 on HP UNIX 11.0. I ma using expdp and
> find it extremely slow. However. Most of the waiting time is during
> metadata (see below) where it waits for several hours. When it starts
> exporting the rows, it progresses reasonably fast.
> Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/
> INDEX
> I have seen some threads on Metalink as well as on Google. All otf
> tem point to these problems in Oracle 10.2 expdp version, but I see
> same problem in Oracle 11.1.0.7.1 as well. A suggestion to exclude
> STAITISTICS has not helped me:
> expdp system/password directory=expdp_dir dumpfile=$BackupExportFile
> logfile=$TLOGFILE \
> full=Y compression=all exclude=STATISTICS TRACE=480300
> I have turned on expdp tracing and I see statements like following for
> various indexes and do not give me any clue to speed things up.
> KUPW:14:48:25.668: 1: Base Process info: 6122 and processing status: C
> and processing state: R
> KUPW:14:48:25.668: 1: new state: R
> KUPW:14:48:25.668: 1: new status: C
> KUPW:14:48:25.668: 1: In procedure BUILD_SUBNAME_LIST with
> INDEX:MAXDEMO71.CONTRACT_NDX2
> KUPW:14:48:25.669: 1: In function NEXT_PO_NUMBER
> KUPW:14:48:25.669: 1: FORALL called.
> KUPW:14:48:25.676: 1: FORALL returned.
> KUPW:14:48:25.678: 1: DBMS_LOB.TRIM called. v_md_xml_clob
> KUPW:14:48:25.679: 1: DBMS_LOB.TRIM returned.
> KUPW:14:48:25.679: 1: DBMS_METADATA.FETCH_XML_CLOB called. Handle:
> 200001
>
> Is ether anyway to speed expdp.
Date: Mon, 5 Apr 2010 23:53:28 -0700 (PDT)
Message-ID: <4214aa1f-96e2-41a7-84e0-8608ff5f58b2_at_y14g2000yqm.googlegroups.com>
On Apr 5, 5:01 am, zigzagdna <zigzag..._at_yahoo.com> wrote:
> I am using Oracle 11.1.0.7.1 on HP UNIX 11.0. I ma using expdp and
> find it extremely slow. However. Most of the waiting time is during
> metadata (see below) where it waits for several hours. When it starts
> exporting the rows, it progresses reasonably fast.
> Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/
> INDEX
> I have seen some threads on Metalink as well as on Google. All otf
> tem point to these problems in Oracle 10.2 expdp version, but I see
> same problem in Oracle 11.1.0.7.1 as well. A suggestion to exclude
> STAITISTICS has not helped me:
> expdp system/password directory=expdp_dir dumpfile=$BackupExportFile
> logfile=$TLOGFILE \
> full=Y compression=all exclude=STATISTICS TRACE=480300
> I have turned on expdp tracing and I see statements like following for
> various indexes and do not give me any clue to speed things up.
> KUPW:14:48:25.668: 1: Base Process info: 6122 and processing status: C
> and processing state: R
> KUPW:14:48:25.668: 1: new state: R
> KUPW:14:48:25.668: 1: new status: C
> KUPW:14:48:25.668: 1: In procedure BUILD_SUBNAME_LIST with
> INDEX:MAXDEMO71.CONTRACT_NDX2
> KUPW:14:48:25.669: 1: In function NEXT_PO_NUMBER
> KUPW:14:48:25.669: 1: FORALL called.
> KUPW:14:48:25.676: 1: FORALL returned.
> KUPW:14:48:25.678: 1: DBMS_LOB.TRIM called. v_md_xml_clob
> KUPW:14:48:25.679: 1: DBMS_LOB.TRIM returned.
> KUPW:14:48:25.679: 1: DBMS_METADATA.FETCH_XML_CLOB called. Handle:
> 200001
>
> Is ether anyway to speed expdp.
About 3 to 4 years ago I experienced the same problem with 10g. I narrowed down the problem to the export of the users. I had about 120,000 users in the DBA_USERS table (PeopleSoft database) and so during testing at one stage I dropped all the users and ran expdp again and it ran much quicker.
Anyway, that was from memory so I hope I have recalled it properly. Received on Tue Apr 06 2010 - 01:53:28 CDT