Re: ProC ESQL and Bind Variables for multiple queries ?

Thomas Kyte <tkyte_at_us.oracle.com> wrote:

[This also emailed to tkyte_at_us.oracle.com]

>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.

>>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?

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.

Thanks again for your reply.

--
Ken Tough Received on Sat May 08 1999 - 16:47:29 CDT