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

> 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