[20181023]12c網路資料壓縮.txt

[20181023]12c網路資料壓縮.txt



--//重複測試:
--//我記得以前也有類似測試,就是如果資料重複率很高的情況下,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