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