| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle API for C/C++ ?
A copy of this was sent to Peter Sylvester <peters_at_mitre.org>
(if that email address didn't require changing)
On Thu, 21 Oct 1999 09:21:48 -0400, you wrote:
>I'll second Kelly's comments.
>
>I haven't used OTL, but tests I've run show that array fetches will
>speed up Pro*C programs that are extracting lots of records by 2-5X,
>depending on the array size. It does require some extra logic, that you
>have to get right, though.
>
Pro*C and OCI perform comparably. I can write slow OCI and fast pro*c (or vice versa). Give me a pro*c program that works slower then a corresponding OCI app and I can make the pro*c go at least as fast (and vice versa).
It is interesting to note that in Oracle8i, release 8.1, you can put array fetching into a pro*c app without changing a line of code. For example, i just ran:
static void process( int x, char * string )
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR object_name[35];
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL ALTER SESSION SET SQL_TRACE=TRUE;
EXEC SQL DECLARE C1 CURSOR FOR
SELECT OBJECT_NAME FROM ALL_OBJECTS
UNION ALL
SELECT OBJECT_NAME FROM ALL_OBJECTS
UNION ALL
SELECT OBJECT_NAME FROM ALL_OBJECTS;
EXEC SQL OPEN C1;
for( ;; )
{
EXEC SQL WHENEVER NOT FOUND DO break;
EXEC SQL FETCH C1 INTO :object_name;
}
and by default, the TKPROF shows me:
select OBJECT_NAME
from
ALL_OBJECTS union all select OBJECT_NAME from ALL_OBJECTS union all
select OBJECT_NAME from ALL_OBJECTS
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.60 0.60 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 4438 17.12 17.56 7 46866 12 8874 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4440 17.72 18.16 7 46866 12 8874
the fetches are 1/2 of the rows. The prefetch count (new pro*c feature) defaults to 1 -- pro*c fetches 2 rows for every 1 you ask for. It caches this row for you and the second fetch you make -- it gives it to you. I then re-precompiled the code with "prefetch=100", i get:
select OBJECT_NAME
from
ALL_OBJECTS union all select OBJECT_NAME from ALL_OBJECTS union all
select OBJECT_NAME from ALL_OBJECTS
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.01 0 0 0 0 Fetch 88 14.58 14.75 0 36717 12 8874 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 90 14.59 14.76 0 36717 12 8874
from the same exact piece of code. note how the fetches went way down (but the number of rows stayed the same). If I had been using a network -- the times would have be very different as well.
Adding array fetching to pro*c apps is now a simple switch (OCI in 8 has a similar prefetch attribute you can set as well)
>Peter
>
>Kelly Burkhart wrote:
>
>> The speed up you notice is probably due to OTL using array fetches
>> which can make a huge difference if you are going over SQL*Net. Pro*C
>> supports array fetches, and probably could be made to perform as well
>> as OTL. I would be very surprised if Pro*C is 2-3 times slower than
>> OCI in a fair comparison.
>>
>> --
>> Kelly R. Burkhart
>> kburk_at_sky.net
>>
>> Just enjoy the Ozzy and keep your mouth shut
>> - George Plimpton (on SNL)
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
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 Thu Oct 21 1999 - 09:51:32 CDT
 |
 |