When working with Oracle Exadata, terms such as Hybrid Columnar Compression (HCC), Smart Scan, and Storage Index are frequently discussed together.

But knowing the terminology is different from understanding what actually happens during execution.

In this hands-on lab, I wanted to answer three practical questions:

How much storage can HCC actually save?
Can we see predicate processing happening at the Exadata storage layer?
Can we obtain actual runtime evidence that a Storage Index saved physical I/O?

Rather than relying only on execution plans, I built controlled test tables and measured the results using Oracle statistics.

1. Lab Objective

The objective was to build a practical Exadata performance test covering:

HCC
 │
 ├── Storage reduction
 │
 └── Query processing
        │
        ▼
   Smart Scan
        │
        ├── Storage-level predicate processing
        │
        └── Storage Index
                │
                ▼
        Physical I/O savings

The important part was to measure each layer independently.

A common mistake in Exadata troubleshooting is to see:

TABLE ACCESS STORAGE FULL

and immediately conclude that Storage Index was used.

That conclusion is not justified by the execution plan alone.

This lab helped demonstrate why.

2. Building the Production-Style Test Table

I created a dedicated tablespace for the performance lab:

CREATE TABLESPACE perf_ts
DATAFILE SIZE 2G
AUTOEXTEND ON
NEXT 512M
MAXSIZE 20G;

The main test table was:

CREATE TABLE sales_fact_baseline
(
    sale_id       NUMBER,
    customer_id   NUMBER,
    product_id    NUMBER,
    region        VARCHAR2(20),
    sale_amount   NUMBER(12,2),
    sale_date     DATE
)
TABLESPACE perf_ts;

The intention was to simulate a simple analytical sales fact table containing:

10 million rows
Customers
Products
Regions
Sale amounts
Dates

Because a single 10-million-row CONNECT BY operation caused:

ORA-30009: Not enough memory for CONNECT BY operation

I changed the loading strategy to 1-million-row batches.

This itself was a useful DBA lesson:

Large data generation operations should consider the memory requirements of the row-generation method, not just the final table size.

After completing the batches:

SELECT COUNT(*)
FROM sales_fact_baseline;

returned:

10,000,000

3. Establishing the Uncompressed Baseline

Before doing any compression experiment, I gathered statistics:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    'PERF_LAB',
    'SALES_FACT_BASELINE'
);

Then I checked the segment size:

SELECT
    segment_name,
    ROUND(bytes/1024/1024,2) AS size_mb
FROM dba_segments
WHERE owner = 'PERF_LAB'
AND segment_name = 'SALES_FACT_BASELINE';

The result was:

SALES_FACT_BASELINE
680.56 MB

This became our baseline.

4. Creating the HCC Query High Table

Next, I created an HCC Query High copy:

CREATE TABLE sales_fact_hcc_qh
COMPRESS FOR QUERY HIGH
TABLESPACE perf_ts
AS
SELECT *
FROM sales_fact_baseline;

Statistics were gathered:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    'PERF_LAB',
    'SALES_FACT_HCC_QH'
);

Then I compared the segment sizes:

SALES_FACT_BASELINE    680.56 MB
SALES_FACT_HCC_QH        3 MB

That means the test data achieved approximately:

680.56 / 3 ≈ 227×

or approximately:

99.56% space reduction

Important production caveat

I would not present 227× as a normal production HCC compression ratio.

The test data was intentionally repetitive and therefore highly compressible.

Actual HCC compression depends on factors such as:

Data characteristics
Repetition
Column cardinality
Compression level
Load method
Workload
Table structure

So the correct lesson is:

HCC can provide substantial storage reduction, but the actual compression ratio must be measured against representative production data.

5. Testing Smart Scan / Storage-Level Predicate Processing

Next, I ran an analytical query against the baseline table.

SELECT /*+ GATHER_PLAN_STATISTICS */
       region,
       COUNT(*) AS sale_count,
       SUM(sale_amount) AS total_sales,
       AVG(sale_amount) AS avg_sale
FROM sales_fact_baseline
WHERE region = 'NORTH'
  AND sale_date >= DATE '2025-10-01'
