Re: Very slow metadata-only expdp

Surprised nobody faced this ever!

> On Oct 10, 2016, at 9:54 PM, Deepak Sharma (Redacted sender "sharmakdeep_oracle" for DMARC) <dmarc-noreply_at_freelists.org> 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;
>
> Doing a 10046 trace (in another session for about 10 mins) shows max time spent on the following SQL:
>
> SQL ID: 1n1bm7xa28vtq Plan Hash: 167961705
>
> select file#, block#, type#, nvl(spare1,0), hwmincr, cachehint,
> nvl(scanhint,0)
> from
> seg$ where ts# = :1
>
>
> call count cpu elapsed disk query current rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> Parse 9 0.00 0.00 0 0 0 0
> Execute 9 0.00 0.00 0 0 0 0
> Fetch 73737 1.64 2.83 0 223521 0 73728
> ------- ------ -------- ---------- ---------- ---------- ---------- ----------
> total 73755 1.65 2.84 0 223521 0 73728
>
> Misses in library cache during parse: 1
> Misses in library cache during execute: 1
> Optimizer mode: RULE
> Parsing user id: SYS (recursive depth: 3)
> Number of plan statistics captured: 3
>
> Rows (1st) Rows (avg) Rows (max) Row Source Operation
> ---------- ---------- ---------- ---------------------------------------------------
> 688 515 850 TABLE ACCESS CLUSTER SEG$ (cr=1550 pr=0 pw=0 time=15081 us)
> 693 517 850 INDEX RANGE SCAN I_FILE#_BLOCK# (cr=518 pr=0 pw=0 time=2956 us)(object id 9)
> ...
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total Waited
> ---------------------------------------- Waited ---------- ------------
> db file sequential read 74275 0.47 1477.70
> Disk file operations I/O 130 0.00 0.01
>
>
>
> Metalink has a close match but in that case they used a workaround to exclude table statistics (in our case we want to get only constraints)
>
> Any thoughts or suggestions? We are opening an SR, but just wanted to see if anyone else has run into a similar issue.
>
>

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