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
