Prepare Statement and (Oracle's) SGA

Hello,

We're using the PreparedStatement Object Pool provided in Websphere AppServer with an Oracle 8.0.5 RDMS (yes I know :-) .

At one time we had 3 connections alloted using the same query. Since a PreparedStatement is associated with a Connection object at that time we had 3 connections objects and 3 preparedStatement objects (1 / connection).

Now when we looked at the v$sql table regarding the specific query, we noted that the parse_call was 1. This means that SGA cached the parsed query. Now I understand that the SGA is buffer where come and go. Now may question is this :

• Is it possible under heavy load that the SGA clears all parsed query related to the preparedStatement object so that even if a PreparedStatement is cache in the Application Server the parsed query might be gone on the RDMS side; forcing to prase again the statement.
  • Or do preparedStatement have a different life cycle so while the connection is open no preparedStatement (associated to that connection) is to be "Garbage collected" from the SGA.
  • Because of the PreparedStatement Object Pool does the SGA size need to be increased ?

Thanks for your help.

Alain.

I'd like to have some information about the following : We have t Received on Sat Jul 21 2001 - 18:52:07 CDT