GROUP BY region
ORDER BY region;

The result was:

REGION  SALE_COUNT  TOTAL_SALES    AVG_SALE
NORTH      164395   4.1117E+10     250111.205

I then inspected the actual execution statistics:

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        NULL,
        NULL,
        'ALLSTATS LAST +IOSTATS'
    )
);

The important part of the plan was:

TABLE ACCESS STORAGE FULL

and:

storage(
    "SALE_DATE">=...
    AND "REGION"='NORTH'
)

The baseline table reported:

Buffers: 86,017
Reads:   86,012

6. Running the Same Query Against HCC

I then ran exactly the same business query against:

SALES_FACT_HCC_QH

The result was identical:

REGION  SALE_COUNT  TOTAL_SALES    AVG_SALE
NORTH      164395   4.1117E+10     250111.205

But the execution statistics changed dramatically.

The HCC table showed:

Buffers: 309

compared with:

Baseline: 86,017
HCC:          309

That's approximately:

99.64% fewer buffers

The overall elapsed time in this particular test was approximately:

Baseline: ~0.68 sec
HCC:      ~0.29 sec

The plan continued to show:

TABLE ACCESS STORAGE FULL

and:

storage(...)

7. What Does `storage()` Actually Tell Us?

This was one of the most important lessons from the lab.

Seeing:

storage(...)

does not automatically mean:

Storage Index was used.

Instead, it indicates that the predicate is being processed at the storage layer.

Conceptually:

Database SQL
     │
     ▼
Storage-level processing
     │
     ├── Predicate evaluation
     ├── Smart Scan processing
     └── Potential Storage Index optimization

Therefore:

TABLE ACCESS STORAGE FULL

plus:

storage(...)

is evidence of storage-level processing.

It is not sufficient evidence by itself to claim Storage Index activity.

8. Designing a Dedicated Storage Index Experiment

To investigate Storage Index separately, I created:

CREATE TABLE storage_index_lab
(
    id          NUMBER,
    sale_amount NUMBER,
    sale_date   DATE,
    region      VARCHAR2(10)
)
TABLESPACE perf_ts;

The intention was to create a physically ordered SALE_AMOUNT range.

The final lab table contained:

Total rows:        10,000,000
Distinct amounts:   9,000,000
Minimum amount:             1
Maximum amount:     9,000,000

There were also:

1,000,000

values with duplicates due to the way the lab batches had been loaded.

The segment size was:

392.56 MB

The important thing here was that we verified the actual state of the table instead of assuming that the loading process had produced exactly what we intended.

That's another practical DBA lesson:

Always validate the physical and logical state of a test dataset before drawing performance conclusions.

9. First Storage Index Query

I used a highly selective predicate:

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*) AS row_count,
       MIN(sale_amount) AS min_amount,
       MAX(sale_amount) AS max_amount
FROM storage_index_lab
WHERE sale_amount BETWEEN 8000000 AND 8000100;

The query returned:

ROW_COUNT   MIN_AMOUNT   MAX_AMOUNT
---------   ----------   ----------
201         8000000      8000100

Only 201 rows matched.

The execution plan showed:

TABLE ACCESS STORAGE FULL

with:

storage(
    "SALE_AMOUNT">=8000000
    AND "SALE_AMOUNT"<=8000100
)

The reported statistics included approximately:

Buffers: 49,110
Reads:   49,106

At this point, I deliberately did not claim Storage Index usage.

Why?

Because the execution plan alone didn't prove it.

10. Looking at Exadata Runtime Statistics

This was the turning point of the experiment.

I queried:

SELECT
    name,
    value
FROM v$sysstat
WHERE LOWER(name) LIKE '%storage index%'
   OR LOWER(name) LIKE '%cell physical%'
ORDER BY name;

Among the returned statistics was:

cell physical IO bytes saved by storage index

This is much more useful for our investigation than simply looking at:

TABLE ACCESS STORAGE FULL

The initial value was:

93,339,648

11. Before-and-After Storage Index Measurement

I captured the counter before executing the selective query:

SELECT
    name,
    value
FROM v$sysstat
WHERE name = 'cell physical IO bytes saved by storage index';

