| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Bind variables in anonymous PL/SQL blocks.
I heard from Thomas via email, and what he wrote makes *perfect* sense to me (I should have seen the ramifications earlier ;-). I wanted to post his response and my follow-up reply (also via email):
<Quote>
the execute immediate would be using SHARED SQL however, the statement as a
whole that JAVA would execute would be:
begin execute immediate 'insert into MyTable values (:x,:x,:x)' using 1,2,3;
end;
^^^^^^
constants
in order to insert 4,5,6 you would have to execute:
begin execute immediate 'insert into MyTable values (:x,:x,:x)' using 4,5,6;
end;
^^^^^
different constants
the problem is the anon-PLSQL block submitted by JAVA would not be using bind variables -- that begin/end block submitted by java would trash the shared pool -- java would be submitting totally unique statements -- even though the execute immediate within them would be reusing statements. </Quote>
As I say...in retrospect, this now seems obvious. ;-) I then asked Thomas:
<Quote>
Hello, Thomas!
This makes perfect sense. I didn't initially see that the USING clause would render the PL/SQL block to be different with unique constants, thus resorting to the shared memory pool. Thanks to your persistence, I think I finally understand. ;-)
Another question then, if I may:
You suggested an alternative might be:
sQuery = "begin execute immediate 'insert into myTable values(:x,:y,:z)' using ?,?,?; end;"
For this "one-up" statement, obviously there wouldn't be much performance gain than just submitting it outside the PL/SQL block (as we have been doing). But, what if we were to submit all 30-40 statements like this inside of a PL/SQL block? Would this, then, have a greater chance of leveraging the shared SQL (caching)?
I understand that if our Java code changes some of the statements (by omitting them, or their order) that the block would be effectively "different". But, we've got some areas where the 30-40 SQL calls are always the same number of calls. Do you think the above technique of batching them using JDBC bind variables in the USING clause would help us to realize some performance gains (mostly network based)?
Thanks again for your help!
</Quote>
As I've cut-and-pasted this dialogue, I'm struck by *why* this would need to be in a PL/SQL block. Can't JDBC submit a query that has multiple statements? Something like:
delete MyTable where ID=?;
insert into MyTable values(?,?,?);
I seem to remember that it can't, which is why I've gone down the PL/SQL "wrapper" path...but I'd sure feel sheepish if it could do the above "batch" as a single statement.
Any additional ideas/thoughts would be most appreciated! Thank you Spencer and Randall for your insight. :-)
Regards,
John Peterson
"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 Fri Jun 01 2001 - 09:19:53 CDT
 |
 |