Re: Multi threading and ProC

From: Berend Reitsma <berend_at_united-info.com>
Date: 1997/05/05
Message-ID: <336DB7D6.1CF45C3E@united-info.com>

This is a multi-part message in MIME format.

--------------2C35B6C247FA611C26D6A62F
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

On Fri, 2 May 1997, todd lair wrote:

> In article <3368722B.B67F309_at_united-info.com> you wrote:
>
> : When we enable multi-threading in our application we get a
> : performance penalty more than 10 times of the single-threaded
> : application.
>
> I'm running into the exact same problem on a similar platform.
> Did you find anything that helped this situation?

Currently we disable the use of 'ENABLE THREADS' in our source. This seems to work for select statements. We are using this in a semi-production environment for several days and we didn't detect any failure. Insert, update and delete are not tested yet.

We have for each thread a logon and a sql_context wich isn't shared with other threads. We are doing multiple parallel requests in the multi-threaded program (10 threads) and it works.

!!! WARNING !!! WARNING !!!
This is to be debugged and traced. We don't have full test result yet. Use this at your own risk or don't use it at all! We are not responsible for any failures you get when you are using this strategy too!

I have attached a simple ProC program which demonstrates the bad behaviour we get.
Below are outcomes of two runs (with timing!) With a '-e' option you enable threads in the code.

---------------8<----------------

% make ora_mt
proc mode=oracle dbms=v7 lines=yes
include=/opt/SUNWspro/SC3.0.1/include/CC include=/usr/include code=ansi_c threads=yes iname=ora_mt.pc

Pro*C/C++: Release 2.2.2.0.0 - Production on Mon May 5 11:11:37 1997

System default option values taken from: /home/oracle/precomp/admin/pcscfg.h

cc -g -K PIC -Xc -xstrconst -xcg92 -xF -mr -DSLXMX_ENABLE -DSLTS_ENABLE

-D_REENTRANT -I/ui/oracle/precomp/public -Dsolaris2
-D_POSIX_C_SOURCE=199506L 
-D__EXTENSIONS__  -I/home/oracle/precomp/public -c  ora_mt.c

cc -o ora_mt ora_mt.o -L/home/oracle/lib -lclntsh -lsql -lsqlnet -lncr

-lsqlnet -lclient -lcommon -lgeneric -lsqlnet -lncr -lsqlnet -lclient 
-lcommon -lgeneric -lepc -lnlsrtl3 -lc3v6 -lcore3 -lnlsrtl3 -lcore3 
-lnlsrtl3  `cat /home/oracle/rdbms/lib/sysliblist` -lm -lthread

---------------8<----------------

There are just 25 rows in the table calendars. First run without enable threads...

% /usr/bin/time -p ora_mt "select * from calendars" Query: select * from calendars
-1 No Calendar
0 Amsterdam
...
23 Zurich

real 0.30
user 0.09
sys 0.07

Next run with enable threads...

% /usr/bin/time -p ora_mt -e "select * from calendars" Query: select * from calendars
-1 No Calendar
0 Amsterdam
...
23 Zurich

real 16.97
user 0.07
sys 0.20

---------------8<----------------

As you see there is a penalty in enabling threads with Oracle. :-(

We welcome any comment or suggestion on this topic.

With kind regards,
Berend.

--

Berend Reitsma

  United Info B.V.         |  Phone: +31 (0)513 465200
  P.O. Box 10              |  Fax:   +31 (0)513 461588
  8408 ZH Lippenhuizen     |  Email: berend_at_united-info.com
  The Netherlands          |  URL:   http://www.asset-control.com/

--------------2C35B6C247FA611C26D6A62F
Content-Type: text/plain; charset=us-ascii; name="ora_mt.pc"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="ora_mt.pc"

#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include <sqlca.h>
#include <sqlda.h>
#include <sqlcpr.h>

#define MAX_ITEMS 32
#define MAX_VNAME_LEN 30

int do_query(char *qry)
{
  struct sqlca sqlca;
  EXEC SQL BEGIN DECLARE SECTION;
  VARCHAR username[128];
  VARCHAR password[32];
  VARCHAR sql_qry[1024];
  EXEC SQL END DECLARE SECTION;
  char *p;
  int i;
  sql_context ctx;
  SQLDA *select_dp;
  int null_ok;

  EXEC SQL CONTEXT ALLOCATE :ctx;
  EXEC SQL CONTEXT USE :ctx;
  cuserid((char*)username.arr);
  username.len = strlen((char*)username.arr);
  if ((p = getenv("ORACLE_PASSWORD")) == NULL)
    p = "";
  strcpy((char*)password.arr, p);
  password.len = strlen(p);
  
  EXEC SQL WHENEVER SQLERROR GOTO connect_error;
  
  EXEC SQL CONNECT :username IDENTIFIED BY :password;
  
  select_dp = sqlaldt(ctx, MAX_ITEMS, MAX_VNAME_LEN, MAX_VNAME_LEN);
  for (i=0; i < MAX_ITEMS; ++i){
    select_dp->I[i] = (short *)calloc(1, sizeof(short));
    select_dp->V[i] = (char *)calloc(1, 256);
  }
  EXEC SQL WHENEVER SQLERROR GOTO prepare_error;

  sql_qry.len = strlen(qry);
  strcpy((char*)sql_qry.arr, qry);
  EXEC SQL PREPARE SS from :sql_qry;
  EXEC SQL DECLARE SSC CURSOR FOR SS;
  EXEC SQL OPEN SSC;
  select_dp->N = MAX_ITEMS;
  EXEC SQL DESCRIBE SELECT LIST FOR SS INTO select_dp;
  if (select_dp->F < 0)
    goto describe_error;
  select_dp->N = select_dp->F;
  for (i=0; i < select_dp->F; ++i){
    sqlnult(ctx, (ushort*)&select_dp->T[i], (ushort*)&select_dp->T[i],
            &null_ok);
    select_dp->T[i] = 5; /* CHAR ASCIIZ */
    select_dp->L[i] = 255;
  }

  EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;
  while (1)
  {
    EXEC SQL FETCH SSC USING DESCRIPTOR select_dp;
    for (i=0; i < select_dp->F; ++i)
      printf("%s ", select_dp->V[i]);
    printf("\n");
  }
end_select_loop:
  EXEC SQL COMMIT WORK;

  for (i=0; i < MAX_ITEMS; ++i){
    free(select_dp->I[i]);
    free(select_dp->V[i]);
  }
  sqlclut(ctx, select_dp);
  EXEC SQL COMMIT WORK RELEASE;
  EXEC SQL CONTEXT FREE :ctx;    
  return 0;

  EXEC SQL WHENEVER SQLERROR CONTINUE;

prepare_error:
  fprintf(stderr, "Can't prepare\n");
  return 1;

describe_error:
  fprintf(stderr, "Can't describe\n");
  return 1;

connect_error:
  fprintf(stderr, "Can't connect\n");
  return 1;
}

	
int main(int argc, char *argv[])
{
  int n;

  if (argc > 1 && strcmp(argv[1], "-e") == 0)
  {
    EXEC SQL ENABLE THREADS;
    argc--;
    argv++;
  }
  
  for (n = 1; n < argc; n++)
  {
    printf("Query: %s\n", argv[n]);
    if (do_query(argv[n]) != 0)
      printf("Query failed\n");
  }
 return 0;
}


--------------2C35B6C247FA611C26D6A62F--

Received on Mon May 05 1997 - 00:00:00 CDT