| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: concerning soft parses
It's essentially a soft parse which is a session cursor cache hit.
Since the session has the cursor cached, there is less work involved,
and most importantly, less library cache latch activity. Less latch
activity means decreased serialization, means increased scalability.
-Mark
-----Original Message-----
From: Ryan [mailto:ryan.gaffuri_at_cox.net] Sent: Tue 3/2/2004 8:47 PM To: oracle-l_at_freelists.org Cc: Subject: Re: concerning soft parses
> 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_at_cox.net [mailto:ryan.gaffuri_at_cox.net]
> Sent: Tuesday, March 02, 2004 5:15 PM
> To: oracle-l_at_freelists.org
> 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Mar 03 2004 - 01:54:21 CST---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
- application/ms-tnef attachment: winmail.dat
 |
 |