Prepare Statement and (Oracle's) SGA

Article View: comp.databases.oracle.misc

Article #155

Back to comp.databases.oracle.misc Previous Next

#155

From: Alain Kreienbuhl
Date: Tue, 19 Jun 2001 13:02

33 lines
2542 bytes

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

Technical Details

Message-ID: <3B2F4D6B.72B1787E@fisystem.com>

Path:

arc1-ca.pugleaf.net!archiver1.google.com!newsfeed.google.com!newsfeed.stanford.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed00.sul.t-online.de!t-online.de!grolier!easynet-fr!tengri.easynet.fr!not-for-mail

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: f99a8,ce0a30a991f3a3e0,start
X-Google-Attributes: gidf99a8,public
X-Google-Thread: 1090f0,ce0a30a991f3a3e0,start
X-Google-Attributes: gid1090f0,public
X-Google-Thread: 107d46,ce0a30a991f3a3e0,start
X-Google-Attributes: gid107d46,public
X-Google-ArrivalTime: 2001-06-19 06:05:05 PST
Path: arc1-ca.pugleaf.net!archiver1.google.com!newsfeed.google.com!newsfeed.stanford.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed00.sul.t-online.de!t-online.de!grolier!easynet-fr!tengri.easynet.fr!not-for-mail
Message-ID: <3B2F4D6B.72B1787E@fisystem.com>
From: Alain Kreienbuhl <alain.kreienbuhl@fisystem.com>
X-Mailer: Mozilla 4.7 [fr] (WinNT; I)
X-Accept-Language: fr
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.misc,comp.lang.java.databases,ibm.software.websphere.application-server
Subject: Prepare Statement and (Oracle's) SGA 
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 35
Date: Tue, 19 Jun 2001 15:02:35 +0200
NNTP-Posting-Host: 212.11.24.240
X-Complaints-To: abuse@easynet.fr
X-Trace: tengri.easynet.fr 992955754 212.11.24.240 (Tue, 19 Jun 2001 15:02:34 MET DST)
NNTP-Posting-Date: Tue, 19 Jun 2001 15:02:34 MET DST
Organization: [Posted via] Easynet France