Slow return on JDBC call from WebLogic : Weblogic

From within Java code, We are creating prepared statements and using JDBC t=
o execute them on an Oracle 8.1.7 database. The times vary (sometimes being=
as 'quickly' as 35 seconds to 70 secs, and other times runing at well over=
200 secs.) This variation only occurs when making the call thru the JDBC d=
river running on our WebLogic Server (version 6.1)=20

However, when this same SQL (taken from our log file, is run (obviously wit=
hout using bind variables) and run using SQLPlus or from DBVisualizer (whic=
h uses the same Oracle Driver as our WebLogic Server) it runs in less than =
10 secs each time. It appears that there must be some sort of Load contenti=
on going on, but this contention does not appear to be in the DB. It seems =
to be related to going thru WebLogic. Any suggestions on what or where the =
problem might be would be greatly appreciated.

Steve

Steve wrote:
> From within Java code, We are creating prepared statements and using JDBC=
to execute them on an Oracle 8.1.7 database. The times vary (sometimes bei=
ng as 'quickly' as 35 seconds to 70 secs, and other times runing at well ov=
er 200 secs.) This variation only occurs when making the call thru the JDBC=
driver running on our WebLogic Server (version 6.1)=20
>=20
> However, when this same SQL (taken from our log file, is run (obviously w=
ithout using bind variables) and run using SQLPlus or from DBVisualizer (wh=
ich uses the same Oracle Driver as our WebLogic Server) it runs in less tha=
n 10 secs each time. It appears that there must be some sort of Load conten=
tion going on, but this contention does not appear to be in the DB. It seem=
s to be related to going thru WebLogic. Any suggestions on what or where th=
e problem might be would be greatly appreciated.
>=20
> Steve

Hi!
1 - What version/service pack level of 6.1?
2 - How many execute-threads are you running with the Weblogic JVM?
3 - Are you using our connection pools? Are you having the pool cache prepa=
red statements?
4 -Is your code calling DriverManager.getConnection()?

Joe

Joe,

Thanks for the email. Sorry it has been a couple of days for me to reply. W=
e have
been working several other issues besides this one. Since my original quest=
ion,
we have discovered more on this issue.

We wrote some independent programs to bypass WebLogic, but still use the Or=
acle
Driver. This provided the same result. So we then tweak the code to remove =
the
bind variables in the preparedStatement. This drop our over 200 second time=
s to
about 1 second. Quite a significant change. These tests were conducted on t=
he
Production system where the problem was identified. When we ran these some =
tests,
there was no time difference either way. Both ran (with and without bind va=
riables)
at around 20 - 30
seconds.

Here are the answers to your questions as best as we could answer.


1 - What version/service pack level of 6.1?
SP version 4

2 - How many execute-threads are you running with the Weblogic JVM?
Not sure. How do we check this?

3 - Are you using our connection pools? Are you having the pool cache prepa=
red
statements?
Yes connection pools, no pool cache statements. What exactly does the po=
ol
cache prepared statements do?

4 -Is your code calling DriverManager.getConnection()?
Yes

Thanks,
Steve

Joe Weinstein <joeNOSPAM@bea.com> wrote:
>
>
>Steve wrote:
>> From within Java code, We are creating prepared statements and using
>JDBC to execute them on an Oracle 8.1.7 database. The times vary (sometime=
s
>being as 'quickly' as 35 seconds to 70 secs, and other times runing
>at well over 200 secs.) This variation only occurs when making the call
>thru the JDBC driver running on our WebLogic Server (version 6.1)=20
>>=20
>> However, when this same SQL (taken from our log file, is run (obviously
>without using bind variables) and run using SQLPlus or from DBVisualizer
>(which uses the same Oracle Driver as our WebLogic Server) it runs in
>less than 10 secs each time. It appears that there must be some sort
>of Load contention going on, but this contention does not appear to
>be in the DB. It seems to be related to going thru WebLogic. Any suggestio=
ns
>on what or where the problem might be would be greatly appreciated.
>>=20
>> Steve
>
>Hi!
>1 - What version/service pack level of 6.1?
>2 - How many execute-threads are you running with the Weblogic JVM?
>3 - Are you using our connection pools? Are you having the pool cache
>prepared statements?
>4 -Is your code calling DriverManager.getConnection()?
>
>Joe
>

Steve wrote:

> Joe,
>=20
> Thanks for the email. Sorry it has been a couple of days for me to reply.=
We have
> been working several other issues besides this one.

Me too.

> Since my original question,
> we have discovered more on this issue.
>=20
> We wrote some independent programs to bypass WebLogic, but still use the =
Oracle
> Driver. This provided the same result. So we then tweak the code to remov=
e the
> bind variables in the preparedStatement. This drop our over 200 second ti=
mes to
> about 1 second. Quite a significant change. These tests were conducted on=
the
> Production system where the problem was identified. When we ran these som=
e tests,
> there was no time difference either way. Both ran (with and without bind =
variables)
> at around 20 - 30
> seconds.

So to be clear, are you saying that:
1 - the independent code got the same bad performance.
2 - Removing the parameter variables in the prepared statements made the
performance much better.
3 - ?? I don't follow what happened after you state 'quite a significant ch=
ange'.

> Here are the answers to your questions as best as we could answer.
>=20
> 1 - What version/service pack level of 6.1?
> SP version 4
>=20
> 2 - How many execute-threads are you running with the Weblogic JVM?
> Not sure. How do we check this?

No matter. Now that we have found that it's parameter-related, it's not thr=
ead-related.

> 3 - Are you using our connection pools? Are you having the pool cache pre=
pared
> statements?
> Yes connection pools, no pool cache statements. What exactly does the =
pool
> cache prepared statements do?

Caches would be very good to try. They transparently cache prepared stateme=
nts
so if you code does a lot of 'create prepared statement, execute prepared s=
tatement,
close prepared statement (such as if method is done)', we will be able to m=
ake the
prepare a NO-OP, and re-use a statement that was prepared for the same SQL.=
This
may be a big win for you, because the preparation of a statement involves p=
arsing
it at the DBMS, which may be expensive/slow especially with parameters. If =
you
cache and re-use, the plan can get it's performance benefit from reuse, ins=
tead of
needless cost of recompiling every time.

> 4 -Is your code calling DriverManager.getConnection()?
> Yes

It should be changed not to. DriverManager is terribly slow and kills concu=
rrency
because of it's class-level locks. Show me your code and I'll show you a be=
tter
alternative.

Joe


>=20
> Thanks,
> Steve
>=20
> Joe Weinstein <joeNOSPAM@bea.com> wrote:
>=20
>>
>>Steve wrote:
>>
>>>From within Java code, We are creating prepared statements and using
>>
>>JDBC to execute them on an Oracle 8.1.7 database. The times vary (sometim=
es
>>being as 'quickly' as 35 seconds to 70 secs, and other times runing
>>at well over 200 secs.) This variation only occurs when making the call
>>thru the JDBC driver running on our WebLogic Server (version 6.1)=20
>>
>>>However, when this same SQL (taken from our log file, is run (obviously
>>
>>without using bind variables) and run using SQLPlus or from DBVisualizer
>>(which uses the same Oracle Driver as our WebLogic Server) it runs in
>>less than 10 secs each time. It appears that there must be some sort
>>of Load contention going on, but this contention does not appear to
>>be in the DB. It seems to be related to going thru WebLogic. Any suggesti=
ons
>>on what or where the problem might be would be greatly appreciated.
>>
>>>Steve
>>
>>Hi!
>>1 - What version/service pack level of 6.1?
>>2 - How many execute-threads are you running with the Weblogic JVM?
>>3 - Are you using our connection pools? Are you having the pool cache
>>prepared statements?
>>4 -Is your code calling DriverManager.getConnection()?
>>
>>Joe
>>
>=20
>=20