TKPROF: Release 11.2.0.1.0 - Development on Tue Jan 10 13:07:51 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Trace file: r2_ora_316.trc 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 ******************************************************************************** SQL ID: 4kvc1pjyh5n7x Plan Hash: 0 ALTER SESSION SET SQL_TRACE=TRUE 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 mode: ALL_ROWS Parsing user id: 65 ******************************************************************************** SQL ID: 7ng34ruy5awxq Plan Hash: 3992920156 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property, i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey, i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256), i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0), nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null, null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 15 0.00 0.01 0 0 0 0 Fetch 27 0.00 0.00 1 94 0 12 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 44 0.00 0.01 1 94 0 12 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=3 pr=0 pw=0 time=0 us cost=7 size=376 card=2) 0 HASH JOIN OUTER (cr=3 pr=0 pw=0 time=0 us cost=6 size=376 card=2) 0 NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=0 us cost=2 size=290 card=2) 0 TABLE ACCESS CLUSTER IND$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=186 card=2) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 3) 0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=52 card=1) 0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 432) 0 VIEW (cr=0 pr=0 pw=0 time=0 us cost=3 size=43 card=1) 0 SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=3 size=15 card=1) 0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=15 card=1) 0 INDEX RANGE SCAN I_CDEF2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=4)(object id 54) ******************************************************************************** SQL ID: 96g93hntrzjtr Plan Hash: 2239883476 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 214 0.01 0.00 0 0 0 0 Fetch 214 0.00 0.03 10 583 0 153 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 430 0.01 0.04 10 583 0 153 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: RULE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=1 pw=0 time=0 us) 1 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=0 us)(object id 426) ******************************************************************************** SQL ID: db78fxqxwxt7r Plan Hash: 3312420081 select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 145 0.01 0.00 0 0 0 0 Fetch 145 0.01 0.13 45 448 0 2365 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 292 0.03 0.13 45 448 0 2365 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: RULE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 20 SORT ORDER BY (cr=3 pr=1 pw=0 time=19 us cost=0 size=0 card=0) 20 TABLE ACCESS CLUSTER HISTGRM$ (cr=3 pr=1 pw=0 time=19 us) 1 INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=0 us)(object id 421) ******************************************************************************** SQL ID: 83taa7kaw59c1 Plan Hash: 3765558045 select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2, nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182, scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$, rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2, nvl(spare3,0) from col$ where obj#=:1 order by intcol# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 15 0.00 0.00 0 0 0 0 Fetch 232 0.00 0.00 0 50 0 217 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 249 0.00 0.00 0 50 0 217 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT ORDER BY (cr=3 pr=0 pw=0 time=0 us cost=3 size=840 card=14) 1 TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=840 card=14) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 3) ******************************************************************************** SQL ID: 3w4qs0tbpmxr6 Plan Hash: 1224215794 select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 from cdef$ where robj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 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 10 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.00 0.00 0 10 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=2 pr=0 pw=0 time=0 us cost=2 size=87 card=3) 0 INDEX RANGE SCAN I_CDEF3 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 55) ******************************************************************************** SQL ID: gx4mv66pvj3xz Plan Hash: 1932954096 select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0), rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3 from cdef$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 5 0.00 0.00 0 0 0 0 Fetch 12 0.00 0.00 0 24 0 7 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 19 0.00 0.00 0 24 0 7 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=2 pr=0 pw=0 time=0 us cost=2 size=192 card=4) 0 INDEX RANGE SCAN I_CDEF2 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=4)(object id 54) ******************************************************************************** SQL ID: a9u0s3g93f47z Plan Hash: 515562855 select decode(u.type#, 2, u.ext_username, u.name), o.name, t.update$, t.insert$, t.delete$, t.enabled, decode(bitand(t.property, 8192), 8192, 1, 0), decode(bitand(t.property, 65536), 65536, 1, 0), decode(bitand(t.property, 131072), 131072, 1, 0), (select o.name from obj$ o where o.obj# = u.spare2 and o.type# =57) from sys.obj$ o, sys.user$ u, sys.trigger$ t, sys.obj$ bo where t.baseobject=bo.obj# and bo.name = :1 and bo.spare3 = :2 and bo.namespace = 1 and t.obj#=o.obj# and o.owner#=u.user# and o.type# = 12 and bitand(property,16)=0 and bitand(property,8)=0 order by o.obj# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.00 0.00 0 0 0 0 Execute 4 0.01 0.00 0 0 0 0 Fetch 4 0.00 0.00 0 20 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.01 0.00 0 20 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=34 card=1) 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 36) 0 SORT ORDER BY (cr=5 pr=0 pw=0 time=0 us cost=9 size=202 card=1) 0 NESTED LOOPS (cr=5 pr=0 pw=0 time=0 us cost=8 size=202 card=1) 0 NESTED LOOPS (cr=5 pr=0 pw=0 time=0 us cost=7 size=99 card=1) 0 NESTED LOOPS (cr=5 pr=0 pw=0 time=0 us cost=5 size=62 card=1) 1 INDEX RANGE SCAN I_OBJ5 (cr=3 pr=0 pw=0 time=0 us cost=3 size=36 card=1)(object id 40) 0 TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=2 pr=0 pw=0 time=0 us cost=2 size=26 card=1) 0 INDEX RANGE SCAN I_TRIGGER1 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 161) 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=37 card=1) 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 36) 0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=103 card=1) 0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 11) ******************************************************************************** SQL ID: 53saa2zkr6wc3 Plan Hash: 3954488388 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 7 0.00 0.00 0 0 0 0 Fetch 14 0.00 0.00 0 28 0 7 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 22 0.00 0.00 0 28 0 7 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID CCOL$ (cr=4 pr=0 pw=0 time=0 us cost=3 size=16 card=1) 1 INDEX RANGE SCAN I_CCOL1 (cr=3 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 57) ******************************************************************************** SQL ID: 6aq34nj2zb2n7 Plan Hash: 2874733959 select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 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 10 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.00 0.00 0 10 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT GROUP BY (cr=2 pr=0 pw=0 time=0 us cost=4 size=15 card=1) 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=0 pw=0 time=0 us cost=3 size=15 card=1) 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 62) ******************************************************************************** SQL ID: 2q93zsrvbdw48 Plan Hash: 2874733959 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 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 10 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 12 0.00 0.00 0 10 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT GROUP BY (cr=2 pr=0 pw=0 time=0 us cost=4 size=15 card=1) 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=0 pw=0 time=0 us cost=3 size=15 card=1) 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 62) ******************************************************************************** SQL ID: dpt3qt5vk0wj5 Plan Hash: 0 INSERT INTO PARAMS VALUES ('ORD-055176') call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.01 3 1 4 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.01 3 1 4 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 Rows Row Source Operation ------- --------------------------------------------------- 0 LOAD TABLE CONVENTIONAL (cr=1 pr=3 pw=0 time=0 us) ******************************************************************************** SQL ID: 7b40gr3s64bq2 Plan Hash: 0 BEGIN GENERATESHIPPINGASSETS (100, 0.0,' ',0, 1, 0, 0,'11110',0,1,0,'902001', to_date('02/07/2011','mm/dd/yyyy'),to_date('02/07/2011','mm/dd/yyyy'),1,' ') ; END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.02 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.02 0 0 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 65 ******************************************************************************** SQL ID: 3nkd3g3ju5ph1 Plan Hash: 2853959010 select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 29 0.00 0.00 0 0 0 0 Fetch 29 0.00 0.00 1 113 0 26 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 59 0.00 0.00 1 113 0 26 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=4 pr=0 pw=0 time=0 us cost=4 size=84 card=1) 1 INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 37) ******************************************************************************** SQL ID: b1wc53ddd6h3p Plan Hash: 1637390370 select audit$,options from procedure$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 8 0.00 0.00 0 0 0 0 Execute 8 0.00 0.00 0 0 0 0 Fetch 8 0.00 0.00 2 24 0 8 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 24 0.00 0.00 2 24 0 8 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID PROCEDURE$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=47 card=1) 1 INDEX UNIQUE SCAN I_PROCEDURE1 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 230) ******************************************************************************** SQL ID: 1gu8t96d0bdmu Plan Hash: 2035254952 select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols, nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans, t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln, t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1), nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0), nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit, ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 10 0.03 0.00 0 0 0 0 Fetch 10 0.00 0.02 5 44 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 21 0.03 0.03 5 44 0 10 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 MERGE JOIN OUTER (cr=5 pr=2 pw=0 time=0 us cost=2 size=193 card=1) 1 TABLE ACCESS CLUSTER TAB$ (cr=4 pr=2 pw=0 time=0 us cost=2 size=141 card=1) 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 3) 0 BUFFER SORT (cr=1 pr=0 pw=0 time=0 us cost=0 size=52 card=1) 0 TABLE ACCESS BY INDEX ROWID TAB_STATS$ (cr=1 pr=0 pw=0 time=0 us cost=0 size=52 card=1) 0 INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 430) ******************************************************************************** SQL ID: 5n1fs4m2n2y0r Plan Hash: 299250003 select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 12 0.00 0.00 0 0 0 0 Fetch 47 0.00 0.00 0 94 0 35 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 60 0.00 0.00 0 94 0 35 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID ICOL$ (cr=4 pr=0 pw=0 time=0 us cost=2 size=54 card=2) 1 INDEX RANGE SCAN I_ICOL1 (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=2)(object id 42) ******************************************************************************** SQL ID: 87gaftwrm2h68 Plan Hash: 1218588913 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname 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 8 0.00 0.00 0 0 0 0 Fetch 8 0.00 0.00 0 24 0 8 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 17 0.00 0.00 0 24 0 8 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=0 us cost=3 size=84 card=1) 1 INDEX RANGE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 36) ******************************************************************************** SQL ID: 3ktacv9r56b51 Plan Hash: 4184428695 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 8 0.00 0.00 0 0 0 0 Execute 8 0.00 0.00 0 0 0 0 Fetch 104 0.00 0.06 12 226 0 96 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 120 0.00 0.06 12 226 0 96 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 34 SORT ORDER BY (cr=65 pr=3 pw=0 time=0 us cost=13 size=448 card=4) 34 NESTED LOOPS OUTER (cr=65 pr=3 pw=0 time=660 us cost=12 size=448 card=4) 34 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=5 pr=2 pw=0 time=66 us cost=4 size=112 card=4) 34 INDEX RANGE SCAN I_DEPENDENCY1 (cr=4 pr=2 pw=0 time=0 us cost=3 size=0 card=4)(object id 106) 34 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=60 pr=1 pw=0 time=0 us cost=2 size=84 card=1) 34 INDEX RANGE SCAN I_OBJ1 (cr=36 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 36) ******************************************************************************** SQL ID: 8swypbbr0m372 Plan Hash: 893970548 select order#,columns,types from access$ where d_obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 8 0.00 0.00 0 0 0 0 Execute 8 0.00 0.00 0 0 0 0 Fetch 101 0.00 0.03 10 202 0 93 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 117 0.00 0.03 10 202 0 93 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 37 TABLE ACCESS BY INDEX ROWID ACCESS$ (cr=76 pr=3 pw=0 time=0 us cost=3 size=182 card=7) 37 INDEX RANGE SCAN I_ACCESS1 (cr=39 pr=1 pw=0 time=1080 us cost=2 size=0 card=7)(object id 108) ******************************************************************************** SQL ID: ga9j9xk5cy9s0 Plan Hash: 1697022209 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 11 0.00 0.00 0 0 0 0 Execute 11 0.00 0.00 0 0 0 0 Fetch 25 0.00 0.02 6 64 0 14 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 47 0.00 0.02 6 64 0 14 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID IDL_SB4$ (cr=4 pr=2 pw=0 time=0 us cost=3 size=19 card=1) 1 INDEX RANGE SCAN I_IDL_SB41 (cr=3 pr=1 pw=0 time=0 us cost=2 size=0 card=1)(object id 238) ******************************************************************************** SQL ID: cvn54b7yz0s8u Plan Hash: 3246118364 select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 11 0.00 0.00 0 0 0 0 Execute 11 0.00 0.00 0 0 0 0 Fetch 40 0.00 0.12 47 145 0 37 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 62 0.00 0.12 47 145 0 37 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 15 TABLE ACCESS BY INDEX ROWID IDL_UB1$ (cr=32 pr=16 pw=0 time=0 us cost=3 size=20 card=1) 15 INDEX RANGE SCAN I_IDL_UB11 (cr=16 pr=1 pw=0 time=56 us cost=2 size=0 card=1)(object id 235) ******************************************************************************** SQL ID: c6awqs517jpj0 Plan Hash: 1319326155 select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 11 0.00 0.00 0 0 0 0 Execute 11 0.00 0.00 0 0 0 0 Fetch 14 0.00 0.00 1 31 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 36 0.00 0.00 1 31 0 3 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID IDL_CHAR$ (cr=2 pr=0 pw=0 time=0 us cost=3 size=21 card=1) 0 INDEX RANGE SCAN I_IDL_CHAR1 (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 236) ******************************************************************************** SQL ID: 39m4sx9k63ba2 Plan Hash: 2317816222 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 11 0.00 0.00 0 0 0 0 Execute 11 0.00 0.00 0 0 0 0 Fetch 14 0.00 0.00 3 37 0 6 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 36 0.00 0.00 3 37 0 6 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID IDL_UB2$ (cr=2 pr=0 pw=0 time=0 us cost=3 size=42 card=2) 0 INDEX RANGE SCAN I_IDL_UB21 (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=2)(object id 237) ******************************************************************************** SQL ID: 03bfs0f3u3k3c Plan Hash: 2917859151 SELECT SITEIID FROM SITEMASTER WHERE SITEID=:B1 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 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID SITEMASTER (cr=2 pr=0 pw=0 time=0 us cost=2 size=25 card=1) 1 INDEX UNIQUE SCAN IDX_SITEMASTERSITEID (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 76692) ******************************************************************************** SQL ID: a79sajs0h4r0s Plan Hash: 899761363 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("PARAMS") FULL("PARAMS") NO_PARALLEL_INDEX("PARAMS") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "PARAMS" "PARAMS") SAMPLESUB 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 1 0.00 0.01 4 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 4 7 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=7 pr=4 pw=0 time=0 us) 1 TABLE ACCESS FULL PARAMS (cr=7 pr=4 pw=0 time=0 us cost=3 size=0 card=409) ******************************************************************************** SQL ID: 3dt1xjtywuuf7 Plan Hash: 2392326484 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("CONTRACT") FULL("CONTRACT") NO_PARALLEL_INDEX("CONTRACT") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "CONTRACT" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5") SEED (:"SYS_B_6") "CONTRACT") SAMPLESUB 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 1 0.00 0.00 0 63 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 63 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=63 pr=0 pw=0 time=0 us) 1008 TABLE ACCESS SAMPLE CONTRACT (cr=63 pr=0 pw=0 time=1007 us cost=19 size=61752 card=5146) ******************************************************************************** SQL ID: 6gjw662vmmv9b Plan Hash: 1196380172 SELECT SITEIID FROM CONTRACT WHERE CONTRACTID=(SELECT MAX(STR) FROM PARAMS) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 2 0 0 Execute 1 0.00 0.00 0 7 0 0 Fetch 1 0.00 0.00 1 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 1 12 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID CONTRACT (cr=10 pr=1 pw=0 time=0 us cost=2 size=21 card=1) 1 INDEX UNIQUE SCAN IDX_CONTRACTCONTRACTID (cr=9 pr=1 pw=0 time=0 us cost=1 size=0 card=1)(object id 75926) 1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us) 1 TABLE ACCESS FULL PARAMS (cr=7 pr=0 pw=0 time=0 us cost=3 size=27 card=1) ******************************************************************************** SQL ID: 9tgj4g8y4rwy8 Plan Hash: 3755742892 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts, NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0), NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 2 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 1 6 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 1 6 2 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS CLUSTER SEG$ (cr=3 pr=1 pw=0 time=0 us cost=2 size=68 card=1) 1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 9) ******************************************************************************** SQL ID: ckn9k2w7jf4p9 Plan Hash: 1962589811 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("GENERICREPORT2") FULL("GENERICREPORT2") NO_PARALLEL_INDEX("GENERICREPORT2") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "GENERICREPORT2" "GENERICREPORT2") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 5 14 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 5 14 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=7 pr=5 pw=0 time=0 us) 0 TABLE ACCESS FULL GENERICREPORT2 (cr=7 pr=5 pw=0 time=0 us cost=3 size=0 card=409) ******************************************************************************** SQL ID: gyjnrqgznv42k Plan Hash: 1517686073 DELETE FROM GENERICREPORT2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 2 2 0 0 Execute 1 0.00 0.00 0 7 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 2 9 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE GENERICREPORT2 (cr=7 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL GENERICREPORT2 (cr=7 pr=0 pw=0 time=0 us cost=3 size=0 card=1) ******************************************************************************** SQL ID: 2tchvwg3s5q8y Plan Hash: 946076197 SELECT CONTRACTID, CONTRACTIID, INVIID, DESCRIPTION, SKU, ITEMTYPE, NVL(SUM(ORD), 0) ORDQTY, NVL(SUM(FILL), 0) FILLQTY, RENTTYPE, COMPANYNAME, POID,SHELFLOCATION,BINNUMBER FROM ( SELECT C.CONTRACTID, C.CONTRACTIID, B.INVIID, SUBSTR(GETPRODUCTDESCRIPTION(B.INVIID,:B4 ),0,100) DESCRIPTION, GETPRODUCTSKU(B.INVIID) SKU,CASE WHEN GETPRODUCTQTYSERIAL(B.INVIID)=101 THEN 'Non-Serial' WHEN GETPRODUCTQTYSERIAL(B.INVIID)=102 THEN 'Serial' END ITEMTYPE, NVL(CASE WHEN B.STATUS=3 THEN B.ORDEREDQTY WHEN E.STATUS=4 THEN B.ORDEREDQTY WHEN B.STATUS=6 THEN B.ORDEREDQTY WHEN B.STATUS=9 THEN B.ORDEREDQTY WHEN B.STATUS=7 THEN B.ORDEREDQTY WHEN B.STATUS=10 THEN B.ORDEREDQTY WHEN B.STATUS=16 THEN B.ORDEREDQTY WHEN B.STATUS=5 THEN B.ORDEREDQTY WHEN B.STATUS=18 THEN B.ORDEREDQTY WHEN B.STATUS=19 THEN B.ORDEREDQTY WHEN B.STATUS=2 THEN B.ORDEREDQTY WHEN B.STATUS=12 THEN B.ORDEREDQTY WHEN B.STATUS=13 THEN B.ORDEREDQTY WHEN B.STATUS=14 THEN B.ORDEREDQTY WHEN B.STATUS=23 THEN B.ORDEREDQTY WHEN B.STATUS=24 THEN B.ORDEREDQTY WHEN B.STATUS=25 THEN B.ORDEREDQTY WHEN B.STATUS=20 THEN B.ORDEREDQTY WHEN B.STATUS=21 THEN B.ORDEREDQTY WHEN B.STATUS=22 THEN B.ORDEREDQTY WHEN B.STATUS=28 THEN B.ORDEREDQTY WHEN B.STATUS=8 THEN B.ORDEREDQTY WHEN B.STATUS=11 THEN B.ORDEREDQTY END,0) ORD, NVL(CASE WHEN B.STATUS=16 THEN B.SHIPPEDQTY WHEN B.STATUS=12 THEN B.SHIPPEDQTY WHEN B.STATUS=13 THEN B.SHIPPEDQTY WHEN B.STATUS=2 THEN B.SHIPPEDQTY WHEN B.STATUS=5 THEN B.RETURNEDQTY WHEN B.STATUS=4 THEN B.RETURNEDQTY WHEN B.STATUS=18 THEN B.RETURNEDQTY WHEN B.STATUS=19 THEN B.RETURNEDQTY WHEN B.STATUS=23 THEN B.SHIPPEDQTY WHEN B.STATUS=24 THEN B.SHIPPEDQTY WHEN B.STATUS=25 THEN B.RETURNEDQTY WHEN B.STATUS=20 THEN B.SHIPPEDQTY WHEN B.STATUS=21 THEN B.SHIPPEDQTY WHEN B.STATUS=22 THEN B.RETURNEDQTY END,0) FILL, CASE WHEN B.STATUS=3 THEN 'Reserved' WHEN B.STATUS=4 THEN 'Reserved' WHEN B.STATUS=12 THEN 'Reserved' WHEN B.STATUS=13 THEN 'Reserved' WHEN B.STATUS=14 THEN 'NA' WHEN B.STATUS=16 THEN 'Reserved' WHEN B.STATUS=5 THEN 'Reserved' WHEN B.STATUS=18 THEN 'Reserved' WHEN B.STATUS=19 THEN 'Reserved' WHEN B.STATUS=2 THEN 'Reserved' WHEN B.STATUS=6 THEN 'S-Rent' WHEN B.STATUS=9 THEN 'S-Assign' WHEN B.STATUS=23 THEN 'S-Filled' WHEN B.STATUS=24 THEN 'S-Filled' WHEN B.STATUS=25 THEN 'S-Filled' WHEN B.STATUS= 7 THEN 'Transfer' WHEN B.STATUS=10 THEN 'T-Assign' WHEN B.STATUS=20 THEN 'T-Filled' WHEN B.STATUS=21 THEN 'T-Filled' WHEN B.STATUS=22 THEN 'T-Filled' WHEN B.STATUS=27 THEN 'Reserved' WHEN B.STATUS=28 THEN 'Transfer' WHEN B.STATUS=8 THEN 'Purchase' WHEN B.STATUS=11 THEN 'P-Assign' END RENTTYPE ,I.COMPANYNAME,G.POID,E.SHELFLOCATION,E.BINNUMBER FROM CONTRACTLINESERIAL B, CONTRACT C, PARAMS D, CONTRACTLINE E,APPOLINE F,APPO G,CONTACT H,CUSTOMER I WHERE B.CONTRACTIID = C.CONTRACTIID AND C.STATUS<>3 AND C.TYPE<>0 AND B.CONLINEIID=F.CONLINEIID(+) AND F.POIID=G.POIID(+) AND G.VENDORCONTACTIID=H.CONTACTIID(+) AND H.CUSTOMERIID=I.CUSTOMERIID(+) AND E.CONLINEIID(+) = B.CONLINEIID AND C.CONTRACTID = D.STR AND B.STATUS NOT IN (15, 26) AND B.SHIPPINGSTATUS <> 6 AND NVL(E.LEVELNO, 1) = 1 AND NVL(GETISMISCITEM(B.INVIID),0) <> 1 AND CASE WHEN :B2 =1 THEN E.STARTDATE WHEN :B2 =2 THEN GETPERIODDATE(E.CONLINEIID,1,2) WHEN :B2 =3 THEN GETPERIODDATE(E.CONLINEIID,7,2) WHEN :B2 =4 THEN E.ENDDATE ELSE TRUNC(SYSDATE) END BETWEEN CASE WHEN :B2 =0 THEN TRUNC(SYSDATE) ELSE :B3 END AND CASE WHEN :B2 =0 THEN TRUNC(SYSDATE) ELSE :B1 END) WHERE NVL(INVIID, 0)<>0 GROUP BY CONTRACTIID, CONTRACTID, INVIID, DESCRIPTION, SKU, RENTTYPE, ITEMTYPE,COMPANYNAME,POID,SHELFLOCATION,BINNUMBER call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.10 0.06 0 10 0 0 Fetch 1 39.89 39.74 2 15437 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 40.00 39.81 2 15447 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 HASH GROUP BY (cr=1479710 pr=16 pw=0 time=0 us cost=27318 size=2488892 card=6041) 0 FILTER (cr=1479710 pr=16 pw=0 time=0 us) 0 FILTER (cr=1479710 pr=16 pw=0 time=0 us) 5 NESTED LOOPS OUTER (cr=1479710 pr=16 pw=0 time=112 us cost=26319 size=8715860 card=21155) 5 HASH JOIN OUTER (cr=1479693 pr=14 pw=0 time=20 us cost=5155 size=7065770 card=21155) 5 HASH JOIN RIGHT OUTER (cr=1478556 pr=14 pw=0 time=48 us cost=4379 size=4632945 card=21155) 40347 TABLE ACCESS FULL CONTACT (cr=869 pr=0 pw=0 time=13533 us cost=207 size=403260 card=40326) 5 HASH JOIN RIGHT OUTER (cr=1477687 pr=14 pw=0 time=40 us cost=4171 size=4421395 card=21155) 6042 TABLE ACCESS FULL APPO (cr=253 pr=0 pw=0 time=2517 us cost=69 size=132902 card=6041) 5 HASH JOIN RIGHT OUTER (cr=1477434 pr=14 pw=0 time=28 us cost=4101 size=3955985 card=21155) 21614 VIEW index$_join$_006 (cr=146 pr=0 pw=0 time=32099 us cost=141 size=194526 card=21614) 21614 HASH JOIN (cr=146 pr=0 pw=0 time=19950 us) 21614 INDEX FAST FULL SCAN IDX_APPOLINECONLINEIID (cr=56 pr=0 pw=0 time=8184 us cost=66 size=194526 card=21614)(object id 75829) 35987 INDEX FAST FULL SCAN IDX_APPOLINEPOIID (cr=90 pr=0 pw=0 time=14675 us cost=109 size=194526 card=21614)(object id 75831) 5 HASH JOIN (cr=1477288 pr=14 pw=0 time=12 us cost=3959 size=3765412 card=21154) 485531 TABLE ACCESS FULL CONTRACTLINESERIAL (cr=1477278 pr=14 pw=0 time=48925020 us cost=3541 size=2200016 card=21154) 1 NESTED LOOPS (cr=10 pr=0 pw=0 time=0 us) 1 NESTED LOOPS (cr=9 pr=0 pw=0 time=0 us cost=4 size=5383722 card=72753) 1 TABLE ACCESS FULL PARAMS (cr=7 pr=0 pw=0 time=0 us cost=3 size=27 card=1) 1 INDEX UNIQUE SCAN IDX_CONTRACTCONTRACTID (cr=2 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 75926) 1 TABLE ACCESS BY INDEX ROWID CONTRACT (cr=1 pr=0 pw=0 time=0 us cost=1 size=3419391 card=72753) 35351 TABLE ACCESS FULL CUSTOMER (cr=1137 pr=0 pw=0 time=15314 us cost=309 size=4500525 card=39135) 5 TABLE ACCESS BY INDEX ROWID CONTRACTLINE (cr=17 pr=2 pw=0 time=0 us cost=1 size=78 card=1) 5 INDEX UNIQUE SCAN PK_CONTRACTLINE (cr=12 pr=2 pw=0 time=0 us cost=0 size=0 card=1)(object id 75956) ******************************************************************************** SQL ID: dbrc67dcmwwh4 Plan Hash: 3869505815 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"), NVL(SUM(C2),:"SYS_B_01"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_02" ELSE :"SYS_B_03" END),:"SYS_B_04") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("B") FULL("B") NO_PARALLEL_INDEX("B") */ :"SYS_B_05" AS C1, CASE WHEN NVL("B"."INVIID", :"SYS_B_06")<>:"SYS_B_07" AND "B"."STATUS"<>:"SYS_B_08" AND "B"."STATUS"<>:"SYS_B_09" AND "B"."SHIPPINGSTATUS"<>:"SYS_B_10" THEN :"SYS_B_11" ELSE :"SYS_B_12" END AS C2, "B"."CONTRACTIID" AS C3 FROM "CONTRACTLINESERIAL" SAMPLE BLOCK (:"SYS_B_13" , :"SYS_B_14") SEED (:"SYS_B_15") "B") SAMPLESUB 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 1 0.00 0.00 0 60 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 60 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=60 pr=0 pw=0 time=0 us) 205 VIEW VW_DAG_0 (cr=60 pr=0 pw=0 time=204 us cost=20 size=267592 card=5146) 205 HASH GROUP BY (cr=60 pr=0 pw=0 time=102 us cost=20 size=329344 card=5146) 3059 TABLE ACCESS SAMPLE CONTRACTLINESERIAL (cr=60 pr=0 pw=0 time=1274 us cost=19 size=329344 card=5146) ******************************************************************************** SQL ID: 2syvqzbxp4k9z Plan Hash: 1423211129 select u.name, o.name, a.interface_version#, o.obj# from association$ a, user$ u, obj$ o where a.obj# = :1 and a.property = :2 and a.statstype# = o.obj# and u.user# = o.owner# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 9 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 9 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS (cr=3 pr=0 pw=0 time=0 us cost=5 size=66 card=1) 0 NESTED LOOPS (cr=3 pr=0 pw=0 time=0 us cost=4 size=49 card=1) 0 TABLE ACCESS FULL ASSOCIATION$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=16 card=1) 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=33 card=1) 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 36) 0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=17 card=1) 0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 11) ******************************************************************************** SQL ID: 2xyb5d6xg9srh Plan Hash: 785096182 select a.default_cpu_cost, a.default_io_cost from association$ a where a.obj# = :1 and a.property = :2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 9 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 9 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS FULL ASSOCIATION$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=12 card=1) ******************************************************************************** SQL ID: 72mggd9148wp6 Plan Hash: 835741039 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"), NVL(SUM(C2),:"SYS_B_01"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_02" ELSE :"SYS_B_03" END),:"SYS_B_04") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("C") FULL("C") NO_PARALLEL_INDEX("C") */ :"SYS_B_05" AS C1, CASE WHEN "C"."STATUS"<>:"SYS_B_06" AND "C"."TYPE"<>:"SYS_B_07" THEN :"SYS_B_08" ELSE :"SYS_B_09" END AS C2, "C"."CONTRACTIID" AS C3 FROM "CONTRACT" SAMPLE BLOCK (:"SYS_B_10" , :"SYS_B_11") SEED (:"SYS_B_12") "C") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.00 0 126 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.01 0.00 0 126 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT GROUP BY (cr=63 pr=0 pw=0 time=0 us) 1008 TABLE ACCESS SAMPLE CONTRACT (cr=63 pr=0 pw=0 time=1007 us cost=19 size=262446 card=5146) ******************************************************************************** SQL ID: 5u7zqddj0avk9 Plan Hash: 410580630 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4") FROM (SELECT /*+ NO_PARALLEL("D") FULL("D") NO_PARALLEL_INDEX("D") */ :"SYS_B_5" AS C1, :"SYS_B_6" AS C2, "D"."STR" AS C3 FROM "PARAMS" "D") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 21 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 21 0 3 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us) 1 VIEW VW_DAG_0 (cr=7 pr=0 pw=0 time=0 us cost=4 size=26994 card=409) 1 HASH GROUP BY (cr=7 pr=0 pw=0 time=0 us cost=4 size=11043 card=409) 1 TABLE ACCESS FULL PARAMS (cr=7 pr=0 pw=0 time=0 us cost=3 size=11043 card=409) ******************************************************************************** SQL ID: dn6cua3ufubdp Plan Hash: 2612424869 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4") FROM (SELECT /*+ NO_PARALLEL("E") FULL("E") NO_PARALLEL_INDEX("E") */ :"SYS_B_5" AS C1, :"SYS_B_6" AS C2, "E"."CONLINEIID" AS C3 FROM "CONTRACTLINE" SAMPLE BLOCK (:"SYS_B_7" , :"SYS_B_8") SEED (:"SYS_B_9") "E") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.00 0 192 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.01 0.01 0 192 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=96 pr=0 pw=0 time=0 us) 1930 VIEW VW_DAG_0 (cr=96 pr=0 pw=0 time=1929 us cost=20 size=267592 card=5146) 1930 HASH GROUP BY (cr=96 pr=0 pw=0 time=843 us cost=20 size=128650 card=5146) 1930 TABLE ACCESS SAMPLE CONTRACTLINE (cr=96 pr=0 pw=0 time=602 us cost=19 size=128650 card=5146) ******************************************************************************** SQL ID: 3rq1q9r4fn0cc Plan Hash: 1019966289 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4") FROM (SELECT /*+ NO_PARALLEL("I") FULL("I") NO_PARALLEL_INDEX("I") */ :"SYS_B_5" AS C1, :"SYS_B_6" AS C2, "I"."CUSTOMERIID" AS C3 FROM "CUSTOMER" SAMPLE BLOCK (:"SYS_B_7" , :"SYS_B_8") SEED (:"SYS_B_9") "I") SAMPLESUB 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 1 0.00 0.00 0 69 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 69 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=69 pr=0 pw=0 time=0 us) 2178 VIEW VW_DAG_0 (cr=69 pr=0 pw=0 time=2297 us cost=20 size=267592 card=5146) 2178 HASH GROUP BY (cr=69 pr=0 pw=0 time=1209 us cost=20 size=128650 card=5146) 2178 TABLE ACCESS SAMPLE CUSTOMER (cr=69 pr=0 pw=0 time=1330 us cost=19 size=128650 card=5146) ******************************************************************************** SQL ID: dcstr36r0vz0d Plan Hash: 3798950322 select procedure#,procedurename,properties,itypeobj# from procedureinfo$ where obj#=:1 order by procedurename desc, overload# desc call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.00 0 4 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID PROCEDUREINFO$ (cr=4 pr=0 pw=0 time=0 us cost=3 size=231 card=7) 1 INDEX RANGE SCAN DESCENDING I_PROCEDUREINFO1 (cr=3 pr=0 pw=0 time=0 us cost=2 size=0 card=7)(object id 231) ******************************************************************************** SQL ID: 32hbap2vtmf53 Plan Hash: 3421168214 select position#,sequence#,level#,argument,type#,charsetid,charsetform, properties,nvl(length, 0), nvl(precision#, 0),nvl(scale, 0),nvl(radix, 0), type_owner,type_name,type_subname,type_linkname,pls_type from argument$ where obj#=:1 and procedure#=:2 order by sequence# desc 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 3 0.00 0.00 0 7 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 0.00 0 7 0 2 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 2 TABLE ACCESS BY INDEX ROWID ARGUMENT$ (cr=7 pr=0 pw=0 time=0 us cost=4 size=74 card=1) 2 INDEX RANGE SCAN DESCENDING I_ARGUMENT2 (cr=5 pr=0 pw=0 time=5 us cost=3 size=0 card=1)(object id 233) ******************************************************************************** SQL ID: 8wutkrpf8j81q Plan Hash: 1011998884 select max(procedure#) from procedurec$ where obj#=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.00 0 2 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=0 us) 0 FIRST ROW (cr=2 pr=0 pw=0 time=0 us cost=2 size=9 card=1) 0 INDEX RANGE SCAN (MIN/MAX) I_PROCEDUREC$ (cr=2 pr=0 pw=0 time=0 us cost=2 size=9 card=1)(object id 248) ******************************************************************************** SQL ID: 2xgubd6ayhyb1 Plan Hash: 3418045132 select max(procedure#) from procedureplsql$ where obj#=:1 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 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=2 pr=0 pw=0 time=0 us) 1 FIRST ROW (cr=2 pr=0 pw=0 time=0 us cost=2 size=9 card=1) 1 INDEX RANGE SCAN (MIN/MAX) I_PROCEDUREPLSQL$ (cr=2 pr=0 pw=0 time=0 us cost=2 size=9 card=1)(object id 250) ******************************************************************************** SQL ID: 5km6gkvuh3n43 Plan Hash: 2075413643 select max(procedure#) from procedurejava$ where obj#=:1 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 1 0.00 0.00 0 1 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 1 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1 pr=0 pw=0 time=0 us) 0 FIRST ROW (cr=1 pr=0 pw=0 time=0 us cost=1 size=9 card=1) 0 INDEX RANGE SCAN (MIN/MAX) I_PROCEDUREJAVA$ (cr=1 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 246) ******************************************************************************** SQL ID: 971vpk0tp7ahm Plan Hash: 726556434 select procedure#,entrypoint# from procedurec$ where obj#=:1 order by procedure# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.00 0 2 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID PROCEDUREC$ (cr=2 pr=0 pw=0 time=0 us cost=3 size=66 card=6) 0 INDEX RANGE SCAN I_PROCEDUREC$ (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=6)(object id 248) ******************************************************************************** SQL ID: bgjhtnqhr5u9h Plan Hash: 4040384485 select procedure#,entrypoint# from procedureplsql$ where obj#=:1 order by procedure# 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 2 0.00 0.00 1 4 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 1 4 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID PROCEDUREPLSQL$ (cr=4 pr=1 pw=0 time=0 us cost=3 size=66 card=6) 1 INDEX RANGE SCAN I_PROCEDUREPLSQL$ (cr=3 pr=0 pw=0 time=0 us cost=2 size=0 card=6)(object id 250) ******************************************************************************** SQL ID: 5j4c2v06qdhqq Plan Hash: 423935871 select procedure#,ownerlength,classlength,methodlength,siglength, flagslength, cookiesize from procedurejava$ where obj#=:1 order by procedure# 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 1 0.00 0.00 0 1 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 1 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID PROCEDUREJAVA$ (cr=1 pr=0 pw=0 time=0 us cost=2 size=232 card=8) 0 INDEX RANGE SCAN I_PROCEDUREJAVA$ (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=8)(object id 246) ******************************************************************************** SQL ID: 83cq1aqjw5gmg Plan Hash: 0 select ownername,classname,methodname,signature,flags from procedurejava$ where obj#=:1 and procedure#=:2 order by procedure# call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 0 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: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 2) ******************************************************************************** SQL ID: b2n8nag766s8h Plan Hash: 3487493439 SELECT ISMISCITEM FROM INVMASTER WHERE INVIID=:B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 487918 5.45 5.49 0 0 0 0 Fetch 487918 3.98 4.32 0 1463754 0 487918 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 975837 9.43 9.81 0 1463754 0 487918 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID INVMASTER (cr=3 pr=0 pw=0 time=0 us cost=2 size=16 card=1) 1 INDEX UNIQUE SCAN PK_INVMASTER (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 76249) ******************************************************************************** *** 2012-01-10 13:01:19.288 SELECT ISMISCITEM FROM INVMASTER W 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 1 0.00 0.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 3 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) ******************************************************************************** *** 2012-01-10 13:01:21.319 SELECT ISMISCITEM FROM INVMASTER W 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 1 0.00 0.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 3 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) ******************************************************************************** *** 2012-01-10 13:01:22.335 SELECT ISMISCITEM FROM INVMASTER W 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 1 0.00 0.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 3 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) ******************************************************************************** *** 2012-01-10 13:01:47.730 SELECT ISMISCITEM FROM INVMASTER W 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 1 0.00 0.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 3 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) ******************************************************************************** *** 2012-01-10 13:02:06.014 SELECT ISMISCITEM FROM INVMASTER W 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 1 0.00 0.00 0 3 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 3 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) ******************************************************************************** SQL ID: dxqyws8q2frvf Plan Hash: 2237508096 SELECT CONTRACTID, CONTRACTIID, INVIID, SUBSTR(DESCRIPT,0,100) DESCRIPTION, SKU, ITEMTYPE, NVL(SUM(ORD),0) ORDQTY, NVL(SUM(FILL), 0) FILLQTY, RENTTYPE, LINENO, CONLINEIID,SHELFLOCATION,BINNUMBER FROM ( SELECT C.CONTRACTID, C.CONTRACTIID, B.INVIID, GETPRODUCTSKU(B.INVIID) SKU, CASE WHEN GETISMISCITEM(B.INVIID)=NULL THEN B.DESCRIPTION WHEN GETISMISCITEM(B.INVIID)='0' THEN GETPRODUCTDESCRIPTION(B.INVIID,:B4 ) WHEN GETISMISCITEM(B.INVIID)='1' THEN NVL(B.DESCRIPTION, GETPRODUCTDESCRIPTION(B.INVIID,:B4 )) END DESCRIPT, CASE WHEN GETPRODUCTQTYSERIAL(B.INVIID)=101 THEN 'Non-Serial' WHEN GETPRODUCTQTYSERIAL(B.INVIID)=102 THEN 'Serial' END ITEMTYPE, NVL(CASE WHEN E.STATUS=3 THEN ORDEREDQTY WHEN E.STATUS=4 THEN ORDEREDQTY WHEN E.STATUS=6 THEN ORDEREDQTY WHEN E.STATUS=9 THEN ORDEREDQTY WHEN E.STATUS=7 THEN ORDEREDQTY WHEN E.STATUS=10 THEN ORDEREDQTY WHEN E.STATUS=16 THEN ORDEREDQTY WHEN E.STATUS=5 THEN ORDEREDQTY WHEN E.STATUS=18 THEN ORDEREDQTY WHEN E.STATUS=19 THEN ORDEREDQTY WHEN E.STATUS=2 THEN ORDEREDQTY WHEN E.STATUS=12 THEN ORDEREDQTY WHEN E.STATUS=13 THEN ORDEREDQTY WHEN E.STATUS= 14 THEN ORDEREDQTY WHEN E.STATUS=23 THEN ORDEREDQTY WHEN E.STATUS=24 THEN ORDEREDQTY WHEN E.STATUS=25 THEN ORDEREDQTY WHEN E.STATUS=20 THEN ORDEREDQTY WHEN E.STATUS=21 THEN ORDEREDQTY WHEN E.STATUS=22 THEN ORDEREDQTY WHEN E.STATUS=28 THEN ORDEREDQTY WHEN E.STATUS=8 THEN ORDEREDQTY WHEN E.STATUS=11 THEN ORDEREDQTY END,0) ORD, NVL(CASE WHEN E.STATUS=16 THEN SHIPPEDQTY WHEN E.STATUS=12 THEN SHIPPEDQTY WHEN E.STATUS=13 THEN SHIPPEDQTY WHEN E.STATUS=2 THEN SHIPPEDQTY WHEN E.STATUS=5 THEN RETURNEDQTY WHEN E.STATUS=4 THEN RETURNEDQTY WHEN E.STATUS=18 THEN RETURNEDQTY WHEN E.STATUS=19 THEN RETURNEDQTY WHEN E.STATUS=23 THEN SHIPPEDQTY WHEN E.STATUS= 24 THEN SHIPPEDQTY WHEN E.STATUS=25 THEN RETURNEDQTY WHEN E.STATUS=20 THEN SHIPPEDQTY WHEN E.STATUS=21 THEN SHIPPEDQTY WHEN E.STATUS=22 THEN RETURNEDQTY END,0) FILL, CASE WHEN E.STATUS=3 THEN 'Reserved' WHEN E.STATUS= 4 THEN 'Reserved' WHEN E.STATUS=12 THEN 'Reserved' WHEN E.STATUS=13 THEN 'Reserved' WHEN E.STATUS=14 THEN 'NA' WHEN E.STATUS=16 THEN 'Reserved' WHEN E.STATUS=5 THEN 'Reserved' WHEN E.STATUS=18 THEN 'Reserved' WHEN E.STATUS= 19 THEN 'Reserved' WHEN E.STATUS=2 THEN 'Reserved' WHEN E.STATUS=6 THEN 'S-Rent' WHEN E.STATUS=9 THEN 'S-Assign' WHEN E.STATUS=23 THEN 'S-Filled' WHEN E.STATUS=24 THEN 'S-Filled' WHEN E.STATUS=25 THEN 'S-Filled' WHEN E.STATUS=7 THEN 'Transfer' WHEN E.STATUS=10 THEN 'T-Assign' WHEN E.STATUS= 20 THEN 'T-Filled' WHEN E.STATUS=21 THEN 'T-Filled' WHEN E.STATUS=22 THEN 'T-Filled' WHEN E.STATUS=27 THEN 'Reserved' WHEN E.STATUS=28 THEN 'Transfer' WHEN E.STATUS=8 THEN 'Purchase' WHEN E.STATUS=11 THEN 'P-Assign' END RENTTYPE, B.LINENO, B.CONLINEIID,B.SHELFLOCATION,B.BINNUMBER FROM CONTRACTLINE B, CONTRACT C,PARAMS D, CONTRACTLINESERIAL E WHERE B.CONTRACTIID = C.CONTRACTIID AND C.STATUS<>3 AND C.TYPE<>0 AND B.CONTRACTIID = E.CONTRACTIID(+) AND C.CONTRACTID = D.STR AND B.CONLINEIID = E.CONLINEIID(+) AND NVL(E.STATUS,15) NOT IN (26) AND NVL(E.SHIPPINGSTATUS, 0) <> 6 AND CASE WHEN :B2 =1 THEN B.STARTDATE WHEN :B2 =2 THEN GETPERIODDATE(B.CONLINEIID,1,2) WHEN :B2 =3 THEN GETPERIODDATE(B.CONLINEIID, 7,2) WHEN :B2 =4 THEN B.ENDDATE ELSE TRUNC(SYSDATE) END BETWEEN CASE WHEN :B2 =0 THEN TRUNC(SYSDATE) ELSE :B3 END AND CASE WHEN :B2 =0 THEN TRUNC(SYSDATE) ELSE :B1 END) WHERE INVIID<>0 GROUP BY CONTRACTIID, CONTRACTID, LINENO, INVIID, DESCRIPT, SKU, RENTTYPE, ITEMTYPE, CONLINEIID, SHELFLOCATION,BINNUMBER ORDER BY LINENO call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.02 0 10 0 0 Fetch 1 1.25 1.42 2531 82677 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 1.26 1.44 2531 82687 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT GROUP BY (cr=82677 pr=2531 pw=0 time=0 us cost=26632 size=1916016 card=5352) 0 FILTER (cr=82677 pr=2531 pw=0 time=0 us) 0 FILTER (cr=82677 pr=2531 pw=0 time=0 us) 0 HASH JOIN OUTER (cr=82677 pr=2531 pw=0 time=0 us cost=26631 size=1916016 card=5352) 0 HASH JOIN (cr=82677 pr=2531 pw=0 time=0 us cost=23088 size=1428984 card=5352) 0 TABLE ACCESS FULL CONTRACTLINE (cr=82677 pr=2531 pw=0 time=0 us cost=23083 size=1032936 card=5352) 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us) 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=4 size=5383722 card=72753) 0 TABLE ACCESS FULL PARAMS (cr=0 pr=0 pw=0 time=0 us cost=3 size=27 card=1) 0 INDEX UNIQUE SCAN IDX_CONTRACTCONTRACTID (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 75926) 0 TABLE ACCESS BY INDEX ROWID CONTRACT (cr=0 pr=0 pw=0 time=0 us cost=1 size=3419391 card=72753) 0 TABLE ACCESS FULL CONTRACTLINESERIAL (cr=0 pr=0 pw=0 time=0 us cost=3539 size=57450029 card=631319) ******************************************************************************** SQL ID: 4n7bqxkwfk8tr Plan Hash: 2612424869 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"), NVL(SUM(C2),:"SYS_B_01"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_02" ELSE :"SYS_B_03" END),:"SYS_B_04") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("B") FULL("B") NO_PARALLEL_INDEX("B") */ :"SYS_B_05" AS C1, CASE WHEN "B"."INVIID"<>:"SYS_B_06" THEN :"SYS_B_07" ELSE :"SYS_B_08" END AS C2, "B"."CONTRACTIID" AS C3 FROM "CONTRACTLINE" SAMPLE BLOCK (:"SYS_B_09" , :"SYS_B_10") SEED (:"SYS_B_11") "B") SAMPLESUB 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 1 0.00 0.00 0 96 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 96 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=96 pr=0 pw=0 time=0 us) 320 VIEW VW_DAG_0 (cr=96 pr=0 pw=0 time=319 us cost=20 size=267592 card=5146) 320 HASH GROUP BY (cr=96 pr=0 pw=0 time=212 us cost=20 size=195548 card=5146) 1930 TABLE ACCESS SAMPLE CONTRACTLINE (cr=96 pr=0 pw=0 time=1205 us cost=19 size=195548 card=5146) ******************************************************************************** SQL ID: 1u5z4hur5cspz Plan Hash: 3869505815 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4") FROM (SELECT /*+ NO_PARALLEL("E") FULL("E") NO_PARALLEL_INDEX("E") */ :"SYS_B_5" AS C1, :"SYS_B_6" AS C2, "E"."CONTRACTIID" AS C3 FROM "CONTRACTLINESERIAL" SAMPLE BLOCK (:"SYS_B_7" , :"SYS_B_8") SEED (:"SYS_B_9") "E") SAMPLESUB 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 1 0.00 0.00 0 60 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 60 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=60 pr=0 pw=0 time=0 us) 205 VIEW VW_DAG_0 (cr=60 pr=0 pw=0 time=204 us cost=20 size=267592 card=5146) 205 HASH GROUP BY (cr=60 pr=0 pw=0 time=102 us cost=20 size=128650 card=5146) 3059 TABLE ACCESS SAMPLE CONTRACTLINESERIAL (cr=60 pr=0 pw=0 time=1274 us cost=19 size=128650 card=5146) ******************************************************************************** SQL ID: 92x9x7bv37rzy Plan Hash: 2262553975 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"), NVL(SUM(C2),:"SYS_B_01"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_02" ELSE :"SYS_B_03" END),:"SYS_B_04") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("A") FULL("A") NO_PARALLEL_INDEX("A") */ :"SYS_B_05" AS C1, CASE WHEN TO_NUMBER(NVL("A"."ISMISCITEM",:"SYS_B_06"))=:"SYS_B_07" THEN :"SYS_B_08" ELSE :"SYS_B_09" END AS C2, "A"."INVIID" AS C3 FROM "INVMASTER" SAMPLE BLOCK (:"SYS_B_10" , :"SYS_B_11") SEED (:"SYS_B_12") "A") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 62 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.00 0 62 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=62 pr=0 pw=0 time=0 us) 1271 VIEW VW_DAG_0 (cr=62 pr=0 pw=0 time=1397 us cost=20 size=267592 card=5146) 1271 HASH GROUP BY (cr=62 pr=0 pw=0 time=254 us cost=20 size=144088 card=5146) 1271 TABLE ACCESS SAMPLE INVMASTER (cr=62 pr=0 pw=0 time=889 us cost=19 size=144088 card=5146) ******************************************************************************** SQL ID: 19n41rn8tt1f7 Plan Hash: 3869505815 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"), NVL(SUM(C2),:"SYS_B_01"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_02" ELSE :"SYS_B_03" END),:"SYS_B_04") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("B") FULL("B") NO_PARALLEL_INDEX("B") */ :"SYS_B_05" AS C1, CASE WHEN "B"."STATUS"<>:"SYS_B_06" AND "B"."SHIPPINGSTATUS"<>:"SYS_B_07" AND "B"."CONLINEIID" IS NULL THEN :"SYS_B_08" ELSE :"SYS_B_09" END AS C2, "B"."INVIID" AS C3 FROM "CONTRACTLINESERIAL" SAMPLE BLOCK (:"SYS_B_10" , :"SYS_B_11") SEED (:"SYS_B_12") "B") SAMPLESUB 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 1 0.00 0.00 0 60 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 60 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=60 pr=0 pw=0 time=0 us) 779 VIEW VW_DAG_0 (cr=60 pr=0 pw=0 time=1111 us cost=20 size=267592 card=5146) 779 HASH GROUP BY (cr=60 pr=0 pw=0 time=555 us cost=20 size=329344 card=5146) 3059 TABLE ACCESS SAMPLE CONTRACTLINESERIAL (cr=60 pr=0 pw=0 time=1529 us cost=19 size=329344 card=5146) ******************************************************************************** SQL ID: 13a7g3tnxr4u4 Plan Hash: 835741039 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4") FROM (SELECT /*+ NO_PARALLEL("C") FULL("C") NO_PARALLEL_INDEX("C") */ :"SYS_B_5" AS C1, :"SYS_B_6" AS C2, "C"."CONTRACTIID" AS C3 FROM "CONTRACT" SAMPLE BLOCK (:"SYS_B_7" , :"SYS_B_8") SEED (:"SYS_B_9") "C") SAMPLESUB 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 1 0.00 0.00 0 63 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 63 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT GROUP BY (cr=63 pr=0 pw=0 time=0 us) 1008 TABLE ACCESS SAMPLE CONTRACT (cr=63 pr=0 pw=0 time=1132 us cost=19 size=128650 card=5146) ******************************************************************************** SQL ID: 4cp93u5prnnvd Plan Hash: 1435395180 SELECT C.CONTRACTID, C.CONTRACTIID, A.INVIID, A.DESCRIPTION, A.SKU, DECODE(A.QTYSERIAL, 101, 'Non-Serial', 102, 'Serial') ITEMTYPE, NVL(DECODE(B.STATUS, 3, (ORDEREDQTY), 16, (ORDEREDQTY), 26, (ORDEREDQTY)), 0) ORDQTY, NVL(DECODE(B.STATUS, 16, (SHIPPEDQTY)), 0) FILLQTY,E.BINNUMBER, E.SHELFLOCATION FROM INVMASTER A, CONTRACTLINESERIAL B, CONTRACT C, PARAMS D, CONTRACTLINE E WHERE A.INVIID = B.INVIID AND B.CONTRACTIID = C.CONTRACTIID AND E.CONLINEIID(+) = B.CONLINEIID AND C.CONTRACTID = D.STR AND B.STATUS <> 14 AND B.SHIPPINGSTATUS <> 6 AND B.CONLINEIID IS NULL AND NVL(A.ISMISCITEM,0) = 1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 8 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 1 3 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 1 11 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS OUTER (cr=3 pr=1 pw=0 time=0 us cost=95 size=1585 card=5) 0 NESTED LOOPS (cr=3 pr=1 pw=0 time=0 us cost=90 size=1415 card=5) 0 HASH JOIN (cr=3 pr=1 pw=0 time=0 us cost=11 size=10981 card=79) 0 TABLE ACCESS BY INDEX ROWID CONTRACTLINESERIAL (cr=3 pr=1 pw=0 time=0 us cost=6 size=7189 card=79) 0 INDEX RANGE SCAN IDX_CLSACTIONSTATUSINVIID (cr=3 pr=1 pw=0 time=0 us cost=3 size=0 card=1578)(object id 75986) 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us) 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=4 size=6458880 card=134560) 0 TABLE ACCESS FULL PARAMS (cr=0 pr=0 pw=0 time=0 us cost=3 size=27 card=1) 0 INDEX UNIQUE SCAN IDX_CONTRACTCONTRACTID (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 75926) 0 TABLE ACCESS BY INDEX ROWID CONTRACT (cr=0 pr=0 pw=0 time=0 us cost=1 size=2825760 card=134560) 0 TABLE ACCESS BY INDEX ROWID INVMASTER (cr=0 pr=0 pw=0 time=0 us cost=1 size=144 card=1) 0 INDEX UNIQUE SCAN PK_INVMASTER (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 76249) 0 TABLE ACCESS BY INDEX ROWID CONTRACTLINE (cr=0 pr=0 pw=0 time=0 us cost=1 size=34 card=1) 0 INDEX UNIQUE SCAN PK_CONTRACTLINE (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 75956) ******************************************************************************** SQL ID: fun5r6xrk5ds4 Plan Hash: 1962589811 SELECT COUNT(*) FROM GENERICREPORT2 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 8 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL GENERICREPORT2 (cr=7 pr=0 pw=0 time=0 us cost=3 size=0 card=1) ******************************************************************************** SQL ID: 9185gtcv2n9j4 Plan Hash: 410580630 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4") FROM (SELECT /*+ NO_PARALLEL("A") FULL("A") NO_PARALLEL_INDEX("A") */ :"SYS_B_5" AS C1, :"SYS_B_6" AS C2, "A"."STR" AS C3 FROM "PARAMS" "A") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 3 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 21 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 21 0 3 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us) 1 VIEW VW_DAG_0 (cr=7 pr=0 pw=0 time=0 us cost=4 size=26994 card=409) 1 HASH GROUP BY (cr=7 pr=0 pw=0 time=0 us cost=4 size=11043 card=409) 1 TABLE ACCESS FULL PARAMS (cr=7 pr=0 pw=0 time=0 us cost=3 size=11043 card=409) ******************************************************************************** SQL ID: 3ncytpnnh0v0w Plan Hash: 835741039 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4") FROM (SELECT /*+ NO_PARALLEL("B") FULL("B") NO_PARALLEL_INDEX("B") */ :"SYS_B_5" AS C1, :"SYS_B_6" AS C2, "B"."CONTRACTID" AS C3 FROM "CONTRACT" SAMPLE BLOCK (:"SYS_B_7" , :"SYS_B_8") SEED (:"SYS_B_9") "B") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 3 0.01 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 189 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.01 0.00 0 189 0 3 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT GROUP BY (cr=63 pr=0 pw=0 time=0 us) 1008 TABLE ACCESS SAMPLE CONTRACT (cr=63 pr=0 pw=0 time=755 us cost=19 size=102920 card=5146) ******************************************************************************** SQL ID: 59mqbb3u7scnx Plan Hash: 1260682331 SELECT CONTRACTIID FROM PARAMS A,CONTRACT B WHERE A.STR=B.CONTRACTID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 3 2 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 10 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 13 2 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=10 pr=0 pw=0 time=0 us) 1 NESTED LOOPS (cr=9 pr=0 pw=0 time=0 us cost=4 size=48 card=1) 1 TABLE ACCESS FULL PARAMS (cr=7 pr=0 pw=0 time=0 us cost=3 size=27 card=1) 1 INDEX UNIQUE SCAN IDX_CONTRACTCONTRACTID (cr=2 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 75926) 1 TABLE ACCESS BY INDEX ROWID CONTRACT (cr=1 pr=0 pw=0 time=0 us cost=1 size=21 card=1) ******************************************************************************** SQL ID: 0hzwwjc95hrnp Plan Hash: 2018118908 SELECT CONLINESERIALIID FROM CONTRACTLINESERIAL WHERE CONTRACTIID = :B1 FOR UPDATE NOWAIT call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 1 5 6 0 Fetch 6 0.00 0.00 0 0 0 5 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 8 0.00 0.00 1 5 6 5 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 5 FOR UPDATE (cr=4 pr=1 pw=0 time=0 us) 10 BUFFER SORT (cr=4 pr=1 pw=0 time=8 us) 5 TABLE ACCESS BY INDEX ROWID CONTRACTLINESERIAL (cr=4 pr=1 pw=0 time=8 us cost=5 size=3718 card=143) 5 INDEX RANGE SCAN IDX_CONLINESERIALCONTRACTIID (cr=3 pr=1 pw=0 time=4 us cost=1 size=0 card=2525)(object id 75979) ******************************************************************************** SQL ID: 8w6vum3a0ah94 Plan Hash: 2436008989 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("CONTRACTLINESERIAL") FULL("CONTRACTLINESERIAL") NO_PARALLEL_INDEX("CONTRACTLINESERIAL") */ 1 AS C1, CASE WHEN "CONTRACTLINESERIAL"."CONTRACTIID"=:B1 THEN 1 ELSE 0 END AS C2 FROM "CONTRACTLINESERIAL" SAMPLE BLOCK (0.484541 , 1) SEED (1) "CONTRACTLINESERIAL") SAMPLESUB 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 1 0.00 0.00 0 60 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 60 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=60 pr=0 pw=0 time=0 us) 3059 TABLE ACCESS SAMPLE CONTRACTLINESERIAL (cr=60 pr=0 pw=0 time=1019 us cost=19 size=128650 card=5146) ******************************************************************************** SQL ID: 8g3kxtmv5qh27 Plan Hash: 458298550 UPDATE CONTRACTLINESERIAL SET DEPARTMENTID = '2' WHERE CONLINESERIALIID = :B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 2 0 Execute 5 0.00 0.00 1 15 5 5 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.00 1 15 7 5 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE CONTRACTLINESERIAL (cr=3 pr=1 pw=0 time=0 us) 1 INDEX UNIQUE SCAN PK_CONLINESERIAL (cr=3 pr=1 pw=0 time=0 us cost=1 size=35 card=1)(object id 75977) ******************************************************************************** SQL ID: 8ggw94h7mvxd7 Plan Hash: 0 COMMIT 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 1 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 1 0 Misses in library cache during parse: 0 Parsing user id: 65 (recursive depth: 1) ******************************************************************************** SQL ID: 73tst6y6bzg56 Plan Hash: 3936742166 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("GENERICREPORT") FULL("GENERICREPORT") NO_PARALLEL_INDEX("GENERICREPORT") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "GENERICREPORT" "GENERICREPORT") SAMPLESUB 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 1 0.01 0.01 3 53 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.01 3 53 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=53 pr=3 pw=0 time=0 us) 297 TABLE ACCESS FULL GENERICREPORT (cr=53 pr=3 pw=0 time=197 us cost=2 size=0 card=82) ******************************************************************************** SQL ID: ag2fmhvp6p7pg Plan Hash: 3936742166 SELECT MAX(LINENO)+1 FROM GENERICREPORT call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 2 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 53 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 54 2 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=53 pr=0 pw=0 time=0 us) 297 TABLE ACCESS FULL GENERICREPORT (cr=53 pr=0 pw=0 time=296 us cost=15 size=3861 card=297) ******************************************************************************** SQL ID: f51svrv2vyz47 Plan Hash: 628020070 SELECT * FROM GENERICREPORT WHERE AMT12=1 AND :B1 =IID ORDER BY STR1,LINENO call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 2 0 Execute 1 0.00 0.00 0 1 0 0 Fetch 1 0.00 0.00 0 53 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 54 2 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=53 pr=0 pw=0 time=0 us cost=16 size=60352 card=1) 0 TABLE ACCESS FULL GENERICREPORT (cr=53 pr=0 pw=0 time=0 us cost=15 size=60352 card=1) ******************************************************************************** SQL ID: 7kdw6t0zkrbnc Plan Hash: 3936742166 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("GENERICREPORT") FULL("GENERICREPORT") NO_PARALLEL_INDEX("GENERICREPORT") */ 1 AS C1, CASE WHEN "GENERICREPORT"."AMT12"=1 AND "GENERICREPORT"."IID"=:B1 THEN 1 ELSE 0 END AS C2 FROM "GENERICREPORT" "GENERICREPORT") SAMPLESUB 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 1 0.00 0.00 0 53 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 53 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=53 pr=0 pw=0 time=0 us) 297 TABLE ACCESS FULL GENERICREPORT (cr=53 pr=0 pw=0 time=98 us cost=2 size=2132 card=82) ******************************************************************************** SQL ID: g1wmz7cpj9jh4 Plan Hash: 2864636868 DELETE FROM GENERICREPORT WHERE IID=:B1 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 54 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 54 0 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE GENERICREPORT (cr=53 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL GENERICREPORT (cr=53 pr=0 pw=0 time=0 us cost=15 size=13 card=1) ******************************************************************************** SQL ID: 535430xpnd35y Plan Hash: 3936742166 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("GENERICREPORT") FULL("GENERICREPORT") NO_PARALLEL_INDEX("GENERICREPORT") */ 1 AS C1, CASE WHEN "GENERICREPORT"."IID"=:B1 THEN 1 ELSE 0 END AS C2 FROM "GENERICREPORT" "GENERICREPORT") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 2 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 53 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 53 2 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=53 pr=0 pw=0 time=0 us) 297 TABLE ACCESS FULL GENERICREPORT (cr=53 pr=0 pw=0 time=197 us cost=2 size=1066 card=82) ******************************************************************************** SQL ID: 421wgx5s31v80 Plan Hash: 122101700 INSERT INTO GENERICREPORT SELECT * FROM TEMPGENERICREPORT WHERE IID=:B1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 2 0 Execute 1 0.01 0.01 2 18 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.01 2 18 2 0 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 LOAD TABLE CONVENTIONAL (cr=16 pr=0 pw=0 time=0 us) 0 TABLE ACCESS FULL TEMPGENERICREPORT (cr=16 pr=0 pw=0 time=0 us cost=5 size=60352 card=1) ******************************************************************************** SQL ID: 7chzdcnsvs0qt Plan Hash: 22086749 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("TEMPGENERICREPORT") FULL("TEMPGENERICREPORT") NO_PARALLEL_INDEX("TEMPGENERICREPORT") */ 1 AS C1, CASE WHEN "TEMPGENERICREPORT"."IID"=:B1 THEN 1 ELSE 0 END AS C2 FROM "TEMPGENERICREPORT" "TEMPGENERICREPORT") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.01 13 16 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.01 13 16 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=16 pr=13 pw=0 time=0 us) 0 TABLE ACCESS FULL TEMPGENERICREPORT (cr=16 pr=13 pw=0 time=0 us cost=5 size=13806 card=1062) ******************************************************************************** SQL ID: 0gh49nnvz4p7f Plan Hash: 2344478153 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_2" ELSE :"SYS_B_3" END),:"SYS_B_4") FROM (SELECT /*+ NO_PARALLEL("C") FULL("C") NO_PARALLEL_INDEX("C") */ :"SYS_B_5" AS C1, :"SYS_B_6" AS C2, "C"."CONTRACTIID" AS C3 FROM "CONTRACTLABORLINE" SAMPLE BLOCK (:"SYS_B_7" , :"SYS_B_8") SEED (:"SYS_B_9") "C") SAMPLESUB 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 1 0.00 0.00 0 55 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 55 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=55 pr=0 pw=0 time=0 us) 771 VIEW VW_DAG_0 (cr=55 pr=0 pw=0 time=770 us cost=20 size=267592 card=5146) 771 HASH GROUP BY (cr=55 pr=0 pw=0 time=330 us cost=20 size=128650 card=5146) 1922 TABLE ACCESS SAMPLE CONTRACTLABORLINE (cr=55 pr=0 pw=0 time=1320 us cost=19 size=128650 card=5146) ******************************************************************************** SQL ID: 5vwncb0dhzvpu Plan Hash: 291582566 SELECT C.*,(C.AMOUNT-NVL(C.AMOUNT*(C.DISCOUNTPERCENT/100),0)) EXTENDEDPRICE, B.CONTRACTID,D.DESCRIPTION TASK FROM PARAMS A,CONTRACT B,CONTRACTLABORLINE C,CATEGORYMASTER D WHERE A.STR= B.CONTRACTID AND B.CONTRACTIID=C.CONTRACTIID AND C.CATEGORYIID= D.CATEGORYIID(+) AND NVL(D.TYPE,0)=0 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.01 1 5 2 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 1 20 0 6 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.02 2 25 2 6 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 6 FILTER (cr=20 pr=1 pw=0 time=60 us) 6 HASH JOIN RIGHT OUTER (cr=20 pr=1 pw=0 time=55 us cost=10 size=56544 card=57) 20 TABLE ACCESS FULL CATEGORYMASTER (cr=7 pr=0 pw=0 time=0 us cost=3 size=400 card=20) 6 NESTED LOOPS (cr=13 pr=1 pw=0 time=45 us) 6 NESTED LOOPS (cr=12 pr=1 pw=0 time=20 us cost=6 size=142884 card=147) 1 NESTED LOOPS (cr=10 pr=0 pw=0 time=0 us cost=4 size=48 card=1) 1 TABLE ACCESS FULL PARAMS (cr=7 pr=0 pw=0 time=0 us cost=3 size=27 card=1) 1 TABLE ACCESS BY INDEX ROWID CONTRACT (cr=3 pr=0 pw=0 time=0 us cost=1 size=21 card=1) 1 INDEX UNIQUE SCAN IDX_CONTRACTCONTRACTID (cr=2 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 75926) 6 INDEX RANGE SCAN IDX_CONTLABLINECONTIID (cr=2 pr=1 pw=0 time=5 us cost=1 size=0 card=6)(object id 75950) 6 TABLE ACCESS BY INDEX ROWID CONTRACTLABORLINE (cr=1 pr=0 pw=0 time=0 us cost=2 size=135828 card=147) ******************************************************************************** SQL ID: 7dsfkg0r6vp1h Plan Hash: 0 INSERT INTO GENERICREPORT(IID,STR1,STR2,STR3,STR4,STR5,STR6,STR7,STR8,STR9, STR10,STR11,STR12, DATE1,DATE2,STR29,LINENO) VALUES (:B16 ,:B15 ,:B14 , :B13 , CASE WHEN NVL(:B12 ,0)-FLOOR(NVL(:B12 ,0))=0 THEN NVL(:B12 ,0)||'.00' ELSE TO_CHAR(NVL(:B12 ,0)) END , CASE WHEN NVL(:B11 ,0) -FLOOR(NVL(:B11 ,0))=0 THEN NVL(:B11 ,0)||'.00' ELSE TO_CHAR(NVL(:B11 ,0)) END , :B10 , CASE WHEN NVL(:B9 ,0)-FLOOR(NVL(:B9 ,0))=0 THEN NVL(:B9 ,0) ||'.00' ELSE TO_CHAR(NVL(:B9 ,0)) END , CASE WHEN NVL(:B8 ,0)-FLOOR(NVL(:B8 ,0))=0 THEN NVL(:B8 ,0)||'.00' ELSE TO_CHAR(NVL(:B8 ,0)) END , CASE WHEN NVL(:B7 ,0)-FLOOR(NVL(:B7 ,0))=0 THEN NVL(:B7 ,0)||'.00' ELSE TO_CHAR(NVL(:B7 ,0)) END , CASE WHEN NVL(:B6 ,0)-FLOOR(NVL(:B6 ,0))=0 THEN NVL(:B6 ,0)||'.00' ELSE TO_CHAR(NVL(:B6 ,0)) END , :B5 ,:B4 ,:B3 ,:B2 , '1', :B1 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 2 0 Execute 6 0.00 0.00 0 1 8 6 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.00 0 1 10 6 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=0 us) ******************************************************************************** SQL ID: a9zx00a7m5hgh Plan Hash: 2612424869 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"), NVL(SUM(C2),:"SYS_B_01"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_02" ELSE :"SYS_B_03" END),:"SYS_B_04") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("C") FULL("C") NO_PARALLEL_INDEX("C") */ :"SYS_B_05" AS C1, CASE WHEN "C"."ACTION"= :"SYS_B_06" THEN :"SYS_B_07" ELSE :"SYS_B_08" END AS C2, "C"."CONTRACTIID" AS C3 FROM "CONTRACTLINE" SAMPLE BLOCK (:"SYS_B_09" , :"SYS_B_10") SEED (:"SYS_B_11") "C") SAMPLESUB 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 1 0.01 0.00 0 96 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.00 0 96 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=96 pr=0 pw=0 time=0 us) 320 VIEW VW_DAG_0 (cr=96 pr=0 pw=0 time=319 us cost=20 size=267592 card=5146) 320 HASH GROUP BY (cr=96 pr=0 pw=0 time=212 us cost=20 size=195548 card=5146) 1930 TABLE ACCESS SAMPLE CONTRACTLINE (cr=96 pr=0 pw=0 time=1326 us cost=19 size=195548 card=5146) ******************************************************************************** SQL ID: 9pfb4118ad4pd Plan Hash: 2262553975 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"), NVL(SUM(C2),:"SYS_B_01"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN :"SYS_B_02" ELSE :"SYS_B_03" END),:"SYS_B_04") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("D") FULL("D") NO_PARALLEL_INDEX("D") */ :"SYS_B_05" AS C1, CASE WHEN TO_NUMBER(NVL("D"."ISFREIGHT",:"SYS_B_06"))<>:"SYS_B_07" THEN :"SYS_B_08" ELSE :"SYS_B_09" END AS C2, "D"."INVIID" AS C3 FROM "INVMASTER" SAMPLE BLOCK (:"SYS_B_10" , :"SYS_B_11") SEED (:"SYS_B_12") "D") SAMPLESUB 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 1 0.00 0.00 0 62 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 62 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=62 pr=0 pw=0 time=0 us) 1271 VIEW VW_DAG_0 (cr=62 pr=0 pw=0 time=1397 us cost=20 size=267592 card=5146) 1271 HASH GROUP BY (cr=62 pr=0 pw=0 time=762 us cost=20 size=144088 card=5146) 1271 TABLE ACCESS SAMPLE INVMASTER (cr=62 pr=0 pw=0 time=1143 us cost=19 size=144088 card=5146) ******************************************************************************** SQL ID: 07c3cu2j6vpgx Plan Hash: 119722505 SELECT C.*,(C.AMOUNT- NVL(C.AMOUNT*(C.DISCOUNTPERCENT/100),0)) EXTENDEDPRICE, B.CONTRACTID FROM PARAMS A,CONTRACT B,CONTRACTLINE C,INVMASTER D WHERE A.STR=B.CONTRACTID AND B.CONTRACTIID=C.CONTRACTIID AND C.INVIID=D.INVIID AND C.ACTION=8 AND NVL(D.ISFREIGHT,0)<>1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 9 2 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 1 16 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.01 1 25 2 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 65 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 NESTED LOOPS (cr=16 pr=1 pw=0 time=0 us) 0 NESTED LOOPS (cr=16 pr=1 pw=0 time=0 us cost=20 size=503025 card=475) 0 NESTED LOOPS (cr=16 pr=1 pw=0 time=0 us cost=6 size=495425 card=475) 1 NESTED LOOPS (cr=10 pr=0 pw=0 time=0 us cost=4 size=48 card=1) 1 TABLE ACCESS FULL PARAMS (cr=7 pr=0 pw=0 time=0 us cost=3 size=27 card=1) 1 TABLE ACCESS BY INDEX ROWID CONTRACT (cr=3 pr=0 pw=0 time=0 us cost=1 size=21 card=1) 1 INDEX UNIQUE SCAN IDX_CONTRACTCONTRACTID (cr=2 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 75926) 0 TABLE ACCESS BY INDEX ROWID CONTRACTLINE (cr=6 pr=1 pw=0 time=0 us cost=2 size=472625 card=475) 9 INDEX RANGE SCAN IDX_CONLINECONTRACTIIDSTATUS (cr=3 pr=1 pw=0 time=8 us cost=1 size=0 card=121)(object id 75962) 0 INDEX UNIQUE SCAN PK_INVMASTER (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 76249) 0 TABLE ACCESS BY INDEX ROWID INVMASTER (cr=0 pr=0 pw=0 time=0 us cost=1 size=16 card=1) ******************************************************************************** SQL ID: dbmdavb6ydwb4 Plan Hash: 0 ALTER SESSION SET SQL_TRACE=FALSE 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 mode: ALL_ROWS Parsing user id: 65 ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.00 0.00 0 0 0 0 Execute 4 0.01 0.03 3 1 4 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.01 0.03 3 1 4 2 Misses in library cache during parse: 2 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 161 0.01 0.07 3 31 20 0 Execute 488545 5.75 5.72 4 128 20 11 Fetch 489055 45.20 46.09 2707 1565929 0 491077 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 977761 50.96 51.88 2714 1566088 40 491088 Misses in library cache during parse: 74 Misses in library cache during execute: 64 487986 user SQL statements in session. 564 internal SQL statements in session. 488550 SQL statements in session. ******************************************************************************** Trace file: r2_ora_316.trc Trace file compatibility: 11.1.0.7 Sort options: default 1 session in tracefile. 487986 user SQL statements in trace file. 564 internal SQL statements in trace file. 488550 SQL statements in trace file. 84 unique SQL statements in trace file. 3421069 lines in trace file. 241 elapsed seconds in trace file.