Improve performance by Compression
New Features in lists the following new features among others:
- Larger Session Data Unit Sizes
Oracle Net supports large session data unit (SDU) sizes, with a new upper limit of 2 MB. The larger SDU size can be used to achieve better utilization of available network bandwidth in networks that have high bandwidth delay products and host resources, according to application characteristics.
- Advance Network Compression
Advanced Network Compression is another new feature that reduces the amount of data transmitted over the network. New parameters for the sqlnet.ora file enable compression and selection of the preferred compression level. Advanced Network Compression is available as part of Advanced Compression Option.
Setup Compression
Without compression, I ran the following tests:
SQL> set arraysize 1000
SQL> set timing on autotrace traceonly statistics
SQL> select * from dba_objects;
90931 rows selected.
Elapsed: 00:00:00.74
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6698 consistent gets
0 physical reads
0 redo size
3847666 bytes sent via SQL*Net to client
1541 bytes received via SQL*Net from client
92 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90931 rows processed
SQL> /
90931 rows selected.
Elapsed: 00:00:00.64
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6698 consistent gets
0 physical reads
0 redo size
3847666 bytes sent via SQL*Net to client
1541 bytes received via SQL*Net from client
92 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90931 rows processed
SQL> /
90931 rows selected.
Elapsed: 00:00:00.67
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6698 consistent gets
0 physical reads
0 redo size
3847666 bytes sent via SQL*Net to client
1541 bytes received via SQL*Net from client
92 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90931 rows processed
In /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora , the following lines were added:
sqlnet.compression=on SQLNET.COMPRESSION_LEVELS=(low) SQLNET.COMPRESSION_THRESHOLD=1024
Repeating the same test again, I get similar results:
SQL> connect sys@plum as sysdba
Enter password:
Connected.
SQL> set arraysize 1000
SQL> set timing on autotrace traceonly statistics
SQL> select * from dba_objects;
90931 rows selected.
Elapsed: 00:00:00.69
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6698 consistent gets
0 physical reads
0 redo size
3847666 bytes sent via SQL*Net to client
1541 bytes received via SQL*Net from client
92 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90931 rows processed
SQL> /
90931 rows selected.
Elapsed: 00:00:00.70
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6698 consistent gets
0 physical reads
0 redo size
3847666 bytes sent via SQL*Net to client
1541 bytes received via SQL*Net from client
92 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90931 rows processed
SQL> /
90931 rows selected.
Elapsed: 00:00:00.71
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6698 consistent gets
0 physical reads
0 redo size
3847666 bytes sent via SQL*Net to client
1541 bytes received via SQL*Net from client
92 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90931 rows processed
This is strange. I am connecting to the local host ( personal ) via SQL*Net because of the PDB.