A sql statement is taking a long time to run from Java, but it runs
quicky from Toad. I am on 10.1.0 on Windows. Can anybody tell me where
to look at? Thanks.
I am pasting two trace files below.
This is the TRACE File generated when i run the query from TOAD:
Dump file
c:\oracle\product\10.1.0\admin\saracenp\udump\saracenp_ora_6028.trc
Thu May 04 16:40:48 2006
ORACLE V10.1.0.2.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Release 10.1.0.2.0 - Production
Windows Server 2003 Version V5.2 Service Pack 1
CPU : 4 - type 586, 2 Physical Cores
Process Affinity: 0x00000000
Memory (A/P) : PH:1309M/3071M, PG:2780M/4457M, VA:1240M/2047M
Instance name: saracenp
Redo thread mounted by this instance: 1
Oracle process number: 22
Windows thread id: 6028, image: ORACLE.EXE (SHAD)
- 2006-05-04 16:40:48.312
- ACTION NAME:() 2006-05-04 16:40:48.312
- MODULE NAME:(TOAD 8.0.0.47) 2006-05-04 16:40:48.312
- SERVICE NAME:(saracenp.saracen.com) 2006-05-04 16:40:48.312
- SESSION ID:(50.564) 2006-05-04 16:40:48.312
PARSING IN CURSOR #4 len=70 dep=0 uid=44 oct=42 lid=44 tim=2414441638
hv=2467678342 ad='2531f9a4'
alter session set events '10046 trace name context forever, level 12'
END OF STMT
EXEC #4:c=0,e=131,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=2414441628
WAIT #4: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
- 2006-05-04 16:41:17.671
WAIT #4: nam='SQL*Net message from client' ela= 29355654 p1=1413697536
p2=1 p3=0
PARSING IN CURSOR #3 len=326 dep=0 uid=44 oct=3 lid=44 tim=2443805377
hv=4097688719 ad='211b9eb4'
SELECT trading_day, ppd_id, INTERVAL, price, price_adj1, price_adj2,
price_adj3, price_adj4, was_processed, date_created,
date_modified,
created_by, modified_by
FROM sys_price_archive
WHERE 1 = 1
AND ppd_id = :1
AND INTERVAL >= :2
AND INTERVAL <= :3
AND trading_day >= :4
AND trading_day <= :5
END OF STMT
PARSE #3:c=0,e=539,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=2443805370
PARSING IN CURSOR #1 len=40 dep=1 uid=0 oct=3 lid=0 tim=2443808894
hv=2821867121 ad='33aad204'
select default$ from col$ where rowid=:1
END OF STMT
PARSE #1:c=0,e=113,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2443808886
BINDS #1:
bind 0: dty=11 mxl=16(16) mal=00 scl=00 pre=00 oacflg=18 oacfl2=0001
size=16 offset=0
bfp=07b2c52c bln=16 avl=16 flg=05
value=0000D009.0027.0001
EXEC #1:c=0,e=908,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2443811099
WAIT #1: nam='db file sequential read' ela= 7853 p1=1 p2=53257 p3=1
FETCH #1:c=0,e=8346,p=1,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=2443819699
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=21 op='TABLE ACCESS BY USER ROWID
COL$ (cr=0 pr=0 pw=0 time=0 us)'
PARSING IN CURSOR #4 len=42 dep=1 uid=0 oct=3 lid=0 tim=2443821079
hv=844002283 ad='31ff9294'
select condition from cdef$ where rowid=:1
END OF STMT
PARSE #4:c=0,e=70,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2443821073
BINDS #4:
bind 0: dty=11 mxl=16(16) mal=00 scl=00 pre=00 oacflg=18 oacfl2=0001
size=16 offset=0
bfp=07b2b874 bln=16 avl=16 flg=05
value=0000C95F.0028.0001
EXEC
#4:c=15625,e=1387,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2443823814
WAIT #4: nam='db file sequential read' ela= 8979 p1=1 p2=51551 p3=1
FETCH #4:c=0,e=9488,p=1,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=2443833704
STAT #4 id=1 cnt=0 pid=0 pos=1 obj=31 op='TABLE ACCESS BY USER ROWID
CDEF$ (cr=0 pr=0 pw=0 time=0 us)'
BINDS #3:
bind 0: dty=1 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=01
oacfl2=0010 size=4000 offset=0
bfp=07b3d764 bln=4000 avl=02 flg=05
value="39"
bind 1: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0000
size=32 offset=0
bfp=07b2b854 bln=07 avl=07 flg=05
value="3/1/2006 0:0:0"
bind 2: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0000
size=0 offset=8
bfp=07b2b85c bln=07 avl=07 flg=01
value="3/1/2006 0:0:0"
bind 3: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0000
size=0 offset=16
bfp=07b2b864 bln=07 avl=07 flg=01
value="3/2/2006 0:0:0"
bind 4: dty=12 mxl=07(07) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0000
size=0 offset=24
bfp=07b2b86c bln=07 avl=07 flg=01
value="3/3/2006 0:0:0"
EXEC
#3:c=31250,e=31316,p=2,cr=4,cu=0,mis=1,r=0,dep=0,og=1,tim=2443839582
WAIT #3: nam='SQL*Net message to client' ela= 6 p1=1413697536 p2=1 p3=0
WAIT #3: nam='SQL*Net message from client' ela= 734 p1=1413697536 p2=1
p3=0
WAIT #3: nam='db file sequential read' ela= 8324 p1=24 p2=448687 p3=1
WAIT #3: nam='db file sequential read' ela= 6934 p1=27 p2=2491908 p3=1
WAIT #3: nam='db file sequential read' ela= 4728 p1=27 p2=362170 p3=1
WAIT #3: nam='db file sequential read' ela= 10120 p1=23 p2=407653 p3=1
WAIT #3: nam='SQL*Net message to client' ela= 6 p1=1413697536 p2=1 p3=0
WAIT #3: nam='db file sequential read' ela= 6242 p1=29 p2=238477 p3=1
WAIT #3: nam='db file sequential read' ela= 30950 p1=27 p2=362553 p3=1
WAIT #3: nam='db file sequential read' ela= 5085 p1=23 p2=424213 p3=1
FETCH #3:c=0,e=83455,p=7,cr=8,cu=0,mis=0,r=2,dep=0,og=1,tim=2443925029
WAIT #3: nam='SQL*Net message from client' ela= 28820 p1=1413697536
p2=1 p3=0
STAT #3 id=1 cnt=2 pid=0 pos=1 obj=0 op='FILTER (cr=8 pr=7 pw=0
time=83125 us)'
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=46280 op='TABLE ACCESS BY INDEX
ROWID SYS_PRICE_ARCHIVE (cr=8 pr=7 pw=0 time=83100 us)'
STAT #3 id=3 cnt=2 pid=2 pos=1 obj=46281 op='INDEX SKIP SCAN PRC_ARC_PK
(cr=6 pr=5 pw=0 time=26563 us)'
PARSING IN CURSOR #1 len=56 dep=0 uid=44 oct=42 lid=44 tim=2443956382
hv=3193596740 ad='2244e730'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #1:c=0,e=439,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=2443956376
BINDS #1:
EXEC #1:c=0,e=396,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2443958313
And this is the trace file that is generated when i run it from JAVA
Dump file
c:\oracle\product\10.1.0\admin\saracenp\udump\saracenp_ora_4300.trc
Thu May 04 16:33:02 2006
ORACLE V10.1.0.2.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Release 10.1.0.2.0 - Production
Windows Server 2003 Version V5.2 Service Pack 1
CPU : 4 - type 586, 2 Physical Cores
Process Affinity: 0x00000000
Memory (A/P) : PH:1285M/3071M, PG:2650M/4457M, VA:1103M/2047M
Instance name: saracenp
Redo thread mounted by this instance: 1
Oracle process number: 56
Windows thread id: 4300, image: ORACLE.EXE (SHAD)
- SERVICE NAME:(SYS$USERS) 2006-05-04 16:33:02.781
- SESSION ID:(118.16103) 2006-05-04 16:33:02.781
PARSING IN CURSOR #1 len=69 dep=0 uid=44 oct=42 lid=44 tim=1948921964
hv=3164292706 ad='22a0b308'
alter session set events '10046 trace name context forever, level 12'
END OF STMT
EXEC #1:c=0,e=213,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1948921952
WAIT #1: nam='SQL*Net message to client' ela= 8 p1=675365956 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 632 p1=675365956 p2=1
p3=0
WAIT #0: nam='SQL*Net message to client' ela= 4 p1=675365956 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 12699 p1=675365956 p2=1
p3=0
WAIT #2: nam='SQL*Net message to client' ela= 6 p1=675365956 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 38140 p1=675365956 p2=1
p3=0
PARSING IN CURSOR #2 len=325 dep=0 uid=44 oct=3 lid=44 tim=1948985140
hv=251636996 ad='2567aedc'
SELECT trading_day, ppd_id, INTERVAL, price, price_adj1, price_adj2,
price_adj3, price_adj4, was_processed, date_created,
date_modified,
created_by, modified_by
FROM sys_price_archive
WHERE 1 = 1
AND ppd_id = :1
AND INTERVAL >= :2
AND INTERVAL <= :3
AND trading_day >= :4
AND trading_day <= :5
END OF STMT
PARSE #2:c=0,e=228,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1948985127
BINDS #2:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0000
size=56 offset=0
bfp=397ae338 bln=22 avl=02 flg=05
value=39
bind 1: dty=12 mxl=07(49) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0000
size=0 offset=24
bfp=397ae350 bln=07 avl=07 flg=01
value="3/1/2006 0:0:0"
bind 2: dty=12 mxl=07(49) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0000
size=0 offset=32
bfp=397ae358 bln=07 avl=07 flg=01
value="3/1/2006 0:0:0"
bind 3: dty=12 mxl=07(49) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0000
size=0 offset=40
bfp=397ae360 bln=07 avl=07 flg=01
value="3/2/2006 0:0:0"
bind 4: dty=12 mxl=07(49) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0000
size=0 offset=48
bfp=397ae368 bln=07 avl=07 flg=01
value="3/3/2006 0:0:0"
EXEC
#2:c=15625,e=6298,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1948996618
WAIT #2: nam='SQL*Net message to client' ela= 7 p1=675365956 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 673 p1=675365956 p2=1
p3=0
WAIT #2: nam='SQL*Net message to client' ela= 7 p1=675365956 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 4012 p1=675365956 p2=1
p3=0
WAIT #2: nam='db file sequential read' ela= 5359 p1=24 p2=448687 p3=1
WAIT #2: nam='db file sequential read' ela= 10293 p1=27 p2=2491908 p3=1
WAIT #2: nam='db file sequential read' ela= 9500 p1=27 p2=362170 p3=1
WAIT #2: nam='db file sequential read' ela= 4256 p1=23 p2=407653 p3=1
WAIT #2: nam='SQL*Net message to client' ela= 5 p1=675365956 p2=1 p3=0
WAIT #2: nam='db file sequential read' ela= 7815 p1=24 p2=428144 p3=1
WAIT #2: nam='db file sequential read' ela= 3646 p1=24 p2=427760 p3=1
WAIT #2: nam='db file sequential read' ela= 5819 p1=24 p2=428208 p3=1
WAIT #2: nam='db file sequential read' ela= 4633 p1=24 p2=427952 p3=1
WAIT #2: nam='db file sequential read' ela= 1253 p1=24 p2=428272 p3=1
WAIT #2: nam='db file sequential read' ela= 7963 p1=24 p2=427824 p3=1
WAIT #2: nam='db file sequential read' ela= 5470 p1=24 p2=428336 p3=1
WAIT #2: nam='db file sequential read' ela= 1292 p1=24 p2=428016 p3=1
WAIT #2: nam='db file sequential read' ela= 3057 p1=24 p2=427889 p3=1
WAIT #2: nam='db file sequential read' ela= 3585 p1=24 p2=433039 p3=1
WAIT #2: nam='db file sequential read' ela= 1037 p1=24 p2=427888 p3=1
WAIT #2: nam='db file sequential read' ela= 1360 p1=24 p2=428080 p3=1
WAIT #2: nam='db file sequential read' ela= 1431 p1=24 p2=428400 p3=1
WAIT #2: nam='db file sequential read' ela= 996 p1=24 p2=427953 p3=1
WAIT #2: nam='db file sequential read' ela= 815 p1=24 p2=428017 p3=1
WAIT #2: nam='db file sequential read' ela= 9507 p1=24 p2=428081 p3=1
.
.
.
LOTS OF LINES WITH THE SAME WAIT
.
.
.
WAIT #2: nam='db file sequential read' ela= 967 p1=24 p2=428145 p3=1
WAIT #2: nam='db file sequential read' ela= 5182 p1=27 p2=406813 p3=1
WAIT #2: nam='db file sequential read' ela= 7816 p1=27 p2=406690 p3=1
WAIT #2: nam='db file sequential read' ela= 2098 p1=27 p2=406885 p3=1
WAIT #2: nam='db file sequential read' ela= 6499 p1=27 p2=407737 p3=1
WAIT #2: nam='db file sequential read' ela= 863 p1=27 p2=407739 p3=1
WAIT #2: nam='db file sequential read' ela= 6478 p1=27 p2=408333 p3=1
WAIT #2: nam='db file sequential read' ela= 5969 p1=27 p2=408973 p3=1
WAIT #2: nam='db file sequential read' ela= 7298 p1=27 p2=408735 p3=1
WAIT #2: nam='db file sequential read' ela= 3874 p1=27 p2=408612 p3=1
WAIT #2: nam='db file sequential read' ela= 2284 p1=27 p2=408500 p3=1
WAIT #2: nam='db file sequential read' ela= 5333 p1=27 p2=393420 p3=1
WAIT #2: nam='db file sequential read' ela= 3004 p1=27 p2=393165 p3=1
WAIT #2: nam='db file sequential read' ela= 4193 p1=27 p2=392871 p3=1
WAIT #2: nam='db file sequential read' ela= 5344 p1=27 p2=439141 p3=1
WAIT #2: nam='db file sequential read' ela= 4911 p1=27 p2=438833 p3=1
WAIT #2: nam='db file sequential read' ela= 876 p1=27 p2=439268 p3=1
WAIT #2: nam='db file sequential read' ela= 7178 p1=27 p2=440840 p3=1
WAIT #2: nam='db file sequential read' ela= 6582 p1=27 p2=440585 p3=1
WAIT #2: nam='db file sequential read' ela= 898 p1=27 p2=440650 p3=1
WAIT #2: nam='db file sequential read' ela= 8521 p1=27 p2=2491972 p3=1
WAIT #2: nam='db file sequential read' ela= 4789 p1=27 p2=2529275 p3=1
WAIT #2: nam='db file sequential read' ela= 6198 p1=29 p2=218942 p3=1
WAIT #2: nam='db file sequential read' ela= 5744 p1=27 p2=478092 p3=1
WAIT #2: nam='db file sequential read' ela= 3363 p1=27 p2=478491 p3=1
WAIT #2: nam='db file sequential read' ela= 6298 p1=27 p2=477862 p3=1
WAIT #2: nam='db file sequential read' ela= 10696 p1=27 p2=2296501 p3=1
WAIT #2: nam='db file sequential read' ela= 2429 p1=27 p2=2296973 p3=1
WAIT #2: nam='db file sequential read' ela= 5683 p1=27 p2=2296229 p3=1
WAIT #2: nam='db file sequential read' ela= 8334 p1=27 p2=2423298 p3=1
WAIT #2: nam='db file sequential read' ela= 8074 p1=27 p2=2422741 p3=1
WAIT #2: nam='db file sequential read' ela= 10897 p1=27 p2=2423189 p3=1
WAIT #2: nam='db file sequential read' ela= 9167 p1=27 p2=2470150 p3=1
WAIT #2: nam='db file sequential read' ela= 7243 p1=27 p2=2470678 p3=1
WAIT #2: nam='db file sequential read' ela= 4244 p1=27 p2=2472804 p3=1
WAIT #2: nam='db file sequential read' ela= 8401 p1=27 p2=2472311 p3=1
WAIT #2: nam='db file sequential read' ela= 5162 p1=27 p2=2473610 p3=1
WAIT #2: nam='db file sequential read' ela= 888 p1=27 p2=2472428 p3=1
WAIT #2: nam='db file sequential read' ela= 8013 p1=29 p2=241423 p3=1
WAIT #2: nam='db file sequential read' ela= 7276 p1=27 p2=391896 p3=1
WAIT #2: nam='db file sequential read' ela= 5515 p1=27 p2=362639 p3=1
WAIT #2: nam='db file sequential read' ela= 8515 p1=27 p2=389621 p3=1
WAIT #2: nam='db file sequential read' ela= 8160 p1=27 p2=362553 p3=1
WAIT #2: nam='db file sequential read' ela= 4798 p1=23 p2=424213 p3=1
FETCH
#2:c=1609375,e=57143619,p=14840,cr=14841,cu=0,mis=0,r=2,dep=0,og=1,tim=2006147818
WAIT #2: nam='SQL*Net message from client' ela= 2709 p1=675365956 p2=1
p3=0
WAIT #2: nam='SQL*Net message to client' ela= 2 p1=675365956 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 464 p1=675365956 p2=1
p3=0
STAT #2 id=1 cnt=2 pid=0 pos=1 obj=0 op='FILTER (cr=14841 pr=14840
pw=0 time=57143119 us)'
STAT #2 id=2 cnt=2 pid=1 pos=1 obj=46280 op='TABLE ACCESS BY INDEX
ROWID SYS_PRICE_ARCHIVE (cr=14841 pr=14840 pw=0 time=57143102 us)'
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=46281 op='INDEX RANGE SCAN
PRC_ARC_PK (cr=14839 pr=14838 pw=0 time=27958 us)'
WAIT #0: nam='SQL*Net message to client' ela= 2 p1=675365956 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 240 p1=675365956 p2=1
p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=675365956 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 429 p1=675365956 p2=1
p3=0
PARSING IN CURSOR #1 len=55 dep=0 uid=44 oct=42 lid=44 tim=2006152417
hv=2217940283 ad='205c8568'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #1:c=0,e=276,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=2006152413
BINDS #1:
EXEC #1:c=0,e=95,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2006152580
Received on Thu May 04 2006 - 18:27:56 CDT
