Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** ALTER SESSION SET EVENTS '10046 trace name context forever, level 12' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer goal: CHOOSE Parsing user id: 28 (EFDATA) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.00 0.00 SQL*Net more data from client 2 0.00 0.00 ******************************************************************************** SELECT obj.obid, obj.n1uniquekey, obj.n1clsrevobid, obj.n1class, obj.n1name, obj.n1description, obj.n1creationsesid, obj.n1creationdate, obj.n1lastupdated, obj.n1terminationsesid, obj.n1terminationdate, obj.n1accessflag, obj.n1owninggroup, obj.n1owner, obj.n1projectname, obj.n1implchangename, obj.n1lcstate, obj.n1revisestate, obj.n1revgrpmasterobid, obj.n1supersededdate, obj.n1issuestate, obj.n1issuedate, obj.n1intrevision, obj.n1extrevision, obj.n1revschemename, obj.n1majorrevision, obj.n1minorrevision, obj.n1version, obj.n1checkedoutind, obj.n1workflowobid, obj.n1workflowname, obj.n1workflowstatus, obj.n1workflowrev, obj.n1config, obj.n1efcreationdate, obj.n1efterminationdate, obj.n1workflowsubmitter, obj.n1eficonclass, obj.n1origclassdefuid, '' AS n1clsclass, '' AS n1clsname, '' AS n1clsdescription, '' AS n1classification, '' AS ifname FROM n2spcrev obj, eft_publishstate eft, n3rgrprev r2, n2revgrp o2, n2ifobj if2, n3rgrprev r4, n2revgrp o4, n2ifobj if4, n3rgrprev r6, n2revgrp o6, n2ifobj if6, n2ifobj if8, n3objattr oa8, n2spcattr sa8, n2clsattr ca8, n2ifobj if16, n3objattr oa16, n2spcattr sa16, n2clsattr ca16, n2ifobj ifo WHERE ( eft.n1docobid(+) = obj.obid AND obj.n1issuestate IN ('CURRENT', 'WORKING') AND (obj.n1revisestate IS NULL OR obj.n1revisestate = '') AND obj.obid = r2.rightrel AND r2.leftrel = o2.obid AND r2.reldefid = 'CNBusinessUnitAllDwgRev' AND if2.n1bobjobid = o2.obid AND if2.n1name = 'ICNBusinessUnit' AND o2.n1name = 'BP' AND r2.n1terminationdate IS NULL AND o2.n1terminationdate IS NULL AND obj.obid = r4.rightrel AND r4.leftrel = o4.obid AND r4.reldefid = 'CNPlantAllDwgRev' AND if4.n1bobjobid = o4.obid AND if4.n1name = 'ICNPlant' AND o4.n1name = '24' AND r4.n1terminationdate IS NULL AND o4.n1terminationdate IS NULL AND obj.obid = r6.rightrel AND r6.leftrel = o6.obid AND r6.reldefid = 'CNTrainAllDwgRev' AND if6.n1bobjobid = o6.obid AND if6.n1name = 'ICNTrain' AND o6.n1name = '0' AND r6.n1terminationdate IS NULL AND o6.n1terminationdate IS NULL AND obj.obid = if8.n1bobjobid AND oa8.leftrel = if8.obid AND oa8.rightrel = sa8.obid AND sa8.n1strvalue = 'General Drawings' AND sa8.n1attrclassobid = ca8.obid AND ca8.n1attrclassname = 'CNCADDwgClass' AND oa8.n1terminationdate IS NULL AND obj.obid = if16.n1bobjobid AND oa16.leftrel = if16.obid AND oa16.rightrel = sa16.obid AND sa16.n1strvalue = 'PID' AND sa16.n1attrclassobid = ca16.obid AND ca16.n1attrclassname = 'CNCADDwgType' AND oa16.n1terminationdate IS NULL AND obj.obid = ifo.n1bobjobid AND ( obj.n1config IS NULL OR obj.n1config = '' OR obj.n1config LIKE 'N3-FMCVRVSVEMAA-3XRHF5IF,%' ) ) AND NOT EXISTS ( SELECT 1 FROM t2spcrev WHERE obid = obj.obid AND (n1config LIKE 'N3-FMCVRVSVEMAA-3XRHF5IF,%')) AND ( obj.n1owner = 'SachinP' OR obj.n1owninggroup IN ('ADMIN', 'ALL_VIEWONLY', 'BP-R-Edit', 'BP-Restricted', 'BP-U-Edit', 'BP-Unrestricted', 'CO-U-Edit', 'CO-Unrestricted', 'EI-R-Edit', 'EI-Restricted', 'EI-U-Edit', 'EI-Unrestricted', 'IF-R-Edit', 'IF-Restricted', 'IF-U-Edit', 'IF-Unrestricted', 'LS-R-Edit', 'LS-Restricted', 'LS-U-Edit', 'LS-Unrestricted', 'MN-R-Edit', 'MN-Restricted', 'MN-U-Edit', 'MN-Unrestricted', 'OS-R-Edit', 'OS-Restricted', 'OS-U-Edit', 'OS-Unrestricted', 'PC-R-Edit', 'PC-Restricted', 'PC-U-Edit', 'PC-Unrestricted', 'POWER', 'PUG-R-Edit', 'PUG-Restricted', 'PUG-U-Edit', 'PUG-Unrestricted', 'SUG-R-Edit', 'SUG-Restricted', 'SUG-U-Edit', 'SUG-Unrestricted', 'SUP-R-Edit', 'SUP-Restricted', 'SUP-U-Edit', 'SUP-Unrestricted', 'UT-R-Edit', 'UT-Restricted', 'UT-U-Edit', 'UT-Unrestricted', 'VIEWONLY' ) ) AND ifo.n1name = 'ICNAllDwgRev' AND obj.n1uniquekey LIKE '%' AND ROWNUM <= 32001 ORDER BY obj.n1name call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.12 0.12 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 4 200.82 197.71 17040 73288447 0 41 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 200.94 197.83 17040 73288447 0 41 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 28 (EFDATA) Rows Row Source Operation ------- --------------------------------------------------- 41 SORT ORDER BY 41 COUNT STOPKEY 41 TABLE ACCESS BY INDEX ROWID N3OBJATTR 858817 NESTED LOOPS 756 NESTED LOOPS 106992 HASH JOIN ANTI 106992 TABLE ACCESS BY INDEX ROWID N2IFOBJ 115909 NESTED LOOPS 8916 NESTED LOOPS 743 NESTED LOOPS 743 NESTED LOOPS 957 NESTED LOOPS 957 NESTED LOOPS 957 NESTED LOOPS 1346 NESTED LOOPS 1346 NESTED LOOPS 1346 HASH JOIN OUTER 1346 NESTED LOOPS 1431 NESTED LOOPS 1 NESTED LOOPS 1 NESTED LOOPS 1 NESTED LOOPS 1 NESTED LOOPS 1 NESTED LOOPS 1 TABLE ACCESS BY INDEX ROWID N2CLSATTR 1 INDEX UNIQUE SCAN N2CLSATTR_N1NAME (object id 7217) 1 TABLE ACCESS BY INDEX ROWID N2CLSATTR 1 INDEX UNIQUE SCAN N2CLSATTR_N1NAME (object id 7217) 1 TABLE ACCESS BY INDEX ROWID N2REVGRP 1 INDEX RANGE SCAN N2REVGRP_N1NAME (object id 7251) 1 TABLE ACCESS BY INDEX ROWID N2SPCATTR 1 INDEX RANGE SCAN N2SPCATTR_OBID_N1STRVALUE (object id 7260) 1 TABLE ACCESS BY INDEX ROWID N2SPCATTR 1 INDEX RANGE SCAN N2SPCATTR_OBID_N1STRVALUE (object id 7260) 1 TABLE ACCESS BY INDEX ROWID N2IFOBJ 2 INDEX RANGE SCAN N2IFOBJ_N1BOBJOBID (object id 7229) 1431 TABLE ACCESS BY INDEX ROWID N3RGRPREV 1431 INDEX RANGE SCAN N3RGRPREV_RLTLEFTRELINDEX (object id 7319) 1346 TABLE ACCESS BY INDEX ROWID N2SPCREV 1431 INDEX UNIQUE SCAN PK_N2SPCREV (object id 7266) 10 TABLE ACCESS FULL EFT_PUBLISHSTATE 1346 TABLE ACCESS BY INDEX ROWID N2IFOBJ 16152 INDEX RANGE SCAN N2IFOBJ_N1BOBJOBID (object id 7229) 1346 TABLE ACCESS BY INDEX ROWID N3RGRPREV 18136 INDEX RANGE SCAN N3RGRPREV_RLTRIGHTRELINDEX (object id 7320) 957 TABLE ACCESS BY INDEX ROWID N2REVGRP 1346 INDEX UNIQUE SCAN PK_N2REVGRP (object id 7253) 957 TABLE ACCESS BY INDEX ROWID N2IFOBJ 1914 INDEX RANGE SCAN N2IFOBJ_N1BOBJOBID (object id 7229) 957 TABLE ACCESS BY INDEX ROWID N3RGRPREV 12623 INDEX RANGE SCAN N3RGRPREV_RLTRIGHTRELINDEX (object id 7320) 743 TABLE ACCESS BY INDEX ROWID N2REVGRP 957 INDEX UNIQUE SCAN PK_N2REVGRP (object id 7253) 743 TABLE ACCESS BY INDEX ROWID N2IFOBJ 1486 INDEX RANGE SCAN N2IFOBJ_N1BOBJOBID (object id 7229) 8916 TABLE ACCESS BY INDEX ROWID N2IFOBJ 8916 INDEX RANGE SCAN N2IFOBJ_N1BOBJOBID (object id 7229) 106992 INDEX RANGE SCAN N2IFOBJ_N1BOBJOBID (object id 7229) 0 TABLE ACCESS FULL T2SPCREV 756 TABLE ACCESS BY INDEX ROWID N3OBJATTR 135451872 INDEX RANGE SCAN N3OBJATTR_RLTRIGHTRELINDEX (object id 7307) 858060 INDEX RANGE SCAN N3OBJATTR_RLTRIGHTRELINDEX (object id 7307) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 41 SORT (ORDER BY) 41 COUNT (STOPKEY) 41 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N3OBJATTR' 858817 NESTED LOOPS 756 NESTED LOOPS 106992 HASH JOIN (ANTI) 106992 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2IFOBJ' 115909 NESTED LOOPS 8916 NESTED LOOPS 743 NESTED LOOPS 743 NESTED LOOPS 957 NESTED LOOPS 957 NESTED LOOPS 957 NESTED LOOPS 1346 NESTED LOOPS 1346 NESTED LOOPS 1346 HASH JOIN (OUTER) 1346 NESTED LOOPS 1431 NESTED LOOPS 1 NESTED LOOPS 1 NESTED LOOPS 1 NESTED LOOPS 1 NESTED LOOPS 1 NESTED LOOPS 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2CLSATTR' 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'N2CLSATTR_N1NAME' (UNIQUE) 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2CLSATTR' 1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'N2CLSATTR_N1NAME' (UNIQUE) 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2REVGRP' 1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N2REVGRP_N1NAME' (NON-UNIQUE) 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2SPCATTR' 1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N2SPCATTR_OBID_N1STRVALUE' (NON-UNIQUE) 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2SPCATTR' 1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N2SPCATTR_OBID_N1STRVALUE' (NON-UNIQUE) 1 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2IFOBJ' 2 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N2IFOBJ_N1BOBJOBID' (NON-UNIQUE) 1431 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N3RGRPREV' 1431 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N3RGRPREV_RLTLEFTRELINDEX' (NON-UNIQUE) 1346 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2SPCREV' 1431 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_N2SPCREV' (UNIQUE) 10 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EFT_PUBLISHSTATE' 1346 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2IFOBJ' 16152 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N2IFOBJ_N1BOBJOBID' (NON-UNIQUE) 1346 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N3RGRPREV' 18136 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N3RGRPREV_RLTRIGHTRELINDEX' (NON-UNIQUE) 957 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2REVGRP' 1346 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_N2REVGRP' (UNIQUE) 957 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2IFOBJ' 1914 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N2IFOBJ_N1BOBJOBID' (NON-UNIQUE) 957 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N3RGRPREV' 12623 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N3RGRPREV_RLTRIGHTRELINDEX' (NON-UNIQUE) 743 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2REVGRP' 957 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_N2REVGRP' (UNIQUE) 743 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2IFOBJ' 1486 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N2IFOBJ_N1BOBJOBID' (NON-UNIQUE) 8916 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N2IFOBJ' 8916 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N2IFOBJ_N1BOBJOBID' (NON-UNIQUE) 106992 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N2IFOBJ_N1BOBJOBID' (NON-UNIQUE) 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T2SPCREV' 756 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'N3OBJATTR' 135451872 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N3OBJATTR_RLTRIGHTRELINDEX' (NON-UNIQUE) 858060 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'N3OBJATTR_RLTRIGHTRELINDEX' (NON-UNIQUE) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 4 0.00 0.00 SQL*Net more data to client 4 0.00 0.00 direct path write 1136 0.00 0.00 direct path read 1136 0.00 0.00 SQL*Net message from client 4 2.25 4.77 ******************************************************************************** select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname, o.dataobj#,o.flags from obj$ o where o.obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 5 0.00 0.00 0 0 0 0 Fetch 5 0.00 0.00 0 15 0 5 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 11 0.00 0.00 0 15 0 5 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) ******************************************************************************** ALTER SESSION SET EVENTS '10046 trace name context off' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 28 (EFDATA) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.12 0.12 0 0 0 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 4 200.82 197.71 17040 73288447 0 41 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 200.95 197.83 17040 73288447 0 41 Misses in library cache during parse: 2 Misses in library cache during execute: 1 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 5 0.00 0.00 SQL*Net message from client 5 2.25 4.77 SQL*Net more data from client 2 0.00 0.00 SQL*Net more data to client 4 0.00 0.00 direct path write 1136 0.00 0.00 direct path read 1136 0.00 0.00 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 5 0.00 0.00 0 0 0 0 Fetch 5 0.00 0.00 0 15 0 5 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 11 0.00 0.00 0 15 0 5 Misses in library cache during parse: 1 3 user SQL statements in session. 1 internal SQL statements in session. 4 SQL statements in session. 1 statement EXPLAINed in this session. ******************************************************************************** Trace file: eftst01_ora_22441.trc Trace file compatibility: 9.02.00 Sort options: default 1 session in tracefile. 3 user SQL statements in trace file. 1 internal SQL statements in trace file. 4 SQL statements in trace file. 4 unique SQL statements in trace file. 1 SQL statements EXPLAINed using schema: EFDATA.prof$plan_table Default table was used. Table was created. Table was dropped. 2575 lines in trace file.