From oracle-l-bounce@freelists.org Wed Mar 3 01:54:21 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i237sLR20811 for ; Wed, 3 Mar 2004 01:54:21 -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 i237sEo20805 for ; Wed, 3 Mar 2004 01:54:15 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3BFF5394E80; Wed, 3 Mar 2004 02:54:06 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 03 Mar 2004 02:53:00 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from tera.umi.com (tera.umi.com [192.195.245.144]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id ADD41394DB7 for ; Wed, 3 Mar 2004 02:52:58 -0500 (EST) Received: from bosmail02.bos.il.pqe (bosmail02.bos.il.pqe [172.24.3.66]) by tera.umi.com (8.11.6/8.11.6) with ESMTP id i237uj921687 for ; Wed, 3 Mar 2004 02:56:45 -0500 Received: from bosmail00.bos.il.pqe ([172.24.3.64]) by bosmail02.bos.il.pqe with Microsoft SMTPSVC(5.0.2195.6713); Wed, 3 Mar 2004 02:56:45 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----_=_NextPart_001_01C400F5.12D893AC" Subject: RE: concerning soft parses Date: Wed, 3 Mar 2004 02:56:45 -0500 Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFC30@bosmail00.bos.il.pqe> X-MS-Has-Attach: X-MS-TNEF-Correlator: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFC30@bosmail00.bos.il.pqe> Thread-Topic: concerning soft parses Thread-Index: AcQAwZln11Ekq14JT0yXJNGDOkL8mgAMzOhj From: "Bobak, Mark" To: X-OriginalArrivalTime: 03 Mar 2004 07:56:45.0922 (UTC) FILETIME=[1303CC20:01C400F5] X-archive-position: 132 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Mark.Bobak@il.proquest.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l ------_=_NextPart_001_01C400F5.12D893AC Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable 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@cox.net] Sent: Tue 3/2/2004 8:47 PM To: oracle-l@freelists.org Cc:=09 Subject: Re: concerning soft parses what is a 'softer' soft parse? ----- Original Message -----=20 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 ----------------------------------------------------------------- ------_=_NextPart_001_01C400F5.12D893AC Content-Type: application/ms-tnef; name="winmail.dat" Content-Transfer-Encoding: base64 eJ8+Ii4HAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEEgAEAGwAAAFJFOiBjb25jZXJuaW5nIHNv ZnQgcGFyc2VzAKEJAQWAAwAOAAAA1AcDAAMAAgA4AC0AAwBLAQEggAMADgAAANQHAwADAAIAOAAt AAMASwEBCYABACEAAABCQTdDREExQzc5MjkxMDQyOTBERjcxOEFBMDJDMTRFRgBCBwEDkAYALA4A ADcAAAADACYAAAAAAAMANgAAAAAAQAA5AKyT2BL1AMQBHgA9AAEAAAAFAAAAUkU6IAAAAAACAUcA AQAAADAAAABjPXVzO2E9IDtwPVBRRTtsPUJPU01BSUwwMC0wNDAzMDMwNzU2NDVaLTEyMTIzMAAe AEkAAQAAABsAAABSZTogY29uY2VybmluZyBzb2Z0IHBhcnNlcwAAQABOAIDOdXzBAMQBHgBaAAEA AAAFAAAAUnlhbgAAAAACAVsAAQAAADcAAAAAAAAAgSsfpL6jEBmdbgDdAQ9UAgAAAABSeWFuAFNN VFAAcnlhbi5nYWZmdXJpQGNveC5uZXQAAAIBXAABAAAAGgAAAFNNVFA6UllBTi5HQUZGVVJJQENP WC5ORVQAAAAeAF0AAQAAAAUAAABSeWFuAAAAAAIBXgABAAAANwAAAAAAAACBKx+kvqMQGZ1uAN0B D1QCAAAAAFJ5YW4AU01UUAByeWFuLmdhZmZ1cmlAY294Lm5ldAAAAgFfAAEAAAAaAAAAU01UUDpS WUFOLkdBRkZVUklAQ09YLk5FVAAAAB4AZgABAAAABQAAAFNNVFAAAAAAHgBnAAEAAAAVAAAAcnlh bi5nYWZmdXJpQGNveC5uZXQAAAAAHgBoAAEAAAAFAAAAU01UUAAAAAAeAGkAAQAAABUAAAByeWFu LmdhZmZ1cmlAY294Lm5ldAAAAAAeAHAAAQAAABcAAABjb25jZXJuaW5nIHNvZnQgcGFyc2VzAAAC AXEAAQAAABsAAAABxADBmWfXUSSrXglPTJck0YM6QvyaAAzM6GMAHgB0AAEAAAAXAAAAb3JhY2xl LWxAZnJlZWxpc3RzLm9yZwAAHgAaDAEAAAAMAAAAQm9iYWssIE1hcmsAHgAdDgEAAAAXAAAAY29u Y2VybmluZyBzb2Z0IHBhcnNlcwAAAgEJEAEAAAACCAAA/gcAAAYTAABMWkZ1D7KaDAMACgByY3Bn MTI14jIDQ3RleAVBAQMB9/8KgAKkA+QHEwKAD/MAUARWPwhVB7IRJQ5RAwECAGNo4QrAc2V0MgYA BsMRJfYzBEYTtzASLBEzCO8J97Y7GB8OMDURIgxgYwBQMwsJAWQzNhZQC6YgSXx0JwQgB5AUEAIw BzFsQHkgYSBzbwGAICMKsRQQIHdoDeBoIJcEAB3yHUFpAiAgYwhwax4gBcBjANBoHrAe4HRWLgqi CoBTC4BjHrB0PyChH4YT4AQgIdIgCmQsdyHCGCAfImwdQR7ABbBr6R8gbnYG8HYjwSEUAHC0ZCAE YHMFQAdwcBfBHQBwdB3QI+Akk2xpYt5yCsAd4CB0C2B0HwEA0AUdkHYg4HkuICBM3yeTKMIlxSkl JlBlBiIFgZ8YICKgCYAfcQciaXoowH8fwSPgK1QhgSvmIHALYGJPAxApYiEUIRQtTQrAa9cvCi9l MTJPBRBnC4AHQGMF0B1BYWdlMTMhFEatA2E6DIIH8HkDkVsAwDEDEHRvOiggAHAuZxZhASAIcUAF oHgubl0UIF0hFR1xM3RUClAgCDMvMjdAMDA0IIA4OjQ3IFBNIRQ+VDSAM4MFsADQJJAtbJ5AA1AJ 4CfQJoBzLgWwMmchFENjM3QhFXVixmoFkDaFUmU6H/ACINchoASgC4BnHhlzIRQe0F8owB8kHRAe MQSQJx4ZPzUw2SAxjiBAxDLZICJgQm9iYWsj4C/SIlQgPC/SLkNjQAMQLlpwA2BxClAmgC4FoG3+ Pjg3RCA5HzojReU2UzbiOHNkYSdRL9EfATAy6yPgN3M1N9AxOAc7tgfw7kU8rz27Lwo+M8MltUXl 7U5wSQOgIdJpAQAHQCPgWnAEkGY78STSbCPUILxmaRQABUAfhiTgdVFg/0viNbApIFGERVEoID4l UtPuYh6wHnMmQCgh0D6hVKdvHgAT4SZAHnMpI+NP5HZ/CsAHMAJgB5FUpk4WBuB1/yYwI+AmIiHT AZAOsAeAAjBVHTB4BZB1DrBkKZFU/z6SUkw1sCWABcAT4CWAIcD+bx5kJcU08AuAKZEc8B4Qfyax HdE1sAmAIrFd4BggLf8ukCYxJiJgYVtFHfAEIAOB/x3gHZAHgiKgIRRTUTIiKCD/IQVOcFvwIfJL 8SwyUmwHgP8d8BewTHEmIh50VaEfMVYJ/x4iIRRXC2CTWBgmIltFKZH/BhBmASQSI+Ag4F0VX9de D+lOFlNvI+B5CGBddAIg1yCxVsdQoiBZ0GlFUR4Q/nEDIFqoJiJvsh4oaIUSgX8fhnCvWsVbxR0R UAcpkUG6cEUheAdwWsEd0XoEkPpvThZhd2An0CBwLOIkxYNnREkgISA7LSlO7f84kFoRA+ASgW8h BcBFUyzzO1uALqF6TGIh2iALZG/ZB5Fub2h1OdFtMcFhoZ8eJ0xhXwR/kiPgaG98MP9dQS0hQ5BR wQWxPw8T8iW1/x7TThZFISlQAQAEIINyCcH/WsFz0S5ZW9IfMSBwaZF/cbta4HmBdCgAW5EewC8e Ef9mAV9jWSUOsHzxZmVDcCxR/ylQfdVrkFnBBUAeMCfPKNHvgaWNYG8iF7BvMAV40QVAAFYkU1RB VE5B9E1FbwMnHcB+MVqEBCB2cxrQKPFzM3BO/JCSSfGQkElDIwewkOFOFjEz/0DElSiWb5d/mI8y tjLFTnDxmqUxNzkeZQWgjRJVoO9/8AdAetaapzgWUJtrVrJ9nI85SoB+TyCVPhWfSTP9oIZhW4BX kR2QeUVO7iug/1BAiiQOwFCxB3ECMCzTa8XPJiKlICHQCGBnaCaRURH/IqMkYjnxIcE+oSHRIRQY IP+SQDRgJMEkIibQJoAsITRwai6rYnkrYGgj4KUgavZ1JoEgkWODQCZBHeFJgT8pQGwBj4KtIVnC IRQxL/w4LzeQThaDdCHQK+FqwfcdgScwLCEiPIIGAKfAMmDffPN/wV/hCYBL0Ewowzmib0IgjXYh FCBzImPGThZIzm9QsKkUIKBscIJgTu3/L8lPRpSrMY8ymE5wQvQ0rz81sTQfNS9uo0iPSZwxNf84 B3uyS9BGrzopbsFLNkv//z3ZuF4h0gIgHdF6YW0SgHj/HgugYR4AU/MewCDgHxBpvd8EAIrlbTAU EaAGXyOEzXD5IBNzPwewgqAfImwxqtH3AJBYYYNjdCTgpTEBICQh/1sRoBXMKRPhIbVrkiAFBbH/ A/CRcWdDB0B6YQQgqgQlIf+QMSSQIqByET+vuL3Xr9i/99nPmUxOcFDVQiHyIbQeMA1R4GMHMUEQ UkFDTMhFLUy7YEFRxNCn4MB0cDovL3fesDox+b4AYXFFotbv4J/hr+K//7q6e9FZ0JJAjBAFAVUB HWHfasG9Mm0RsUHChi0YIEVT/8MPhfRbgIPA5Mk/kNTxIdP/O8SNcTWwY8e62AcQrVMfQS8kMbYi 3ljnmy+tNXMv/cKGL7r33gAfI+1P7li2UpIvAhBtLYwiZS8gc+gvMS6n4G0YAN+v9Y//9p/3r7q6 9//6j/uf+D/br//cv93P3t/5XwMPBB/8j+Qv/+U/5k/nX+hv6X/qj+uf7K//8Y/uz+/f8O/x//MP 9B8XP/8YTxlfBY9AaRwvHT8eT5lM//4P/x8ALwE8Ht8lXyZvHx//OHIG3wfvCP85/AsPDB8NKP8y tg6PE28QrxG8Er8xnxTfvxXvJ085fzqPJ39N5309UAAAHgA1EAEAAAA+AAAAPDRDOUI2RkRBMEIw NkZFNERBRjU5MThCQkYwQUQ4MkNGRUNGQzMwQGJvc21haWwwMC5ib3MuaWwucHFlPgAAAB4ARxAB AAAADwAAAG1lc3NhZ2UvcmZjODIyAAALAPIQAQAAAB8A8xABAAAAQgAAAFIARQAlADMAQQAgAGMA bwBuAGMAZQByAG4AaQBuAGcAIABzAG8AZgB0ACAAcABhAHIAcwBlAHMALgBFAE0ATAAAAAAACwD2 EAAAAABAAAcwXLgIzfQAxAFAAAgwBvbaEvUAxAEDAN4/5AQAAAMA8T8JBAAAHgD4PwEAAAAMAAAA Qm9iYWssIE1hcmsAAgH5PwEAAABMAAAAAAAAANynQMjAQhAatLkIACsv4YIBAAAAAAAAAC9PPVBR RS9PVT1JTkwgQU5OIEFSQk9SL0NOPVJFQ0lQSUVOVFMvQ049TUJPQkFLAB4A+j8BAAAAFQAAAFN5 c3RlbSBBZG1pbmlzdHJhdG9yAAAAAAIB+z8BAAAAHgAAAAAAAADcp0DIwEIQGrS5CAArL+GCAQAA AAAAAAAuAAAAAwD9P+QEAAADABlAAAAAAAMAGkAAAAAAAwAdQAAAAAADAB5AAAAAAB4AMEABAAAA BwAAAE1CT0JBSwAAHgAxQAEAAAAHAAAATUJPQkFLAAAeADJAAQAAABUAAAByeWFuLmdhZmZ1cmlA Y294Lm5ldAAAAAAeADNAAQAAABUAAAByeWFuLmdhZmZ1cmlAY294Lm5ldAAAAAAeADhAAQAAAAcA AABNQk9CQUsAAB4AOUABAAAAAgAAAC4AAAALACkAAAAAAAsAIwAAAAAAAwAGENkPqWcDAAcQMw0A AAMAEBAAAAAAAwAREAAAAAAeAAgQAQAAAGUAAABJVFNFU1NFTlRJQUxMWUFTT0ZUUEFSU0VXSElD SElTQVNFU1NJT05DVVJTT1JDQUNIRUhJVFNJTkNFVEhFU0VTU0lPTkhBU1RIRUNVUlNPUkNBQ0hF RCxUSEVSRUlTTEVTU1dPAAAAAAIBfwABAAAAPgAAADw0QzlCNkZEQTBCMDZGRTREQUY1OTE4QkJG MEFEODJDRkVDRkMzMEBib3NtYWlsMDAuYm9zLmlsLnBxZT4AAABXSg== ------_=_NextPart_001_01C400F5.12D893AC-- ---------------------------------------------------------------- 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 -----------------------------------------------------------------