Re: Prepare Statement and (Oracle's) SGA

I have similar observations. My guess [just a guess], for Oracle, it does not make difference
-- source : sql-plus, jdbc, odbc, dbi.... -- owner: user, connection, preparedStatement ...similar requests will go to single "parse_call".

please, see some comments below...

"Alain Kreienbuhl" <alain.kreienbuhl_at_fisystem.com> wrote in message news:3B2F4D6B.72B1787E_at_fisystem.com...
> 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.

 I noted 20-60 sec average life-time for non-repeated request under average load. This time may vary a lot.

> - 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.

most probably Oracle does not care... and aged requests will be coolected...

> - Because of the PreparedStatement Object Pool does the SGA size need
> to be increased ?

Count aproximate number of _different_ prepared Statement in your project. Most probably <1000 will not require adjustments.... Better talk with your DBA.

> Thanks for your help.
>
> Alain.
>

AlexV Received on Sat Jul 21 2001 - 18:52:11 CDT