RE: Re: Very slow metadata-only expdp
Date: Fri, 21 Oct 2016 19:51:00 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901503448E4_at_exmbx05.thus.corp>
Deepak,
The first thing I did when I saw your post was to try the same command on a small system with tracing enabled.
The statement appeared in one of the resulting trace files. As far as I could tell it populates a global temporary table with results that are never used again - which does make it look rather like an accident.
The tkprof summary of the activity was a little suspect - the numbers didn't tally well - because of the multiple dependency levels. The statements to select segment ids from each tablespace in turn appeared one level below the insert. At some point I might use a logoff trigger to capture the dictionary cache stats and session stats while the export runs, but I suspect that - on top of the fact that the statement appears to be redundant anyway - there may be some anomaly in the way the segment header is used to generate the used extent lists (e.g. multiple buffer gets on the segments header - one for each, or maybe every N, used extents).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: Deepak Sharma [sharmakdeep_oracle_at_yahoo.com] Sent: 19 October 2016 21:12
To: Jonathan Lewis; oracle-l_at_freelists.org Subject: Re: Re: Very slow metadata-only expdp
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 and about 40mins getting the DDLs for the constraints. We can see it doing that DDL extract using dbms_metadata when monitoring the expdp job.
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:
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<mailto:dmarc-noreply_at_freelists.org>> wrote:
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 21 2016 - 21:51:00 CEST