From oracle-l-bounce@freelists.org Tue Mar 2 19:44:18 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i231iIl17781 for ; Tue, 2 Mar 2004 19:44:18 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i231iIo17776 for ; Tue, 2 Mar 2004 19:44:18 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EBE86394D3B; Tue, 2 Mar 2004 20:44:49 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Mar 2004 20:43:45 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from lakemtao03.cox.net (lakemtao03.cox.net [68.1.17.242]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C2C74394D22 for ; Tue, 2 Mar 2004 20:43:42 -0500 (EST) Received: from ryan2le36ofjce ([68.98.163.81]) by lakemtao03.cox.net (InterMail vM.5.01.06.08 201-253-122-130-108-20031117) with SMTP id <20040303014727.SAQI29592.lakemtao03.cox.net@ryan2le36ofjce> for ; Tue, 2 Mar 2004 20:47:27 -0500 Message-ID: <102e01c400c1$7d4860c0$51a36244@ryan2le36ofjce> From: "Ryan" To: References: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFC2F@bosmail00.bos.il.pqe> Subject: Re: concerning soft parses Date: Tue, 2 Mar 2004 20:47:29 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-archive-position: 121 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ryan.gaffuri@cox.net Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l what is a 'softer' soft parse? ----- Original Message ----- From: "Bobak, Mark" To: 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@cox.net [mailto:ryan.gaffuri@cox.net] > Sent: Tuesday, March 02, 2004 5:15 PM > To: oracle-l@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@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@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@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 -----------------------------------------------------------------