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@xxxxxxxxxx <l.flatz@xxxxxxxxxx>:
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@xxxxxxxxxxxxx
Datum : 19/10/2016 - 09:54 (UTC)
An : dmarc-noreply@xxxxxxxxxxxxx
Cc : oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx> 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.
