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

From: uunet <r_at_b.com>
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'

---

1. 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 <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;

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

 takes

[Quoted] > > 100 secs.
> >
> > Question:   Why  is  PROC 50 times slower than PL/SQL ?
> >
> > On our Developer workstations,   the  PL/SQL to PROC performance ration

 is

[Quoted] > > about 2-3.
> >
> > Need  help from the Oracle Gurus out there to identify the bottleneck

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