Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sql running slow when run from java but runs fine when run from toad

Re: sql running slow when run from java but runs fine when run from toad

From: picksdba <Utpal.Dhar_at_gmail.com>
Date: 5 May 2006 08:11:48 -0700
Message-ID: <1146841908.831783.142030@u72g2000cwu.googlegroups.com>

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)

Received on Fri May 05 2006 - 10:11:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US