Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Prepare Statement and (Oracle's) SGA

Re: Prepare Statement and (Oracle's) SGA

From: Robert Klemme <robert.klemme_at_myview.de>
Date: Sat, 21 Jul 2001 23:52:17 GMT
Message-ID: <3B375307.87E94E18@myview.de>

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
-------------------------------------------------------------
Received on Sat Jul 21 2001 - 18:52:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US