Conventional INSERTs into HCC tables may be slower since Oracle 12.2

I work with several customers at the moment on their Oracle 12.2/18c “go live” projects. And one of these customers encountered a strange issue when comparing Oracle 11.2.0.4 to Oracle 12.2.0.1/18.3.0: Conventional INSERTs into HCC tables may be slower since Oracle 12.2. Actually in this particular case, significantly slower. Read below why this can happen and how the workarounds look like.

Hybrid Columnar Compression

There’s a lot of material to read and study about Hybrid Columnar Compression (HCC). With HCC we store the same column for a group of rows together. The data block does not store data in row-major format, but uses a combination of both row and columnar methods.

Storing column data together, with the same data type and similar characteristics, dramatically increases the storage savings achieved from compression. This is an Oracle Exadata feature which is available on some Oracle storage systems as well, and of course in the Exadata cloud services as well.

New Feature: HCC Compression for Array Inserts

Since Oracle Database 12.2.0.1 we offer a new feature called HCC Compression for Array Inserts. This feature allows SQL INSERT SELECT statements without APPEND hint can to use HCC, and array inserts from programmatic interfaces such as PL/SQL and the Oracle Call Interface (OCI) can do the same.

Unfortunately you can first of all easily skip this part when reading the documentation. Or, as it has happened to my customer, you go directly from Oracle 11.2.0.4 to Oracle 18.3.0 and don’t study the Oracle 12.2.0.1 New Features Guide before (I addressed this internally) but only the Oracle 18c New Features Guide.

The Problem

This nice new feature is on by default. And it may have a bad impact on conventional path INSERTs into HCC tables.

Here’s an example which was executed on the same Exadata:

So you see the difference. With the new feature, the compression could be leveraged even with a conventional INSERT. But it took much longer for the bulk operation than before.

In order to benefit from the HCC compression in Oracle 11.2.0.4 during a bulk INSERT, you could use the APPEND hint:

Workaround and Solution

There are actually two potential workarounds available.

Workaround 1:

Set the compression type to NOCOMPRESS before INSERT. It will disable HCC array insert as well.

ALTER TABLE test_tab_1 NOCOMPRESS;
ALTER TABLE test_tab_2 MODIFY PARTITION test_tab_q4 NOCOMPRESS;

Then return the table/partition to the desired HCC compression level after insert is completed.

Workaround 2:

Set the underscore parameter to the below value to disable the immediate HCC compression during conventional path inserts on HCC compressed tables/partitions.

ALTER SYSTEM SET "_disable_hcc_array_insert"=TRUE;

Summary and Links

First of all, this may be an extreme example and not represent the usual usage of HCC tables. I did discuss this with several other Exadata customers and they all told me that they are not doing such operations with HCC tables. Nevertheless, the impact may be visible. Hence you may check such cases when you test your go-live on Oracle 12.2, 18c and 19c.

And just for the records as I received this question on Twitter:
This of course applies as well for upgrades from 12.1 to 12.2 or higher as this is an 12.2 feature. The above example relates to a specific customer case where we learned about it. But it is not limited to Oracle 11.2.0.4 as source.

The following links will give you more guidance:

• Oracle 18c Concepts Guide: Hybrid Columnar Compression
• Oracle 12.2 New Features Guide: HCC Array Compression for Bulk Inserts
• MOS Note: 2469912.1 – Inserts on HCC Compressed Tables Can Be Significantly Slower in 12.2 Compared to 11.2

–Mike