RE: Question - Pro*C / OCI binary on Linux -set FETCHSIZE parameter?

If you can find the pro*C source code and you determine that your queries are opened via the open cursor method, as from the Pro*C programmer’s guide,    

int emp_number[20];

float salary[20];  

EXEC SQL DECLARE emp_cursor CURSOR FOR

    SELECT empno, sal FROM emp;  

EXEC SQL OPEN emp_cursor;  

EXEC SQL WHENEVER NOT FOUND do break;

for (;;)

{

    EXEC SQL FETCH emp_cursor

        INTO :emp_number, :salary;

    /* process batch of rows */

    ...

}

...  

This example is set up for 20-row batches looping until it gets fewer than 20 in a fetch. If you can find where your array lengths are defined, make them something else (bigger, I suppose), and that should change things. It does take memory, but memory is not usually much of a concern versus high latency round trips (within reason). Of course if you’re already fetching all the rows in one go and then the network chit chat is parceling out the answer in multiple round trips, that is a different thing altogether. But I suggest you find the source and check this first. You may even find that the application processes one row at a time.  

Good luck. Use of both definitions of Oracle’s overloaded acronym OCI in one post: I think I dithered out which ones you meant both times. If the application is actually built in C using OCI rather than Pro*C calling OCI libraries, you may find that array and buffer sizes are defined for the C program.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chris Taylor Sent: Monday, June 17, 2024 9:27 AM
To: oracle-l_at_freelists.org
Subject: Question - Pro*C / OCI binary on Linux -set FETCHSIZE parameter?  

We recently migrated to OCI (last night) from AWS.  

One of our applications is quite old and built in pro*c (I think - not positive) and uses OCI libraries to connect to the database.  

Due to the new latency 10ms between hosts, this application is suffering due to number of roundtrips required to answer some queries. (>5000 round trips in some cases)  

Does anyone know if its possible to set a system variable in Linux to control the fetchsize/arraysize for an OCI compiled binary that the binary might possibly pick up and use?  

Thanks,  

Chris

--
http://www.freelists.org/webpage/oracle-l