Oracle 11g Data Compression Tips for the Database Administrator
Oracle 11g Tips by Burleson Consulting

 

One of the exciting new features of Oracle 11g is their inline data compression utility. While it is true that data storage prices have fallen dramatically over the last decade, and continue to fall rapidly, Oracle data compression has far more appealing benefits than simply saving on disk storage cost.  Because indexes and the data itself can be highly compressed, information can be fetched off of the disk devices with less physical IO, which radically improves query performance under certain conditions. 

 

Let's take a closer look at how one would implement Oracle 11g Data Compression in order to achieve the optimal results.

 

Understanding data compression

 

Data compression techniques, such as the Huffman algorithm, have been around for nearly a century, but only today are being put to use within main stream information systems processing.  Using these techniques, a decompression utility is called immediately upon the data block fetch.  Within the Oracle data buffers, the fully uncompressed version of the data remains in the data buffers, even though the information remains compressed on the data blocks themselves.  This leads to an anomaly between the size of information on the data blocks and the size of the information within the data buffers.  Upon applying Oracle data compression, people will find that far more rows will fit on a data block of a given size, but there is still no impact on the data base management system from the point of view of the SGA (system global area). Because the decompression routine is called upon block fetch, the Oracle data buffers remain largely unchanged while the data blocks themselves tend to have a lot more data on them.

 

Read more here: http://www.dba-oracle.com/oracle11g/sf_Oracle_11g_Data_Compression_Tips_for_the_DBA.html

Tests show that 11g compression result is slower transaction throughput but creates less writes because of higher row density on the data block.  See this benchmark of transparent data encryption. Overall, the benchmark slows that I/O writes being reduced while CPU increases, resulting in slowing SQL throughput:

 

*****************************************

Certified Instructors needed!

Burleson Consulting has opportunities for top Oracle instructors to provide
onsite corporate Oracle training. Candidates must be outstanding, dedicated
Oracle professionals with a proven track-record of progressive responsibility
in Oracle database management.

Trainers earn $1000.00 per day plus travel expenses.

Please email resume to job@remote-dba.net along with a cover letter.