Re: ProC ESQL and Bind Variables for multiple queries ?

A copy of this was sent to Ken Tough <ken_at_objectech.co.uk> (if that email address didn't require changing) On Sat, 8 May 1999 22:47:29 +0100, you wrote:

>Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>
>[This also emailed to tkyte_at_us.oracle.com]
>
> Hi Thomas. Thanks for your quick reply:
>
> >Is there still advantages to using Dynamic SQL
> >> method 2, where the query is parsed every time it's executed?
> >> We've been warned our (non-bind var) method could lead to
> >> "latch contention" problems.
>
> >Yes -- by using bind variables you'll be using the shared sql area.
> >Your first parse time after a database startup will be large.
> >Subsequent parses will be very very small. Consider for example
> >this example. I ran the same query 2 times in different sessions.
> >the first trace is from the very first time the query was executed
> >since the database was up, the second taken after the first had run:
>
> Ahh, well that seems to change the picture significantly for me.
>
> Does the same apply if there are intervening different queries?
> That is, if I formulate all my 200 queries as as strings with
> place-holders for bind variables, then use the same piece of code:
>
> EXEC SQL PREPARE S from :mystatement;
> EXEC SQL DECLARE C CURSOR FOR S;
> EXEC SQL OPEN C USING :myvar;
>
> to process each statement, from what you say I gather that still
> gains a big advantage for me. Note that I don't connect/disconnect
> sessions each time, if that helps.
>

yes, you will reap the benefits of shared sql and massively reduced parse times. consider:

select * from emp where ename = 'KING'
select * from emp where ename = 'BLACK'

each of those will take about .1 or more cpu seconds to parse. if "select * from emp where ename = 'KING'" had already been parsed by either the same program or some other program while the database was up, it would be much faster. If you code instead:

select * from emp where ename = :b1

then everyone benefits.

> >>Is there any way to do this in ESQL (rather not use OCI for
> >>portability reasons).
> >
> >OCI is pretty portable - at least as much as pro*c. (and with
> Oracle8.0 and
> >before, its the only way to have an array of cursors given your
> example above).
>
> Okay, thanks, that clarifies the cursor issue. You say with Oracle8.0
> and before it's the only way. Is there something (coming) in a post
> 8.0 release to do it with pro*c?
>

Yes, you can dynamically open a cursor variable, of which you may have an array, using plsql blocks. In 8.0 and before, cursor variables in pro*c needed a precompiled stored procedure to open some static sql OR a static plsql block in a proc program. you can not (in 8.0 and before) dynamically open a cursor variable.

> About OCI, isn't that an oracle-specific API? I take your point
> about pro*c having a lot of oracle-specifics in it as well, though.
>

Yes, OCI is oracle specific (as would be the cursor variable referenced above, as would plsql which would play a big part of it).

> Thanks again for your reply.
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun May 09 1999 - 10:32:37 CDT