Re: Java prepared statements

You are correct about the reduction of the hard parses. I wrote a similar Java utility to re-execute SQL that has been run on our various databases. Once I had the mechanics of extracting the SQL out, re-running it incremented the "executions" in v$sqlarea.

On 3/21/06, Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com> wrote:
>
> For you Java experts, a question:
>
> I am asking a developer to replace multiple (hundreds of thousands!)
> statements using literals with a single statement using binds. This, I
> gather, is the purpose of a Java "prepared statement". The developer tells
> me that an external program calls this Java utility, which would create the
> prepared statement each time it's called, bind the variables, and execute
> (it's a simple delete from ...where column = value).
>
> Will this result in a single SQL statement in the shared pool, hard parsed
> only once? Will it be soft parsed each time it's created? If so, that
> should achieve the desired result of reducing hard parses (this database
> suffers from very several library cache/shared pool latch contention). Is
> there a way to go even further and eliminate soft parsing, too?
>
> 9iR2 on Solaris, Oracle JDBC driver.
>
> Thanks,
>
>
> Paul Baumgartel
> paul.baumgartel_at_credit-suisse.com
> 212.538.1143
>
>
>
> ==============================================================================
> Please access the attached hyperlink for an important electronic
> communications disclaimer:
>
> http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
>
> ==============================================================================
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l