Oracle Performance implications with many PreparedStatements.

From: vdg <vdgaitonde_at_yahoo.com>
Date: Fri, 10 May 2002 21:35:03 GMT
Message-ID: <edb7ef26.0111121133.f456723@posting.google.com>

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 Fri May 10 2002 - 16:35:03 CDT