Re: Re: Very slow metadata-only expdp

Only with the trace file we can map db file sequential read to Cursors (SQL statements) and objects/[Files/blocks].
that would be my path to identify where/why the read was done.

I'm not sure if tkprof is capable of such analysis.

Martin

2016-10-19 10:17 GMT+02:00 l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch>:

> HI,
>
> there is something strange here. The wait event is db file sequential
> read , but the statement from your traces shows not disk reads.
>
> Regards
>
> Lohar
>
> ----Ursprüngliche Nachricht----
> Von : dmarc-noreply_at_freelists.org
> Datum : 19/10/2016 - 09:54 (UTC)
> An : dmarc-noreply_at_freelists.org
> Cc : oracle-l_at_freelists.org
> Betreff : 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
> <https://support.oracle.com/epmos/faces/BugDisplay?_afrLoop=249069429018914&id=18389118&_afrWindowMode=0&_adf.ctrl-state=h2tjl3914_530>
> 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.
>
>
>
>
>

-- 
Martin Berger           martin.a.berger_at_gmail.com
_at_martinberx <https://twitter.com/martinberx>
http://berxblog.blogspot.com

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