Hi Matt
It looks like you have followed exactly the correct path in troubleshooting.
You don't say what this batch process is written in, but it might be that it
uses a technology that deliberately parses the same statement twice. I
believe JDBC does this by default and that perl dbi does as well. If so it
is likely that you can turn off the excessive parse calls when setting up
the db connections. The bottom line though is that it is going to be the
client that is issuing the parse calls and so the problem will need to be
fixed at the client end (I'm eliminating a ludicrously small shared pool
here).
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Matt" <mccmx_at_hotmail.com> wrote in message
news:cfee5bcf.0402260016.1f6e8e4_at_posting.google.com...
> Are you saying that "session_cached_cursors" has no effect....?
>
> The code isn't as bad as some I've dealt with in the past... at least
> they are making use of bind variables.
>
> In the absence of 'ideal code' I was hoping to squeeze some benefit
> from using a cursor cache (which I thought was its intended purpose).
>
> Also bear in mind that the 'recursive' SQL is also being repeatedly
> reparsed (which can't be blamed on the developers).
>
> For a process that is 75% parse time, surely there is something at the
> back end I can do to improve the response time.
>
> I've already identified that the large bulk of parse time is due to
> the repeated reparsing of 'every' statement.
>
> Thanks for your time
>
> Matt
Received on Thu Feb 26 2004 - 04:51:34 CST
