Brian Peasland wrote:
> > This is the TRACE File generated when i run the query from TOAD:
> >
> > *****************************************************************************************************
> >
>
> <snip>
>
> > 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"
>
> In the query you run from Toad, the bind value for this variable is
> "39". Yet in the query you run from Java, the value is simply 39, no
> double quotes. One is a numeric value and the other is a string value.
> This also helps to explain why the two statements have different hash
> values when, except for the bind variable contents, the two queries look
> like the exact same SQL statement. Different values when using CBO can
> lead to different execution plans for the same SQL statement.
>
> The rest of your bind variable values look the same from both trace files.
>
> <more snipping of the trace files>
>
> > 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)'
>
> Above, you can see the Explain Plan of the query as run through Toad.
> Let's look at the Explain Plan of the query run from your Java program:
>
> <more snipping of the trace files>
>
> > 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)'
>
> Your Toad version of the query does an INDEX SKIP SCAN on the PRC_ARC_PK
> index. The Java version of the query does an INDEX RANGE SCAN on the
> PRC_ARC_PK index. Obviously, one is faster than the other. The indexes
> are being used two different ways.
>
> What is the datatype of the PPD_ID column of the SYS_PRICE_ARCHIVE
> table? Is it a numeric datatype or a character datatype? Your type
> conversion is influencing the CBO to make a decision one way or
> another...that is giving you different run times.
>
> In your case, the INDEX SKIP SCAN is faster probably because less blocks
> need to be read in the index. The INDEX RANGE SCAN causes more blocks to
> be read, hence the "db file sequential read wait" event, over and over
> again.
>
> HTH,
> Brian
>
> --
> ===================================================================
>
> Brian Peasland
> oracle_dba_at_nospam.peasland.net
> http://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown
Brian,
Thank you for looking into this.
I ran the Java code by passing a character instead of a number and i
get the same results.
I am pasting a portion of the trace file below..
Dump file
c:\oracle\product\10.1.0\admin\saracenp\udump\saracenp_ora_2876.trc
Fri May 05 10:57:55 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:1264M/3071M, PG:2570M/4457M, VA:1121M/2047M
Instance name: saracenp
Redo thread mounted by this instance: 1
Oracle process number: 42
Windows thread id: 2876, image: ORACLE.EXE (SHAD)
- SERVICE NAME:(SYS$USERS) 2006-05-05 10:57:55.500
- SESSION ID:(51.10706) 2006-05-05 10:57:55.500
PARSING IN CURSOR #1 len=69 dep=0 uid=44 oct=42 lid=44 tim=1852905777
hv=3164292706 ad='24ef2ab0'
alter session set events '10046 trace name context forever, level 12'
END OF STMT
EXEC #1:c=0,e=93,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1852905772
WAIT #1: nam='SQL*Net message to client' ela= 5 p1=675365956 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 356 p1=675365956 p2=1
p3=0
WAIT #0: nam='SQL*Net message to client' ela= 3 p1=675365956 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 10092 p1=675365956 p2=1
p3=0
WAIT #2: nam='SQL*Net message to client' ela= 9 p1=675365956 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 12234 p1=675365956 p2=1
p3=0
PARSING IN CURSOR #1 len=40 dep=1 uid=0 oct=3 lid=0 tim=1852935348
hv=2821867121 ad='33aad204'
select default$ from col$ where rowid=:1
END OF STMT
PARSE #1:c=0,e=603,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1852935343
BINDS #1:
bind 0: dty=11 mxl=16(16) mal=00 scl=00 pre=00 oacflg=18 oacfl2=0001
size=16 offset=0
bfp=0cd1bf14 bln=16 avl=16 flg=05
value=0000D009.0027.0001
EXEC #1:c=0,e=1654,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1852938022
WAIT #1: nam='db file sequential read' ela= 12517 p1=1 p2=53257 p3=1
FETCH #1:c=0,e=13111,p=1,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1852951335
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 #2 len=325 dep=0 uid=44 oct=3 lid=44 tim=1852952468
hv=251636996 ad='23d37204'
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=18378,p=1,cr=2,cu=0,mis=1,r=0,dep=0,og=1,tim=1852952462
PARSING IN CURSOR #1 len=40 dep=1 uid=0 oct=3 lid=0 tim=1852955422
hv=2821867121 ad='33aad204'
select default$ from col$ where rowid=:1
END OF STMT
PARSE #1:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1852955418
BINDS #1:
bind 0: dty=11 mxl=16(16) mal=00 scl=00 pre=00 oacflg=18 oacfl2=0001
size=16 offset=0
bfp=0cd1be9c bln=16 avl=16 flg=05
value=0000D009.0027.0001
EXEC #1:c=0,e=734,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1852957147
FETCH #1:c=0,e=38,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1852957383
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 #1 len=42 dep=1 uid=0 oct=3 lid=0 tim=1852958482
hv=844002283 ad='31ff9294'
select condition from cdef$ where rowid=:1
END OF STMT
PARSE #1:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1852958477
BINDS #1:
bind 0: dty=11 mxl=16(16) mal=00 scl=00 pre=00 oacflg=18 oacfl2=0001
size=16 offset=0
bfp=0cd1bc24 bln=16 avl=16 flg=05
value=0000C95F.0028.0001
EXEC #1:c=0,e=740,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1852960215
FETCH #1:c=0,e=39,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,tim=1852960455
STAT #1 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 #2:
bind 0: dty=1 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=01
oacfl2=0000 size=4000 offset=0
bfp=0cd2d334 bln=4000 avl=02 flg=05
value="39"
bind 1: dty=12 mxl=07(49) mal=00 scl=00 pre=00 oacflg=01 oacfl2=0000
size=32 offset=0
bfp=0cd1b8b4 bln=07 avl=07 flg=05
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=8
bfp=0cd1b8bc 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=16
bfp=0cd1b8c4 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=24
bfp=0cd1b8cc bln=07 avl=07 flg=01
value="3/3/2006 0:0:0"
EXEC
#2:c=15625,e=10493,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=1,tim=1852965138
WAIT #2: nam='SQL*Net message to client' ela= 5 p1=675365956 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 537 p1=675365956 p2=1
p3=0
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=675365956 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 3963 p1=675365956 p2=1
p3=0
WAIT #2: nam='db file sequential read' ela= 12884 p1=24 p2=6828 p3=1
WAIT #2: nam='db file sequential read' ela= 6715 p1=24 p2=448687 p3=1
WAIT #2: nam='db file sequential read' ela= 12567 p1=27 p2=2491908 p3=1
WAIT #2: nam='db file sequential read' ela= 40499 p1=27 p2=362170 p3=1
WAIT #2: nam='SQL*Net message to client' ela= 4 p1=675365956 p2=1 p3=0
WAIT #2: nam='db file sequential read' ela= 8322 p1=24 p2=428144 p3=1
WAIT #2: nam='db file sequential read' ela= 7015 p1=24 p2=427760 p3=1
WAIT #2: nam='db file sequential read' ela= 4287 p1=24 p2=428208 p3=1
WAIT #2: nam='db file sequential read' ela= 12795 p1=24 p2=427952 p3=1
.
.
.
WAIT #2: nam='db file sequential read' ela= 13249 p1=27 p2=391896 p3=1
WAIT #2: nam='db file sequential read' ela= 8267 p1=27 p2=362639 p3=1
WAIT #2: nam='db file sequential read' ela= 16015 p1=27 p2=389621 p3=1
WAIT #2: nam='db file sequential read' ela= 11951 p1=27 p2=362553 p3=1
WAIT #2: nam='db file sequential read' ela= 8087 p1=23 p2=424213 p3=1
FETCH
#2:c=1484375,e=174873684,p=14840,cr=14841,cu=0,mis=0,r=2,dep=0,og=1,tim=2027844595
WAIT #2: nam='SQL*Net message from client' ela= 28423 p1=675365956 p2=1
p3=0
WAIT #2: nam='SQL*Net message to client' ela= 3 p1=675365956 p2=1 p3=0
WAIT #2: nam='SQL*Net message from client' ela= 517 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=174873365 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=174873352 us)'
STAT #2 id=3 cnt=2 pid=2 pos=1 obj=46281 op='INDEX RANGE SCAN
PRC_ARC_PK (cr=14839 pr=14839 pw=0 time=74416 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 p1=675365956 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 358 p1=675365956 p2=1
p3=0
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=675365956 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 472 p1=675365956 p2=1
p3=0
PARSING IN CURSOR #1 len=55 dep=0 uid=44 oct=42 lid=44 tim=2027875258
hv=2217940283 ad='21db640c'
alter session set events '10046 trace name context off'
END OF STMT
PARSE #1:c=0,e=317,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=2027875254
BINDS #1:
EXEC #1:c=0,e=104,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2027875436
Received on Fri May 05 2006 - 10:11:48 CDT