REPOST: Re: Why Oracle always reparses anonymous blocks?

In article <1f566w3.mqwvb958hnnmN%etechweb_at_yahoo.com>, etechweb_at_yahoo.com says...
>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>
>> It is upto the programmer to ensure a block is parsed only ONCE per
>> program, not once per execution.
>>
>> I see this lots in java, the developers open a callable statement (that
>> PARSES it, it'll be a soft parse, not as bad as a hard but still overhead
>> and inhibits scalability), bind to it (if we are lucky), execute it and
>> then close it.
>>
>> They should:
>>
>> open it once per session.
>>
>> bind it / execute it many times over and over.
>>
>> close it or just disconnect, that'll close it.
>>
>> then you will see the parse count go waaaayyy down.
>
>Is this the correct thing to do even when (in Java) using pooled
>connections? We are using pooled connection with OC4J and Oracle 8.1.7
>JDBC drivers, and many code samples that I've seen recommend to close
>the statement and the logical connection after the execution.
>
>For example, after doing something like:
>
>stmt = connection.prepareCall(...);
>stmt.setString(...);
>rset = stmt.executeQuery();
>
>we do (in a finally block):
>
>rset.close();
>stmt.close();
>connection.close();
>

the key is:

>>
>> open it once per session.
>>

if you are closing the connection, you should close up the statements. You should strive to open a statement ONCE per session, not once per execution per session.

>to return the logical connection to the pool. The next time we ask for a
>logical connection, the pool may give us a completely different
>"physical" connection to the database: what to do in this case? I don't
>know (and I won't be able to try until 1/7) if the statement reference
>is still meaningful after closing the connection it was obtained from.
>Do you recommend to leave the stmt reference in the above pseudo-code as
>a class-scoped variable (as opposed to a method-scoped variable)?
>
>(BTW: OC4J 1.0.2.2.1, Oracle 8.1.7 [patched to 8.1.7.2 IIRC], Windows
>2000 SP2)
>
>Thanks for any pointers.
>Sebastiano Pilla

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

========= WAS CANCELLED BY =======:
Path: news.sol.net!spool1-nwblwi.newsops.execpc.com!newsfeeds.sol.net!newsengine.sol.net!137.192.100.17.MISMATCH!upp1.onvoy!onvoy.com!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!newsfeed.direct.ca!look.ca!newshub2.rdc1.sfba.home.com!news.home.com!newsfeed.mesh.ad.jp!osa.uu.net!sac.uu.net!lax.uu.net!news.navix.net!u-n-c-a-n-c-e-l-l-e-r
From: Thomas Kyte <tkyte_at_us.oracle.com>
Newsgroups: alt.config,comp.lang.c,comp.databases.oracle.server
Subject: cmsg cancel <a0ksng02jlf_at_drn.newsguy.com>
Control: cancel <a0ksng02jlf_at_drn.newsguy.com>
Date: Mon, 31 Dec 2001 08:46:21 GMT
Organization: Navix Internet Subscribers
Lines: 2
Message-ID: <cancel.a0ksng02jlf_at_drn.newsguy.com>
NNTP-Posting-Host: 166.102.15.34
X-Trace: iac5.navix.net 1009788936 26548 166.102.15.34 (31 Dec 2001 08:55:36 GMT)
X-Complaints-To: abuse_at_navix.net
NNTP-Posting-Date: 31 Dec 2001 08:55:36 GMT
X-No-Archive: yes
Comment: Anarchy! Fuck You!
X-Commentary: I love NewsAgent 1.10, Sandblaster Build 74 (19 March 1999) and the Polaris Cancel Engine V. 6.1
X-Unacanc3l: yes

This message was cancelled from within Mozilla...not