[20181023]12c网络数据压缩.txt

https://connor-mcdonald.com/2018/01/08/licensed-for-advanced-compression-dont-forget-the-network/

--//重复测试:
--//我记得以前也有类似测试,就是如果数据重复率很高的情况下,oracle有一定的压缩传输的.

1.环境:
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SCOTT@test01p> create table comp_Test as select rownum x, rpad('x',1000,'x') y from dual connect by level <= 100000;
Table created.

2.测试:
SCOTT@test01p> select sid, network_service_banner from V$SESSION_CONNECT_INFO where sid = sys_context('USERENV','SID');
       SID NETWORK_SERVICE_BANNER
---------- ------------------------------------------------------------------------------------------
       183 Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 12.2.0.1.0 - Production
       183 Authentication service for 64-bit Windows: Version 12.2.0.1.0 - Production
       183 NTS Authentication service adapter for 64-bit Windows: Version 2.0.0.0.0 - Production
       183 Encryption service for 64-bit Windows: Version 12.2.0.1.0 - Production
       183 Crypto-checksumming service for 64-bit Windows: Version 12.2.0.1.0 - Production

set timing on
set arraysize 1000
set feedback only

select * from scott.comp_test;
Elapsed: 00:00:02.92

SCOTT@test01p> @ viewsess 'bytes sent via SQL*Net to client'
NAME                                                                   STATISTIC#      VALUE        SID
---------------------------------------------------------------------- ---------- ---------- ----------
bytes sent via SQL*Net to client                                             1707  102119457        183

3.修改sqlnet.ora加入如下:

SQLNET.COMPRESSION=on
SQLNET.COMPRESSION_LEVELS=(low,high)
SQLNET.COMPRESSION_THRESHOLD=1024

--//重复测试,要重新登陆:
SCOTT@test01p> column NETWORK_SERVICE_BANNER format a100
SCOTT@test01p> select sid, network_service_banner from V$SESSION_CONNECT_INFO where sid = sys_context('USERENV','SID');
       SID NETWORK_SERVICE_BANNER
---------- ----------------------------------------------------------------------------------------------------
       183 Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 12.2.0.1.0 - Production
       183 Oracle Advanced Network Compression Service for 64-bit Windows: Version 12.2.0.1.0 - Production
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~       
       183 NTS Authentication service adapter for 64-bit Windows: Version 2.0.0.0.0 - Production
       183 Encryption service for 64-bit Windows: Version 12.2.0.1.0 - Production
       183 Crypto-checksumming service for 64-bit Windows: Version 12.2.0.1.0 - Production

--//注意看下划线与上面的显示不同。

set timing on
set arraysize 1000
set feedback only
select * from scott.comp_test;
Elapsed: 00:00:01.52
--//不明显，但是还是快一点。

SCOTT@test01p> @ viewsess 'bytes sent via SQL*Net to client'
NAME                                                                   STATISTIC#      VALUE        SID
---------------------------------------------------------------------- ---------- ---------- ----------
bytes sent via SQL*Net to client                                             1707  102126557        183
--//通过这个参数看不出来。

4.修改sqlnet.ora加入如下:

DEFAULT_SDU_SIZE=1048576
SQLNET.COMPRESSION=on
SQLNET.COMPRESSION_LEVELS=(low,high)
SQLNET.COMPRESSION_THRESHOLD=1024

--//12c支持更大的SDU.
set timing on
set arraysize 1000
set feedback only
select * from scott.comp_test;
Elapsed: 00:00:01.57
--//看不出效果!!

SCOTT@test01p> set feedback 6
SCOTT@test01p> @ viewsess 'bytes sent via SQL*Net to client'
NAME                                                                   STATISTIC#      VALUE        SID
---------------------------------------------------------------------- ---------- ---------- ----------
bytes sent via SQL*Net to client                                             1707  102113743         87