Re: Peformance degradation during array fetch in Pro*C

From: Oracle User <lcrusader_at_yahoo.com>
Date: 17 Apr 2003 17:07:31 -0700
Message-ID: <47cd09c.0304171607.4a55476a_at_posting.google.com>


This problem turned out to be due to network issue. Getting data over sqlnet was slowing down the process. Once I ran the program on the instance server, there was no change in fetch time.

Another interesting point I noted is that when I ran the program on the instance server with TWO_TASK set (effectively going through sql*net), performance degraded as usual. It looks like SQL*Net is also a culprit.

Thank you guys for going over this with me.

lcrusader_at_yahoo.com (Oracle User) wrote in message news:<47cd09c.0303220646.5cfecc20_at_posting.google.com>...
> Hi guys,
>
> My program uses host arrays in Pro*C to read data from oracle db. The
> size of the table is arbitrary and ranges from 0 - 10 mil records.
>
> The program worked fine for small tables, but for larger tables,
> performance was poorer than sql*plus spooler. So, I traced the timings
> between each fetch and found that as the program fetched more and more
> rows, the time to fetch the same number of records also grew, from 1
> sec to 15 secs. Since the delay is from the server, I couldn't find
> any way to improve my program.
>
> Is there any way to overcome this hurdle? I have tried the program
> with different array sizes, with the same result. My program uses
> Dynamic SQL and connects over sql*net. I have seen some posts pointing
> to the network connection as a culprit. But that doesn't answer why my
> fetches are faster in the beginning and slower towards the end?
>
> Any help is greatly appreciated.
>
> Thanks.
Received on Fri Apr 18 2003 - 02:07:31 CEST

Original text of this message