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

In article <pHH27.261453$Z2.3133216_at_nnrp1.uunet.ca>, "uunet" says...
>
>A Unique Index was added on EMP.EMPNO column.
>Table contains 10,000 records, unique on the column 'EMPNO'
>

odds are your Pro*c program resides on a machine other then the server and you are going back and forth over the network one row at a time.

PLSQL -- runs in the server.

Both programs would run faster if you employed array processing. Load up arrays and use them as bind variables in the Pro*C app and use the "forall" plsql verb to update an array.

>==============================================================
>1) PROC PROGRAM
>==============================================================
>/*-----------------------------------------------------------------
> Author : Rath Yelandur
> Program : empupd.pc
>
> Usage : empupd [#Records]
>
> Notes : Updates specified number of records in the EMP table
> for the user SCOTT/TIGER
>
> Example : To update 1 Record in EMP table
> empupd
>
> To update 10000 records in EMP Table
> empupd 10000
>-----------------------------------------------------------------*/
>
>#include <stdio.h>
>#include <string.h>
>#include <stdlib.h>
>#include <time.h>
>
>#define USERNAME "SCOTT"
>#define PASSWORD "TIGER"
>
>#include <sqlca.h>
>#include <oraca.h>
>
>EXEC ORACLE OPTION (ORACA=YES);
>
>char *username = USERNAME;
>char *password = PASSWORD;
>VARCHAR dynstmt[80];
>int empno, num_rec=1;
>
>void sql_error();
>
>/************ MAIN ******************/
>
>int main (int ac, char **av)
>{
> double time_diff;
> time_t start_time, end_time;
>
> time (&start_time);
> printf("\n----- BEGIN : %s", ctime (&start_time));
>
> if (ac == 1)
> num_rec=1;
> else
> num_rec=atoi(av[1]);
>
> printf ("Number of Records to Update: %d\n", num_rec);
>
> EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error");
> oraca.orastxtf = ORASTFERR;
>
> EXEC SQL CONNECT :username IDENTIFIED BY :password;
> printf("Connected to Oracle as User: %s\n", username);
>
> strcpy ( (char *) dynstmt.arr,
> "UPDATE EMP SET "
> "ENAME = 'EMP_NAME_X',"
> "JOB = 'EMP_JOB_X', "
> "MGR = 1234, "
> "HIREDATE = SYSDATE, "
> "SAL = 12345.67, "
> "COMM = 12345.67, "
> "DEPTNO = 12 "
> "WHERE EMPNO = :v1"
> );
>
> dynstmt.len = strlen((char *)dynstmt.arr);
>
> EXEC SQL PREPARE S FROM :dynstmt;
>
> EXEC SQL WHENEVER NOT FOUND DO break;
>
> for (empno=1; empno<=num_rec; empno++)
> {
> EXEC SQL EXECUTE S USING :empno;
> EXEC SQL COMMIT;
> }
>
> time (&end_time);
>
> /* Time difference */
> printf ("Updated ... %d ... records in %g Seconds\n",
> num_rec, difftime (end_time, start_time));
>
> printf("----- END : %s\n", ctime (&end_time));
>
> exit(0);
>}
>
>void sql_error(msg)
>char *msg;
>{
> printf("\n%s", msg);
> sqlca.sqlerrm.sqlerrmc[sqlca.sqlerrm.sqlerrml] = '\0';
> oraca.orastxt.orastxtc[oraca.orastxt.orastxtl] = '\0';
> oraca.orasfnm.orasfnmc[oraca.orasfnm.orasfnml] = '\0';
> printf("\n%s\n", sqlca.sqlerrm.sqlerrmc);
> printf("in \"%s...\"\n", oraca.orastxt.orastxtc);
> printf("on line %d of %s.\n\n", oraca.oraslnr,
> oraca.orasfnm.orasfnmc);
>
> EXEC SQL WHENEVER SQLERROR CONTINUE;
>
> EXEC SQL ROLLBACK RELEASE;
> exit(1);
>}
>
>==============================================================
>2) PL/SQL Script
>==============================================================
>
>#!/bin/ksh
>#---------------------------------------------------------------
># Author : Rath Yelandur
># Program : plsql_upd.sh
>#
># Usage : plsql_upd.sh <RecKnt>
>#
># Notes : Updates 1 EMP record by default
># Generates a logfile 'plsql_upd.log'
># Uses SCOTT/TIGER info to login to the database
>#
># Example : To update 1000 records in EMP table
># plsql_upd.sh 1000
>#
>#---------------------------------------------------------------
>
>LF=plsql_upd.log
>
>#--- UpdateRecords() ---
>function UpdateRecords
>{
> REC_KNT=$1
>
> sqlplus -S SCOTT/TIGER 1>>$LF 2>&1 <<-ENDSQL
>
> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
> SET PAGESIZE 0
> SET SERVEROUTPUT ON
>
> DECLARE
> BEGIN_DATE DATE;
> END_DATE DATE;
> TOT_SEC NUMBER;
> BEGIN
> SELECT SYSDATE INTO BEGIN_DATE FROM DUAL;
>
> FOR I IN 1..$REC_KNT LOOP
> UPDATE EMP SET
> ENAME = 'EMP_NAME_X',
> JOB = 'EMP_JOB_X',
> MGR = 1234,
> HIREDATE = SYSDATE,
> SAL = 12345.67,
> COMM = 12345.67,
> DEPTNO = 12
> WHERE EMPNO = I;
>
> COMMIT;
> END LOOP;
>
> SELECT SYSDATE INTO END_DATE FROM DUAL;
> SELECT (END_DATE-BEGIN_DATE)*86400 INTO TOT_SEC FROM DUAL;
> DBMS_OUTPUT.PUT_LINE ('START TIME: ' || BEGIN_DATE);
> DBMS_OUTPUT.PUT_LINE ('END TIME: ' || END_DATE);
> DBMS_OUTPUT.PUT_LINE ('TEST_PLSQL : #REC: ' || $REC_KNT ||
> ', NET TIME: ' || TOT_SEC || ' SECONDS');
>
> END;
>/
>
> SELECT 'END = ' || SYSDATE FROM DUAL;
>
>ENDSQL
>}
>
>#--- Main() ---
>{
> # 1: RecKnt
>
> echo '' > $LF
>
> #--- 1: RecKnt ----
> if [ "$1" = "" ]; then
> REC_KNT=1;
> else
> REC_KNT=$1
> fi
>
> echo "\n--- Updating [$REC_KNT] records in EMP table ---" | tee -a $LF
>
> i=0
>
> #--- Update Records ---
> UpdateRecords $REC_KNT
>
> echo "\n--- TEST COMPLETE - LogFile: [$LF] ---" | tee -a $LF
>
> cat $LF
>}
>
>==============================================================
>
>"Daniel A. Morgan" <Daniel.Morgan_at_attws.com> wrote in message
>news:3B4B31C1.7AD4EA4C_at_attws.com...
>> uunet wrote:
>>
>> > We have an AIX server running Oracle 8.1.6.
>> >
>> > OS : AIX 4.3.3 (64 bit)
>> > Oracle: 8.1.6.2.0
>> > # CPU's: 12 (450 MHz processors)
>> > Memory: 40 GB (RAM)
>> > Oracle: 8.1.6.2.0
>> >
>> > PL / SQL script does an UPDATE/COMMIT of 10,000 records in 2 Secs.
>> > PROC program doing the exact same UPDATE/COMMIT of 10,000 records
 takes
>> > 100 secs.
>> >
>> > Question: Why is PROC 50 times slower than PL/SQL ?
>> >
>> > On our Developer workstations, the PL/SQL to PROC performance ration
 is
>> > about 2-3.
>> >
>> > Need help from the Oracle Gurus out there to identify the bottleneck
 w.r.t.
>> > PROC program.
>> > System was 99% idle and these tests were run in stand alone mode.
>> >
>> > Rath
>>
>> It would be very helpful to see the actual code. Because I have never
>> experienced anything like what you describe.
>>
>> So my first instinct is that the code is not, in fact, identical.
>>
>> Daniel A. Morgan
>>
>
>
>
>
>
>
>
>
>
>

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