Updating tables with Oracle 11g compression

Updating tables with Oracle 11g compression

Oracle 11g compression has been heralded as a great Oracle tuning tool that does far more than reduce disk usage.  It also packs the rows more tightly on the data blocks, making it faster for SQL. 

See my important notes here on 11g table compression.

Some unknown issues (as of September 2007) with implementing Oracle11g data compression include the amount of overhead.  The compress/decompress operations are computationally intensive but super small (probably measured in microseconds).

 

This CPU overhead might be significantly measurable, but we can assume that the overhead will be the same (or smaller) than data compression in legacy databases  (with the possible exception of PC-based Oracle databases). 

 

In a perfect implementation, incoming data would only be decompressed once (at read time) and the uncompressed copy of the disk block would reside in RAM, thereby minimizing changes to the Oracle kernel code. The overhead on DML must involve these operations:

• Overhead at DML time - Whenever a SQL update, insert of delete changes a data block in RAM, Oracle must determine if the data block should be unlinked from the freelist (this threshold is defined by the PCTFREE parameter).
  
• Compression on write - An outbound data block must be compressed to fit into it's tertiary block size (as defined by db_block_size and the tablespace blocksize keyword).  For example, an uncompressed block in RAM might occupy up to 96k in RAM and be compressed into it's tertiary disk blocksize of 32k upon a physical disk write.
  
• Decompress on read - At physical read time, incoming disk blocks must be expanded once and stored in the RAM data buffer.  The exact mechanism for this expansion is not published in the Oracle11g documentation, but it's most likely a block versioning scheme similar to the one used for maintaining read consistency.
  
• Increased likelihood of disk contention - Because the data is tightly compressed on the data blocks, more rows can be stored, thus increasing the possibility of "hot" blocks on disk.  Of course, using large data buffers and/or solid-state disk (RAM-SAN) will alleviate this issue. 

In this discussion we see a case where the Oracle 11g compression is allegedly  “undone” by a update to the table.  The author cites that after updating all of the rows in table, his compress ration is reduced dramatically. 

The discussion cites MOSC Note: 466362.1 which shows that subsequent inserted block in 11g compression are not compressed immediately.  Rather, the block compression is triggered when the data block is unlinked from the freelist ( as set by PCTFREE freelist threshold) :

"As an example on what would happen when inserting into block that is part of a compressed table,  the cycle would be like :

- The block is empty and available for inserts.

- When you start inserting into this block, data is stored in an uncompressed format (like for uncompressed tables).

- However, as soon as you reach the PCTFREE of that block, the data is
automatically compressed, potentially reducing the space it originally occupied.

- This allows for new uncompressed inserts to take place in the same
block, until PCTFREE is reached again. At that point compression is triggered again to reduce space occupation in the block."