Re: Pro host arrays

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 11 Aug 1999 12:44:27 GMT
Message-ID: <37b56b1e.2996919@newshost.us.oracle.com>

A copy of this was sent to nandagopalj_at_hotmail.com (if that email address didn't require changing) On Tue, 10 Aug 1999 23:53:05 GMT, you wrote:

>Hello:
>
>I have been using the host arrays using Pro-C/C++ for my C++ application
>to access data from oracle database. The data being processed is in the
>order of 2.7 million records. To improve performance I have been using
>5000 as the host array size for select statement. This causes oracle to
>respond when it is ready with 5000 records at a time.
>
>Is there a optimum size for host arrays to maximize performance?
>Any info is appreciated.
>

no, in general there is not.

however -- i've empirically found that once you go over about 100 records per fetch, performance decreases. 5,000 is a number I would never use myself.

the only way to be certain is to benchmark it.

Here is a small example. I set this program up to benchmark an array fetch from all_objects. it can array fetch upto 5000 records at a time (but I control it from the command line). For array fetches of 1, 10, 50, 100, 200, 1000, 5000 the results were:

    #fetches   cpu        elapsed             
1   16374      5.08       4.98          0     126551          5       16373
10   1638      3.94       3.94          0      97448          5       16373
50    328      3.22       3.25          0      94625          5       16373
100   164      3.52       3.52          0      94250          5       16373
200    82      3.86       3.91          0      94053          5       16373
1000   17      3.95       4.03          0      93893          5       16373
2000    4      4.08       4.12          0      93857          5       16373

the best timings were 50-100 rows. after that performance starts to drop off again.

you might take this program, modify it to be your query and put a "where rowcount < <some reasonable number>" and run it with varying array sizes to see which is best for you. Use TKPROF on the trace files to get the above statistics after each run.

#include <stdio.h>
#include <string.h>

static char * USERID = "user/pass_at_database"; static int Cnt;

#define SQLCA_INIT
EXEC SQL INCLUDE sqlca;

static void sqlerror_hard()
{

    EXEC SQL WHENEVER SQLERROR CONTINUE;     printf("\nORACLE error detected:");     printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

    EXEC SQL ROLLBACK WORK RELEASE;
    exit(1);
}

static void process()
{

VARCHAR     owner[5000][50];
VARCHAR     object_name[5000][50];
VARCHAR     object_id[5000][50];
VARCHAR     object_type[5000][50];
VARCHAR     created[5000][50];
VARCHAR     last_ddl_time[5000][50];
VARCHAR     timestamp[5000][50];
VARCHAR     status[5000][50];
int         cnt = 0;

    exec sql whenever sqlerror do sqlerror_hard();     exec sql declare c1 cursor for
    select owner, object_name, object_id,

           object_type, created, last_ddl_time, timestamp, status
      from all_objects;

    exec sql alter session set sql_trace=true;     exec sql open c1;
    for( cnt = 0;; cnt++ )
    {

        exec sql for :Cnt fetch c1 into :owner, :object_name, :object_id,
              :object_type, :created, :last_ddl_time, :timestamp, :status;

        printf( "total fetched %d\n", sqlca.sqlerrd[2] );

        if ( sqlca.sqlcode > 0 ) break;
        /* ... process it */

    }
    printf( "Total fetches = %d\n", cnt );

    exec sql close c1;
}

main( argc, argv )
int argc;
char * argv[];
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR oracleid[50];
EXEC SQL END DECLARE SECTION;     if ( argc != 2 )
    {

        printf( "%s N\n", argv[0] );
        exit (1);

    }
    Cnt = atoi( argv[1] );
    printf( "Array fetch size will be %d\n", Cnt );

    strcpy( oracleid.arr, USERID );
    oracleid.len = strlen( oracleid.arr );     exec sql whenever sqlerror do sqlerror_hard();     EXEC SQL CONNECT :oracleid;
    printf("\nConnected to ORACLE as user: %s %d\n\n", oracleid.arr,sqlca.sqlcod e);

    process();

    /* Disconnect from ORACLE. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(0);
}

>Thanks
>Nan.
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 11 1999 - 07:44:27 CDT