Exadata Tips: Reduce Storage, Improve Performance with Compression

We live in an era where businesses are powered by data, running their most critical systems on robust platforms such as Oracle Exadata Cloud@Customer and Oracle Exadata Cloud Service. Yet, after migration, many organizations tend to overlook the rich set of features these platforms provide.

Think of it this way: if you buy a Ferrari, you don’t drive it like a regular car. You learn its features, understand its capabilities, and drive it like a Ferrari. The same mindset applies to Exadata – to get the most out of it, you need to leverage its features fully.

One key feature that is often underutilized is Exadata compression. Exadata offers multiple compression options, including Hybrid Columnar Compression (HCC), OLTP table compression, and basic table compression, designed to reduce storage costs, improve I/O performance, and accelerate analytic workloads. By implementing the right compression strategies, organizations can unlock significant efficiency gains without compromising performance, making Exadata not just a platform for running databases but a powerhouse for high-performance, cost-effective data management.

Understanding Compression Options Available in Exadata

Exadata supports a variety of compression technologies, each optimized for different workload types. Below are the primary options:

1. Basic Compression (Included in Enterprise Edition)

• Applied during direct-path operations (CTAS, bulk loads).
• Good for data warehousing and batch loads.

2. OLTP Compression (Advanced Compression License)

• Designed for OLTP systems with frequent DML.
• Compresses data at the block level without major performance overhead.

3. Hybrid Columnar Compression (HCC) — Exadata Only

• Offers the highest compression ratios.
• Two main modes:
  • Query High / Query Low → optimized for analytics
  • Archive High / Archive Low → optimized for cold/archival data
• Can achieve 10x–50x compression depending on data type.

4. Flash Cache Compression (Exadata)

• Compresses blocks stored in Smart Flash Cache.
• Enables more data to fit in flash, improving cache hit ratio.

5. RMAN Backup Compression

• Backup sets are compressed for faster backup/restore.
• Reduces SBT/tape or object storage usage.

For this article, I will specifically highlight the benefits of Oracle Advanced Compression.

Advantages of Oracle Advanced Compression

Oracle Advanced Compression delivers a wide range of benefits that help reduce storage, improve performance, and lower operational costs. Below are the key advantages:

