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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: concerning soft parses

RE: concerning soft parses

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 2 Mar 2004 17:41:07 -0500
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFC2F@bosmail00.bos.il.pqe>


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
Received on Tue Mar 02 2004 - 16:37:57 CST

Original text of this message

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