Orcale Compression in 26ai:
Are you tired of choosing between fast direct loads and the massive space savings of Hybrid Columnar Compression (HCC)?
Oracle Database 26ai introduces a game-changer: Automatic Storage Compression (ASC).
https://www.linkedin.com/feed/update/urn:li:groupPost:8151826-7429626918938791936
ASC solves the classic dilemma:
HCC’s compression overhead can slow down high-volume direct loads.
With ASC, you get the speed of uncompressed direct loads first, and the space efficiency of HCC later, all automatically in the background.
ASC allows you to benefit from two traditionally opposing advantages simultaneously:
- The World of Speed (Fast Direct Loads): When you load a large amount of data into a database, you want the process to be as fast as possible. Compression, especially a high-ratio compression like Hybrid Columnar Compression (HCC), adds overhead that can slow down the initial data loading process. ASC initially loads the data uncompressed to achieve the fastest possible direct load performance.
- The World of Efficiency (Massive Space Savings): Once the data is loaded, you want it to take up the least amount of disk space and be optimized for analytical queries. HCC provides superior space savings and fast analytics. ASC automatically and gradually compresses the data into the HCC format in the background after the initial load is complete and the data is no longer being actively modified.
How it Works: The Intelligent Background Process
Instead of forcing compression during the load, ASC uses a smart, two-phase approach:
Phase 1: Speed: Data is direct-loaded into an uncompressed format, ensuring maximum load performance.
Phase 2: Space: After a user-specified DML inactivity threshold is met, a background Automatic Compression AutoTask gradually moves and HCC-compresses the data.
This is a huge improvement over the manual ILM process, which often required full segment rebuilds and complex policy management.
Prerequisites for Activation
To enable this feature, ensure your environment meets these simple requirements:
- Set `HEAT_MAP=ON` in your Pluggable Database (PDB).
- The table must be specified for HCC and reside on a tablespace using `SEGMENT SPACE MANAGEMENT AUTO` and `AUTOALLOCATE`.
Step-by-Step Usage Example
Here’s how easy it is to enable and monitor this feature:
1. Enable Automatic Storage Compression at the PDB level:
SQL> exec dbms_ilm_admin.enable_auto_optimize
2. Check the initial compression state (Uncompressed = 1):
SELECT UNIQUE dbms_compression.get_compression_type( 'SCOTT', 'MYTAB', rowid)
FROM scott.mytab;
-- Result: 1 (Uncompressed)
3. Monitor the uncompressed size before compression starts:
SELECT bytes/1024/1024 MB
FROM dba_segments
WHERE owner = 'SCOTT' AND segment_name = 'MYTAB';
-- Result: 5.625 (MB)
4. Monitor the background compression progress:
The `v$sysstat` view tracks the data moved and compressed by the background task.
SELECT name, value
FROM v$sysstat
WHERE name LIKE 'Auto compression data%';
Name | Value(initial) | Value after Compression |
Auto compression data movement success | 0 | 0 |
Auto compression data movement failure | 0 | 0 |
Auto compression data moved | 0 | 6 (MB) |
5. Check the final compression state (HCC Query Low = 8) and size:
SELECT UNIQUE dbms_compression.get_compression_type('SCOTT', 'MYTAB', rowid)
FROM scott.mytab;
-- Result: 8 (HCC Query Low)
SELECT bytes/1024/1024 MB
FROM dba_segments
WHERE owner = 'SCOTT' AND segment_name = 'MYTAB';
-- Result: .3125 (MB)
From 5.625MB uncompressed to 0.3125MB compressed—that’s a massive space saving, achieved without sacrificing load performance!
Using this feature in OLTP vs. OLAP env
The Automatic Storage Compression (ASC) feature in Oracle 23ai is primarily designed to be useful for OLAP (Online Analytical Processing) environments, or more specifically, Data Warehousing environments, which are characterized by:
- High-volume, bulk data loading (Direct Loads): This is where the initial speed benefit of ASC comes into play.
- Large, mostly static data sets: This is where the massive space savings and analytical performance benefits of Hybrid Columnar Compression (HCC) are most valuable.
Why it’s best for OLAP/Data Warehousing
ASC is a direct solution to a problem common in data warehouses: the trade-off between fast data loading and HCC’s compression overhead.
- Fast Loading: Data is loaded quickly in an uncompressed state.
- Space/Query Efficiency: The data is then automatically converted to HCC, which is optimized for read-heavy analytical queries and provides superior compression ratios.
- DML Inactivity Threshold: The compression only happens after a period of DML (Data Manipulation Language) inactivity, which is typical for historical or read-only partitions in a data warehouse.
Why it’s less suited for OLTP
OLTP (Online Transaction Processing) environments are characterized by frequent, small, and concurrent transactions (inserts, updates, deletes).
- Compression Type: OLTP environments typically use basic compression (like `COMPRESS FOR ALL OPERATIONS`), which is optimized for transactional workloads and allows for DML on compressed blocks. HCC, which ASC uses, is generally not recommended for tables with high DML activity because it can lead to block splits and reduced performance.
- Inactivity Requirement: ASC’s core mechanism relies on a “DML inactivity threshold” before compression begins. This threshold is rarely met in a high-activity OLTP system, meaning the data would likely remain uncompressed, defeating the purpose of the feature.
Summary
Automatic Storage Compression in Oracle 26ai is a significant step forward for data warehousing and large-scale data ingestion.
It ensures you get the best of both worlds: blazing-fast direct loads and the superior space efficiency of Hybrid Columnar Compression.
ASC is a powerful automation feature that maximizes the benefits of Hybrid Columnar Compression, making it a highly useful and relevant feature for OLAP and Data Warehousing environments.
*********************************************
