Skip to main content

Oracle Exadata Smart Scan Explained – How It Accelerates Query Performance

By Gowthami | apps-dba.com | Oracle Exadata Series

If you work with Oracle Exadata, one of the most transformative features you will encounter is Smart Scan. It is the engine behind Exadata's legendary query acceleration, and understanding it deeply is essential for any Oracle DBA working in Exadata environments. In this post, we will break down exactly how Smart Scan works, when it activates, how to verify it, and how to tune your environment to get the most out of it.

What is Smart Scan?

Smart Scan is Exadata's ability to offload SQL processing — specifically full table scans and index range scans — directly to the Exadata Storage Cells. Instead of the database server reading all data blocks and filtering them in the database layer, Exadata pushes the filter predicates, column projections, and even joins down to the storage cells. Only the relevant rows and columns are returned to the database tier.

This is a radical departure from traditional Oracle storage architecture, where storage is "dumb" — it only reads and returns blocks. Exadata storage cells are intelligent processing units running Cellsrv software that understands SQL predicates.

How Smart Scan Works – Step by Step

  1. Predicate Offloading: Oracle sends the WHERE clause predicates down to each storage cell.
  2. Column Projection: Only the columns referenced in the SELECT list are returned — not all columns in the table.
  3. Row Filtering at Storage: Each cell evaluates predicates row by row and discards non-matching rows before sending data back.
  4. Parallel Processing: Multiple storage cells process different data segments simultaneously in parallel.
  5. IB Network Transfer: Only the filtered, projected result set travels over the InfiniBand network to the database server.
💡 Key Insight: In a traditional setup, a 1 TB table scan might transfer 1 TB over the storage network. With Smart Scan, only the matching rows and required columns are transferred — potentially reducing data movement by 99%.

Prerequisites for Smart Scan to Activate

ConditionDetail
Full Table Scan or Fast Full Index ScanSmart Scan only works with these access paths — not regular index range scans
Direct Path ReadThe segment must be read via direct path (bypassing buffer cache). Happens automatically for large objects on Exadata.
Object on Exadata StorageThe table must reside on ASM diskgroups backed by Exadata storage cells
Cell Offload Compatible SQLNot all SQL constructs can be offloaded. Complex PL/SQL functions may prevent offloading.

Verifying Smart Scan is Active

1. Check Cell Physical IO Statistics

SELECT name, value
FROM v$mystat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name IN (
  'cell physical IO interconnect bytes',
  'cell physical IO interconnect bytes returned by smart scan',
  'physical read total bytes'
)
ORDER BY name;

2. Execution Plan – Storage Predicate

EXPLAIN PLAN FOR
SELECT /*+ FULL(e) */ department_id, SUM(salary)
FROM employees e
WHERE hire_date > DATE '2020-01-01'
GROUP BY department_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Look for "storage" in the Predicate Information section — this indicates predicate pushdown to storage cells.

What Prevents Smart Scan?

  • Small tables – Oracle may cache them in buffer cache rather than use direct path reads
  • Index access paths – Regular B-tree index range scans do not trigger Smart Scan
  • Non-offloadable SQL functions – User-defined PL/SQL functions in WHERE clauses cannot be pushed to cells
  • Cell offload disabled – The parameter cell_offload_processing is set to FALSE (default is TRUE)

Forcing or Disabling Smart Scan

-- Disable Smart Scan for current session
ALTER SESSION SET cell_offload_processing = FALSE;

-- Re-enable
ALTER SESSION SET cell_offload_processing = TRUE;

-- Check current setting
SELECT name, value FROM v$parameter WHERE name = 'cell_offload_processing';

Smart Scan and Storage Indexes

Smart Scan works hand-in-hand with Storage Indexes — an Exadata-only feature that maintains in-memory min/max value ranges for each 1MB storage region. Before performing a Smart Scan, Exadata consults storage indexes to skip entire 1MB storage regions that cannot possibly contain matching rows. This further reduces I/O dramatically.

Summary

Smart Scan is one of the most powerful features that distinguishes Exadata from conventional Oracle deployments. By pushing filter and projection processing down to the storage cells, it enables massive reductions in data movement and dramatically accelerates analytical queries and large table scans.

Tags: Oracle Exadata, Smart Scan, Exadata Performance, Oracle DBA, Cell Offload

Labels:

Comments

Post a Comment

Popular posts from this blog

Data Safe - Introduction

Oracle Data Safe - Practical Guide Oracle Data Safe learner guide Oracle Data Safe Assess risk, discover sensitive data, audit activity, and mask safely It focuses on what Data Safe helps you do operationally: review security posture, find risky identities, centralize auditing, locate sensitive data, and produce safer non-production copies. Contents 01 Why Data Safe matters 02 Where it fits 03 Capability map 04 Assessments 05 Activity Auditing 06 Discovery and Masking 07 Operating model 08 First 30 days 09 Knowledge check Section 01 Why Data Safe matters Database security work is often fragmented. One process checks configuration drift, another stores audit logs, another team scans for PII, and another team writes masking logic for test refreshes. Data Safe is useful because it turns those separate jobs into one security workflow. Key idea The best way to think about Data Safe is as a control plane for database security posture: assess the target, identify risky accounts, d...
Post a Comment
Read more

Testing Different Access Paths : Concatenated Index

Oracle Concatenated Indexes - Practical Deep Dive Oracle concatenated index deep dive Concatenated Indexes How composite indexes really work, why column order matters, and when skip scan changes the story Concatenated indexes, also called composite indexes, are easy to explain badly and surprisingly rich to explain well. The usual summary is “Oracle can use the index only when the leading column is present,” but that is only the starting point. To design them properly, you need to think about leading portions, equality versus range predicates, ordering requirements, skip scan eligibility, covering behavior, and whether one composite index can replace several single-column indexes in a given workload. Contents 01 What concatenated indexes are 02 Leading edge and leading portion 03 Why column order matters 04 Skip scan and when it helps 05 Access patterns and plan reading 06 Covering and sort elimination 07 Design rules that actually hold 08 Common mistakes 09 End-to-end demo 1...
Post a Comment
Read more

Database Replay - Real Application Testing (RAT)

Oracle Database Replay and RAT - Practical Deep Dive Oracle Database Replay deep dive Database Replay and Real Application Testing How to validate upgrades, patches, migrations, and risky changes with real workload behavior Database Replay is one of the most practical risk-reduction tools in the Oracle DBA toolbox. Instead of trusting synthetic benchmarks, isolated SQL tests, or intuition, you capture a real production workload, restore a test system to the same logical starting point, replay that workload, and analyze whether performance, errors, timing, and transactional behavior still look safe. Contents 01 What RAT actually is 02 Why Database Replay matters 03 End-to-end workflow 04 Capture design and prerequisites 05 Preprocess, calibrate, replay 06 Reading the results well 07 Pitfalls and unreplayable work 08 Database Replay vs SPA 09 Practical playbooks 10 Knowledge check Section 01 What Real Application Testing actually is Real Application Testing, usually shortened...
Post a Comment
Read more