Less bytes when transferring table

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Thu, 2 Apr 2009 12:58:10 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E36060441E5_at_CWYMSX04.Corp.Acxiom.net>

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.

I've got a remote database ("remote_db"), that's sitting on Tru64, running, with a table (TEST_TABLE1_TB). This table was created with PCTFREE 0, has no empty blocks, is 19,950 MB in size, has 270,552,077 rows, has an average row length of 71, 538 extents, and 638,373 blocks (32KB block size for the db). The destination database is running RHEL 4. The transfer is rather simple: truncate the local table, then run:

INSERT /*+ APPEND NOPARALLEL(a) */ INTO test_table1_tb AS SELECT * FROM test_table1_tb_at_remote_db_dbl;

If I check "bytes received via SQL*Net from dblink", it shows that 8,590 MB was transferred, which is 43% of the table's size. But this smaller number does match numbers from /proc/net/dev.

Is there something in Oracle Net that optimizes what is transferred, like doing some sort of compression?

Any help would be appreciated.

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

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.

Received on Thu Apr 02 2009 - 12:58:10 CDT

Original text of this message