Home » RDBMS Server » Performance Tuning » Query running more time (Oracle 11g standard Version)
Query running more time [message #538851] Mon, 09 January 2012 19:21 Go to next message
sank
Messages: 5
Registered: January 2012
Location: chennai
Junior Member
Hello All,
Scenario : When we run the views for examplse "REP_ORDERHEADERVIEW" it takes longed then the expected time sometime and CPU peaks to 100%.
Scenario 2: If ran the same view after some time it run's @ the expected speed.

I don't find any process that's consuming the memory.
can you please throw some light here.

Attached statskpack report, explain plan,v$sql query for your reference.

it there anyway i can find the sql query executed / tails at that time or



Thanks & regard
sankar
Re: Query running more time [message #538852 is a reply to message #538851] Mon, 09 January 2012 19:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Attached statskpack report, explain plan,v$sql query for your reference.

It appears a problem occurred.

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Query running more time [message #538988 is a reply to message #538852] Tue, 10 January 2012 12:34 Go to previous messageGo to next message
sank
Messages: 5
Registered: January 2012
Location: chennai
Junior Member
Hello,

Really sorry for posting information in unacceptable format.

Many thanks for providing tons of useful informtion.

I have attached session trace file.Please may i know whether this helps in finding the cause.

Please let me know the way to read the trace file.

Thanks
sankar
Re: Query running more time [message #538991 is a reply to message #538988] Tue, 10 January 2012 12:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You are not making this easy!

>Please may i know whether this helps in finding the cause.
cause of what exactly?

>REP_ORDERHEADERVIEW
Do you realize that string above does not appear within posted trace file?

I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Query running more time [message #539015 is a reply to message #538991] Tue, 10 January 2012 14:50 Go to previous message
sank
Messages: 5
Registered: January 2012
Location: chennai
Junior Member
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.


Previous Topic: HTML AWR Report
Next Topic: truncate table
Goto Forum:
  


Current Time: Thu Mar 28 11:03:06 CDT 2024