Result:

93,339,648

Then I executed:

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*) AS row_count,
       MIN(sale_amount) AS min_amount,
       MAX(sale_amount) AS max_amount
FROM storage_index_lab
WHERE sale_amount BETWEEN 8000000 AND 8000100;

Again, the result was:

201 rows

Immediately afterward, I checked the same statistic:

SELECT
    name,
    value
FROM v$sysstat
WHERE name = 'cell physical IO bytes saved by storage index';

The new value was:

445,161,472

Therefore the counter increased by:

445,161,472
-
93,339,648
----------------
351,821,824 bytes

Approximately:

335.5 MiB

12. An Important Interpretation Caveat

This is where performance analysis needs to be precise.

It would be tempting to write:

"Our query saved 335.5 MiB through Storage Index."

I would avoid making that exact statement.

Why?

Because:

V$SYSSTAT

contains cumulative system-level statistics.

Therefore, the more technically accurate statement is:

During the controlled test interval, the cell physical IO bytes saved by storage index cumulative counter increased by 351,821,824 bytes (~335.5 MiB).

That is strong evidence that Storage Index activity occurred during the interval.

However, it isn't a per-SQL measurement proving that every one of those bytes was caused exclusively by that single SQL statement.

This distinction matters in production troubleshooting.

13. The Complete Exadata Performance Picture

Our experiment demonstrated several different layers.

Layer 1 — HCC

The table went from:

680.56 MB

to:

3 MB

in this controlled dataset.

HCC dramatically reduced the physical storage footprint.

Layer 2 — Smart Scan / Storage Processing

The execution plan showed:

TABLE ACCESS STORAGE FULL

and:

storage(...)

This demonstrated that predicates were being processed at the storage layer.

Layer 3 — Storage Index

The Exadata runtime statistic:

cell physical IO bytes saved by storage index

provided additional runtime evidence of Storage Index activity.

The controlled interval showed an increase of:

351,821,824 bytes

in that cumulative counter.

14. What I Learned From This Lab

The biggest lesson wasn't the 227× compression ratio.

It wasn't even the 99.6% reduction in buffers.

The most important lesson was:

Don't confuse an execution-plan indication with proof of a storage optimization.

For example:

TABLE ACCESS STORAGE FULL

doesn't automatically mean:

Storage Index used

And:

storage(...)

doesn't automatically mean:

Storage Index used

Instead, performance analysis should combine:

Execution Plan
      +
Actual Execution Statistics
      +
Exadata Runtime Metrics
      =
Evidence-based conclusion

15. HCC vs Smart Scan vs Storage Index

It's also important to understand that these technologies solve different problems.

Technology	Primary purpose
HCC	Reduce storage footprint and data volume
Smart Scan	Process eligible operations in the storage layer
Storage Index	Avoid unnecessary storage-region reads when metadata allows elimination

Conceptually:

                    Oracle Exadata
                         │
                         ▼
                    SQL Request
                         │
                         ▼
                 Storage Processing
                         │
             ┌───────────┴───────────┐
             │                       │
            HCC                 Smart Scan
       Reduce data volume       Process predicates
                                     │
                                     ▼
                              Storage Index
                              Skip irrelevant
                              storage regions

These capabilities can work together, but they are not interchangeable concepts.

16. Production DBA Takeaways

When troubleshooting an Exadata query, I would approach it systematically.

Step 1 — Check the execution plan

Look for:

TABLE ACCESS STORAGE FULL

and storage predicates:

storage(...)

Step 2 — Check actual execution statistics

Use:

DBMS_XPLAN.DISPLAY_CURSOR(
    NULL,
    NULL,
    'ALLSTATS LAST +IOSTATS'
);

Look at:

Buffers
Reads
Actual rows
Elapsed time
Storage operations

Step 3 — Check Exadata runtime statistics

Don't stop at the execution plan.

Investigate relevant:

cell physical IO ...

statistics.

Step 4 — Establish before/after measurements

For cumulative counters:

Before
   ↓
Execute controlled workload
   ↓
After
   ↓
Delta

This is much more useful than looking at one cumulative number without context.

