Database Storage Optimization best practices, tips and tricks and guidance from Database Compression Product Management

Row Level Locking with Hybrid Columnar Compression (HCC)

Guest Author

Columnar Compression (HCC)
is a very unique feature in that it gives a very
big gain in compression ratios (10x on average), makes table scans run a lot
faster (up to 10x), and all this not for a subset of DW scenarios, but for
every DW use case. HCC is used by most if not all Exadata DW customers and

The only real limitation of HCC in
first release was that it was not suited for data that is actively being
modified (thought it can be still used in such environments as part of an ILM
strategy). As of Oracle Database 11g Release 2, Hybrid Columnar Compression didn’t
support row level locking like with other table formats in Oracle, such as non-compressed
or Advanced Row compressed tables.

Oracle’s Hybrid Columnar Compression technology is a
different and new method for organizing data within a database block. As the
name implies, this technology utilizes a combination of both row and columnar
methods for storing data. This hybrid approach achieves the compression
benefits of columnar storage, while avoiding the performance shortfalls of a
pure columnar format. A logical construct called the compression unit (CU) is
used to store a set of hybrid columnar compressed rows. When data is loaded,
column values for a set of rows are grouped together, compressed and stored in
a compression unit. With Oracle Database 11g Release 2,
HCC supported compression unit level locking; that is, locking the entire
compression unit on an active transaction modifying a single row in the unit.

Row level locking is a requirement for
mixed OLTP and DW environments and is a definite requirement for OLTP
applications. For mixed workloads, ILM approach likely works fine, but needs to
be used much more cautiously. Lack of row level locking was a major restriction
for HCC, and as a result HCC was mainly targeted for Data Warehouse
environments. With Oracle Database 12c, HCC tables now support row level locking.
In order to do so, it needs to be able to store the locking information on per
row basis (including whether or not a row is locked, and if locked the locking
transaction information). The approach is similar to locking in non-compressed
or Advanced Row compressed tables as described next.

For non-compressed or Advanced Row compressed
tables Oracle stores a lock byte (index into the list of active transactions in
the data block) in the row header to indicate whether or not the row is
locked, and if locked then information about the locking transaction. This
approach works well when there can be high concurrency and many active
transactions in a block at a given time. But in the most typical cases
allocating a byte per row is an over kill.

For HCC tables, the approach is similar
but a more efficient. Rather than allocating the lock byte upfront, Oracle
allocates lock bit(s) on demand and based on number of active and anticipated
transactions on a compression unit. It could pre-allocate enough bits to support anticipated concurrency on the block (dependent on INITRANS setting for the table), OR with a CU level lock for the entire compression unit.  Since most of the times there won’t
be any transactions modifying data in the CUs, there is no need to allocate any
bits per row for locking. If there are active transactions and a need for
row level locking, Oracle allocates more bit(s) per row to be able to support
row level locking. 1 bit per row will be able to support 1 active transaction,
2 bits supports up to 3 concurrent transactions, 3 bits supports up to 7
concurrent transactions and so on... The maximum need is to support 255 active
transactions per compression unit, as the number of ITLs (interested
transaction list) in the data blocks is limited to 255. That is, we will need
maximum of 1 byte per row.

As you see, this approach doesn’t
pre-allocate 1 byte per row; but allocates it on demand. It is possible to support
255 concurrent transactions (like uncompressed or Advanced Row compressed
blocks); have CU level locking; or anywhere in between. In the worst case,
Oracle needs 1 byte per row to support this; but in most practical cases, we
will be much better than that.

The next question that comes to one’s
mind is where does the space for allocating locking bit(s) come from? Just like
non-compressed tables use PCTFREE to extend the ITLs as needed, Oracle uses
PCTFREE in the blocks to extend ITLs and allocate any additional lock bits for
HCC tables. This is the most natural place to allocate the space from.

And where are lock bits stored? The
lock information is stored uncompressed in the CU header. Today Oracle already
stores other information for rows in the CU header. Lock bit(s) are stored in
the CU header along with other per row information.

The ability to have row level locking
has further widened the applicability of HCC’s columnar and compression
technology to OLTP or mixed workload environments. It has allowed for
making the use of HCC wide spread and operationally complete. Row Level Locking
for Hybrid Columnar Compression is part of Advanced
Compression Option
which enables the highest levels of data compression and
provides enterprises with tremendous cost-savings and performance improvements
due to reduced I/O.

Join the discussion

Comments ( 2 )
  • David Wang Tuesday, March 6, 2018
    Very good explanation for how the row locking works in HCC. The row locking is needed when the row is modified. When the row is updated, it will remain uncompressed until the CU is recreated. Will the new row locking in HCC will change the behavior for update rows within CU?
  • Gregg Christman Monday, April 23, 2018
    An updated row gets moved out of its CU to a new CU and this row will be compressed to a lesser level. Automatic Data Optimization HCC policies can be used to automatically compress the updated row back to HCC compression levels. HCC RLL doesn't change any of this behavior.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.