Advanced Compression and Physical I/O
sunirNov 23 2009 — edited Dec 1 2010
Excerpt from AC twp.
"As stated above, the Table Compression feature has no adverse impact on read operations. However, compression requires additional work to be performed while writing the data making it unavoidable to eliminate performance overhead for write operations. Oracle has put in a significant amount of work to minimize such overhead for OLTP Table Compression. Oracle compresses a block in batch mode rather than compressing data every single time a write operation takes place. A newly initialized block remains uncompressed until data in the block reaches an internally controlled threshold. When a transaction causes the data in the block to reach this threshold, all contents of the block are compressed. Subsequently, as more data is added to the block and the threshold is again reached, the entire block
is recompressed to achieve the highest level of compression. This process repeats until Oracle determines that the block can no longer benefit from further compression. Only transactions that trigger the compression of the block will experience the minimal compression overhead. Therefore, a majority of OLTP transactions on compressed blocks will have the exact same performance as they would with uncompressed blocks.
How much physical disk i/o is this going to create?
Does it increase physical i/o because OLTP transactions are written uncompressed then re-written compressed when block thresholds are reached?
Background: Loading (using conventional path) hundreds of millions of records per day (200GB a day). Data is for OLTP.