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

As Thomas Kyte already pointed out, You are probably measuring network performance in the PROC case . You might also try to consider to get COMMITs out of the main processing loop - can't decide without knowing the app/data, but COMMIT
might kill any cache used by DB -and double network effect.

Rolf

uunet <r_at_b.com> schrieb in im Newsbeitrag: pHH27.261453$Z2.3133216_at_nnrp1.uunet.ca...
> A Unique Index was added on EMP.EMPNO column.
> Table contains 10,000 records, unique on the column 'EMPNO'
>
> ==============================================================
> 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
> >
>
>
>
>
>
>
>
>
>
>
Received on Wed Jul 11 2001 - 03:45:11 CDT