Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Linheng.

Asked: October 06, 2015 - 5:30 pm UTC

Last updated: September 03, 2019 - 5:24 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I would like to get your opinion this new parameter SQLNET.COMPRESSION for network performance and the additional ones (compression level) in 12c, and any recommendations to use it under some circumstances.

I know this is a very vague question, but like to understand aspects in using it.

Thanks,
Linheng

and Connor said...

Well, the first thing to remember is that you need to be licensed for the Advanced Compression option in order to use it. So that's a significant decision point right there.

Secondly, is it already happening at a lower level in the stack anyway - that's one for your network admins to advise on. Little point in doubling up if its already being done.

Thirdly, you would want to gauge do you *need* compression over the network ? You would want some good evidence that for a response time of 'x', that a large chunk of 'x' is being consumed via network transmission.

If the answers to those three still end up at "Yes, we think we might benefit", then you would want to do some tests with it on and off, and quantify those benefits (and look at any risks - just about every compression concept, whether it be network, disk, memory, etc...typically consume a CPU resource to do it).

And dont forget, than just plain old sqlnet already has some basic 'compression' in that data is de-duped if possible before transmission. That's why

select * from my_table

will typically use more network than

select * from my_table order by col1, col2

Hope this helps.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to test sqlnet compression

harri, November 19, 2015 - 2:05 pm UTC

Hi!

How can I do a simple test to see that the sqlnet compression really happens when I have tried to set it up?

There doesn't seem to be any difference in these statistics when doing a simple sqlplus test with autotrace on:
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
...should there be a difference between compression on/off or are those statistics always the same regardless of compression settings?

BR,
Harri


Chris Saxon
November 20, 2015 - 2:00 am UTC

I do not know whether those statistics are a measure of pre or post compression.

However, a couple of things to consider

1) there is already some basic compression (aka de-duplication) going on with sqlnet.

Check out these links for some demos

https://jonathanlewis.wordpress.com/2010/05/07/sqlnet-compression/
https://jonathanlewis.wordpress.com/2013/06/09/sqlnet-compression-2/

2) The additional sqlnet compression appears to be targetted mainly a low bandwidth environments. Check out the whitepaper below - they really only saw significant benefits in special cases

http://www.oracle.com/technetwork/database/enterprise-edition/advancednetworkcompression-2141325.pdf


Hope this helps.

SQLNET.COMPRESSION

A reader, February 09, 2016 - 5:51 pm UTC

Thank you for sharing the high level information regarding the parameter in sqlnet.ora! I have not done any further setup and testintg on this, but based on what you described and further update with additional information, I would not think we need to use the parameter.
To have a community like this really opens up knowledge sharing and understanding of some fundamental questions.
Linheng

Chris Saxon
February 10, 2016 - 12:38 am UTC

Thanks for the feedback.

Is there a test to prove it works?

Charles ONeal, June 09, 2017 - 6:19 pm UTC

I have Advanced Compression and a relatively slow WAN between my desktop and remote servers. I can see the compression enabled/and disabled in the client trace files with the sqlnet.ora settings enabled:

trace_level_client = 4
trace_unique_client = on
trace_file_client = sqlnet.trc
trace_directory_client = D:\trace
SQLNET.COMPRESSION = ON  #client and server
SQLNET.COMPRESSION_LEVELS = HIGH #client, server is LOW, HIGH 


Trace file excerpts when it is enabled:
nlstddp_dump_ptable:   SQLNET.COMPRESSION_LEVELS = HIGH
nlstddp_dump_ptable:   SQLNET.COMPRESSION = ON
nspsend: no compression:input=193,output=8403,ratio=-42.54,error=0
nspsend: no compression:input=318,output=8403,ratio=-25.42,error=0
nspsend: compression: input=2214, output=2211, ratio=0.00
nspsend: compression: input=2249, output=2241, ratio=0.00


I liberated a test case from https://stackoverflow.com/questions/29968947/advanced-network-compression-is-not-working-on-oracle-12c and a simple one with autotrace:
SET AUTOT TRACEONLY STATISTICS
SET TIMING ON
SET TIME ON
SET ARRAYSIZE 500
SELECT * FROM DUAL;
SELECT * FROM all_source;  

SET AUTOT TRACEONLY STATISTICS
SET TIMING ON
SET TIME ON
SET ARRAYSIZE 500

SELECT * FROM all_source; 


Generates:

Statistics
----------------------------------------------------------
29272 recursive calls
0 db block gets
138512 consistent gets
14810 physical reads
0 redo size
82122760 bytes sent via SQL*Net to client
24982 bytes received via SQL*Net from client
3092 SQL*Net roundtrips to/from client
2104 sorts (memory)
0 sorts (disk)
1545230 rows processed

The results are the same regardless of the SQLNET.COMPRESSION% settings. High, low, on, off...they are all the same.

There has to be some test other than pretty graphs in a white paper that can demonstrate the impact of this feature which just liberated a fortune from king's purse.
Connor McDonald
December 27, 2017 - 6:20 am UTC

From what I can work out, the stats

bytes sent via SQL*Net to client
bytes received via SQL*Net from client

are derived post-compression, because my tests with various levels of compression etc, all return the same values, which match the uncompressed size

What I *can* see is the change in performance - I did the following over a nice crappy wifi connection :-)

Source table - with lots of potential for compression here

SQL create table comp_Test
  2  as select rownum x, rpad('x',1000,'x') y from dual
  3  connect by level <= 100000;

Table created.


default settings
================

C:\temp>sqlplus scott/tiger@pdb122

SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 27 13:48:05 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Wed Dec 27 2017 13:48:26 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> set timing on
SQL> set arraysize 1000
SQL> set feedback only
SQL> select * from scott.comp_test;

100000 rows selected.

Elapsed: 00:00:24.68



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

(fresh connection)

SQL> set timing on
SQL> set arraysize 1000
SQL> set feedback only
SQL> select * from scott.comp_test;

100000 rows selected.

Elapsed: 00:00:01.93


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

SQL> set timing on
SQL> set arraysize 1000
SQL> set feedback only
SQL> select * from scott.comp_test;

100000 rows selected.

Elapsed: 00:00:00.98


Is the SQLNET.COMPRESSION also used by SQLcL?

Vikram, August 28, 2019 - 3:51 pm UTC

We are planning to use SQLcL, so wanted to confirm if the sqlnet.ora settings are used by SQLcL as well?
If not then what are the options to enable compression and encryption.
Connor McDonald
September 03, 2019 - 5:24 am UTC

I'm not sure if jdbc supports compression (I'll see if I can find out), but as a fallback, sqlcl can be configured to use a thick client like sqlplus

See the FAQ for details

https://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/sqlcl-faq-3014678.html