In Exadata environments, Smart Scan normally activates automatically when Oracle performs a full table scan with direct path reads.
However, in several real-world scenarios, the database optimizer cannot offload the work to storage servers, so Smart Scan does not occur.

Below are the most common technical reasons.

---

1. Query Uses Index Access Instead of Full Table Scan

Smart Scan works only with Full Table Scan.

If Oracle uses:

• Index Range Scan
• Index Unique Scan
• Index Skip Scan

then data blocks must be fetched individually from storage, so offloading cannot occur.

Example:

SELECT * FROM employees WHERE employee_id = 100;

Oracle will use an index, so Smart Scan is not triggered.

---

2. Direct Path Read Is Not Used

Smart Scan requires Direct Path Read.

If Oracle uses buffer cache reads instead, Smart Scan cannot happen.

This may occur when:

• Table is small
• Table blocks already exist in buffer cache
• Database decides buffered read is faster

---

3. cell_offload_processing Parameter Disabled

Smart Scan requires this parameter.

Check:

SHOW PARAMETER cell_offload_processing;

If it is FALSE, storage servers cannot perform Smart Scan.

---

4. Unsupported SQL Operations

Some SQL operations cannot be offloaded.

Examples include:

• Certain PL/SQL functions
• Non-deterministic functions
• Complex expressions
• Unsupported operators

Example:

SELECT * FROM sales
WHERE TO_CHAR(sales_date,'YYYY') = '2024';

The function prevents predicate offloading.

---

5. Tables Not Stored on Exadata Storage

Smart Scan works only on Exadata storage cells.

If the table is stored on:

• NFS storage
• Non-Exadata disks
• ACFS
• External tables

then Smart Scan cannot occur.

---

6. Parallel Execution Disabled for Large Tables

Although Smart Scan can work without parallelism, many large scans rely on parallel query.

If parallel execution is not enabled, Oracle may choose a different plan.

---

7. Row-Level Locks

If a query must read rows that are locked by another transaction, Oracle may revert to buffered reads instead of Smart Scan.

---

8. Data Already in Buffer Cache

If the blocks are already cached in memory, Oracle prefers reading from the buffer cache rather than performing Smart Scan.

---

9. Certain Data Types Are Not Offloadable

Smart Scan cannot process some data types efficiently.

Examples include:

• LOB columns
• LONG datatype
• Certain encrypted columns
• Object types

---

10. Queries Using ROWNUM

Some queries with ROWNUM or stopkey operations may disable Smart Scan.

Example:

SELECT * FROM sales WHERE ROWNUM < 10;

Oracle may avoid full scan offloading.

---

11. Serial Direct Reads Not Triggered

Smart Scan often depends on serial direct reads.

If Oracle decides to use buffered reads, Smart Scan will not activate.

---

How to Confirm Whether Smart Scan Was Used

Check execution plan:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

If Smart Scan is used, you will see:

TABLE ACCESS STORAGE FULL

If not used:

TABLE ACCESS FULL

---

Simple Summary

Smart Scan will NOT trigger when:

1. Index scan is used
2. Direct path read not used
3. cell_offload_processing = FALSE
4. Unsupported SQL functions
5. Data not stored on Exadata storage
6. Blocks already in buffer cache
7. Row locking exists
8. Unsupported datatypes
9. ROWNUM or stopkey operations used