TwelveC

Most of you are aware that Oracle does simple compression when you have a ordered result set by sending the data once along with count for duplicate data. This is prior to Oracle 12c. Starting with Oracle 12c, Oracle supports Advanced Network Compression. Too bad, it is available as part of Advanced Compression option.

Lets forget Oracle for sometime and look at what are the main constraints for networking? It is generally the network bandwidth and data volume. Lets think of network bandwidth as a pipe and data volume to
be water. If you need to send more water across the pipe, you need to have a bigger pipe or convert the water to a different form to occupy less space so that more water in different
form can be send across the same pipe. The best example for data volume is YouTube & NetFlix who gobble up more than 60% of the internet traffic.

Now back to Oracle, Oracle does something similar to what I explained above with advanced network compression. Oracle compresses the data to be transmitted over network at the sending side and then
convert  data back to original at the receiver side to reduce the network traffic ; End result being transparent to user. As with any other advanced compression option, Oracle supports GZIP or LZO for
compression of data.

You can notice significant improvement in performance if network is the bottleneck. If you have a CPU bound system, you are most likely going to make things worst from bad.
How to implement?

1. Using SQL*Net parameters

• SQLNET.COMPRESSION: This parameter enables or disables data compression. Compression will be used only if this parameter is set to ON  at both the server and client side. Also please note that SQLNET.COMPRESSION is not applicable to Oracle Data Guard streaming redo and SecureFiles LOBs.

• SQLNET.COMPRESSION_LEVELS: This parameter specifies the compression ratio. A value of LOW consumes less CPU for compression resulting in lower compression ratio whereas a value of HIGH consumes more CPU for compression resulting in higher compression ratio.

• SQLNET.COMPRESSION_THRESHOLD: This parameter specifies in bytes the minimum data size for which compression will be done. Compression is not done for any value below this size

2. Using TNSNAMES parameters

• Compression can also be enabled using connect descriptor for an individual connection using COMPRESSION and COMPRESSION_LEVELS. These parameter settings have same meaning as SQL*Net compression parameters

• Example:

• mydb= (DESCRIPTION= (COMPRESSION=on) (COMPRESSION_LEVELS=(LEVEL=high)) ADDRESS_LIST=