Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 
 

Updating tables with Oracle 11g compression

Oracle Database Tips by Donald BurlesonJuly 31, 2015


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 2015) 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."


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.