RE: Less bytes when transferring table

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Thu, 2 Apr 2009 15:51:41 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E36060442BC_at_CWYMSX04.Corp.Acxiom.net>

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_at_poderc.com] Sent: Thursday, April 02, 2009 3:25 PM
To: Herring Dave - dherri; oracle-l_at_freelists.org 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_at_freelists.org 

> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Herring 

> Dave - dherri

> Sent: 02 April 2009 19:58

> To: oracle-l_at_freelists.org

> 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.
****************************************************************************

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 02 2009 - 15:51:41 CDT