As of March 2011, Oracle RDBMS occupies 48.1% of the market share (according to Gartner Worldwide). With each new release; a lot of known security vulnerabilities, security holes and other bugs are rectified. But more importantly, with each new version release you’ll find a new feature or accompanied by an introduction to a new system or a new feature. Database compression is one such factor each version release tries to enhance; and in Oracle 11g, compression has been claimed to achieve a 3:1 ratio. What Oracle claims is impressive, but it needs to be substantiated with a proper case. Along with a case study, it’s important to understand what the new version of Oracle brings to the table. So, let’s start with taking a long look at Oracle compression techniques and how it’s evolved over the past few versions.
Evolution of compression techniques
Before we get on with 11g’s compression algorithm, let’s take a quick look at the algorithms employed by the previous versions. Enhancing database compression ratios means working on the compression algorithm itself. Till Oracle 11g, table compression was not recommended for OLTP environment or table with high OLTP. But from 11g onwards, OLTP compression is supported, so that’s a step forward for database compression.
Oracle 8i employed a simple index compression algorithm first. Then, in Oracle 9ir2, the algorithm was shifted to table-level compression. The next change happened in Oracle 10g, with the company deciding to use the LOB compression algorithm. Now, the latest release (Oracle 11g) employs a new algorithm that follows row-level compression. Oracle 11g also employs columnar compression for Exadata storage servers.
Advantages of 11g’s compression algorithm
This new algorithm not only compresses at a better ratio (as a result, saving your hard disk space), but it also promises the benefits given below:
1. Better network traffic flows: This is possible because the actual compression-decompression process is done within Oracle itself. As a result, the sizes of external network packets are small, giving a better traffic flow.
2. Better I/O operations: Since the tables reside in fewer data blocks, index range scans and full table scans will result in less I/O operations.
3. Significant reduction in buffer cache requirement: Another benefit of better compression is the better utilization of buffer cache. In this case, both disk and buffer cache requirements are reduced.
4. Cheaper investments in solid state disks: Compressed tables will only need lesser data blocks to be stored on. Because of that, organizations can be able to afford solid state flash disks. Investing in solid state disks can result in I/O operations speed up to 300 times faster than platter disk.
Some concerns are there with regards to Oracle 11g’s compression, such as overhead costs. First and foremost, the exact overhead cost of Oracle 11g is not known. For calculating such an estimate, DML operations (such as hot blocks, outbound data block, data block unlinking, incoming disk blocks and RAM demands) must be taken into account.
Oracle 11gr2
The release of Oracle 11gr2 also sees the introduction of the ‘Exadata Hybrid Columnar Compression (EHCC)’. Claiming to significantly increase performance while reducing the overall cost of storage, EHCC is a feature that will benefit data warehousing on exadata database machines.
When comparing columnar compression to row-level, it’s shown that compressing data column-wise is more effective. Oracle explains this as follows – compression works when there’s a repeated pattern in data. And since there’s more repetition present in columns than in rows, compression works better column-wise. It’s important to note that EHCC is only available for exadata database machines.
Now, the downside to this algorithm is its overhead costs. DML operations are expensive, since a single row insert will result in updating all the other data present in the columns. To implement columnar compression in Oracle 11gr2, you can use the “COMPRESS FOR ARCHIVE LEVEL=compression_level” syntax in the CREATE TABLE statement. Compression_level can also be assigned the value of 1, 2 or 3. A higher value does lead to a higher compression rate and a decrease in table scan times, but its associated costs also increase along with the DML operation times. Whether your database server is Exadata or not, you’ll find that compression using Oracle 11g is impressive.
Database compression is out there, offered by companies like Oracle, Microsoft etc. So, it’s imperative that new RDBMS releases feature new compression algorithm s that are capable of better database compression than their predecessors. Hence, to check whether the compression rate claimed by Oracle 11g is accurate or not; we, at Nous Infosystems, conducted a case study to see firsthand what Oracle 11g’s compression is capable of. But before getting on with the case, let’s take a quick peek at how Oracle 11g’s compressed database information and what are the processes through which it follows.
Oracle 11g’s compression process
As mentioned previously, Oracle 11g offers two kinds of compression algorithms – row-level and column level.
Row-level
Let’s take a look at how Oracle 11g row-level compression works.
The above diagram gives you a pictorial representation of how the compression system really works. It follows these steps:
- Even though a table is created using the compression command, initially it is uncompressed.
- Once the table is created, new data is loaded directly and also updated accordingly.
- Once the data gets loaded and the PCTFREE limit is attained, then Oracle’s algorithm works it magic and starts compressing.
- Accordingly, data is inserted and deleted from the table and updates decompress the table rows.
- After the PCTFREE limits are reached again, the data is compressed again and then rows are packed down.
Column level compression
When Exadata servers are used, then 11g gives you the option to compress data column wise. As previously noted, column-wise compression provides you with a better compression ratio when compared to row-wise, but at the cost of load times and system performance. If you look at the chart below, you’ll find the results of a benchmark test performed by Oracle guru Guy Harrison.
From the above chart, you can see that the data was compressed at three levels, using the command ‘COMPRESS FOR ARCHIVE LEVEL’. The highest compression level commanded the highest load time too whereas the lowest compression level did not experience any significant numbers in load time. Nonetheless, the compression ratios are still very impressive figures and can positively affect database performance. So, Exadata storage server will benefit from this feature exclusively.
Both compression algorithms are different but still offer a lot when it comes saving database space, enhancing performance and securing database information. So, Oracle 11g is a safe bet regardless of whether the database storage server is Exadata or not.
Case Study to investigate Oracle’s compression claims
Claiming to compress data up to 3 times its original size, Oracle 11g does promise a lot and fortunately, it delivers too. Following a new algorithm, Oracle 11g can provide gains in both storage and performance; because of the reduction in the number of the blocks that has to be read while performing I/O.
Here at Nous Infosystems, a case study was done to measure how effective Oracle 11g’s compression techniques were. Claiming to compress data up to three times its original size, the case study was conducted to see whether these Oracle claims were accurate or not.
For the case study, a dual core Intel processor CPU running on 2 GB of RAM was used. The operating system used for this test was Linux 2.6 and Oracle 11g (with the advanced compression option license) was installed. Under these testing conditions, the case study was performed as follows:
1. First, two tables were created. One with the compression option enabled was named MY_COMPRESSED_TABLE and the other one without compression was named TEST.
2. Once these tables were created, data was loaded onto both the tables. In both tables, the number of rows inserted was of the same size and same length. This way, the compression rate of the application can be accurately shown.
3. As expected, the table with the compression option enabled consumed less space than its counterpart; but by how much? Well, the results of the case study is as follows:
- MY_COMPRESSED table (compressed) = 9077 blocks.
- TEST (regular) = 18257 blocks.
The compressed table is almost half the size of the uncompressed one. Compression at this rate is definitely impressive.
4. Such high compression is explained as follows – Instead of compressing the rows of the table while being inserted to the table, a different algorithm is used where the uncompressed rows in the block are compressed. Also, since no compression happens during the data insert, the impact on the performance of the system is minimal. The case study also observes that the threshold is defined by the RDBMS engine.
The case study consisted of a simple test. Create two identical tables, compress one of them and see by how much 11g compressed the table. While the case study didn’t achieve the rate at which Oracle 11g claims to compress at, the results of this case study are still impressive. Plus, the case study observes that less consumption of database space reduces the I/O operations drastically, hence boosting the query result performance. Such compression rates will guarantee any organization not only savings in database space, but also an increase in productivity too. So, even if the ratio is 3:1 or 2:1, using Oracle 11g is going to give you impressive compression ratios.
Summing up Oracle 11g’s compression feature
Other than saving space, using Oracle 11g’s advanced inline compression system benefits you by reducing the time taken for data retrieval and creating less network traffic. In short, the case study shows that using Oracle 11g for compressing data not only saved hard disk space, but also reduced the time taken to go across the network, saved additional space while creating a backup and most importantly, maintaining production database copies for QA and testing.
Oracle 11g offers two different compression algorithm, depending on whether your database storage server is Exadata or not. Exadata servers have the additional option of enabling column wise compression, which offers better compression ratios than row level compression. But both these compression algorithms deliver,
The case study conducted on Oracle 11g’s efficiency presents an interesting view on data management and performance; particularly on the fact that performance won’t be compromised for the sake of compression anymore. Even though, Oracle claims their compression ratios are 3:1, this case study does show that it is capable of attaining a compression ratio of 2:1. Plus, you’ve got the added bonus of not experiencing real performance overheads when deleting or inserting into compressed data.
After analyzing Oracle 11g through the case study, it’s clear that the compression technique used is effective and it drives productivity upwards. Overhead costs issues aside, Oracle’s latest RDBMS release is a sure bet for increate database security and performance. Regardless of whether operations are OLTP or not, Oracle 11g has added support for OLTP operations, along with an additional feature of columnar compression for its own Exadata storage servers. In a nutshell, for better database information management, Oracle 11g is the RDBMS you can turn to.
Authored by Ponnusamy Rangasamy and Ashley John Sales
References:
http://www.articles.freemegazone.com/oracle-compression.php?ref=2
http://www.oracle-base.com/articles/11g/TableCompressionEnhancements_11gR1.php
http://www.oracle.com/technetwork/community/database-11g-product-family-technic-133664.pdf (White paper, only contains a paragraph on it)
http://gavinsoorma.com/2009/09/oracle-11g-advanced-compression/
http://www.rittmanmead.com/2008/09/testing-advanced-oltp-compression-in-oracle-11g/
http://www.dba-oracle.com/images/11g_block_compression_changes.jpg (Picture recreated from this link)
http://www.articles.freemegazone.com/oracle-compression-concerns.php (Compression algorithms for different versions)
http://guyharrison.squarespace.com/blog/2009/9/2/columnar-compression-in-11gr2.html (Oracle 11gr2 info)
http://www.morganslibrary.com/pres/oow09_hcc.pdf (Compression process explanation)















