Re: DP export 5x slower in 11.2 than 10.1
From: Rich Jesse <rjoralist2_at_society.servebeer.com>
Date: Tue, 31 Jul 2012 13:49:13 -0500 (CDT)
Message-ID: <38eaf77d9a265ce244d004626a125d1b.squirrel_at_society.servebeer.com>
Hey Tim,
Date: Tue, 31 Jul 2012 13:49:13 -0500 (CDT)
Message-ID: <38eaf77d9a265ce244d004626a125d1b.squirrel_at_society.servebeer.com>
Hey Tim,
> What does "tkprof /<trc-file> <output-file>/sort=prsela,exeela,fchela"
> say after you SQL trace the DP session?
I haven't traced it yet, but EM12 shows the top SQL by far for the DP session as:
SELECT
/*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('INDEX_T', '7')), KU$.OBJ_NUM , KU$.ANC_OBJ.NAME , KU$.ANC_OBJ.OWNER_NAME , KU$.ANC_OBJ.TYPE_NAME , KU$.SCHEMA_OBJ.NAME , KU$.SCHEMA_OBJ.NAME , 'INDEX' , KU$.PROPERTY , KU$.SCHEMA_OBJ.OWNER_NAME , KU$.TS_NAME , TO_CHAR(KU$.TYPE_NUM) , DECODE(cardinality(KU$.COL_LIST),0,'1','0') FROM SYS.KU$_INDEX_VIEW KU$ WHERE NOT KU$.FOR_PKOID =1 AND NOT KU$.FOR_REFPAR=1 AND NOT ( KU$.TYPE_NUM =1 AND KU$.INTCOLS =1 AND KU$.OID_OR_SETID!=0 ) AND NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,4) =4 AND KU$.BASE_OBJ_NUM IN ( SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(100001)) ) AND KU$.SCHEMA_OBJ.OWNER_NAME LIKE '%' AND KU$.TYPE_NUM NOT IN (2,4,8,9) AND BITAND(KU$.PROPERTY,16)!=16 ORDER BY KU$.SCHEMA_OBJ.OWNER_NUM, KU$.SCHEMA_OBJ.DATAOBJ_NUM
The "100001" object number looks suspiciously magic, and when I try running the above, I get an ORA-31600 "invalid input value" on it. Perhaps it's only valid during the DP session.
MOS is up again and it match one of the bugs fixed by patch 14192178.
Thanks for the reply!
Rich
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 31 2012 - 13:49:13 CDT