Oracle Server - Fetch speed of table with long columns.

Author

Fetch speed of table with long columns.

pista.german@gmail.com

2005-08-23, 11:23 am

Hallo all,
I'm using Oracle DB v. 7.3.4.5.0 (On Unix). Clients run on WXP boxes.
I'm wondering about perfomance of fetching data from tables containing
column of LONG datatype. Most significant impact of this behaviour is
tho one on transfer of data to MS SQL DB (SQL Server 2000) thru linked
server, where fransfer of data (something like select * from
dummy_table) takes enormous amount of time compared to other tables
with similar number of records of data, but without of long column.

I wonder:
If perormance can be really slowed down by that LONG column?
If there is a way for tuning Oracle connection? (e.g. force Oracle to
send larger chunks of data.)
If there is something i can do for optimizing such table? (No structral
changes.)
If I can tune linked server somehow?

Thanks. P.

Ed Prochak

2005-08-23, 1:23 pm

pista.german@gmail.com wrote:
> Hallo all,
> I'm using Oracle DB v. 7.3.4.5.0 (On Unix). Clients run on WXP boxes.
> I'm wondering about perfomance of fetching data from tables containing
> column of LONG datatype. Most significant impact of this behaviour is
> tho one on transfer of data to MS SQL DB (SQL Server 2000) thru linked
> server, where fransfer of data (something like select * from
> dummy_table) takes enormous amount of time compared to other tables
> with similar number of records of data, but without of long column.
>
> I wonder:
> If perormance can be really slowed down by that LONG column?
> If there is a way for tuning Oracle connection? (e.g. force Oracle to
> send larger chunks of data.)
> If there is something i can do for optimizing such table? (No structral
> changes.)
> If I can tune linked server somehow?
>
> Thanks. P.

Have you compared bytes transfered?

One row with a LONG column might transfer many more bytes than hundreds
of rows from a simple table. It might be the disc I/O or the network
I/O. IOW, you might be up against a limit outside ORACLE's control.

Ed.

Oxnard

2005-08-24, 3:23 am

<pista.german@gmail.com> wrote in message
news:1124809138.412956.86590@z14g2000cwz.googlegroups.com...
> Hallo all,
> I'm using Oracle DB v. 7.3.4.5.0 (On Unix). Clients run on WXP boxes.
> I'm wondering about perfomance of fetching data from tables containing
> column of LONG datatype. Most significant impact of this behaviour is
> tho one on transfer of data to MS SQL DB (SQL Server 2000) thru linked
> server, where fransfer of data (something like select * from
> dummy_table) takes enormous amount of time compared to other tables
> with similar number of records of data, but without of long column.
>
> I wonder:
> If perormance can be really slowed down by that LONG column?
> If there is a way for tuning Oracle connection? (e.g. force Oracle to
> send larger chunks of data.)
> If there is something i can do for optimizing such table? (No structral
> changes.)
> If I can tune linked server somehow?
>
> Thanks. P.
>

a long column is just that a long column. I believe long columns could be
used to hold binary data such as graph files.

using sql+ one can control to a large degree using the arrarylist and
buffer. But I cannot say as I have not setup a link between oracle and sql
server. If your using some type of ODBC connection you may try looking for
arraylist and buffer.

sybrandb@yahoo.com

2005-08-24, 7:23 am

1 Yes it can. Due to the LONG rows can get chained into multiple
Oracle blocks.
This will mean a *HUGE* performance hit.
Also the size of your record can exceed the Session Data Unit of
Sqlnet, resulting in multiple roundtrips.
Chaining will however have much more impact than SDU
2 set the SDU in your listener.ora and tnsnames.ora as per the Net 2.3
administrators manual. Note: SDU needs to be a multiple of the MTU of
your network card, usually 1500. By default SDU is 2048.
3 run
analyze table <foo> list chained rows into <chained_rows> table, and
reorganize your table.

--
Sybrand Bakker
Senior Oracle DBA

pista.german@gmail.com

2005-08-25, 9:23 am

Thank you for answer. It pointed me to unknown (to me) Oracle areas. I
will try more combinations of MTU/SDU/TDU in detailed test later. But,
even first tests showed, that speed of transfer can be increased by
tens, in some cases by hundereds of percents.

Regards,
Pista

Sponsored Links