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 11:23:14 +0100, you wrote:

>
>We have a system where there's a fixed list of potential queries
>(up to 200) with run-time supplied parameters. 'Till now we've
>just pumped the queries in using single strings of dynamic SQL,
>and now we'd like to use bind variables to supply the parms at
>run time. We use a multi-threaded environment with several
>threads having open connections to the database, and receiving
>requests from other protocol-handling threads/processes.
>The queries themselves are built from "metadata" in some database
>table, read at startup.
>
>Questions:
> - to get full use of it, presumably we'd have to just parse once
> and use those parsed queries for requests coming in. That
> seems a bit inconvenient right now because of how the thing
> is structured. 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:

select object_name, object_type, status from all_objects where rownum = 1

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.13       0.13          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.02          0         17          3           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.15       0.15          0         17          3           1
********************************************************************************

select object_name, object_type, status from all_objects where rownum = 1

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.02          0         17          3           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          0         17          3           1

As you can see -- the parse time went from measurable to not even measurable. You should expect the same sort of results with your application.

> - The only relevant ESQL syntax would seem to be:
>
>get_query_string ( myquery )
> // e.g. myquery is "select name from cars where price < :p1 "
>
>EXEC SQL PREPARE S from :myquery;
>EXEC SQL DECLARE C CURSOR FOR S;
>EXEC SQL OPEN C USING :myprice;
>
> Problem is if we want to process 200 "generic" queries and
> store the prepare'd queries, how can we refer to the statements?
> The "oracle identifer" (e.g. 'S' above) can't be an array
> name or anything.
>
>e.g.
>EXEC SQL PREPARE S[i] from :myquery;
>
>would be a lot easier. Is there anything we can do for this?
>It would be virtually impossible and horrible to have separate
>SQL statements for every different query, since the whole idea
>is that the queries and handling is generic, from metadata.
>We could have a separate thread for each possible query, but
>that isn't very nice for multi-threaded access, and we'd have
>to have hundreds of threads.
>
>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).

>

>cheers.

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 Sat May 08 1999 - 12:17:13 CDT