REPOST: Re: Why Oracle always reparses anonymous blocks?

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();

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

• WAS CANCELLED BY =======: Path: news.sol.net!spool1-nwblwi.newsops.execpc.com!newsfeeds.sol.net!news-out.visi.com!hermes.visi.com!newsfeed.direct.ca!look.ca!newsfeed1.earthlink.net!newsfeed.earthlink.net!uunet!lax.uu.net!news.navix.net!u-n-c-a-n-c-e-l-l-e-r From: etechweb_at_yahoo.com (Sebastiano Pilla) Newsgroups: alt.config,comp.lang.c,comp.databases.oracle.server Subject: cmsg cancel <1f566w3.mqwvb958hnnmN%etechweb_at_yahoo.com> Control: cancel <1f566w3.mqwvb958hnnmN%etechweb_at_yahoo.com> Date: Mon, 31 Dec 2001 08:08:22 GMT Organization: Navix Internet Subscribers Lines: 2 Message-ID: <cancel.1f566w3.mqwvb958hnnmN%etechweb_at_yahoo.com> NNTP-Posting-Host: 166.102.15.34 X-Trace: iac5.navix.net 1009789975 28060 166.102.15.34 (31 Dec 2001 09:12:55 GMT) X-Complaints-To: abuse_at_navix.net NNTP-Posting-Date: 31 Dec 2001 09:12:55 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 Received on Sat Dec 29 2001 - 03:18:34 CST