Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Q: eXt. Procs. - callbacks

Re: Q: eXt. Procs. - callbacks

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 06 Aug 1999 13:00:14 GMT
Message-ID: <37abd709.2642079@newshost.us.oracle.com>


A copy of this was sent to buzzwang_at_damocles.ourvillage.com (David F. Newman) (if that email address didn't require changing) On 05 Aug 1999 22:04:07 -0400, you wrote:

>
>tkyte_at_us.oracle.com (Thomas Kyte) writes:
>>
>> OCI is the Oracle Call Interface, this is the lowest level interface
>> you as a programmer can code to directly. It is layered on top of
>> Oracle's internal API called UPI/OPI. If you want to code natively
>> in C with no pre-processor, this is what you would use.
>>
>> The precompiler folks have their own (undocumented) API called SQLLIB.
>> You cannot code to SQLLIB yourself. SQLLIB is a layer on top of
>> UPI/OPI. It is what pro*c and the other precompilers "precompile"
>> into. You cannot code (nor would there be any good reason to)
>> directly to SQLLIB. If you want to code directly to an API, you would
>> use the documented and supported OCI layer.
>>
>
>This is very interesting. I was not aware that what Pro*C
>generates is _not_ OCI. I started programming in Pro*C
>because I was able to get started rather quickly. So,
>is it better to use Pro*C or OCI when it comes to performance,
>portability, and development time?

Given a problem to solve I can write either Pro*C and OCI and achieve similar performance from both *most of the time*.

For example: here is a case where OCI will blow away pro*c in performance. In Oracle8i, release 8.1, OCI has an interface to the direct path loader -- pro*c does not. If I was to write a bulk loader program in Oracle8i, release 8.1 and wanted to use the direct path mode, i would write OCI.

For example: here is a case where Pro*c will blow away OCI in development time. Same constraints as above but 1 only plan to use the loader once or twice and i've been given an hour to write it. I would use pro*c due to its ease of use.

For example: here is a case where pro*c will blow away OCI in both performance and development time. I wrote a program. I wasn't aware of the performance gains to be had by array fetching (they can be MASSIVE). I'm told to put array fetches into my program. If I've written this in OCI, i've got to change my databases. If I'm using Pro*C and Oracle8i, release 8.1, I'm in luck. I simply re-precompile my apps with "prefetch=100" and the pro*c code will array fetch 100 rows for me. Here is a small example. the first with with prefetch=0 (no prefetching, fetch 1 row at a time). The second with prefetch=100, array fetch 100 rows:

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.00       0.00          0          0          0           0
Fetch    16341      4.51       4.40          0     125737          5       16340
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    16343      4.51       4.40          0     125737          5       16340



select object_name
from
 all_objects

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

Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      162      3.01       3.01          0      93518          5       16340
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      164      3.02       3.02          0      93518          5       16340



As you can see, with a simple precompile in this case -- Pro*C is easier to tune and I don't have to change any code, just flip a switch. NOTE: you need Oracle8i for this new feature.

I've found pro*c and oci to be 'equally' portable. I've written large oci apps that run on NT and 7 flavors of UNIX without change. I've done the same with Pro*C. To write portable code though, you have to port it and find all the things you need to avoid and things you need to do to make it portable....

So, for most things, pro*c and oci will give you equal performance/portability. I find pro*c to be generally more productive, OCI generally more flexible.

--
See http://govt.us.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 Fri Aug 06 1999 - 08:00:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US