RE: concerning soft parses
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Mar 2004 17:41:07 -0500
Ryan,
In the ideal, perfect world, the first session would connect, the query would
be parsed (that would be a hard parse), then the variables would be
bound, and the statement executed. That session would never have to parse
again. It simply needs to re-bind and re-execute as many times as necessary.
The second session would come along and parse (this would be a soft parse),
then bind variables and execute. Same here, it never needs to parse again.
So, you have one hard parse per unique sql statement and one soft parse per
session per unique sql statement. That's the ideal. Approximately zero
applications work this way! ;-)
To answer your question, utilizing the session cursor cache does not eliminate
soft parsing. It does, however, make for a 'softer' soft parse, which
provides for greater scalability. This can be demonstrated w/ some simple
testing and observing the amount of library cache latching. If you look
at V$STATNAME, you'll see stats such as:
STATISTIC# NAME
---------- ----------------------------------------------------------------
179 parse count (total)
180 parse count (hard)
191 session cursor cache hits
193 cursor authentications
I did some experimentation here, and I thought it was this list that the
results were posted to.....yeah, I just checked my archive, look around 1/8/04
for a thread entitled "Re: Suggestions needed: Latch free - library cache".
Hope that helps,
-Mark
-----Original Message-----
From: ryan.gaffuri@xxxxxxx [mailto:ryan.gaffuri@xxxxxxx]
Sent: Tuesday, March 02, 2004 5:15 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: concerning soft parses
the only way to eliminate a soft parse on a query with bind variables is to set
session_cached_cursors? Now is it possible for two different sessions to share
the same cursor or will this always result in atleast a soft parse?
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
