Available as a separately licensed option for Oracle Database 11g Enterprise Edition, Advanced Compression is a way of writing data to disk so that it takes up less space. It can reduce storage costs, reduce memory and bandwidth requirements, and even improve query performance. (Read my full review, "Oracle Database 11g shoots the moon.")
I tested Advanced Compression in two ways, against two separate databases. In the first case, the database I used for testing was generated from the TPC-C order entry benchmark, which simulates a complete environment for online transaction processing. Transactions include entering and delivering orders, recording payments, checking order status, and monitoring stock levels. See TCP.org for details.
I started by testing bulk loads to get some initial compression numbers on five tables (ranging in size from 5MB to 104GB). I then tested read, write, and mixed read-write performance by using the Quest Benchmark Factory 5.5 load generator to simulate activity of 50 users against the database, running Benchmark Factory on the same server as Oracle Database. The data block size for these tests was the default 8K.
In the second case, I tested Advanced Compression by installing and running the OLTP Table Compression Test Kit provided by Oracle. This involved importing two uncompressed tables (532MB and 1.357GB), creating compressed copies of these tables in 8K and 16K block sizes, and then executing a set of scripts to test the performance of table scans and writes against each of those tables.
For both tests, the database was stored on a RAID-5 array with 14 SATA spindles, directly attached to an Intel-based Windows Server 2003 Enterprise server (32-bit with Service Pack 2 installed) running Oracle Database 11g. The server was configured with four 3GHz Intel Xeon CPUs and 4GB of RAM.
TPC-C compression results. I tested several tables in the TPC-C database with randomly generated data. Although the data was generated, the generated names are real names and addresses and real-world order data. The product description fields in order lines do include random characters. Considering the ordered nature of Oracle compressed columns, and the fact that these descriptions wouldn't typically be ordered together, these random characters shouldn't have a significant impact on overall compression results. However, it will reduce the compression ratio, simply by remaining uncompressed. Again, this is expected, and in fact simulates real-world production environments pretty well. Below are the compression results. Note that the two tables with 0% compressed (C_District and C_Item) are lookup tables; they have no duplicate data, so wouldn't be expected to benefit from Advanced Compression.