Re: PL/SQL vs PROC Performance Issue - Oracle 8.1.6

In article <y9m37.262111$Z2.3155253_at_nnrp1.uunet.ca>, "uunet" says...
>
>Our application has to process INSERTS, UPDATEs and DELETEs of
>transactions that may arrive in some random order.
>

run tkprof and see whats going on

http://asktom.oracle.com/~tkyte/tkprof.html

will fling you to the documentation if you are not familiar with it.

>We wont be able to use 'ARRAYs' all the time to group process transactions
>and issue a commit later on.
>
>Pro*C application and the 'PL/SQL' script were both run on the same server.
>They are not using SQLNET connection.
>
>I agree that PL/SQL will be faster, since the PL/SQL code will be compiled
>on the server and executed in memory (No data traffic), whereas with Pro*C
>program, Update Transaction and COmmit transaction there will be data
>traffic using BEQ connection.
>
>But, Pro*C running 50 times slower than PL/SQL is something I am not able
>to digest. On most other servers (we have quite a few servers running
>Oracle 7.3.4 ... 8.1.7), the ratio is around 2-3.
>i.e. Pro*C is 2-3 times slower than PL/SQL.
>
>So the Question is
>1) Do we need to recompile the Pro*C program with different optimization
>switches ?
>2) Tune the database so that Pro*C programs understand to make use of the
>available CPU power and free memory to get the work done faster .
>
>The test box is 12 Processor box with 40 GB of memory and is 99% idle when
>these tests are run.
>
>
>"Nuno Souto" <nsouto_at_nsw.bigpond.net.au.nospam> wrote in message
>news:3b4c4b30.816444_at_news-server...
>> On Tue, 10 Jul 2001 13:47:15 -0400, "uunet" <r_at_b.com> wrote:
>>
>> Most likely your C program is executing in a client and using Net8 to
>> connect to the server and send 1 row at a time. Use arrays and it will
>> catch up in speed with PL/SQL.
>>
>> Also, this is terribly slow:
>> >
>> > for (empno=1; empno<=num_rec; empno++)
>> > {
>> > EXEC SQL EXECUTE S USING :empno;
>> > EXEC SQL COMMIT;
>> > }
>>
>> COMMIT after every 10 rows and you'll notice a very large performance
>> improvement. Goes for PL/SQL too.
>>
>> Cheers
>> Nuno Souto
>> nsouto_at_bigpond.net.au.nospam
>> http://www.users.bigpond.net.au/the_Den/index.html
>
>

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp