Reducing number of parses

[Igor_Shtern]

Hello,

We are using Powerbuilder 7 on Oracle9i2.
We have an application that executes number of datawindows in a loop.

Recently I have traced our application using one of the Oracle wait event
10046 and discovered that we are reparsing the same statements
in a loop.

e.g

Do
parse
execute
Loop

Is there a driver parameter/(SQLCA) that will allow a session to keep the
client side cursors open. So the code may execute

parse
do
execute
loop

I'm trying SQLCache=100 (it seems to work). Please let me know if I'm on
the right track and if there any know issues with using this parameter.

Thank you
Igor

[WS]

It sure helps to set SQLCache > 0 to gain performance.

What is the execute stmt of the cursor? Does it have any 'binding' of
variables where SQL stmt is created on the fly?
pl check if the disablebind=0 [the default]

"Using bind variables in conjunction with cached statements can improve the
performance of most applications, depending on the application. In general,
applications that perform a large amount of transaction processing benefit
the most from using bind variables and cached statements.
In order to use cached statements, make sure that DisableBind is set to 0.
This enables the binding of input variables to SQL statements in
PowerBuilder."

<Igor_Shtern> wrote in message
news:CA09312C7E36AEE60073FE4185256D60.0073FE6D85256D60@webforums...

[Igor_shtern]

Thank you very much for your reply.

We do have disablebind set to 0.

I have tested our application with SqlCache set to 0 and noticed that we
are parsing once for every execution in a two-hour user specified batch
job. With SqlCache set to 100 we are parsing only once and executing
hundred of times - our DBA is very happy :).

I was concerned that setting this parameter may cause undocumented PB
errors as I have seen posed in deja and other PB user groups.

Thanks
Igor