RE: Less bytes when transferring table

From: Tanel Poder <tanel_at_poderc.com>
Date: Thu, 2 Apr 2009 22:24:38 +0200
Message-ID: <36AA94E43CAF4781B295C93BD3111060_at_porgand>

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.


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

Received on Thu Apr 02 2009 - 15:24:38 CDT