Re: Optimizing fetch of big result set - HELP!
Date: Wed, 25 Jul 2001 18:56:18 +0200
Message-ID: <996080181.15128.0.pluto.d4ee154e_at_news.demon.nl>
[Quoted] "Philippe Laflamme" <plaflamme_at_konova.com> wrote in message
news:cIC77.22126$eY6.2501085_at_news20.bellglobal.com...
> Hello,
> Does anyone have any clues as how to optimize fetching of data from a
result
> set on one table?
>
> Explanation:
> I have a select statement that returns 3 columns and >200000 rows.
> Fetching all these rows (through OLEDB or ODBC) takes ~= 6 seconds...Which
> is too much. I'm already using bulk fetching with no luck (without bulk
> fetching it takes about 40 seconds). The select statement is only on one
> table.
>
> What would be the possible ways to optimize the fetching of theses
results?
>
> Help please....
>
>
1 Please don't crosspost
[Quoted] 2 make sure your number of records times the number of bytes for a single
record does NOT exceed the session data unit of sqlnet, usually 2048 bytes.
[Quoted] [Quoted] If this doesn't work find out what the Max Transmission Unit of your network
[Quoted] [Quoted] card is (usually 1508 bytes) and lower the session data unit in listener.ora
and tnsnames.ora to this number.
[Quoted] [Quoted] You should avoid segmenting by Oracle or by the TCP/IP protocol at all
costs. I have been in a situation where the optimum was to fetch 20 records,
as this was smaller than MTU. As soon as you enlarged the array, performance
collapsed again. These tests where conducted at a customer in a similar
situation.
Hth,
Sybrand Bakker, Senior Oracle DBA Received on Wed Jul 25 2001 - 18:56:18 CEST