Re: Prepare Statement and (Oracle's) SGA

Alain Kreienbuhl schrieb:
> 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 w
 e
> 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 ?

AFAIK oracle does caching not on a per connection basis but globally. so if there are two connections executing the same SQL statement the SQL parser in the server does only need to parse it once and the other executions can benefit from this.

        robert

-- 

Robert Klemme
Software Engineer
-------------------------------------------------------------
myview technologies GmbH & Co. KG
Riemekestra e 160 ~ D-33106 Paderborn ~ Germany
E-Mail: mailto:robert.klemme_at_myview.de
Telefon: +49/5251/69090-321 ~ Fax: +49/5251/69090-399
-------------------------------------------------------------