Step 5 — Don't generalize from a tiny lab

My lab produced:

227× HCC compression

and:

99.64% fewer buffers

Those are observations from this specific dataset and workload, not universal Exadata performance guarantees.

Production data must be measured independently.

17. Final Conclusion

This hands-on lab helped me move from knowing Exadata terminology to actually observing its behavior.

The experiment demonstrated:

10M rows
    │
    ▼
680.56 MB uncompressed
    │
    ▼
3 MB HCC Query High
    │
    ▼
Storage-level predicate processing
    │
    ▼
Smart Scan
    │
    ▼
Storage Index runtime evidence
    │
    ▼
351,821,824-byte increase
in the Storage Index saved-I/O counter
during the controlled test interval

The biggest takeaway for me is simple:

Measure first. Interpret second.

An Exadata execution plan can tell us what the database intends to do and where predicate processing occurs. Exadata runtime statistics help us understand what the storage layer actually accomplished.

That distinction is critical when doing serious Oracle Database performance troubleshooting.

🔥 Key commands from the lab

Execution plan:

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        NULL,
        NULL,
        'ALLSTATS LAST +IOSTATS'
    )
);

Storage Index statistic:

SELECT
    name,
    value
FROM v$sysstat
WHERE name = 'cell physical IO bytes saved by storage index';

Segment size:

SELECT
    segment_name,
    ROUND(bytes/1024/1024,2) AS size_mb
FROM dba_segments
WHERE owner = 'PERF_LAB';

Statistics gathering:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    'PERF_LAB',
    'SALES_FACT_BASELINE'
);

Muthukumar Cloud Oracle DBA & Exadata Knowledge Hub

Search This Blog

Oracle Exadata Performance Lab: HCC, Smart Scan and Storage Index — Proven With Real Metrics

1. Lab Objective

2. Building the Production-Style Test Table

3. Establishing the Uncompressed Baseline

4. Creating the HCC Query High Table

Important production caveat

5. Testing Smart Scan / Storage-Level Predicate Processing

6. Running the Same Query Against HCC

7. What Does `storage()` Actually Tell Us?

8. Designing a Dedicated Storage Index Experiment

9. First Storage Index Query

10. Looking at Exadata Runtime Statistics

11. Before-and-After Storage Index Measurement

12. An Important Interpretation Caveat

13. The Complete Exadata Performance Picture

Layer 1 — HCC

Layer 2 — Smart Scan / Storage Processing

Layer 3 — Storage Index

14. What I Learned From This Lab

15. HCC vs Smart Scan vs Storage Index

16. Production DBA Takeaways

Step 1 — Check the execution plan

Step 2 — Check actual execution statistics

Step 3 — Check Exadata runtime statistics

Step 4 — Establish before/after measurements

Step 5 — Don't generalize from a tiny lab

17. Final Conclusion

🔥 Key commands from the lab

Comments

Post a Comment

Oracle Exadata Performance Lab: HCC, Smart Scan and Storage Index — Proven With Real Metrics

1. Lab Objective

2. Building the Production-Style Test Table

3. Establishing the Uncompressed Baseline

4. Creating the HCC Query High Table

Important production caveat

5. Testing Smart Scan / Storage-Level Predicate Processing

6. Running the Same Query Against HCC

7. What Does storage() Actually Tell Us?

8. Designing a Dedicated Storage Index Experiment

9. First Storage Index Query

10. Looking at Exadata Runtime Statistics

11. Before-and-After Storage Index Measurement

12. An Important Interpretation Caveat

13. The Complete Exadata Performance Picture

Layer 1 — HCC

Layer 2 — Smart Scan / Storage Processing

Layer 3 — Storage Index

14. What I Learned From This Lab

15. HCC vs Smart Scan vs Storage Index

16. Production DBA Takeaways

Step 1 — Check the execution plan

Step 2 — Check actual execution statistics

Step 3 — Check Exadata runtime statistics

Step 4 — Establish before/after measurements

Step 5 — Don't generalize from a tiny lab

17. Final Conclusion

🔥 Key commands from the lab

Comments

Post a Comment

7. What Does `storage()` Actually Tell Us?