Re: expdp is painfully slow
From: zigzagdna <zigzagdna_at_yahoo.com>
Date: Tue, 6 Apr 2010 17:33:32 -0700 (PDT)
Message-ID: <d241280a-10e4-4f4c-8a29-438d56ea8e1d_at_b33g2000yqc.googlegroups.com>
On Apr 6, 2:53 am, DG problem <skatef..._at_gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 6 Apr 2010 17:33:32 -0700 (PDT)
Message-ID: <d241280a-10e4-4f4c-8a29-438d56ea8e1d_at_b33g2000yqc.googlegroups.com>
On Apr 6, 2:53 am, DG problem <skatef..._at_gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
I have narrowed down the problem to domain indexes which get created because of Oracle text indexes which my application needs. Unfortunately, I cannot get rid of domain indexes so stuck with it god knows until when.
I have yet another problem with 11g, cannot create db control most of the time. Oracle support looked at it and there is a bug about it on metalink, yet they will not document my problem as a bug and fix it. Received on Tue Apr 06 2010 - 19:33:32 CDT