Oracle Exadata Smart Scan and Storage Index : How Engineered Storage Changes the Rules of Query Tuning :

Introduction :

Everything covered so far assumes a conventional storage architecture: the database server reads 8 KB blocks from disk, brings them into the buffer cache, and then evaluates the predicates row by row. The cost of a full table scan is proportional to the number of blocks in the table, because every block must cross the I/O boundary between storage and the database server.

Exadata breaks this assumption in two places. Smart Scan moves predicate filtering and column projection from the database server down to the storage cells. Storage Index goes further : it allows the storage cell to skip entire regions of disk without reading them at all, because it knows from an in-memory summary that those regions cannot contain the values the query is looking for .

The consequence for performance tuning is significant. A query that would require a full table scan on conventional storage may perform dramatically better on Exadata without any change to the SQL or the execution plan. Conversely, a DBA who does not understand Smart Scan and Storage Index may misread an execution plan on Exadata and chase the wrong bottleneck.

Theoretical Foundation :

How Smart Scan Works :

Smart Scan is activated automatically for certain scan operations that use the Direct Path Read mechanism, typically parallel queries and large sequential scans. It does not apply to every query. When it is active, the database server sends the query predicates and the list of required columns to the storage cell via the i-DB protocol. The storage cell then performs the following tasks before returning data to the database server :

The performance impact is easiest to see with a concrete example. Consider a 1 TB table queried with SELECT customer_name FROM calls WHERE amount > 200. Without Smart Scan, all 1 TB of blocks must be transferred to the database server. With Smart Scan, the predicate and column filtering happen on the cell, and only the matching rows (in Oracle’s example, 2 MB) are transferred . The I/O reduction is not incremental; it is orders of magnitude.

How Storage Index Works :

Storage Index is a separate mechanism that operates at a lower level. It is an in-memory structure maintained automatically by the Exadata Storage Server software. For each 1 MB region of disk, the storage index tracks the minimum and maximum values of the columns in that region, along with set membership information for low-cardinality columns .

When a query includes a predicate such as WHERE cust_level = 3, the storage cell checks the storage index for each region. If the minimum and maximum values for cust_level in a region are 1 and 2, the cell knows that no row in that region can satisfy the predicate. It skips the I/O for that entire region without reading a single byte from disk .

Storage Index is not an index in the traditional sense. It does not point to specific rows. It is a negative filter: it tells the storage cell which regions definitely do not contain the data, allowing those regions to be eliminated from the scan. Oracle’s documentation describes it as “I/O filtering”,  replacing large numbers of disk reads with a few in-memory lookups .

The Key Limitation :

Storage Index depends on data clustering. If the values of the queried column are scattered randomly across the table, each 1 MB region will contain a wide range of values, and the min/max pruning will be ineffective. The storage index works best when the data is physically ordered by the column that appears in the WHERE clause – for example, a table partitioned by date, or a table loaded in sorted order .

This is why Oracle recommends ordering rows by columns that frequently appear in WHERE clauses. The storage index cannot reorganize the data; it can only exploit the order that already exists.

Step 1 – Confirm That Smart Scan Is Active :

Before diagnosing anything, confirm that the query is actually eligible for Smart Scan and that offload is happening.

Check the Execution Plan :

EXPLAIN PLAN FOR :
SELECT customer_name FROM calls WHERE amount > 200;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Look for TABLE ACCESS STORAGE FULL in the plan. This indicates that the scan is a candidate for Smart Scan. A plain TABLE ACCESS FULL means the storage is not Exadata, or the Smart Scan capability is not enabled.

Check the Session Statistics :

SELECT name, value FROM v$mystat m, v$statname n WHERE m.statistic# = n.statistic# AND n.name IN 
(   'cell physical IO bytes eligible for predicate offload',
    'cell physical IO interconnect bytes returned by smart scan',
    'cell physical IO bytes saved by storage index' );

If the “eligible” statistic is high but the “returned” statistic is much lower, Smart Scan is working. If both are high and close to each other, the offload is not effective – possibly because the predicates are not selective, or the columns are wide.

Step 2 – Measure Storage Index Effectiveness :

The most direct metric for Storage Index benefit is the number of bytes saved.

Query V$SYSSTAT :

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

This statistic shows the cumulative bytes of I/O that Exadata avoided by using the storage index . To evaluate a specific query, run it, then check the delta in this statistic.

Interpret the Ratio :

If the storage index saves a large fraction of the I/O that was eligible for offload, the data is well-clustered for the query predicates. If the saved bytes are small relative to the total scanned bytes, the storage index is not pruning effectively.

A concrete example from an Exadata AWR report: 5.4 GB/s of I/O was eligible for Smart Scan. Of that, 4.6 GB/s was processed by the cells before predicate offload, and only 55 MB/s was returned to the database nodes. The storage index saved 820 MB/s of I/O . The ratio of saved I/O to eligible I/O tells you how much benefit the storage index is providing.

