If you are running workloads on Oracle Exadata, you are likely sitting on a goldmine of performance capability. The most significant differentiator between Exadata and traditional storage architectures is a feature called Smart Scan (also known as Cell Offload Processing).
Instead of treating storage as dumb disks that just serve blocks to the database, Exadata pushes SQL processing down to the storage tier. This article explains how Smart Scan works, how to verify it, and how to control its behaviour for testing.
What is Exadata Smart Scan?
In a traditional database architecture, a large scan can cause the storage layer to send vast amounts of data across the network to the database server, which then filters data in memory. Smart Scan moves predicate evaluation and projection to the storage cells so only qualifying rows/columns traverse the interconnect.
Smart Scan relies on three primary optimisations:
- Predicate filtering — storage cells evaluate
WHEREpredicates and return only matching rows. - Column projection — only the requested columns are returned, reducing payload size.
- Storage indexes — per-extent MIN/MAX metadata lets cells skip extents that cannot satisfy a predicate.
Requirements for Smart Scan to Occur
- Object location: Data must be on ASM disk groups served by Exadata storage cells.
- Access path: Smart Scan is used for full table scans or index fast full scans (not for single-row index lookups or typical index range scans).
- Direct path reads: Queries must use direct path reads for pushdown to be possible.
- Pushdownable predicates: Predicates must use supported operators and avoid non-deterministic UDFs or expressions that prevent pushdown.
How to Check if Smart Scan is Used
1) Execution plan
Run the query and inspect the runtime plan via DBMS_XPLAN. Look for TABLE ACCESS STORAGE or similar storage-access operations and storage predicates.
-- Run the query first, then capture the plan
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
Example plan (illustrative):
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 10 |
| 1 | SORT AGGREGATE | | 1 | 100 | |
| 2 | TABLE ACCESS STORAGE FULL | BIG_TABLE | 1000 | 10000 | 10 (10) |
--------------------------------------------------------------------------------
-- presence of "TABLE ACCESS STORAGE" and storage predicate annotations indicate pushdown intent
2) Session / statistics checks
Capture session-level I/O statistics immediately after running the query to understand bytes eligible for offload and bytes returned by Smart Scan.
SELECT n.name,
ROUND(s.value / 1024 / 1024, 2) AS megabytes
FROM v$sesstat s
JOIN v$statname n ON s.statistic# = n.statistic#
WHERE s.sid = SYS_CONTEXT('USERENV', 'SID')
AND n.name IN (
'physical read total bytes',
'cell physical IO interconnect bytes',
'cell physical IO bytes eligible for predicate offload',
'cell physical IO bytes saved by storage index',
'cell physical IO interconnect bytes returned by smart scan'
);
Example output (illustrative):
NAME MEGABYTES
---------------------------------------------------------- ----------
physical read total bytes 5000.00
cell physical IO bytes eligible for predicate offload 5000.00
cell physical IO bytes saved by storage index 1200.00
cell physical IO interconnect bytes returned by smart scan 250.00
Interpretation:
- 5,000 MB were eligible for offload.
- Storage indexes skipped 1,200 MB of reads.
- Smart Scan returned only 250 MB across the interconnect.
This demonstrates the reduction in physical I/O and network traffic when Smart Scan is effective.
Storage-side checks (cellcli / dcli)
Run these on a cell or across cells to inspect metrics and alerts. Metric names vary by software version; list metrics first and filter for smart/scan related names.
On a single cell:
cellcli -e "LIST METRICCURRENT" | egrep -i "smart|scan|fc_by_used"
cellcli -e "LIST ALERTHISTORY" | tail -n 50
Across cells with dcli (hostnames in cell_group):
dcli -g cell_group -l celladmin "cellcli -e \"LIST METRICCURRENT\" | egrep -i \"smart|scan|fc_by_used\""
dcli -g cell_group -l celladmin "cellcli -e \"LIST ALERTHISTORY\" | tail -n 20"
Other useful cellcli commands:
cellcli -e "LIST PHYSICALDISK ATTRIBUTES name, disktype, physicalrpm, status"
cellcli -e "LIST CELL ATTRIBUTES name, status"
cellcli -e "LIST ALERTHISTORY WHERE severity='critical'"
Practical examples and controls
Force / disable offload (testing only)
You can experiment by changing session parameters to encourage direct path reads or disable offload for comparison. Note: some parameters are undocumented and should be used only for controlled testing.
-- Encourage direct path reads (example, use with care)
ALTER SESSION SET "_serial_direct_read" = always;
-- Disable Smart Scan offload for the session
ALTER SESSION SET cell_offload_processing = false;
Example: detect scanned vs logical reads
Compare buffer_gets / disk_reads or inspect V$SQL before/after. Example:
SELECT sql_id, executions, buffer_gets, disk_reads
FROM v$sql
WHERE sql_text LIKE '%YOUR_QUERY_TEXT%';
If Smart Scan is active you’ll normally observe lower disk_reads and measurable cell-related statistics indicating the work was offloaded.
Verifying if a SQL Function is “Offloadable”
Smart Scan can only offload the WHERE clause if the storage cells know how to compute the functions used in the predicate. If you use a custom PL/SQL function or an unsupported built-in function, the entire block of data must be sent back to the database server, bypassing the Smart Scan benefit entirely.
You can check exactly which functions are offloadable by querying the V$SQLFN_METADATA view.
-- Check if the 'UPPER' function can be offloaded
SELECT name, offloadable
FROM v$sqlfn_metadata
WHERE name = 'UPPER';
-- Check if 'REGEXP_LIKE' can be offloaded
SELECT name, offloadable
FROM v$sqlfn_metadata
WHERE name = 'REGEXP_LIKE';
Expected output (illustrative):
NAME OFFLOADABLE
------------------------------ -----------
UPPER YES
REGEXP_LIKE YES
Example: Monitoring Smart Scan Wait Events
When a query is actively running and utilizing Smart Scan, the session will register specific wait events. You can query V$SESSION_EVENT or V$SESSION to see exactly what the session is waiting on. The hallmark wait event of a successful Smart Scan is cell smart table scan.
-- Find active sessions currently performing a Smart Scan
SELECT sid, serial#, sql_id, event, state
FROM v$session
WHERE event LIKE 'cell smart%';
Example output (illustrative):
SID SERIAL# SQL_ID EVENT STATE
----- ---------- ------------- ---------------------------------------- -------
145 12984 05tk7cd18wbwu cell smart table scan WAITING
201 49211 81ab9zx38cvmo cell smart index scan WAITING
Diagnostic note: If you see cell multiblock physical read instead of cell smart table scan for a query doing a full table scan, it means Smart Scan is not being used and the database is performing traditional, heavier I/O over the interconnect.
Example: Modern Use Case — AI Smart Scan (Vector Search)
If you are running Oracle Database 23ai on Exadata, Smart Scan has been upgraded to handle AI Vector Search. This pushes the vector distance calculation down to the storage cells.
-- AI Smart Scan offloads the VECTOR_DISTANCE calculation to the storage tier
SELECT property_id, price
FROM houses
ORDER BY VECTOR_DISTANCE(photo_vector, :reference_vector)
FETCH APPROXIMATE FIRST 10 ROWS ONLY;
Best practices
- Keep Exadata cell and database software patched for latest pushdown features.
- Avoid non-pushdownable expressions on predicate columns (e.g., wrapping in UDFs) when possible.
- Validate Smart Scan on representative production workloads; use session toggles for controlled A/B testing.
- Collect
exachkandcellcliexports for trend analysis and troubleshooting.