From oracle-l-bounce@freelists.org Sun Mar 7 15:17:11 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i27LHBk13618 for ; Sun, 7 Mar 2004 15:17:11 -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 i27LH9o13612 for ; Sun, 7 Mar 2004 15:17:09 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6794E394C81; Sun, 7 Mar 2004 16:17:12 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Sun, 07 Mar 2004 16:15:46 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from www.hotsos.com (hotsos.com [209.120.206.15]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7F373394BB6 for ; Sun, 7 Mar 2004 16:15:43 -0500 (EST) Received: from CVMLAP02 ([208.251.46.10]) (authenticated (0 bits)) by www.hotsos.com (8.12.11/8.11.0) with ESMTP id i27LKBLh015098 for ; Sun, 7 Mar 2004 15:20:14 -0600 From: "Cary Millsap" To: Subject: RE: concerning hard parses Date: Sun, 7 Mar 2004 15:17:41 -0600 Message-ID: <001901c40489$a52da840$8b00470a@CVMLAP02> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.3416 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 In-Reply-To: Importance: Normal X-archive-position: 35 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: cary.millsap@hotsos.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l I believe it's safe to infer the closure from SQL trace data upon observing either of two phenomena: - STAT #n indicates that cursor #n has been closed. - PARSING IN CURSOR #n, if #n has been used before, indicates that the former cursor #n has been closed. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com * Nullius in verba * Upcoming events: - Performance Diagnosis 101: 3/23 Park City, 4/6 Seattle - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Tim Gorman Sent: Sunday, March 07, 2004 1:25 PM To: oracle-l@freelists.org Subject: Re: concerning hard parses I haven't seen your script (might have gotten stripped out by the list handler?), but the problem is likely the test bed, SQL*Plus. SQL*Plus is (apparently) coded to conserve cursors. SQL tracing a session in SQL*Plus shows that, no matter how often you execute the same query, it is parsed each time, an indirect indication that the previously-used cursor was closed (note: "close cursor" operations do not show up in SQL tracing, so I am only inferring the closure). Note that even when you execute a different SQL statement, the same cursor number is reused. That is, each "dep=0" (i.e. recursive depth zero) cursor always tends to show up with the same cursor number in SQL*Plus, a further indication of cursor re-use and conservation. SQL*Plus only seems to allocate a new cursor number for each new recursive depth (i.e. "dep" > 0) In other words, it's just the way SQL*Plus is coded, nothing more significant. SQL*Plus is not designed to reduce parsing, but it is probably designed to minimize cursor memory resources. In contrast, most forms and reports tools and batch program APIs tend to encourage the use of the HOLD_CURSOR=TRUE RELEASE_CURSOR=FALSE philosophy, where a new cursor is opened for each SQL statement (unless explicitly closed), but by no means is it a requirement... Hope this helps... on 3/5/04 2:26 PM, ryan.gaffuri@cox.net at ryan.gaffuri@cox.net wrote: > I ran a test with bind variables from sqlplus. I am not sure why Im getting a > hard parse. > > 1. create table myTable as select * from dba_objects; > 2. I then ran a script to tell me how many parses my current session has. > 3. I then initialized a bind variable with 'test_bind.sql' > and ran the following query from test_select.sql > > select object_name > from mytable > where object_name like :object_name > and rownum < 2 > > 4. I then re-initialized the bind variable to a different value and ran it > again. I got a hard parse. Shouldn't I get a soft parse since I'm using bind > variables? > > so that its readable. I attached the results and I attached the 3 scripts I > used. I hope this is ok... > > > ---------------------------------------------------------------- > 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 -----------------------------------------------------------------