Re: PL/SQL vs PROC Performance Issue - Oracle 8.1.6
Date: Tue, 10 Jul 2001 13:47:15 -0400
Message-ID: <pHH27.261453$Z2.3133216_at_nnrp1.uunet.ca>
[Quoted] [Quoted] A Unique Index was added on EMP.EMPNO column. Table contains 10,000 records, unique on the column 'EMPNO'
- PROC PROGRAM
/*----------------------------------------------------------------- [Quoted] 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 <stdlib.h>
#include <stdio.h>
#include <string.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;
[Quoted] 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 recordstakes
[Quoted] > > 100 secs. > > > > Question: Why is PROC 50 times slower than PL/SQL ? > > > > On our Developer workstations, the PL/SQL to PROC performance rationis
[Quoted] > > about 2-3. > > > > Need help from the Oracle Gurus out there to identify the bottleneckw.r.t.
[Quoted] > > PROC program. [Quoted] > > 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 Tue Jul 10 2001 - 19:47:15 CEST