Re: Bind variables in anonymous PL/SQL blocks.

Hello, Thomas!

Thank you again for the information. If I understand you correctly, my approach will NOT work because it's not taking advantage of caching? (That is, Oracle can't cache the EXECUTE IMMEDIATE in the way that I'm hoping?)

I noticed this blurb from
http://technet.oracle.com/doc/server.815/a67842/10_dynam.htm#13131:

<Quote>
Caution: You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. However, you incur some overhead because EXECUTE IMMEDIATE re-prepares the dynamic string before every execution. </Quote>

I wasn't sure what was meant by re-prepares the statement. Does this mean it won't use the cache?

From what I can tell from your suggestion, I would need to create a series of prepared statements where I do "n" repetitive operations upon, and use the .addBatch() method to cut down on network traffic. (And I believe it supports bind variables.)

I was hoping my technique would cut down on the number of network calls, but also offer a non-homogenous statement capability. (That is, not all of my "batch" activity needed to be done with the *same* prepared statement.) But it sounds as if I won't be able to take advantage of any caching capability if I go that route. :-(

Is there any other Oracle/JDBC batching facility I could leverage that you know of?

Please let me know if I've understood you correctly. And thank you again! :-)

John Peterson

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:7updht401bmk7ojilf591cluvg04h5lgpq_at_4ax.com...
> A copy of this was sent to "John Peterson" <johnp_at_azstarnet.com>
> (if that email address didn't require changing)
> On Thu, 31 May 2001 14:12:44 -0700, you wrote:
>
> >Thomas,
> >
> >Thank you for such a quick reply! I agree *wholeheartedly* that this is
 an
> >ideal candidate for a stored procedure. I'm trying to get the necessary
> >"buy in" from the management folks to retrofit the application to
 leverage
> >stored procedures. But I think that we're still a ways off.
> >
> >In the interim, I thought if we could come up with a clever way to
 convert
> >our existing statements into equivalent PL/SQL *and* get the benefits of
> >batching, that would bring us one step closer.
> >
> >Looking at Oracle 8i's Native Dynamic SQL "execute immediate"
 functionality,
> >I think we might have hit upon something that could work nicely!
> >
> >Since we use JDBC and bind variables, our queries currently look
 something
> >like:
> >
> >sQuery = "insert into MyTable values (?,?,?)"
> >
> >And we have a corresponding object array for the replaceable parameters.
> >
> >It would be a simple matter to replace the "?" characters with a ":x" (or
> >some such) and then produce:
> >
> >sQuery = "begin execute immediate 'insert into MyTable values (:x,:x,:x)'
> >using 1,2,3; end;"
> >
>
>
> no, that would have to be:
>
> sQuery = "begin execute immediate 'insert into myTable values(:x,:y,:z)'
 using
> ?,?,?; end;"
>
> you would have to BIND to that anyway -- you would achieve no measurable
> increase. If you did not bind, you would kill the shared pool (anonymous
 plsql
> goes there just like sql does)
>
> What you can do is use Oracle batching and >1 prepared statement. If you
 insert
> 20 rows in to myTable, update 5 rows in myOtherTable, delete 3 rows from
> yetAnotherTable -- Oracle can batch them up so that there are 3 network
 calls --
> not 28...
>
>
>
>
> >Then I could batch up each of these statements using an "encapsulating"
> >BEGIN/END block and submit it.
> >
> >I dunno if this will yield a performance improvement, but that's my next
> >exercise. ;-)
> >
> >One thing that caught my eye from
> >http://technet.oracle.com/doc/server.815/a67842/10_dynam.htm#13131:
> >
> ><Quote>
> >Caution: You can execute a dynamic SQL statement repeatedly using new
 values
> >for the bind arguments. However, you incur some overhead because EXECUTE
> >IMMEDIATE re-prepares the dynamic string before every execution.
> ></Quote>
> >
> >What does this mean, exactly? Will the queries that participate in
 EXECUTE
> >IMMEDIATE statements *not* be available in the query cache? If so...that
> >kind of defeats the purpose of the bind variable approach I've outlined
> >above. If you have any more clarification, I'd sure welcome it!
> >
> >Thank you again for your insight!
> >
> >Regards,
> >
> >John Peterson
> >
> >"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
> >news:9f67uv02ob9_at_drn.newsguy.com...
> >> In article <thd1v1ggho701b_at_corp.supernews.com>, "John says...
> >> >
> >> >(Oracle 8.1.7 and JDBC 2.0)
> >> >
> >> >Hello, all!
> >> >
> >> >I'm helping to investigate some potential performance enhancements
 with
 my
> >> >company's application and I've got a couple of questions that I hope
 you
 all
> >> >can help me with. We employ a 3-tier architecture where our 3rd tier
 is
 the
> >> >database access tier. All of the business logic and queries reside in
 this
> >> >tier (Java-based) and not in the database (in the form of stored
 procedures,
> >> >for example).
> >> >
> >> >We've identified a number of "business units" in this tier which may
 make
 a
> >> >number of database calls (sometimes as many as 30-40 queries). We
 also
 try
> >> >to make heavy use of bind variables wherever possible to leverage
 Oracle's
> >> >query preprocessing capability. We are hoping to be able to somehow
 "batch"
> >> >these 30-40 statements together instead of submitting them
 one-at-a-time.
> >> >It appears that the batch facility of JDBC 2.0 works great for
 identical
> >> >statements, but it seems to fall a little short with different types
 of
> >> >statements. (See Section 3.4 of
> >> >http://developer.java.sun.com/developer/Books/JDBCTutorial.)
> >> >
> >> >So...we were thinking of potentially "wrapping" these 30-40 statements
 into
> >> >an anonymous PL/SQL block and making a single submission to the
 database.
> >> >But, I wasn't sure if this approach would allow us the ability to
> >> >incorporate bind variables.
> >> >
> >> >If anyone has any ideas/suggestions and code samples, I'd *greatly*
> >> >appreciate the feedback! :-)
> >> >
> >> >Regards,
> >> >
> >> >John Peterson
> >> >
> >> >
> >>
> >>
> >> well, you could -- but this seems to be screaming "STORED PROCEDURE" if
 you ask
> >> me.
> >>
> >> Rather then have java glue together some 30/40 odd statements, wrap a
 begin/end
> >> around them and then bind -- why not just bind the inputs to a stored
 procedure
> >> that has the 30/40 odd statements.
> >>
> >> If the reason is "well the 30 or 40 odd statements change in the number
 of
> >> statments and their order" -- then you would be defeating the shared
 sql
 concept
> >> (as bad as not using bind variables at all). You would be creating a
 whole
> >> bunch of unique, BIG plsql blocks that others would tend to not reuse.
> >>
> >> You could almost forget about "forcing yourself to use bind variables"
 by
 using
> >> stored procedures. It would become very very natural.
> >>
> >> Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
> >> Howtos and such: http://asktom.oracle.com/
 http://asktom.oracle.com/~tkyte/
> >> Oracle Magazine: http://www.oracle.com/oramag
> >> Opinions are mine and do not necessarily reflect those of Oracle Corp
> >>
>
> --
> Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
> Howtos and such: http://asktom.oracle.com/
 http://asktom.oracle.com/~tkyte/
> Oracle Magazine: http://www.oracle.com/oramag
> Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Thu May 31 2001 - 21:42:49 CDT