Monday, July 16, 2012

HCC and CPU consumption



We tested the CPU consumption for HCC and traditional segments.
We run select sum(Col1) from table  under the 10046 trace for traditional table and its compresses copies. 10046 trace shows the time in microseconds. All segments were in the SGA in order to exclude physical IO. Original table size was 16g, the compressed size is

------------------- ------------------ -------
Table type           Compression ratio   CPU
------------------- ------------------ -------
Original table            1               1
Compress for OLTP         1.8             0.8
QL                        4.9             3.3
QH                       20               3.5
AL                       20               3.5
AH                       59              11  
----------------------------------------------
            
As you can see QL, QH, AL takes 3.3-3.5 more CPU cycles (CPU time).
But we see the significant compression ratio for HCC segments.
Therefore, if any select in traditional DB spend 90% time in IO wait and 10% time in CPU
then with 10-times compression it will spend 30% on the CPU and 90/10=9% in IO = 39% of original time.

The packing time depends on the compression ratio (the more compression ratio the more time to create HCC segment).

The unpacking time not depends on the compression ratio.

HCC algorithms are:

Query Low  - LZO
Query High - ZLIB
Archive Low - ZLIB
Archive High - bzip2

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Does DEALLOCATE UNUSED or SHRINK SPACE will free space occupied by LOB segment?

Lets check how it works. My env is DB 19.20@Linux-x64 1) I created the table with 4 LOB columns of 4 different LOB types: BASICFILE BLOB, BA...