Exadata performance tuning — why your query is not using Smart Scan and how to fix it
Performance
The troubleshooting post every Exadata DBA eventually needs. Covers the exact conditions required for Smart Scan to activate, the ten most common reasons a query bypasses Smart Scan (wrong object type, buffer cache hits, parallel query settings, object encryption), how to diagnose using execution plans and cell offload stats, and how to fix each scenario. Includes real SQL examples.
Exadata — Basics to Pro Series
1. What Is Exadata
· 2. Hardware Components
· 3. Architecture Deep Dive
· 4. Smart Scan, Storage Indexes, HCC
· 5. Monitoring
· 6. Performance Tuning
· 7. Administration
· 8. Patching
· 9. EBS on Exadata
· 10. OCI Exadata
You moved to Exadata and expected dramatic performance improvements for your large queries. Some queries ran 10x faster. Others — especially in development or UAT — seem to perform no better than they did on the old SAN. A developer asks why their report is taking the same 45 minutes it always did. You check the execution plan. The word STORAGE is not there. Smart Scan is not activating.
This is one of the most common and most frustrating Exadata performance problems. The hardware is capable of offloading the query. Something is stopping it. This article gives you a systematic diagnosis process, the 10 most common reasons Smart Scan does not activate, and the exact fix for each one — with SQL you can run immediately.
This article assumes you have read Article 4 (Smart Scan conditions and mechanics) and Article 5 (monitoring with V$SYSSTAT and cellcli). If Smart Scan is completely new to you, start there first. This article focuses on troubleshooting, not fundamentals.
Step 1 — Confirm Smart Scan Is Not Happening
Before investigating reasons, confirm precisely whether Smart Scan is absent and at what level. There are three places to check.
Check 1 — Execution plan keyword
The most immediate check. In an execution plan, Smart Scan shows as TABLE ACCESS STORAGE FULL. Without Smart Scan it shows as TABLE ACCESS FULL. The presence or absence of the word STORAGE in the plan operation is the first and fastest indicator.
Check execution plan for Smart Scan indicator
-- Option 1: Check plan for a SQL_ID from cursor cache
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR('&your_sql_id', NULL, 'ALLSTATS LAST')
);
-- Option 2: Use EXPLAIN PLAN for an ad hoc query
EXPLAIN PLAN FOR
SELECT customer_name FROM orders WHERE amount > 200;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
-- In the plan output look for:
-- WITH Smart Scan: | TABLE ACCESS STORAGE FULL | orders |
-- WITHOUT Smart Scan: | TABLE ACCESS FULL | orders |
--
-- The word STORAGE confirms the cell is handling the scan.
-- Its absence means conventional buffer cache read is used.
Check 2 — V$SQL offload statistics
Check Smart Scan offload from V$SQL for your SQL_ID
-- Check offload statistics for a specific SQL statement
SELECT sql_id,
executions,
ROUND(io_cell_offload_eligible_bytes / 1073741824, 2)
AS eligible_gb,
ROUND(io_cell_offload_returned_bytes / 1073741824, 2)
AS returned_gb,
ROUND((1 - io_cell_offload_returned_bytes /
NULLIF(io_cell_offload_eligible_bytes, 0)) * 100, 1)
AS pct_io_saved
FROM v$sql
WHERE sql_id = '&your_sql_id';
-- Interpretation:
-- eligible_gb = 0 AND returned_gb = 0 : Smart Scan not activating at all
-- eligible_gb > 0, pct_io_saved = 0 : Smart Scan activating but not filtering
-- pct_io_saved > 80% : Smart Scan working well
-- pct_io_saved < 20% : Smart Scan working but predicates not selective
Check 3 — Wait events in the session
Check wait events to confirm read path used
-- Check current wait event for a session (sid = your session)
SELECT sid, event, state, seconds_in_wait
FROM v$session
WHERE sid = &your_sid;
-- Wait event interpretation:
-- "cell smart table scan" : Smart Scan is active -- data offloaded to cell
-- "direct path read" : Direct path read active but offload NOT happening
-- (predicates not sent or cell resource shortage)
-- "cell multiblock physical read": Buffer cache read -- Smart Scan cannot activate
-- "db file scattered read" : Buffer cache scattered read -- Smart Scan bypassed
The key distinction: If you see direct path read wait events but NOT cell smart table scan, it means Oracle is using direct reads (the prerequisite for Smart Scan) but the predicates are not being sent to the cell. This is a different problem from cell multiblock physical read where the buffer cache is being used and Smart Scan is impossible. The fix differs for each.
The 10 Most Common Reasons Smart Scan Does Not Activate
Why it happens
For serial queries, Oracle makes a dynamic runtime decision on whether to use direct path reads or buffer cache reads for each segment separately. If the segment is below approximately 5 times the _small_table_threshold value, Oracle uses buffer cache reads — and Smart Scan cannot activate. This is the single most common reason Smart Scan does not fire in development and UAT environments where tables are smaller than production.
How to diagnose
Look for cell multiblock physical read or db file scattered read wait events. Check the segment size versus buffer cache size.
Check segment size and buffer cache threshold
-- Check segment size
SELECT segment_name, segment_type,
ROUND(bytes / 1024 / 1024 / 1024, 2) AS size_gb
FROM dba_segments
WHERE owner = 'APPS'
AND segment_name = 'ORDERS';
-- Check _small_table_threshold (in blocks)
SELECT ksppstvl AS small_table_threshold_blocks
FROM x$ksppsv
WHERE indx = (
SELECT indx FROM x$ksppi
WHERE ksppinm = '_small_table_threshold'
);
-- Check buffer cache size
SELECT ROUND(current_size / 1024 / 1024 / 1024, 2) AS buffer_cache_gb
FROM v$buffer_pool_statistics
WHERE name = 'DEFAULT';
Fix
For the specific session or query, use parallel execution — parallel queries always use direct path reads. For a one-off fix during testing, set _serial_direct_read = ALWAYS at session level — but only with Oracle Support guidance for production use. The real fix for production is to run analytics queries with parallel hints.
Fix — use parallel execution to force direct path reads
-- Parallel query always uses direct path reads and qualifies for Smart Scan
SELECT /*+ PARALLEL(orders, 4) */
customer_name
FROM orders
WHERE amount > 200;
-- Or set degree of parallelism on the table
ALTER TABLE orders PARALLEL 4;
Why it happens
Smart Scan only activates for full table scans, fast full index scans, and fast full bitmap index scans. If the optimizer chooses an index range scan or index unique scan, it reads blocks through the buffer cache using conventional I/O — Smart Scan is never considered. For OLTP queries returning a small number of rows via an index, this is correct and expected behaviour. Smart Scan is not appropriate for single-row lookups.
How to diagnose
Check the execution plan for INDEX RANGE SCAN or INDEX UNIQUE SCAN operations instead of TABLE ACCESS FULL or TABLE ACCESS STORAGE FULL.
Diagnose — check if index is being used
-- Review execution plan operations
SELECT operation, options, object_name, object_type
FROM v$sql_plan
WHERE sql_id = '&sql_id'
ORDER BY id;
-- If you see INDEX RANGE SCAN, UNIQUE SCAN, or INDEX SKIP SCAN:
-- Smart Scan will not fire for those operations.
-- This is correct for OLTP queries.
-- For analytics that should scan, check why the index is chosen.
Fix
For analytic queries that should be scanning but are incorrectly using an index, use a NO_INDEX hint, gather fresh statistics, or review whether the index is truly appropriate for that query pattern.
Fix — suppress index use for analytic queries
-- Prevent index use and force full scan for Smart Scan eligibility
SELECT /*+ NO_INDEX(orders) PARALLEL(orders, 4) */
customer_name
FROM orders
WHERE amount > 200;
-- Gather fresh statistics if the plan is based on stale stats
EXEC DBMS_STATS.GATHER_TABLE_STATS('APPS', 'ORDERS',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
Why it happens
The database initialisation parameter CELL_OFFLOAD_PROCESSING controls whether Smart Scan processing is enabled. It defaults to TRUE on Exadata. If it has been explicitly set to FALSE at the instance, system, or session level — sometimes by a DBA during troubleshooting who forgot to reverse it — Smart Scan is disabled entirely for that scope.
How to diagnose
Check CELL_OFFLOAD_PROCESSING at all levels
-- Check at instance level
SHOW PARAMETER cell_offload_processing;
-- Check if overridden at session level (your session)
SELECT name, value, isdefault
FROM v$ses_optimizer_env
WHERE sid = SYS_CONTEXT('USERENV','SID')
AND name = 'cell_offload_processing';
-- Check system-level setting
SELECT name, value
FROM v$system_parameter
WHERE name = 'cell_offload_processing';
Fix
Fix — re-enable CELL_OFFLOAD_PROCESSING
-- Re-enable for current session
ALTER SESSION SET cell_offload_processing = TRUE;
-- Re-enable at system level (no restart needed)
ALTER SYSTEM SET cell_offload_processing = TRUE;
-- Re-enable at system level persistently
ALTER SYSTEM SET cell_offload_processing = TRUE SCOPE = BOTH;
Why it happens
Smart Scan requires the segment to be stored in an ASM disk group where cell.smart_scan_capable = TRUE. If a table is stored on a non-Exadata ASM disk group, a local filesystem, or a non-Exadata storage tier, cellsrv never receives the iDB request and Smart Scan cannot happen. This occurs in mixed environments where some storage is Exadata and some is not, or when a table was created in the wrong tablespace.
How to diagnose
Verify segment is on smart_scan_capable disk group
-- Check which tablespace the table is in
SELECT table_name, tablespace_name
FROM dba_tables
WHERE owner = 'APPS'
AND table_name = 'ORDERS';
-- Check if that tablespace's disk group is Smart Scan capable
SELECT dg.name AS disk_group,
a.name AS attribute,
a.value AS attr_value
FROM v$asm_diskgroup dg
JOIN v$asm_attribute a ON dg.group_number = a.group_number
WHERE a.name = 'cell.smart_scan_capable'
ORDER BY dg.name;
-- Result: cell.smart_scan_capable = TRUE = Smart Scan possible
-- cell.smart_scan_capable = FALSE = Smart Scan NOT possible
Fix
Move the table to a tablespace backed by a Smart Scan capable Exadata disk group.
Fix — move table to Exadata-capable tablespace
-- Move table to a tablespace on Exadata smart_scan_capable disk group
ALTER TABLE orders MOVE TABLESPACE exadata_data;
-- Rebuild indexes after table move (they become unusable)
ALTER INDEX orders_pk REBUILD TABLESPACE exadata_data;
-- Confirm new tablespace is on Smart Scan capable storage
SELECT t.tablespace_name, t.status
FROM dba_tablespaces t
WHERE t.tablespace_name = 'EXADATA_DATA';
Why it happens
Oracle Database shared server sessions do not use direct reads for serial queries. This is by design — direct reads require a dedicated connection between the session and the I/O subsystem that is not compatible with the shared server architecture. Without direct reads, Smart Scan cannot activate for serial queries in a shared server session. For parallel queries in shared server sessions, the parallel worker processes can use direct reads — but the query coordinator blocks the shared server for the entire duration.
How to diagnose
Check if session is using shared server
-- Check server type for active sessions
SELECT sid, username, server, status
FROM v$session
WHERE server = 'SHARED'
AND status = 'ACTIVE';
-- Check your own session server type
SELECT server
FROM v$session
WHERE sid = SYS_CONTEXT('USERENV', 'SID');
Fix
Ensure analytic reporting queries connect via dedicated server sessions, not shared server. Review the dispatchers and shared_servers parameters to confirm shared server is only used where required.
Why it happens
Not all SQL predicates and functions can be offloaded to the storage cell. Smart Scan can evaluate most simple predicates and common SQL functions at the cell. However, user-defined functions (UDFs), certain PL/SQL functions, some XML operations, and complex CASE expressions cannot be offloaded. When a non-offloadable predicate is present, Oracle may fall back to conventional I/O — or activate Smart Scan but with reduced filtering effectiveness.
How to diagnose
Check cell_offload_predicates in execution plan
-- Use EXPLAIN PLAN with full format to see predicate information
EXPLAIN PLAN FOR
SELECT customer_name
FROM orders
WHERE my_custom_function(amount) > 200;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));
-- Look for "Predicate Information" section in plan output
-- Offloadable predicates appear under "storage" filter:
-- storage("AMOUNT">200) = offloaded to cell
-- filter(MY_CUSTOM_FUNCTION("AMOUNT")>200) = NOT offloaded, stays at DB node
-- Also check V$SQL for low offload ratio despite full scan
SELECT sql_id,
io_cell_offload_eligible_bytes,
io_cell_offload_returned_bytes,
ROUND(io_cell_offload_returned_bytes /
NULLIF(io_cell_offload_eligible_bytes, 0) * 100, 1)
AS pct_returned
FROM v$sql
WHERE sql_id = '&sql_id';
Fix
Rewrite the predicate using native SQL operators that are offloadable. Replace UDF predicates with equivalent SQL expressions where possible.
Fix — replace non-offloadable predicate with native SQL
-- Non-offloadable: user-defined function in WHERE
-- SELECT ... WHERE my_udf(status) = 'ACTIVE'
-- Offloadable equivalent: native comparison
SELECT customer_name
FROM orders
WHERE status = 'ACTIVE';
-- Non-offloadable: complex CASE in predicate
-- WHERE CASE WHEN type = 'A' THEN val1 ELSE val2 END > 200
-- More offloadable: explicit conditions
SELECT customer_name
FROM orders
WHERE (type = 'A' AND val1 > 200)
OR (type != 'A' AND val2 > 200);
Why it happens
Objects assigned to the KEEP buffer cache pool (BUFFER_POOL KEEP) are explicitly cached in the buffer cache and will always be read from there — never using direct path reads and therefore never using Smart Scan. This is intentional for small OLTP lookup tables. The problem arises when a large table is incorrectly placed in the KEEP pool, preventing Smart Scan from activating for it.
How to diagnose
Check buffer pool assignment for tables
-- Check buffer pool assignment for a specific table
SELECT table_name, buffer_pool, cell_flash_cache
FROM dba_tables
WHERE owner = 'APPS'
AND table_name = 'ORDERS';
-- List all large tables in the KEEP pool (potential problem)
SELECT t.table_name,
t.buffer_pool,
ROUND(s.bytes / 1024 / 1024 / 1024, 2) AS size_gb
FROM dba_tables t
JOIN dba_segments s ON s.segment_name = t.table_name
AND s.owner = t.owner
WHERE t.owner = 'APPS'
AND t.buffer_pool = 'KEEP'
AND s.bytes > 1073741824 -- larger than 1 GB
ORDER BY s.bytes DESC;
Fix
Fix — remove KEEP pool assignment for large tables
-- Remove KEEP pool assignment -- table will use DEFAULT pool
-- and become eligible for Smart Scan and direct path reads
ALTER TABLE orders STORAGE (BUFFER_POOL DEFAULT);
-- Optionally set cell_flash_cache to DEFAULT as well
ALTER TABLE orders STORAGE (CELL_FLASH_CACHE DEFAULT);
Why it happens
By design, the SELECT portion of UPDATE and DELETE statements does not automatically use direct path reads or Smart Scan. When Oracle executes an UPDATE, it needs to maintain undo consistency and row-level locking — this requires reading blocks through the buffer cache rather than bypassing it. This is not a bug — it is a deliberate design decision. Smart Scan is for read-only analytic workloads.
How to diagnose
If you see a large UPDATE or DELETE not using Smart Scan, this is expected. Check the statement type in V$SQL.
Check statement type
-- Confirm statement type for a SQL_ID
SELECT sql_id, command_type, sql_text
FROM v$sql
WHERE sql_id = '&sql_id';
-- command_type = 2 : INSERT
-- command_type = 3 : SELECT (Smart Scan eligible)
-- command_type = 6 : UPDATE (Smart Scan for SELECT portion: by design NOT used)
-- command_type = 7 : DELETE (same as UPDATE)
-- For bulk updates: consider CTAS (Create Table As Select) pattern
-- Create new version of table with updates applied, then rename
Fix
For bulk modifications on large tables, use the Create Table As Select pattern instead of UPDATE — build a new version of the table with the changes applied via SELECT (which does use Smart Scan), then rename tables.
Why it happens
Even when all conditions for Smart Scan are met, the storage cell can fall back to conventional I/O if it is under resource pressure — particularly memory pressure caused by a large number of concurrent parallel queries consuming cell memory for Smart Scan processing. In this case, Oracle sends direct read requests but the cell cannot perform the offload and returns raw blocks instead. The wait event will show direct path read rather than cell smart table scan.
How to diagnose
Check cell CPU and memory pressure via dcli
# Check CPU utilisation on all cells
dcli -g /opt/oracle.SupportTools/onecommand/cell_group \
"cellcli -e LIST METRICCURRENT CL_CPUT"
# Check cell memory utilisation
dcli -g /opt/oracle.SupportTools/onecommand/cell_group \
"cellcli -e LIST METRICCURRENT CL_MEMUT"
# Check for any critical alerts on cells
dcli -g /opt/oracle.SupportTools/onecommand/cell_group \
"cellcli -e LIST METRICCURRENT WHERE alertState != 'normal'"
-- From the database: check wait event breakdown
SELECT event, COUNT(*) AS session_count
FROM v$session
WHERE status = 'ACTIVE'
AND event IN ('cell smart table scan',
'direct path read',
'cell multiblock physical read')
GROUP BY event;
Fix
Review the degree of parallelism settings for concurrent workloads. Use I/O Resource Management (IORM) to limit the parallelism of non-critical workloads and protect cell resources for critical Smart Scan operations.
Why it happens
Tables stored in tablespaces using Transparent Data Encryption (TDE) at the tablespace level cannot use Smart Scan — the storage cell cannot evaluate SQL predicates on encrypted data because it does not hold the encryption keys. The decryption must happen at the database server layer first, which means the data must be read through the conventional I/O path. Column-level TDE encryption has the same limitation for the affected columns.
How to diagnose
Check if tablespace or columns use TDE encryption
-- Check for encrypted tablespaces
SELECT tablespace_name, encrypted
FROM dba_tablespaces
WHERE encrypted = 'YES'
ORDER BY tablespace_name;
-- Check which tables are in encrypted tablespaces
SELECT t.table_name, t.tablespace_name
FROM dba_tables t
JOIN dba_tablespaces ts ON t.tablespace_name = ts.tablespace_name
WHERE t.owner = 'APPS'
AND ts.encrypted = 'YES'
ORDER BY t.table_name;
-- Check for column-level TDE encryption
SELECT table_name, column_name, encryption_alg
FROM dba_encrypted_columns
WHERE owner = 'APPS'
ORDER BY table_name, column_name;
Fix
This is a fundamental architectural trade-off. If Smart Scan performance is critical, TDE tablespace encryption cannot be used for those tables. Evaluate whether column-level encryption of only truly sensitive columns (rather than tablespace-level encryption) can satisfy the security requirement while allowing Smart Scan on non-encrypted columns.
Diagnosis Decision Tree — Which Reason Is It?
| Wait Event / Plan Symptom |
Likely Cause |
Reason # |
cell multiblock physical read or db file scattered read |
Buffer cache read path — not direct path. Serial query on small segment or KEEP pool. |
#1, #7 |
direct path read but NOT cell smart table scan |
Direct reads active but offload not happening. Cell resource pressure or CELL_OFFLOAD_PROCESSING=FALSE. |
#3, #9 |
| Plan shows INDEX RANGE SCAN or INDEX UNIQUE SCAN |
Index used instead of full scan — Smart Scan not applicable. |
#2 |
| Plan shows TABLE ACCESS FULL (not STORAGE FULL) |
Full scan without Smart Scan. Check segment storage, encryption, shared server. |
#4, #5, #10 |
| Smart Scan active but pct_io_saved near zero |
Smart Scan running but predicates not offloadable or not selective. |
#6 |
| Smart Scan works for SELECT but not UPDATE/DELETE |
By design — DML statements do not use Smart Scan for the scan portion. |
#8 |
Complete Smart Scan Diagnosis Script
Run this script with your SQL_ID to get a complete diagnosis picture for any query not using Smart Scan.
Smart Scan diagnosis script — replace &sql_id with your value
-- ============================================
-- SMART SCAN DIAGNOSIS SCRIPT
-- Replace &sql_id with your target SQL_ID
-- ============================================
PROMPT === 1. EXECUTION PLAN - CHECK FOR STORAGE KEYWORD ===
SELECT id, lpad(' ', depth) || operation || ' ' || options AS operation,
object_name, object_type
FROM v$sql_plan
WHERE sql_id = '&sql_id'
ORDER BY id;
PROMPT === 2. SMART SCAN STATISTICS FOR THIS SQL ===
SELECT sql_id, executions,
command_type,
ROUND(io_cell_offload_eligible_bytes / 1073741824, 3) AS eligible_gb,
ROUND(io_cell_offload_returned_bytes / 1073741824, 3) AS returned_gb,
ROUND((1 - io_cell_offload_returned_bytes /
NULLIF(io_cell_offload_eligible_bytes,0))*100,1) AS pct_saved
FROM v$sql
WHERE sql_id = '&sql_id';
PROMPT === 3. CHECK CELL_OFFLOAD_PROCESSING PARAMETER ===
SELECT name, value
FROM v$system_parameter
WHERE name = 'cell_offload_processing';
PROMPT === 4. CHECK TABLE STORAGE PROPERTIES ===
SELECT t.table_name,
t.tablespace_name,
t.buffer_pool,
t.cell_flash_cache,
ts.encrypted,
ROUND(s.bytes / 1024 / 1024 / 1024, 2) AS size_gb
FROM v$sql_plan p
JOIN dba_tables t ON t.table_name = p.object_name
JOIN dba_tablespaces ts ON t.tablespace_name = ts.tablespace_name
JOIN dba_segments s ON s.segment_name = t.table_name
AND s.owner = t.owner
WHERE p.sql_id = '&sql_id'
AND p.object_type LIKE 'TABLE%';
PROMPT === 5. CHECK DISK GROUP SMART SCAN CAPABILITY ===
SELECT dg.name AS disk_group, a.value AS smart_scan_capable
FROM v$asm_diskgroup dg
JOIN v$asm_attribute a ON dg.group_number = a.group_number
WHERE a.name = 'cell.smart_scan_capable';
PROMPT === 6. CURRENT CELL WAIT EVENTS ===
SELECT event, COUNT(*) AS cnt
FROM v$session
WHERE status = 'ACTIVE'
AND wait_class != 'Idle'
AND event LIKE 'cell%'
GROUP BY event
ORDER BY cnt DESC;
PROMPT === DIAGNOSIS COMPLETE ===
Summary — Quick Reference for Smart Scan Diagnosis
| # |
Reason |
Quick Fix |
| 1 |
Serial query — segment too small for direct path read |
Use parallel execution for the query |
| 2 |
Index used instead of full scan |
For analytics: NO_INDEX hint + PARALLEL hint |
| 3 |
CELL_OFFLOAD_PROCESSING = FALSE |
ALTER SYSTEM SET cell_offload_processing = TRUE |
| 4 |
Segment not on smart_scan_capable disk group |
ALTER TABLE ... MOVE TABLESPACE to Exadata disk group |
| 5 |
Shared server session — serial direct reads not supported |
Use dedicated server connection for analytics |
| 6 |
Non-offloadable predicate or UDF in WHERE clause |
Rewrite using native SQL operators |
| 7 |
Large table in KEEP buffer pool |
ALTER TABLE ... STORAGE (BUFFER_POOL DEFAULT) |
| 8 |
UPDATE or DELETE statement — by design |
Use CTAS pattern for bulk modifications |
| 9 |
Cell memory/CPU resource shortage |
Reduce parallel degree of competing workloads, use IORM |
| 10 |
TDE tablespace or column encryption |
Use column-level encryption only on sensitive columns |
No comments:
Post a Comment