RE: Less bytes when transferring table
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: "Tanel Poder" <tanel@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 2 Apr 2009 15:51:41 -0500
Sweet! Thanks Tanel!!! If you ever come across Anjo's blog entry on
this I'd appreciate it if you'd forward it to this list.
David C. Herring | DBA, Acxiom Automotive
630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax
1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com
-----Original Message-----
From: Tanel Poder [mailto:tanel@xxxxxxxxxx]
Sent: Thursday, April 02, 2009 3:25 PM
To: Herring Dave - dherri; oracle-l@xxxxxxxxxxxxx
Subject: RE: Less bytes when transferring table
Yep, Oracle Net does simple compression - when there are lots of rows
with
same consecutive column values sent over sqlnet then Oracle can send the
column only once plus a count. Anjo Kolk wrote about it in his old blog
but
I couldn't find his article anymore.
Check the simple example (look into difference in bytes sent via SQL*Net
even though the result data is the same):
SQL> select owner from dba_source order by dbms_random.random;
299151 rows selected.
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
1994 consistent gets
0 physical reads
0 redo size
2782131 bytes sent via SQL*Net to client
6959 bytes received via SQL*Net from client
600 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
299151 rows processed
SQL>
SQL> select owner from dba_source order by owner;
299151 rows selected.
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
1994 consistent gets
0 physical reads
0 redo size
1572261 bytes sent via SQL*Net to client
6959 bytes received via SQL*Net from client
600 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
299151 rows processed
SQL>
ordered data causes less bytes to be transferred thanks to the simple
compression.
so you can "tune" dblink bulk tranfer over crappy WAN links by ordering
data
somewhat. its not something I'd like to rely on though
--
Regards,
Tanel Poder
http://blog.tanelpoder.com
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Herring
> Dave - dherri
> Sent: 02 April 2009 19:58
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Less bytes when transferring table
>
> I'll admit up front that I'm not a network guy and still have
> trouble spelling TCP. That said, I'm trying to understand
> how the value in "bytes received via SQL*Net from dblink"
> would be less than the size of a table, pulled via a dblink
> from another database and server.
***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.
If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.
Thank You.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
