On Exadata, an execution plan containing a full table scan does not prove that Smart Scan will occur. It only identifies an access path that may be eligible. At runtime, the database must use direct path reads, the storage cells must accept the offloaded work, and the predicates and data state must permit useful filtering.<\/p>\n
This distinction matters because engineers often diagnose Exadata performance from the optimizer plan alone. A plan can remain unchanged while elapsed time, database-node CPU, interconnect traffic, and storage-cell CPU move dramatically. The real questions are whether the scan was eligible, how many bytes were processed by the cells, how many were eliminated, and why any eligible work was returned without filtering.<\/p>\n
Smart Scan can offload predicate evaluation, column projection, decompression, selected functions, and other operations. Current Exadata software also expands support for newer Oracle Database 23ai data types and operators. Compatibility still depends on the exact database release, release update, and Exadata System Software version, so feature support should be validated against the deployed stack rather than inferred from the marketing name.<\/p>\n
Smart Scan normally applies to full table scans, index fast full scans, and similar operations using direct path reads. If Oracle reads blocks into the buffer cache, the cells cannot perform Smart Scan for that operation. Small segments, selective indexed access, and serial scans chosen for buffered I\/O are therefore legitimate cases where offload does not occur.<\/p>\n
Wait events provide an initial classification:<\/p>\n
cell smart table scan<\/code> or a related smart scan event indicates that Smart Scan requests are being issued.<\/li>\ncell multiblock physical read<\/code> usually indicates buffered multiblock I\/O rather than Smart Scan.<\/li>\ndirect path read<\/code> on Exadata can indicate direct I\/O for which predicate processing was not successfully offloaded.<\/li>\n<\/ul>\nDo not force parallel execution merely to make a dashboard show Smart Scan. Parallelism can enable direct reads, but it also consumes database CPU, parallel execution processes, cell CPU, interconnect capacity, and I\/O bandwidth. A small buffered scan may be cheaper than coordinating a parallel Smart Scan. The engineering objective is workload throughput and latency, not offload eligibility in isolation.<\/p>\n
Measure the Byte Flow for the SQL Statement<\/h2>\n
The most useful model follows bytes through the execution. Eligible bytes describe data that could be processed by the cells. Returned bytes describe data sent back after Smart Scan. Interconnect bytes include broader database-to-cell traffic and should not automatically be treated as filtered output.<\/p>\n
Capture statement-level counters from V$SQL<\/code>. Use a known SQL ID rather than matching SQL text in a busy system:<\/p>\nSELECT inst_id,\n child_number,\n plan_hash_value,\n executions,\n ROUND(io_cell_offload_eligible_bytes \/ POWER(1024,3), 2) AS eligible_gb,\n ROUND(io_cell_offload_returned_bytes \/ POWER(1024,3), 2) AS returned_gb,\n ROUND(io_interconnect_bytes \/ POWER(1024,3), 2) AS interconnect_gb,\n ROUND(io_cell_uncompressed_bytes \/ POWER(1024,3), 2) AS uncompressed_gb,\n ROUND(physical_read_bytes \/ POWER(1024,3), 2) AS physical_read_gb\nFROM gv$sql\nWHERE sql_id = :sql_id\nORDER BY inst_id, child_number;<\/code><\/pre>\nThese values are cumulative for each child cursor. Take deltas around a controlled execution or correlate them with execution counts. Aggregating old and new child cursors can hide a regression caused by a plan change, bind-sensitive cursor, service relocation, or instance-specific configuration.<\/p>\n
A useful filtering ratio can be calculated as:<\/p>\n
offload_reduction_pct =\n 100 * (eligible_bytes - returned_bytes) \/ eligible_bytes<\/code><\/pre>\nThis is not a universal performance score. A query returning most rows may offload correctly while achieving little reduction. Conversely, storage indexes may prevent physical reads before predicate filtering, making simple comparisons between on-disk bytes and returned bytes incomplete. Hybrid Columnar Compression and columnar cache also mean that eligible, physical, and uncompressed byte counts represent different stages of processing.<\/p>\n
Use SQL Monitor to Find the Responsible Row Source<\/h2>\n
Statement totals are insufficient when a plan scans several large objects. A SQL Monitor report exposes Smart I\/O metrics by row source, including eligible bytes, bytes filtered, storage-index savings, columnar-cache savings, and passthrough bytes. This identifies whether one table scan is efficient while another dominates interconnect traffic.<\/p>\n
For a reproducible diagnostic execution, attach a unique monitor tag:<\/p>\n
SELECT \/*+ MONITOR GATHER_PLAN_STATISTICS *\/\n customer_region, SUM(net_amount)\nFROM sales_fact\nWHERE sale_ts >= TIMESTAMP '2026-08-01 00:00:00'\nAND status = 'SETTLED'\nGROUP BY customer_region;<\/code><\/pre>\nThen retrieve the report while the cursor is still available:<\/p>\n
SELECT DBMS_SQLTUNE.report_sql_monitor(\n sql_id => :sql_id,\n type => 'TEXT',\n report_level => 'ALL')\nFROM dual;<\/code><\/pre>\nValidate the actual row counts as well as the I\/O counters. Cardinality errors can select an inappropriate join order or excessive degree of parallelism, increasing cell work even when every scan is successfully offloaded.<\/p>\n
Diagnose Passthrough and Reverse Offload<\/h2>\n
Passthrough occurs when data eligible for Smart I\/O is returned for processing by the database nodes. Causes include unsupported expressions, cell memory pressure, quarantined operations, software compatibility conditions, and large amounts of uncommitted data. Uncommitted rows require database-side processing because storage cells do not independently own the complete transactional visibility state.<\/p>\n
Reverse offload is different. When storage-cell CPU is heavily loaded, cells may send work back to database nodes to protect storage throughput. The query still has an offload-capable plan, but database CPU and interconnect traffic rise. Expensive predicates such as regular expressions and case-insensitive transformations can intensify this behavior.<\/p>\n
Inspect database statistics as deltas over the affected interval:<\/p>\n
SELECT name, value\nFROM v$sysstat\nWHERE name IN (\n 'cell physical IO bytes eligible for predicate offload',\n 'cell physical IO bytes eligible for smart IOs',\n 'cell physical IO interconnect bytes returned by smart scan',\n 'cell physical IO bytes saved by storage index',\n 'cell physical IO bytes sent directly to DB node to balance CPU usage',\n 'cell num bytes in passthru during predicate offload',\n 'cell num bytes in filter passthru due to low mem'\n)\nORDER BY name;<\/code><\/pre>\nStatistic names vary across software generations, so query V$STATNAME<\/code> on the target release before embedding them in automation. AWR Smart I\/O sections provide interval deltas and expose passthrough and reverse-offload reasons. ExaWatcher should then be used to correlate cell CPU, memory pressure, I\/O latency, and CELLSRV behavior with the database interval.<\/p>\nAccount for Application and Transaction Behavior<\/h2>\n
An analytical query can regress because of a concurrent application deployment even when its SQL and plan do not change. Large transactions that modify a substantial portion of the scanned data can reduce predicate offload effectiveness until commit. Frequent bulk loads can also invalidate the assumption that an object observed during a quiet benchmark behaves identically during the production batch window.<\/p>\n
Application connection mode matters. Serial queries executed through shared server do not use direct reads and therefore cannot use Smart Scan in the usual way. Service placement also affects diagnosis in RAC: two services may execute the same SQL with different resource-manager plans, parallelism policies, consumer groups, or instance load.<\/p>\n
Predicate design deserves review, but avoid rewriting business logic solely to maximize offload. Confirm datatype conversions, character-set behavior, collation, and timestamp semantics before replacing an expression. A storage-offload improvement that changes result semantics is not a tuning success.<\/p>\n
Build Monitoring Around Rates and Attribution<\/h2>\n
System-wide cumulative counters are poor alerting signals. Monitor per-second deltas and relate them to database, consumer group, service, SQL ID, plan hash, and row source. At minimum, track eligible Smart I\/O bytes, returned bytes, storage-index savings, passthrough bytes by reason, reverse-offload bytes, cell CPU, cell memory pressure, interconnect throughput, database CPU, and parallel execution utilization.<\/p>\n
Alert on sustained behavioral changes rather than a fixed offload percentage. For example, a rise in returned-to-eligible bytes is actionable when accompanied by higher database CPU or elapsed time for a stable workload. The same ratio may be expected for a report that legitimately returns a broad projection.<\/p>\n
A Production Validation Sequence<\/h2>\n\n- Confirm the SQL ID, child cursor, plan hash, service, instance, and execution interval.<\/li>\n
- Verify that the relevant row source performed direct reads and emitted Smart Scan wait events.<\/li>\n
- Measure eligible, returned, interconnect, physical-read, and uncompressed byte deltas.<\/li>\n
- Use SQL Monitor to attribute Smart I\/O and passthrough to individual row sources.<\/li>\n
- Check AWR passthrough and reverse-offload reasons for the same interval.<\/li>\n
- Correlate cell CPU and memory with ExaWatcher rather than assuming a database-only cause.<\/li>\n
- Review concurrent DML, transaction size, parallelism, resource plans, and application connection mode.<\/li>\n
- Repeat under representative concurrency; an isolated execution cannot validate cell behavior at production load.<\/li>\n<\/ul>\n
Smart Scan engineering is ultimately capacity and workload engineering. Eligibility establishes what Exadata could do; runtime counters show what it actually did. Reliable diagnosis comes from reconciling the optimizer plan, direct-read path, byte flow, transactional state, and storage-cell resource pressure over the same execution interval.<\/p>\n","protected":false},"excerpt":{"rendered":"
A production-focused method for proving whether Exadata Smart Scan is occurring, measuring its effectiveness, and diagnosing passthrough, reverse offload, and misleading performance symptoms.<\/p>\n","protected":false},"author":1,"featured_media":3715,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31],"tags":[],"class_list":{"0":"post-3721","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-oracle"},"_links":{"self":[{"href":"https:\/\/www.keilord.com\/index.php\/wp-json\/wp\/v2\/posts\/3721","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.keilord.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.keilord.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.keilord.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.keilord.com\/index.php\/wp-json\/wp\/v2\/comments?post=3721"}],"version-history":[{"count":0,"href":"https:\/\/www.keilord.com\/index.php\/wp-json\/wp\/v2\/posts\/3721\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.keilord.com\/index.php\/wp-json\/wp\/v2\/media\/3715"}],"wp:attachment":[{"href":"https:\/\/www.keilord.com\/index.php\/wp-json\/wp\/v2\/media?parent=3721"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.keilord.com\/index.php\/wp-json\/wp\/v2\/categories?post=3721"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.keilord.com\/index.php\/wp-json\/wp\/v2\/tags?post=3721"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}