db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ashley Hayes <ashley.ha...@macalla.com>
Subject RE: Oracle 9i: Memory pool problem
Date Fri, 07 Nov 2003 17:58:47 GMT
Hi,
 in your previous email you said the problem was with oracle's shared pool.
We had some similar problems, however it was just using JDBC and NOT torque.
Our solution was to use as many bind variable statements as possible in our
PL SQL stored procs/fns and higher the amount of memory for in the shared
pool.

I am not sure how the oracle jdbc DRIVER treats statements and
preparedstatements in relation to oracle bind variables. I'd guess that bind
vars would not be used when:
 - you use JDBC Statements 
 - you use JDBC PreparedStatements with literals instead of jdbc parameters
i.e.
  SELECT * FROM blah WHERE ID = '1000' instead of
  SELECT * FROM blah WHERE ID = ?  and ps.setInt(1,1000);

Bind vars maybe used by the oracle driver when PreparedStatements are
parameterized e.g.
  SELECT * FROM blah WHERE ID = ?  and ps.setInt(1,1000);

Try to find out on the oracle forms if bind vars are used by JDBC statements
as opposed to PL SQL..


>From the SQL I see being passed to the jdbc driver, torque/village uses
parameterised PreparedStatements for updates/inserts,e.g.
  UPDATE blah SET ID = ?, NAME = ? WHERE ID = ?
and uses literals for parameters in queries for
Statements/PreparedStatements
  SELECT * FROM blah WHERE ID = '1000'


Hopes this helps,
A



-----Original Message-----
From: Ramesh Sabeti [mailto:rs@reazon.com]
Sent: 07 November 2003 17:27
To: 'Apache Torque Users List'
Subject: Oracle 9i: Memory pool problem


This is a rephrased version of my question yesterday.  Oracle had been
crashing after an hour of use due to its memory being filled with cached
queries.  It happens when you don't use bind variables with Oracle.
Does anyone know if Torque is capable of using bind variables?

e.g. With bind variables, a sql statement would look something like
	select * from mytable where col = :criteria;

Any help is greatly appreciated.

Ramesh. 


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message