Step 3 – Diagnose Weak Storage Index Pruning :

If the storage index is not saving much I/O, the cause is almost always one of three things.

Cause A – Data is Not Clustered by the Predicate Column :

If the query filters on order_date but the table is loaded in random order, each 1 MB region contains a wide range of dates. The min/max values for each region will span the entire range, and pruning will be ineffective.

Check : Query the table’s clustering factor or examine the physical distribution.

SELECT table_name, blocks, avg_row_len FROM user_tables WHERE table_name = 'YOUR_TABLE';

If the table is a heap table loaded without ordering, Storage Index pruning will be weak for date predicates.

Cause B – The Predicate Uses a Function on the Column :

Storage Index operates on the raw column values. If the query uses WHERE TRUNC(order_date) = '01-JAN-2024', the storage index cannot use its min/max on order_date because the predicate is on a function of the column.

Fix : Rewrite the predicate to reference the bare column:

WHERE order_date >= DATE '2024-01-01' AND order_date < DATE '2024-01-02'

Cause C – The Query Is Not Using Direct Path Read :

Smart Scan and Storage Index only apply to scans that use Direct Path Read. A query running in serial mode against a small table may use the buffer cache path, which bypasses Smart Scan entirely.

Fix : Force parallel execution, or increase the table size so the optimizer chooses a direct path scan.

SELECT /*+ PARALLEL(4) */ customer_name FROM calls WHERE amount > 200;

Step 4 – Use the Hidden Parameter to Test Storage Index Impact :

If you want to measure what a query would cost without Storage Index – for example, to decide whether to invest in data reorganization – you can disable Storage Index at the session level.

ALTER SESSION SET "_KCFIS_STORAGEIDX_DISABLED" = TRUE;

Run the query, note the I/O statistics. Then re-enable :

ALTER SESSION SET "_KCFIS_STORAGEIDX_DISABLED" = FALSE;

Run the query again and compare. The difference is the Storage Index benefit . This is a diagnostic technique, not a production setting.

Step 5 – Improve Storage Index Effectiveness Through Data Organization :

The storage index cannot be tuned directly – it is built and maintained automatically. The only way to improve its effectiveness is to improve the physical clustering of the data.

Option A – Partition the Table by the Predicate Column :

If queries frequently filter on order_date, range-partition the table by order_date. Partition pruning and Storage Index both benefit. Oracle’s documentation shows an example where a range-partitioned Orders table uses Storage Index to avoid scanning partitions that do not contain the target date range .

Option B – Use Exadata Hybrid Columnar Compression (EHCC) with Clustering :

EHCC stores data in columnar format within compression units. When combined with clustering (e.g., CLUSTER BY or SORT on load), the min/max values within each compression unit become narrower, and the storage index prunes more aggressively.

Option C — Reorganize the Table with CTAS Ordered by the Predicate Column :

For an existing heap table, create a new table ordered by the most common predicate column :

CREATE TABLE calls_reordered AS SELECT * FROM calls ORDER BY amount;

This is a one-time reorganization. The storage index will then have narrow min/max ranges for amount and will prune effectively.

Step 6 – Understand When Smart Scan Does Not Help :

Smart Scan is not a universal performance fix. It provides little benefit in the following cases :

The DBA’s job is to know when Exadata’s offload capabilities are relevant and when they are not. Applying Smart Scan expectations to an OLTP workload will lead to incorrect conclusions about performance.

Step 7 – Monitor Storage Index Persistence and Rebuild :

Starting with Exadata System Software 21.2.0, Storage Index can persist across cell restarts and upgrades. The in-memory structures are written to disk during graceful shutdown and restored on restart. This eliminates the rebuild period that previously followed a cell restart, during which Storage Index pruning was less effective .

Check the Persistence Setting :

cellcli -e "LIST CELL ATTRIBUTES storageIndexPersMode"

If persistence is enabled, Storage Index data survives restarts. If not, the index is rebuilt automatically as queries execute, but performance may be weaker immediately after a restart.

Summary :

Closing Note :

Exadata does not change the laws of query optimization. It changes the cost of I/O. On conventional storage, a full table scan of a 1 TB table means reading 1 TB from disk. On Exadata with Smart Scan and Storage Index, the same scan may read only the regions that the storage index cannot eliminate, and return only the rows and columns that the query requires.

The execution plan on Exadata looks different because the work is distributed differently. A DBA who does not understand Smart Scan will see TABLE ACCESS STORAGE FULL and assume it is a full scan. It is a full scan in terms of the object being scanned, but not in terms of the data being read. The storage index may eliminate most of the I/O, and Smart Scan may return a tiny fraction of the rows. The plan is honest; the interpretation requires knowing what Exadata is doing underneath.