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: Brian Peasland <oracle_dba_at_nospam.peasland.net>
Date: Fri, 5 May 2006 15:27:02 GMT
Message-ID: <IystLE.IIy@igsrsparc2.er.usgs.gov>


> 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"

You can see above that the value is a string now.

> 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)'

But yet an INDEX RANGE SCAN is still being used. Have you tried to influence the Optimizer with a hint to use the INDEX SKIP SCAN instead?

    You may also want to implement Plan Stability so that this query runs as expected no matter which platform it is executed from.

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
Received on Fri May 05 2006 - 10:27:02 CDT

Original text of this message

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