Re: Oracle Performance implications with many PreparedStatements.

In article <edb7ef26.0111121133.f456723_at_posting.google.com>, vdgaitonde_at_yahoo.com says...
>
>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?
>

Since they allow for bind variables, and bind variable use is a major predictor of success, they are awesome.

If you are just using "statements" now, you are not binding values -- you are creating unique SQL -- all of which must be hard parsed -- this kills performance, limits scalability, assures eventual failure.

Statements are OK for doing ALTER SESSIONS and DDL.

Prepared (or Callable to get outputs as well as inputs) Statements should be used for everything else.

>Thank you in advance,
>-Vikram.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp