Re: Pro*C Performance Problem

It is several weeks since I posted the original note (included below). I received help from George Robson (george.robson_at_cbis.com) and Hans Plambeck <hans_at_mincom.oz.au>. George came up wth the suggestion that greatly improved performance. Both George and Hans had several other suggestions that weren't fixes for this particular problem but I think are good information and are included here. Many thanks to both George and Hans for their help.

The change that dropped the execute time for the test program from 40 seconds to roughly 8 seconds.

>If you are using version 7 of Oracle, you might try passing a hint
>such as select /*+ FIRST_ROWS */ to force the optimizer to user the
>index.

I am using Oracle 7 and as I already said this helped the performance.

What didn't work:

> Remove the CLOSE; just re-OPEN the cursor.

Indeed the ORACLE Precomilers Guide reccommends this also. However, for the tests I ran I didn't see any difference. When I return the long column it runs in 4-5 seconds and roughly 40 seconds when I return to a char (using lo_arg=1 and hi_arg=30)

> Define the VARCHAR host variable as CHAR; it might confirm

    conversion probles.

The VARCHAR host variable is really a C char array field in a struct. However, I declared the host var as a char and no performance change from other tests.

> Is it the transaction mode? Try using 'SET TRANSACTION READ ONLY'

    after connect.

Tried it. No change.

> Explain Plan

The "Explain Plan" for the query is the same regardless of which column is returned. Neither column is indexed.

• query plan

  ---

• TABLE ACCESS BY ROWID LDG_ACCT_MSTR
• INDEX RANGE SCAN LDG_ACCT_MSTR_PK Generated using: (but with different table/index names in the "select")

delete from plan_table;
explain plan set statement_id = ' ' for
  select /*+ INDEX(jimbo jimbo1) */ * from jimbo where     ( a >= 'X' ) and not
    ( a = 'X' and b = '1' and c < '2' )     order by a,b,c;

select '-- '||lpad(' ',2*(level-1))||operation||' '||options||' '||object_name

       "-- query plan" from plan_table start with id = 1
                                       connect by prior id = parent_id;

The original posting:

|> I am having performance problems using Pro*C 1.5 and Oracle7.
|>
|> Below is a Pro*C pgm that shows the problem. It consists of a single
|> cursor that returns one column from a table (not a view). Its
|> performance varies greatly depending on which column is returned and
|> what the local host variable datatype is. Some tests I've run and the
|> approximate clock time it takes them to run.
|>
|> Pgm call Oracle column returned Host datatype Clock Time
|> -------- ---------------------- ------------- ----------
|> bug7 1 30 <ora_number_col NUMBER(7) long 5 secs
|> bug7 1 30 <ora_date_col> DATE long 5 secs
|> bug7 1 30 <ora_date_col> DATE char (VARCHAR) 40 secs
|> bug7 1 30 <ora_vchr_col> VARCAR2(80) char (VARCHAR) 40 secs
|>
|> Anyone know what is happenning here (i.e. why do the last two tests
|> take 8 times as long as the first two?) and what I can do to resolve
|> it? I assume there is more to the slow down that datatype conversion.
|>
|> Please send email and I will post a summary.
|>
|> Thanks,
|> --
|> Paul Masi
|> Lawrence Livermore National Laboratory
|> Internet: pmasi_at_llnl.gov
|> Phone: (510) 422-4501
|>
|> /***** begin pgm *****/
|> #include <stdio.h>
|> #include <string.h>
|>
|> #define DEBUG 0
|>
|> EXEC SQL BEGIN DECLARE SECTION;
|> VARCHAR uid[20],
|> host_date_char[10],
|> host_vchr2[80];
|>
|> long host_long;
|> long host_date_char;
|>
|> long prime99_account,
|> prime00_account;
|>
|> EXEC SQL END DECLARE SECTION;
|>
|> EXEC SQL DECLARE gl_accounts CURSOR FOR
|> SELECT /* <ora_number_col> */
|> /* <ora_varchar2(80)_col **/
|> /* to_char(<ora_date_col>) **/
|> <ora_date_col>
|> FROM <Table>
|> WHERE <indexed_number_col> between :prime00_account
|> and :prime99_account ;
|>
|> EXEC SQL INCLUDE SQLCA.H;
|>
|> main(argc, argv)
|> int argc;
|> char *argv[];
|> {
|> int lo_arg, hi_arg, prm_acct;
|>
|> lo_arg = atoi(argv[1]);
|> hi_arg = atoi(argv[2]);
|>
|> strcpy(uid.arr,"/_at_xxDBxxded);
|> uid.len=strlen(uid.arr);
|> /* Connect to Oracle */
|> EXEC SQL CONNECT :uid ;
|> if (sqlca.sqlcode)
|> {
|> fprintf(stderr,"sqlcode=%ld\n", sqlca.sqlcode);
|> fprintf(stderr,"Error, cannot connect to Oracle.\n");
|> exit(-1);
|> }
|>
|> for (prm_acct=lo_arg; prm_acct <= hi_arg; prm_acct++)
|> {
|> prime99_account = prm_acct*100+99;
|> prime00_account = prm_acct*100;
|>
|> EXEC SQL OPEN gl_accounts;
|> do
|> {
|> EXEC SQL FETCH gl_accounts INTO
|> :host_date_char;
|> /***
|> :host_date_long;
|> :host_vchr2;
|> :host_long;
|> ***/
|>
|> /**
|> host_vchr2.arr[host_vchr2.len] = '\0';
|> printf("host_vchr2 = %s\n", host_vchr2.arr);
|> **/
|> } while (sqlca.sqlcode == 0);
|>
|> EXEC SQL CLOSE gl_accounts;
|> }
|> }
|> /***** end pgm ****/
|>
|> Precompile:
|> (Pro*C: Release 1.5.9.0.1 - Production on Thu Apr 28 08:42:47 1994)
|>
|> /oracle/prod7/bin/proc ireclen=132 oreclen=132 select_error=no dbms=v7 hold_cursor=yes mode=oracle iname=bug7.pc
|>
|> Compile & Link:
|> cc -Bdynamic -L/oracle/prod7/lib -o bug7 bug7.c /oracle/prod7/lib/libsql.a /oracle/prod7/lib/osntab.o -lsqlnet -lora /oracle/prod7/lib/libpls.a -lsqlnet -lnlsrtl -lcv6 -lcore -lnlsrtl -lcv6 -lcore -lm
|>

--
Paul Masi
Lawrence Livermore National Laboratory
Internet:  pmasi_at_llnl.gov
Phone:     (510) 422-4501