(For more details, you can refer to Oracle’s official documentation: https://www.oracle.com/ca-en/database/advanced-compression/#rc30p2)

1. Significant Storage Savings

• Compresses tables, indexes, and LOB data.
• Reduces overall database storage footprint by 2x–4x, depending on data characteristics.
• Helps minimize storage costs – especially important in cloud environments.

2. Improved I/O Performance

• Smaller compressed blocks mean less data needs to be read from disk.
• Results in faster full table scans and index range scans.
• Works extremely well on Exadata, where Smart Scan accelerates operations by processing less data.

3. Faster Backups and RMAN Operations

• Compressed data produces smaller backup sets.
• Reduces both the backup window and network bandwidth usage.
• Improves Data Guard transport performance by reducing redo volume.

4. Better Memory and Cache Utilization

• More rows fit into the buffer cache due to compression.
• Reduces logical I/O, increasing overall query efficiency.

5. Lower Total Operational Costs

• Storage, I/O, backup, and Data Guard overheads are all reduced.
• Provides significant cost savings, particularly for Exadata Cloud Service, where storage efficiency directly impacts billing.

In this article, I will demonstrate the true power of Oracle Advanced Compression and show how it can significantly reduce storage requirements while maintaining optimal performance. For this example, I am using an Exadata Cloud Service (ExaCS) environment with Advanced OLTP Compression enabled.

To evaluate the impact, I selected a table linked to a query with a very high execution count – making it an ideal candidate for compression testing and performance analysis.

Step 1: Check the Current Table Size

Before applying compression, let’s capture the baseline size of the table.

col segment_name for a30
select segment_name,sum(bytes)/1024/1024/1024 GB from dba_segments where segment_name in ('&table_name') group by segment_name;

Sample Output : 

SEGMENT_NAME                           GB
------------------------------ ----------
TEST_ASSET_MAPPING                    176

The TEST_ASSET_MAPPING table is extremely large, occupying 176 GB, making it a strong candidate for compression.

Step 2: Analyze Table Activity (Inserts & Deletes)

This table also contains an index that is primarily used to retrieve primary key values. Because the table experiences a high volume of insert and delete operations, it is an excellent candidate for Advanced OLTP Compression, which is specifically optimized for environments with heavy DML.

In addition to the frequent DML activity, the large number of deletions has resulted in significant table fragmentation, further reinforcing the need for compression and a segment reorganization to reclaim space and improve efficiency.

Use the following query to examine modification activity:

select table_name,inserts,deletes from dba_tab_modifications where table_name = '&table_name';

Sample output : 

TABLE_NAME                        INSERTS    DELETES
------------------------------ ---------- ----------
TEST_ASSET_MAPPING                 253082    1598890

The heavy DML workload confirms that OLTP Compression is a good fit for this table.

Step 3: Apply Advanced Compression

Compress the Table

ALTER TABLE SCOTT.TEST_ASSET_MAPPING MOVE ROW STORE COMPRESS ADVANCED ONLINE parallel 4; 

Rebuild the Index with Compression

ALTER INDEX SCOTT.IDX_UAM_1 rebuild COMPRESS ADVANCED HIGH ONLINE PARALLEL 4;

These commands compress both the table and its associated index while keeping the objects online, ensuring minimal impact on application availability.

Step 3: Validate Table and Index Compression

After applying compression, the next step is to verify that both the table and its index are correctly compressed.

Validate Table Compression

Use the following SQL query to check the compression status of the table:

set lines 600
col OWNER for a20
col TABLE_NAME for a30
col COMPRESSION for a30
select OWNER,TABLE_NAME,COMPRESSION,COMPRESS_FOR from dba_tables where table_name '&table_name';

-- Sample output

OWNER                TABLE_NAME                     COMPRESS COMPRESS_FOR
-------------------- ------------------------------ -------- ------------------------------
USER_ASSET_SVC       USER_ASSET_MAPPING             ENABLED  ADVANCED

This confirms that Advanced Compression is enabled on the table.

Validate Index Compression

Next, verify the compression settings for the associated index:

Sample Output:

select OWNER,INDEX_NAME,INDEX_TYPE,SAMPLE_SIZE,LAST_ANALYZED,COMPRESSION from dba_indexes where index_name='&index_name';

OWNER                INDEX_NAME      INDEX_TYPE     SAMPLE_SIZE LAST_ANAL COMPRESSION
------------------- ---------------- -------------- ----------- --------- -------------
USER_ASSET_SVC       IDX_UAM_1       NORMAL         43885629    23-JUN-25 ADVANCED HIGH

This confirms that the index has been successfully rebuilt using Advanced High Compression.

Step 4: Compare Table Size Before and After Compression

Once compression is applied, it’s important to measure the impact on storage. Use the same query as before to check the current table size:

Step 5: Summary of Compression Benefits

Real-World Compression Results on a 30TB Database

While the earlier example focused on a single sample table, we also evaluated Advanced Compression across several large tables in a production-sized 30TB database. The results clearly demonstrate the substantial storage savings that can be achieved.

It’s worth noting that if you choose to use Hybrid Columnar Compression (HCC) -available only on Exadata – you can often achieve even higher compression ratios. Ultimately, the level of savings depends on selecting the right compression method for your data and workload patterns.

Conclusion

Oracle’s Advanced Compression is a powerful feature that delivers substantial storage savings, improves I/O performance, and optimizes operational efficiency – especially in Exadata and large-scale database environments.

Through both sample tables and real-world examples from a 30TB database, we observed compression ratios ranging from 2.3x to 5x, demonstrating that even high-DML transactional tables benefit significantly without any performance degradation.

This becomes even more important when considering the cost of ExaCS storage. A single X11M storage server costs approximately $2,830.90 per month, while an X10M storage server costs around $2,160.00 per month. ExaCS makes it easy to add storage with just a few clicks, but that convenience comes at a premium. Using storage wisely – and leveraging features like Advanced Compression – can dramatically reduce consumption and help organizations avoid unnecessary costs.

In short, compression transforms Oracle databases into a high-performance, cost-efficient platform, ensuring that you maximize the value of your Exadata investment while keeping operational expenses under control.