New Automatic Storage Compression Capability with Oracle Database 23ai
Alireza Kamrani
02/07/2025
Organizations use Hybrid Columnar Compression (HCC) for space savings and fast analytics performance.
However, the compression and decompression overhead of Hybrid Columnar Compression can affect direct load performance.
To improve direct load performance, Automatic Storage Compression enables Oracle Database to direct load data into an uncompressed format initially, and then gradually move rows into Hybrid Columnar Compression format in the background.
When Automatic Storage Compression is enabled, direct loads into a Hybrid Columnar Compression object would use the uncompressed format to achieve faster loads. The database will then wait until there are no modifications, to the newly loaded data, for the duration of the user specified DML inactivity threshold.
At that point, the data from the uncompressed direct load will be gradually HCC compressed using a background Automatic Compression AutoTask.
At a high level, the process would appear as follows:
Uncompressed Data Direct Loaded into HCC Table
Database Waits Until User Specified “DML Inactivity Threshold” is Met
When Threshold is Met, Database Automatically Moves and Compresses Data
When Move Completed, the Table is Fully HCC Compressed
Compare this to the existing manual ILM process:
Uncompressed Data Direct Loaded into Table
Table Enabled for HCC Compression
User Manually Creates ILM Policy to Specify When Table can be HCC Compressed
Database Rebuilds Entire Segment, to Enable Compression, when Policy Condition Met
Space Freed by Compression is not Reclaimed Immediately (need new inserts to reuse the space)
Usage Prerequisites
The table must be in a tablespace with these properties:
In the PDB, set HEAT_MAP=ON
Table(s) need to be specified HCC and reside on a tablespace that uses SEGMENT SPACE MANAGEMENT AUTO and AUTOALLOCATE
Using Automatic Storage Compression:
Usage Example
Enable Automatic Storage Compression.
Set DBMS_ILM_ADMIN.ENABLE_AUTO_OPTIMIZE at the PDB level.
exec dbms_ilm_admin.enable_auto_optimize;
Create a table without HCC compression.
For this example, a table named “MYTAB” will be used as an example.
The table was created without any compression.
Check that the table is not compressed.
For this example, we want to demonstrate that the table is not yet compressed..
SQL> select unique dbms_compression.get_compression_type(' SCOTT', 'MYTAB', rowid) from scott.mytab;
DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','MYTAB',ROWID)
-----------------
1
Note: dbms_compression.get_compression_type uses constants that can be used to determine compression type.
“1” indicates that the table is currently not compressed.
ALTER table to add HCC compression and load data using direct path.
For this example, HCC Query LOW compression as added to the table and insert /*+ append */ used to perform the direct-path load.
For this example, we will determine the uncompressed size of the table before Automatic Storage Compression.
select bytes/1024/1024 MB from dba_segments where owner = 'SCOTT' and segment_name = 'MYTAB';
MB
--------
5.625
5.625 indicates the uncompressed table size (MB).
Monitor the incremental progress of the automatic compression by checking the “Auto compression data moved” system statistic, which increases over time as data is moved and compressed.
The one-hour default inactivity interval will allow the segment size to be observed before compression starts.
The value of “Auto compression data moved” indicates “0”.
This shows that automatic compression has not started.
select name, value from v$sysstat where name like 'Auto compression data%';
NAME VALUE
------------- ------------
Auto compression data movement success 0
Auto compression data movement failure 0
Auto compression data moved 0
Note that v$sysstat will show the sum of values, across all tables that are using automatic compression.
If you are compressing more than one table, then the value of “Auto compression data moved” would include the data moved for those tables as well. Also, due to rounding up, the value may not exactly match the actual size of uncompressed data over time.
As the data movement and compression begins, the value of "Auto compression data moved" increases.
select name, value from v$sysstat where name like 'Auto compression data%';
NAME VALUE
------------------------ --------
Auto compression data movement success 1
Auto compression data movement failure 0
Auto compression data moved 6
For this example, the value (MB) of “Auto compression data moved” indicates “6”, meaning that approximately 6MB of uncompressed data was moved to compression.
Note that when automatic compression started, the uncompressed size of the data was 5.625MB.
As this example demonstrates, the segment size before compression, and the amount of data moved during compression, may not be an exact match.
As mentioned earlier, v$sysstat shows the sum of values across all tables using automatic compression, if you are loading more than one table, then the value of “Auto compression data moved” would include the data for those tables as well.
Also, due to rounding up, the value of “Auto compression data moved” may not exactly match the actual size of uncompressed data over time.
Although the value is expected to be similar to the uncompressed size of the data.
Check the compression level of the table and the compressed size.
SQL> select unique
dbms_compression.get_compression_type('SCOTT', 'MYTAB', rowid) from scott.mytab;
DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','MYTAB',ROWID)
------------------
8
Note: dbms_compression.get_compression_type uses constants that can be used to determine compression type.
“8” indicates that the table is currently compressed using HCC Query Low compression.
SQL> select bytes/1024/1024 MB from dba_segments where owner = 'SCOTT' and segment_name = 'MYTAB';
MB
--------
.3125
.3135 indicates the size of the table after automatic compression to HCC Query Low.
Note: “Auto compression data movement failure” is the number of unsuccessful data movement (compression) attempts.
This value incremented if
1) the database ran into any errors during compression, or
2) the database ran out of time in the current background task to process further – in which case compression will resume in the next background task.
Note: “Auto compression data movement success” is the number of successful data movement attempts.
Auto Compression may break up the data movement work for a single direct load into multiple batches, so this may not exactly match the number of segments or direct loads.
Summary
Automatic Storage Compression improves direct load performance, while keeping the advantages of Hybrid Columnar Compression, including space savings and fast analytics performance.
Alireza Kamrani
02/07/2025
No comments:
Post a Comment