Re: Bind variables in anonymous PL/SQL blocks.

i think Thomas spelled it out reasonably clearly.

yes, the EXECUTE IMMEDIATE will force the statement to be executed as dynamic SQL.

"re-prepares" means that oracle will not be able to reuse (take advantage of) a previously prepared statement.

and reuse of previously prepared statements is the reason that i (as a dba) would want you to use bind variables in the first place.

yes, using a PL/SQL block can be a good way to reduce network roundtrips and improve performance, when they are used appropriately.

but a PL/SQL block as a "wrapper" around a dynamic set of SQL, or as a "wrapper" around a set of dynamic SQL statements is just going to cause even more havoc in SQL area (i.e. "kill the shared pool")

if you have a series of identical statements to perform, then "batching" them (using the JDBC feature) would seem to be a reasonable way to go.

but stored procedures (especially as part of a package) can be an even better route to improving performance.

"John Peterson" <johnp_at_azstarnet.com> wrote in message news:the09fj89sqj03_at_corp.supernews.com...
> 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 - 23:56:48 CDT