Re: Oracle Performance implications with many PreparedStatements.

In general, nemory is cheap, and performance is worth it. IMHO The alternative is having the DBMS continually reparsing identical SQL, spinning it's wheels needlessly, and probably taking up the same amount of memory for the 'fresh' query plan as it would for the stored-and-reused one anyway.

Joe Weinstein at B.E.A.

vdg wrote:
>
> Issue: Oracle Performance implications with many PreparedStatements.
>
> DB: Oracle 8.1.6
> Connection type: Oracle JDBC Drivers
> Java Version: 1.2.*
>
> Descr:
> Our system has around 100 - 150 SQL statements which are
> executed as Statements (as opposed to PreparedStatements).
> We typically have around 50 connections opened at initialize
> time.
>
> I think moving all these SQL statments to PreparedStatements
> would make the system faster. I am sure it does that.
>
> However, I havent been able to figure out of manuals and
> documents the implications of PreparedStatements on Oracle's
> performance.
>
> Since the PreparedStatements will be in the context of a
> connection, 50 connections each with 100 PreparedStatements
> would mean 5000 PreparedStatements in Oracle.
>
> How would that effect memory usage for oracle?
> Is there anything else that I am missing out here?
>
> Conversely, if I do need to have PreparedStatements
> with Oracle, what steps should I follow to tune the
> DB to give optimal performance?
>
> On the face of it, PreparedStatements look good. Are they?
>
> Thank you in advance,
> -Vikram.
Received on Mon Nov 12 2001 - 13:54:00 CST