Re: Very slow metadata-only expdp

Hi,

This was one of the first articles we ran into, so all our tests have been with excluding STATS.

The real issue is with that creation of Temp table from X$KTXBUE

Now it may or may not impact your system, totally depends on how large that fixed table is.

We still have this issue and no resolution yet with our SR

> On Oct 24, 2016, at 10:38 AM, Yong Huang (Redacted sender "yong321" for DMARC) <dmarc-noreply_at_freelists.org> wrote:
>
> Hi Deepak,
>
> Bug 18389118 : DATAPUMP EXPORT WITH METADATA_ONLY TAKING UP TO 7 HOURS
>
> seems to match your case. The bug shows the same SQL as yours. The workaround is to set EXCLUDE=TABLE_STATISTICS. If that works, then of course you have to gather stats afterward.
>
> (Maybe unrelated) I used to almost always set exclude=statistics (or table_statistics) because otherwise I would get lots of literal SQLs in the shared pool like
>
> INSERT INTO "SYS"."IMPDP_STATS"...
>
> during impdp (Bug 7185178, fixed in 11gR2).
>
> By the way, I suspect this statement in Documentation (http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_legacy.htm#SUTIL961) is wrong:
> "This parameter (i.e. STATISTICS) is ignored because statistics are always saved for tables as part of a Data Pump Import operation."
>
> Yong Huang
>
>
> ---- Deepak Sharma wrote ----
>
> When exporting only "constraints" on a ~150TB DB (11.2.0.4) using expdp
> metadata_only option, the job spends about 10 (of 10.5) hours just on the
> following SQL. The log with METRICS-Y shows constraints export took only about
> 30 mins of the entire 10.5hrs run!
>
> INSERT INTO SYS.KU$XKTFBUE SELECT KTFBUESEGTSN, KTFBUESEGFNO, KTFBUESEGBNO,
> KTFBUEBLKS FROM SYS.X$KTFBUE;
> ...
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l