Re: Re: Very slow metadata-only expdp
Date: Wed, 19 Oct 2016 20:12:12 +0000 (UTC)
Message-ID: <151441124.1258909.1476907932508_at_mail.yahoo.com>
I guess I should rephrase the real issue wrt the export of metadata.
expdp userid=system/<passwd> DIRECTORY=DP_DIR LOGFILE=expdp_CONSTRAINT_ONLY.log DUMPFILE=expdp_CONSTRAINT_ONLY.dmp FULL=y METRICS=Y CONTENT=METADATA_ONLY INCLUDE=CONSTRAINT
Running the above export to get just the 'constraint' DDLs, the whole job takes about 10Hours 45mins. Out of that, 10Hours is spent on this
INSERT INTO SYS.KU$XKTFBUE SELECT KTFBUESEGTSN, KTFBUESEGFNO, KTFBUESEGBNO,
KTFBUEBLKS FROM SYS.X$KTFBUE
SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('CONSTRAINT_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.NAME ,KU$.NAME ,'CONSTRAINT' ,KU$.OWNER_NAME FROM SYS.KU$_CONSTRAINT_VIEW KU$ WHERE NOT (KU$.CON1.CONTYPE=3 AND KU$.CON1.INTCOLS=1 AND KU$.CON1.OID_OR_SETID!=0) AND NOT (KU$.CON1.CONTYPE=2 AND BITAND(KU$.CON1.PROPERTY,64+4096)!=0) AND NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND KU$.BASE_OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(100001))) AND KU$.BASE_OBJ.OWNER_NAME LIKE '%' AND KU$.OWNER_NAME LIKE '%' AND (KU$.CON1.CONTYPE NOT IN (5) OR KU$.CON1.IND.TYPE_NUM NOT IN (4,8,9))
As I understand, that while this (INSERT..SELECT) statement is running, the 10046 trace shows it looping through several datafiles and blocks, even though it's a "CONSTRAINT" only export. So, there seems to be a long time spent on seemingly unnecessary "overhead" of iterations though several datafiles.
If this particular step (INSERT..SELECT) can somehow be eliminated the datapump process could straightaway proceed to getting the DDL of the Constraints (like it's supposed to do).
On Wednesday, October 19, 2016 10:35 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
#yiv3550607108 P {margin-top:0;margin-bottom:0;} I missed the first post, so replying via one of the responses:
The select from seg$ is, I believe, identifying the segment header blocks in a tablespace, of which you show in excess of 73,000. The insert/select is selecting from "used extents" for locally managed segments - and locally managed segments and it's a view that's generated by reference to the content of the segment header block, so it seems likely that a large fraction of your time is CPU time spent in deriving (possibly lots) of extent descriptions from your segment headers (a process that doesn't need to do any further I/O).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of l.flatz_at_bluewin.ch [l.flatz_at_bluewin.ch]
Sent: 19 October 2016 09:17
To: dmarc-noreply_at_freelists.org
Cc: dmarc-noreply_at_freelists.org; oracle-l_at_freelists.org
Subject: Re: Re: Very slow metadata-only expdp
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 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 - 22:12:12 CEST