Row Level Locking with Hybrid Columnar Compression (HCC)

Database Storage Optimization

Row Level Locking with Hybrid Columnar Compression (HCC)

Hybrid 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 POCs.

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.

Source: https://blogs.oracle.com/DBStorage/entry/row_level_locking_with_hybrid