Re: Re: Very slow metadata-only expdp
From: <l.flatz_at_bluewin.ch>
Date: Wed, 19 Oct 2016 08:17:25 +0000
Message-ID: <21851177.10730.1476865045249.JavaMail.webmail_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
------- ------ -------- ---------- ---------- ---------- ---------- ----------
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
---------- ---------- ---------- ---------------------------------------------------
Elapsed times include waiting on following events:
Date: Wed, 19 Oct 2016 08:17:25 +0000
Message-ID: <21851177.10730.1476865045249.JavaMail.webmail_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 73728Misses in library cache during parse: 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 73755 1.65 2.84 0 223521 0 73728
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.01Metalink 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-lReceived on Wed Oct 19 2016 - 10:17:25 CEST