Re: Pro*C, host array performance

"Graham Turner" <turnergw_at_logica.RemoveSpam.com> wrote in message news:992877808.973730_at_ernani.logica.co.uk...
> I seem to have a major performance problem when using host arrays to read
 in
> data, using Oracle 8.1.6.
>
> I have a table of approximately 300,000 rows - It's a very simple table,
> comprising a single unique ID and four other columns - two integers, two
 dates.
> Doing a 'select * from' this table in Sqlplus outputs all the rows in
 about 40
> seconds. Using 'exp' to export the table as a binary chunk takes about 10
> seconds - and yet, in Pro*C, using a simple cursor and host arrays to read
 the
> data in blocks of 1000 rows, it takes 15 minutes to get all the rows! A
 test
> routine to insert the same set of rows, also using host arrays, takes only
> about 2 minutes!
>
> I can see no reason for the massive discrepancy between selecting the
> data in sqlplus, and in pro*c. There is no other complicated processing
 going
> on, the program simply reads the rows and copies them into another
 structure.
> Adding diagnostics to the program shows that the time taken is almost
 entirely
> used by the Oracle reads, not the extra processing.
>
> Has anyone else come across a similar problem? Is there a better way of
 doing
> this? I simply need to read the whole table into memory, but it will
 have to
> be a lot quicker than this.
>
> Any advice greatly appreciated!
>
> Graham
>
>

Looks like your array is too big when compared with the sqlnet packet size, which is by default 2k.
Either you should experiment with setting the S(ession)D(ata)U(nit) in tnsnames.ora and listener.ora, making sure it is a multiple of the TCP/IP M(ax)T(ransmission)U(nit), typically 1500 bytes on many platforms.

My experiments on Solaris with various array sizes showed a steep performance decrease as soon as the size of the array was bigger than a sqlnet packet. In my case the optimum array size was 20 elements, and anything above 100 was just a major disaster.

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Jun 18 2001 - 13:00:26 CDT