From oracle-l-bounce@freelists.org Sun Mar 7 13:23:59 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i27JNxb09020 for ; Sun, 7 Mar 2004 13:23:59 -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 i27JNxo09014 for ; Sun, 7 Mar 2004 13:23:59 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5A1E5394C71; Sun, 7 Mar 2004 14:24:03 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Sun, 07 Mar 2004 14:22:32 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from mail.sagelogix.com (unknown [69.15.85.3]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 7460A394C68 for ; Sun, 7 Mar 2004 14:22:28 -0500 (EST) Received: (qmail 15000 invoked from network); 7 Mar 2004 19:15:59 -0000 Received: from unknown (HELO ocs.sagelogix.com) (192.168.25.20) by 0 with SMTP; 7 Mar 2004 19:15:59 -0000 Received: from 0-1pool84-181.nas31.thornton1.co.us.da.qwest.net by ocs.sagelogix.com with ESMTP id 480811078686872; Sun, 07 Mar 2004 12:14:32 -0700 User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Sun, 07 Mar 2004 12:25:23 -0700 Subject: Re: concerning hard parses From: Tim Gorman To: Message-ID: In-Reply-To: <20040305212643.LCRM29592.lakemtao03.cox.net@smtp.east.cox.net> Mime-version: 1.0 Content-Type: text/plain; charset="US-ASCII" X-archive-position: 29 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tim@sagelogix.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l 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 -----------------------------------------------------------------