Re: Peformance degradation during array fetch in Pro*C

Oracle User wrote:
> HI Ethel,
>
> I read 1000 rows at a time into a fixed buffer and process these 1000
> rows before fetching the next batch. I don't store more than 1000 rows
> in memory at any given time. The timings observed in my program are
> only for the FETCH statement alone.
>
> Another interesting fact which I noticed today is:
> The total time taken by my program to fetch 8 mil records is just
> above 7 hours as you can see by my debug statements:
> Mar 28 2003 11:06:04: fetching data
> Mar 28 2003 18:13:38: Total rows processed : 8026633
>
> During this run, I enabled sql trace and ran tkprof on the trace file
> and part of the output is shown here:
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 4015 583.78 11641.59 161001 164971 6
> 8026633
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 4017 583.78 11641.59 161001 164971 6
> 8026633
>
> tkprof shows that the elapsed time is 11641 secs which converts to
> less than 3.5 hours. Now, I am not sure which timing is correct. If I
> were to believe the tkprof output, then I could assume that the
> additional 4 hours overhead is due to the network traffic. Even then,
> how does it answer my question about the gradual decrease in
> performance?
>
> Mar 28 2003 11:06:04: Fetching data
> Mar 28 2003 11:09:36: Fetched 500000 rows -- 3 mins
> Mar 28 2003 11:15:56: Fetched 1000000 rows -- 6 mins
> Mar 28 2003 11:24:38: Fetched 1500000 rows -- 9 mins
> Mar 28 2003 11:36:10: Fetched 2000000 rows -- 12 mins
> Mar 28 2003 11:50:46: Fetched 2500000 rows -- 14 mins
> Mar 28 2003 12:08:25: Fetched 3000000 rows -- 18 mins
> Mar 28 2003 12:29:20: Fetched 3500000 rows -- 21 mins
> Mar 28 2003 12:54:47: Fetched 4000000 rows -- 25 mins
> Mar 28 2003 13:22:49: Fetched 4500000 rows -- 28 mins
> Mar 28 2003 13:53:17: Fetched 5000000 rows -- 31 mins
> Mar 28 2003 14:26:59: Fetched 5500000 rows -- 33 mins
> Mar 28 2003 15:04:08: Fetched 6000000 rows -- 38 mins
> Mar 28 2003 15:44:54: Fetched 6500000 rows -- 40 mins
> Mar 28 2003 16:29:35: Fetched 7000000 rows -- 45 mins
> Mar 28 2003 17:17:54: Fetched 7500000 rows -- 48 mins
> Mar 28 2003 18:10:41: Fetched 8000000 rows -- 53 mins
>
> Thank you
>
> bigjobbies_at_hotmail.com (Ethel Aardvark) wrote in message news:<1a8fec49.0303280118.2c69fa04_at_posting.google.com>...
>

>>Are you reading the entire table into the array, or just bits of it at
>>a time?
>>
>>If it is the entire table, try performing the read but not storing the
>>data - this might help determine if it is the database or the Pro*C
>>that is the bottle neck.
>>
>>ETA

Haven't followed this thread, but there is no gradual performance drop - the first 11 batches take 3 minutes each on average. After that there's a drop in performance, maybe due to other processes starting?

-- 
Regards, Frank van Bortel