🚀 go-pugleaf

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@fisystem.com> wrote in message
news:3B2F4D6B.72B1787E@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