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
- mydb= (DESCRIPTION= (COMPRESSION=on) (COMPRESSION_LEVELS=(LEVEL=high)) ADDRESS_LIST=