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@xxxxxxx] Sent: Tue 3/2/2004 8:47 PM To: oracle-l@xxxxxxxxxxxxx Cc: Subject: Re: concerning soft parses what is a 'softer' soft parse? ----- Original Message ----- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Tuesday, March 02, 2004 5:41 PM 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@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 > ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------