Re: Bind variables in anonymous PL/SQL blocks.

Let me test my knowledge here and get Thomas to tell me how far off base I am.

Traditional Dynamic SQL requires that you do a PARSE on your SQL statement, followed by a BIND on each bind variable in your SQL statement. Then you can do an EXECUTE. Each of these are separate calls. However, if you are repeatedly executing the same SQL statement you don't need to redo the PARSE. You can simply do another set of BIND calls followed by another EXECUTE. EXECUTE IMMEDIATE sort of packages the PARSE, the necessary BIND(s), and the EXECUTE into one call. So I am taking the quote from the documentation to mean that the EXECUTE IMMEDIATE incurs the overhad of repeating the PARSE when standard Dynamic SQL would only need to do the next set of BIND(s) and the EXECUTE.

That's my best sense of things based on my limited experiences with EXEC_SQL in Forms.

Best!

Randall

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, b
 ut
> 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 - 00:05:40 CDT