Series: Part 3 of 3

Part 3: How to Tune Oracle Database to Use Exadata Efficiently
A Practical DBA Guide

Introduction

Series: Exadata Investigation & Performance Tuning — Part 3 of 3

Read Part 1: Exadata Health Check and
Part 2: Exadata Performance Investigation
for the platform checks and investigation steps that precede this article.

Exadata can make an inefficient execution plan run faster, but it cannot make unnecessary work disappear. A query that fetches millions of rows through an unsuitable index, scans partitions it does not need, or spills a large hash join to TEMP can remain slow on healthy hardware. This final part turns the evidence from Part 2 into targeted changes and repeatable validation.

Scope: SQL examples target Oracle Database 19c and later on Exadata. Run them in the relevant database/PDB with appropriate catalog privileges. Features and statistic names depend on the database release, RU, Exadata System Software and hardware. AWR/ASH and Real-Time SQL Monitoring examples require the applicable Oracle management-pack entitlements; use permitted alternatives where necessary.

Part 1 asked:

Is the Exadata infrastructure healthy?

Part 2 asked:

Where did the database time go?

Part 3 asks the practical DBA question:

How do we tune the database and SQL so Exadata can do the work it was designed to do?

This does not mean forcing every SQL statement to use Smart Scan. For large analytical scans, combine the following opportunities:

Prune unnecessary partitions; use eligible direct-path scans; reduce I/O and returned data

For selective OLTP access:

Index Lookup → Buffer Cache/XRMEM/Flash → Low Single-Block Latency

These are complementary mechanisms, not a literal pipeline: HCC changes how data is stored, Storage Indexes can eliminate regions, and Smart Scan can filter/project eligible data at the cells. Good Exadata tuning means choosing the right path for the workload.

Choose the tuning target before changing a parameter

Measurement rule: Use counters from the same interval. Most system/session counters and segment statistics are cumulative; cached SQL statistics can cover many executions. A large lifetime total is a candidate signal, not the cost of one execution. In RAC, account for all participating instances and PX workers; in a CDB, retain container identity. A negative delta after a restart or cursor replacement is invalid and must be discarded.

1. First Principle — Do Not “Tune for Exadata” Blindly

A common mistake is:

“It is Exadata, therefore full table scan is always better.”

That is wrong. Another common mistake is:

“Indexes are always faster.”

That is also wrong. The right question is:

How much data does this SQL need, and which access path processes it with the least total work?

A useful starting model is:

• Few rows → Index/single-block path
• Many rows → Scan/direct-path/Smart Scan may become better

There is no universal row-percentage rule. The crossover depends on:

• Table size
• Predicate selectivity
• Clustering factor
• Columns selected
• Partition pruning
• Compression
• Buffer-cache residency
• Flash/XRMEM behavior
• Degree of parallelism
• Storage Index effectiveness
• DB-node CPU
• Cell CPU
• Concurrency

2. Understand the Smart Scan Requirements

Smart Scan optimizes eligible scan operations that use the direct-read path. Common candidates include:

Full table scans
Fast full index scans
Fast full bitmap index scans
Parallel scans
Large sequential serial scans that use direct reads

The important relationship is:

Scan operation + Direct Path Read → Smart Scan can become possible

If blocks are read into the database buffer cache instead, you may see:

cell multiblock physical read

If direct reads occur but storage offload does not, you may see:

direct path read

Do not immediately reach for a hidden parameter because one SQL does not Smart Scan. First prove why the current I/O path was selected.

3. Is There a Table-Size Threshold for Smart Scan?

This question is often oversimplified. The practical answer is:

There is no single supported “table must be X GB” rule that a DBA should use as a universal Smart Scan threshold.

For relatively small segments, Oracle commonly favors buffered reads. For larger scans, direct reads become more likely.

The actual behavior also depends on the access path and runtime conditions. Avoid rules such as:

• Table > 1 GB → Smart Scan
• Table < 1 GB → No Smart Scan

Instead, check the actual execution. Useful wait-path clues are:

• cell multiblock physical read → buffered scan
• cell smart table scan → Smart Scan path
• direct path read → direct reads; investigate why predicate offload is absent

Then correlate those waits with Exadata offload statistics.

Check Segment Size

SELECT owner,
       segment_name,
       segment_type,
       ROUND(bytes/1024/1024/1024,2) size_gb
FROM dba_segments
WHERE owner = UPPER('&OWNER')
  AND segment_name = UPPER('&SEGMENT_NAME');

For a partitioned table:

SELECT segment_name,
       partition_name,
       ROUND(bytes/1024/1024/1024,2) size_gb
FROM dba_segments
WHERE owner = UPPER('&OWNER')
  AND segment_name = UPPER('&TABLE_NAME')
ORDER BY bytes DESC;

The important comparison is not only table size. It is:

Bytes actually scanned ↔ Buffer-cache behavior ↔ Direct-read behavior ↔ Rows/columns returned

4. Database Buffer Cache — Bigger Is Not Automatically Better

On Exadata, buffer-cache sizing is workload-dependent. For OLTP, the buffer cache remains extremely important.

For very large scans, however, we usually do not want huge scan working sets competing with hot OLTP blocks if direct-path Smart Scan is the better execution path. A useful mental model is:

• Hot OLTP blocks → Database Buffer Cache
• Large scans → Direct Path → Smart Scan
• Repeated physical random reads → XRMEM/Flash Cache

Do not shrink the cache simply to force Smart Scan. Do not increase it simply because free memory exists.

Size it for the real workload.

Check the Buffer Cache

SELECT name,
       value
FROM v$parameter
WHERE name IN ('db_cache_size','sga_target','memory_target');

If ASMM is in use:

SELECT component,
       current_size/1024/1024 current_mb,
       min_size/1024/1024 min_mb,
       max_size/1024/1024 max_mb
FROM v$sga_dynamic_components
WHERE component LIKE '%buffer cache%';

Unit check: V$DB_CACHE_ADVICE.SIZE_FOR_ESTIMATE is already in MB. Do not divide it by 1024 twice. Filter to an active advisory and compare a representative workload period. Use it as one input:

SELECT size_for_estimate cache_mb,
       size_factor,
       estd_physical_read_factor,
       estd_physical_reads
FROM v$db_cache_advice
WHERE name = 'DEFAULT'
  AND advice_status = 'ON'
  AND block_size = (SELECT TO_NUMBER(value)
                    FROM v$parameter
                    WHERE name = 'db_block_size')
ORDER BY size_for_estimate;

The Exadata question is not only:

“Can I reduce physical reads?”

It is also:

Which reads should be cached, and which large scans should bypass the cache and be processed by Smart Scan?

5. Detect a Buffered Scan That Is Missing Smart Scan

Suppose the plan shows a full scan but Smart Scan does not occur. If the session waits on:

cell multiblock physical read

the scan is using buffered I/O. If the segment is small, that may be expected.

If the object is large and performance is poor, investigate:

• Segment size
• Serial versus parallel execution
• Buffer-cache residency
• Shared-server usage
• Plan shape
• Direct-read behavior

Then compare Exadata statistics before and after the SQL. The following session counters isolate the current session for a serial test; they do not aggregate remote PX worker activity. Preserve the before values and subtract them from the after values.

SELECT n.name,
       m.value
FROM v$mystat m
JOIN v$statname n
  ON n.statistic# = m.statistic#
WHERE n.name IN (
    'physical read total bytes',
    'cell physical IO bytes eligible for predicate offload',
    'cell physical IO interconnect bytes returned by smart scan'
)
ORDER BY n.name;

Prove the I/O path rather than assuming it from plan text.

6. PGA — Exadata Does Not Eliminate SQL Work Areas

Smart Scan can make a scan extremely fast, but the SQL may still become slow after the data reaches the database server. Examples include:

HASH JOIN | SORT | GROUP BY | Bitmap operations | Analytic/window operations

These operations use PGA work areas. If PGA is insufficient, they may spill to TEMP.

The path becomes:

Fast Smart Scan → Data returned → Hash/Sort too large → TEMP spill → Query slow

This is why PGA is part of Exadata performance tuning.

Check PGA Health

SELECT name,
       value
FROM v$pgastat
WHERE name IN (
    'aggregate PGA target parameter',
    'total PGA allocated',
    'total PGA inuse',
    'over allocation count',
    'cache hit percentage',
    'extra bytes read/written'
)
ORDER BY name;

Check the advisor:

SELECT ROUND(pga_target_for_estimate/1024/1024/1024,2) target_gb,
       pga_target_factor,
       estd_pga_cache_hit_percentage,
       estd_overalloc_count
FROM v$pga_target_advice
ORDER BY pga_target_for_estimate;

Compare over allocation count and extra bytes read/written as interval deltas. They are cumulative counters, while cache hit percentage is a percentage and allocated/in-use values are bytes. A historic over-allocation count alone does not prove current pressure. An optimal execution avoids a workarea spill; one-pass and multipass executions need extra disk work, with multipass generally more expensive. Do not raise PGA_AGGREGATE_TARGET without considering:

• Physical memory
• SGA
• Concurrent sessions
• Parallel execution
• PGA_AGGREGATE_LIMIT
• RAC node balance
• OS memory pressure

7. Find SQL Spilling to TEMP

During a live problem, identify large TEMP consumers using the views available in your release. A practical starting point is GV$TEMPSEG_USAGE together with GV$SESSION.

For example:

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       u.tablespace,
       u.segtype,
       u.blocks
FROM gv$session s
JOIN gv$tempseg_usage u
  ON u.inst_id = s.inst_id
 AND u.session_addr = s.saddr
WHERE s.status = 'ACTIVE'
ORDER BY u.blocks DESC;

Then determine whether the work area is sorting, hashing, or performing another TEMP-heavy operation. The tuning question is:

Is the scan slow, or is processing after the scan spilling to TEMP?

Identify the spilling operator, including PX workers

TEMP allocated to a session is a clue; it does not identify the slowest operator or the total I/O performed. Check active workareas while the SQL is running:

SELECT inst_id, sid, qcinst_id, qcsid, sql_id,
       operation_id, operation_type, number_passes,
       ROUND(actual_mem_used/1024/1024,1) memory_mb,
       ROUND(tempseg_size/1024/1024,1) temp_mb
FROM gv$sql_workarea_active
WHERE sql_id = '&SQL_ID'
ORDER BY tempseg_size DESC NULLS LAST;

TEMPSEG_SIZE is allocated bytes for that active workarea, not cumulative TEMP I/O. NUMBER_PASSES = 0 means optimal execution. Correlate OPERATION_ID with the plan. PX slaves can allocate workareas on other RAC instances, so include the coordinator and its workers. The view is transient: no rows after completion does not prove that the statement never spilled. First reduce rows and row width entering joins/sorts where semantics allow. Then evaluate join order, estimates and DOP. More PGA can help a memory-limited workload, but it does not repair unnecessary joins or unlimited concurrent PX.

8. How an Index Can Prevent Smart Scan

An ordinary index range scan commonly drives single-block rowid access. Example:

INDEX RANGE SCAN → TABLE ACCESS BY INDEX ROWID

For selective SQL, that is exactly what we want. But suppose the predicate now returns millions of rows.

The optimizer may still choose the index because of:

• Incorrect cardinality estimates
• Stale statistics
• Data skew
• Correlated predicates
• Historical plan stability
• Poor clustering factor not fully reflected in the expected cost

The SQL may then perform:

Millions of index probes → Millions of ROWID visits → Huge single-block workload

while a scan could potentially perform:

Scan once → Smart Scan → Filter at cells → Return needed rows

This is one of the most important Exadata tuning cases.

9. Find SQL Using Index Access With No Offload

Start with the large-read/no-offload candidates from Part 2. Then inspect the current plan:

SELECT inst_id,
       sql_id,
       plan_hash_value,
       child_number,
       id,
       parent_id,
       operation,
       options,
       object_owner,
       object_name
FROM gv$sql_plan
WHERE sql_id = '&SQL_ID'
ORDER BY inst_id, child_number, id;

Look for:

INDEX RANGE SCAN
INDEX UNIQUE SCAN
TABLE ACCESS BY INDEX ROWID
TABLE ACCESS BY INDEX ROWID BATCHED

For a historical SQL:

SELECT DISTINCT
       sql_id,
       plan_hash_value,
       id,
       operation,
       options,
       object_owner,
       object_name
FROM dba_hist_sql_plan
WHERE sql_id = '&SQL_ID'
ORDER BY plan_hash_value, id;

Combine that plan evidence with physical reads and offload statistics.

10. Find Candidates Where an Index May Be Too Expensive

A useful candidate pattern is:

Large physical reads
+ Index/ROWID plan
+ Low or zero Smart Scan eligibility
+ Many rows returned
+ High elapsed or User I/O time

Do not automatically force a full scan. Investigate the index:

SELECT owner,
       index_name,
       table_name,
       blevel,
       leaf_blocks,
       distinct_keys,
       clustering_factor,
       num_rows
FROM dba_indexes
WHERE table_owner = UPPER('&OWNER')
  AND table_name = UPPER('&TABLE_NAME')
ORDER BY index_name;

Compare with the table:

SELECT owner,
       table_name,
       num_rows,
       blocks,
       avg_row_len
FROM dba_tables
WHERE owner = UPPER('&OWNER')
  AND table_name = UPPER('&TABLE_NAME');

11. Selectivity, Cardinality, and Clustering Factor

These concepts are central to access-path decisions.

Selectivity

Selectivity asks:

What fraction of the table does the predicate return?

Conceptually:

Selectivity = matching rows / total rows

A predicate returning 10 rows from 100 million is highly selective.

Cardinality

Cardinality is the number of rows Oracle estimates or actually observes at a row source. Example:

Estimated rows = 100 | Actual rows = 5,000,000

That error can produce a poor index plan.

Clustering factor

Clustering factor reflects how well table-row physical order follows index-key order. Compare clustering factor with table blocks and row count: a value near table blocks suggests more clustered access; a value closer to row count suggests more scattered access. This is a costing clue, not an absolute quality score, and its relevance depends on the indexed key range and access pattern.

On Exadata, a huge number of random single-block reads can lose badly to a well-offloaded scan.

12. Do Not Use a Fixed “Percentage of Table” Rule

Rules such as:

• More than 5% → Full scan
• Less than 5% → Index

are not universal. The real comparison is closer to:

Index path = Index blocks + Table blocks by ROWID + Random-read latency + RAC effects
Scan path  = Bytes scanned - Partition pruning - Storage Index savings - Smart Scan filtering + Parallelism effects

Exadata can move the crossover because large scans can be processed efficiently at the storage cells. Test representative executions.

13. Check Optimizer Statistics Before Blaming Exadata

Check table statistics:

SELECT owner,
       table_name,
       num_rows,
       blocks,
       avg_row_len,
       sample_size,
       last_analyzed,
       stale_stats
FROM dba_tab_statistics
WHERE owner = UPPER('&OWNER')
  AND table_name = UPPER('&TABLE_NAME');

Column statistics:

SELECT column_name,
       num_distinct,
       density,
       num_nulls,
       histogram,
       last_analyzed
FROM dba_tab_col_statistics
WHERE owner = UPPER('&OWNER')
  AND table_name = UPPER('&TABLE_NAME')
ORDER BY column_name;

Pay attention to:

• Skewed columns
• Correlated predicates
• Stale statistics
• Partition statistics
• Global statistics
• Histograms that no longer represent the data

A plan should be changed only after understanding why Oracle chose it.

Compare index and scan plans in a controlled test

Use a representative SQL, binds and data volume. The following is an illustrative lab test for a table alias s; substitute your real statement and index. Fetch the result to completion, then inspect that exact cursor immediately. COUNT(*) below is a convenient lab workload; it is not a substitute for the application’s projections, joins or fetch behavior.

-- Test A: index candidate
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(s SALES_REGION_IX) NO_PARALLEL(s) */
       /* exadata_part3_index_test */ COUNT(*)
FROM app.sales s
WHERE s.region_id = :region_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  NULL, NULL, 'ALLSTATS LAST +IOSTATS +MEMSTATS +PARTITION +PREDICATE'));
-- Test B: scan candidate, at the same serial execution setting
SELECT /*+ GATHER_PLAN_STATISTICS FULL(s) NO_PARALLEL(s) */
       /* exadata_part3_scan_test */ COUNT(*)
FROM app.sales s
WHERE s.region_id = :region_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  NULL, NULL, 'ALLSTATS LAST +IOSTATS +MEMSTATS +PARTITION +PREDICATE'));

Hints are a test instrument, not a guarantee of the plan or of Smart Scan. Verify the actual access path and runtime evidence. Compare E-Rows with A-Rows in context: for repeatedly started row sources, consider Starts rather than comparing totals blindly. If A-Rows is missing, row-source statistics were not captured for that execution.

Repeat in alternating order to expose cache warming. Keep bind values, fetch behavior, concurrency and DOP comparable. Record elapsed time, buffer gets, read bytes, CPU and TEMP work. If a scan wins for one high-volume bind, also test selective binds before changing the production plan. Deploy an approved SQL/statistics/plan-management change and retain a rollback path.

For statistics corrections, prefer targeted collection and representative testing. Histograms can help skewed predicates; column-group statistics can help correlated predicates. Do not gather every histogram or regather the whole database merely because one estimate is wrong. Check locked/pending statistics and existing SQL plan baselines before assuming a new statistics set will change the plan.

14. HCC — Which Tables Are Good Candidates?

Hybrid Columnar Compression is a major Exadata feature for scan-heavy data. Strong candidates are often:

Large + Read-mostly + Historical + Scan-heavy + Low update frequency

Examples:

• Historical fact tables
• Closed monthly partitions
• Archive data
• Reporting tables
• Large warehouse objects

Poor candidates are often:

Hot OLTP + Frequent row-by-row updates + Heavy single-row DML

A common design is:

• Current partition → DML-friendly storage/compression
• Older closed partitions → HCC

This keeps active data flexible while historical data gets compression and scan benefits.

15. Find Large Objects for HCC Review

Start with the largest table segments:

SELECT owner,
       segment_name,
       partition_name,
       segment_type,
       ROUND(bytes/1024/1024/1024,2) size_gb
FROM dba_segments
WHERE segment_type LIKE 'TABLE%'
  AND bytes > 10*1024*1024*1024
ORDER BY bytes DESC;

Then correlate size with:

• DML frequency
• AWR segment reads
• Full-scan workload
• Partition age
• Business retention policy

Do not select HCC candidates from size alone. A 500 GB hot OLTP table can be a worse candidate than a 50 GB read-only historical partition.

16. Test Compression Before Converting

DBMS_COMPRESSION.GET_COMPRESSION_RATIO can help estimate compressibility. The exact procedure signature and supported compression types depend on Oracle Database release and options. A compression attribute alone does not guarantee that all existing data has been rewritten into that format. Plan the appropriate load/move/redefinition operation, space, index maintenance and availability requirements before conversion. Compare QUERY LOW/HIGH and ARCHIVE LOW/HIGH against the workload rather than choosing the highest ratio automatically.

Use this process:

Candidate → Estimate ratio → Test representative data → Test representative workload → Convert → Validate

Do not measure only disk savings. Also measure:

• Scan elapsed time
• Cell CPU
• DB CPU
• Physical bytes
• Offload
• DML impact
• Backup/restore impact
• Move/rebuild duration

17. Partitioning Can Make Smart Scan Much More Powerful

A full scan of the correct partition can be excellent. A full scan of the entire table because pruning failed can be disastrous.

Desired path:

Predicate → Partition pruning → Smaller scan → Smart Scan → Offload

Inspect plans:

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        sql_id => '&SQL_ID',
        format => 'ALLSTATS LAST +PARTITION +PREDICATE +IOSTATS'
    )
);

Review:

• Pstart
• Pstop
• Partition range operations
• Actual rows
• Predicates

If a query should access one month but scans twelve, fix pruning before changing Exadata settings. For a DATE partition key, a half-open interval is often a clear way to express the intended range:

WHERE sale_date >= DATE '2026-09-01'
  AND sale_date <  DATE '2026-10-01'

Match bind datatypes to the partition key and avoid unintended implicit conversions. A function on a partition column does not universally disable pruning, but verify the actual plan. Pstart/Pstop = KEY may indicate dynamic pruning; it is not proof that pruning failed.

18. Storage Indexes — No Traditional Index to Build

Exadata Storage Indexes are maintained automatically by the storage cells. They summarize data regions and can avoid reading regions that cannot contain matching values.

Useful statistic:

SELECT name,
       value
FROM v$sysstat
WHERE name = 'cell physical IO bytes saved by storage index';

Conceptually:

Predicate → Storage Index proves region cannot match → Physical I/O avoided

The DBA does not create a traditional Storage Index with DDL. However, data organization matters.

When similar values are physically clustered, region-level min/max elimination can become more effective.

19. Check Whether Storage Indexes Are Helping

For a session-level test:

SELECT n.name,
       m.value
FROM v$mystat m
JOIN v$statname n
  ON n.statistic# = m.statistic#
WHERE n.name = 'cell physical IO bytes saved by storage index';

Capture it before and after the representative SQL. Do not expect every predicate to produce savings.

Effectiveness depends on predicate and data distribution. Compare first and repeated executions: summaries may need to be built or rebuilt. Do not reorganize a table solely for Storage Index savings; ordered data can improve elimination but also change load cost, DML behavior and index access. Treat ordering as a physical-design experiment with workload-wide validation.

20. Smart Flash Cache — Let Exadata Manage It First

Smart Flash Cache automatically caches useful data. The default should normally be the starting point.

It is particularly useful for:

Repeated physical reads + Random access + Working set larger than DB buffer cache

Do not mark every important object as KEEP. Overusing KEEP can reduce space available for other hot objects.

21. Find Candidates for CELL_FLASH_CACHE KEEP

Oracle exposes segment-level optimized-read statistics. Compare interval deltas for physical reads and optimized physical reads. Optimized reads are not a universal Flash Cache hit ratio; the optimized path can include other Exadata mechanisms. Correlate with cell flash metrics and the target SQL:

SELECT owner,
       object_name,
       subobject_name,
       object_type,
       con_id,
       SUM(CASE WHEN statistic_name = 'physical reads'
                THEN value ELSE 0 END) physical_reads,
       SUM(CASE WHEN statistic_name = 'optimized physical reads'
                THEN value ELSE 0 END) optimized_reads
FROM gv$segment_statistics
WHERE statistic_name IN ('physical reads','optimized physical reads')
GROUP BY owner, object_name, subobject_name, object_type, con_id
HAVING SUM(CASE WHEN statistic_name = 'physical reads'
                THEN value ELSE 0 END) > 100000
ORDER BY physical_reads DESC;

Candidates worth reviewing are objects with:

High repeated physical reads + Low optimized reads + Latency-sensitive workload

Then ask:

• Is the object repeatedly reused?
• Is the workload random/single-block?
• Does the object fit reasonably in Flash?
• Is it already mostly in the DB buffer cache?
• Would prioritizing it evict more useful data?
• Is IORM controlling Flash allocation?

22. Use CELL_FLASH_CACHE KEEP Carefully

For a table:

ALTER TABLE owner.table_name
STORAGE (CELL_FLASH_CACHE KEEP);

For an index:

ALTER INDEX owner.index_name
STORAGE (CELL_FLASH_CACHE KEEP);

Treat KEEP as an exception. Use:

Candidate → Baseline → Apply KEEP narrowly → Measure residency/latency → Keep or revert

To return to automatic behavior:

ALTER TABLE owner.table_name
STORAGE (CELL_FLASH_CACHE DEFAULT);

KEEP raises cache priority; it does not guarantee permanent residency. It also raises the priority of mirrored copies, which matters when estimating flash demand. Version boundary: Automatic population of KEEP objects starts with Exadata System Software 24.1.0 together with Oracle Database 23ai. Upgrading cell software to 24.1 while retaining Database 19c does not, by itself, provide that feature. Earlier combinations populate the data as it is read.

For a partitioned table, apply the choice to the relevant partition rather than elevating an entire historical table:

ALTER TABLE app.sales MODIFY PARTITION p_current
  STORAGE (CELL_FLASH_CACHE KEEP);
-- Revert the same partition after a failed experiment
ALTER TABLE app.sales MODIFY PARTITION p_current
  STORAGE (CELL_FLASH_CACHE DEFAULT);

Restoring DEFAULT restores automatic policy; it does not immediately evict the data. Confirm the actual property in the relevant table/index/partition dictionary view and compare cell cache occupancy and latency after the change.

23. XRMEM — Do Not Forget the Low-Latency Path

On supported Exadata systems, the RDMA memory path can accelerate eligible reads. Hardware generations differ: X9M uses persistent-memory hardware, while newer generations implement XRMEM differently. Interpret the statistics exposed by your exact combination; do not expect every xrmem name to appear on an older release. Check:

SELECT name,
       value
FROM v$sysstat
WHERE name LIKE 'cell RDMA%'
   OR name LIKE 'cell xrmem%'
ORDER BY name;

A well-performing OLTP query may show:

No Smart Scan + Excellent XRMEM/Flash single-block latency

That is not a failure to use Exadata. That is Exadata being used correctly for OLTP.

24. Parallel Execution — Powerful but Not Free

Eligible parallel scans commonly use direct reads and can become strong Smart Scan candidates. Parallel execution does not make every operation offloadable. But excessive parallelism can create:

More PX → More DB CPU + More cell CPU + More PGA + More memory pressure + More concurrency

Think in terms of:

• Too little PX → scan takes too long
• Correct PX → good throughput
• Too much PX → saturation and worse total throughput

Correlate requested and actual DOP with DB CPU, PGA, cell CPU, cell memory and concurrent workloads. A parallel plan may use more than one PX server set, so DOP alone is not the total worker count. Test modest DOP values under concurrency and monitor Resource Manager queuing instead of benchmarking only an isolated query.

25. IORM — SQL Tuning Cannot Override Resource Policy

If databases share cells, IORM may intentionally restrict one workload. Before concluding that SQL or storage is slow:

Plan good? → Smart Scan good? → Cells healthy? → IORM allocation?

Check:

CellCLI> LIST IORMPLAN DETAIL

A database can be well tuned and still receive less capacity because a higher-priority workload is being protected.

26. DB_FILE_MULTIBLOCK_READ_COUNT — Do Not Use It as a Smart Scan Switch

Do not increase DB_FILE_MULTIBLOCK_READ_COUNT simply because the database runs on Exadata. Smart Scan depends on an eligible scan using the direct-read path, not on manually forcing a high multiblock-read-count value.

In modern Oracle Database releases, leaving it unset normally allows Oracle to determine appropriate behavior. The parameter controls the maximum blocks requested for a sequential-scan I/O and can influence optimizer scan costing. Its default is platform-dependent; an explicitly configured value and an automatically chosen effective value are different situations. Review configuration provenance before any change and test plan/response-time effects. It does not enable Smart Scan.

27. Build an Exadata Candidate List

Divide the workload into three groups.

Group A — Large scans already using Exadata well

Pattern:

Large eligible bytes + Low returned bytes + Strong throughput + Acceptable elapsed time

Action:

Probably leave Smart Scan alone; reduce data volume only if response-time requirements demand it.

Group B — Large SQL using index/ROWID access

Pattern:

Large physical reads + Index plan + Low offload + High elapsed/User I/O

Action:

Review selectivity, cardinality, clustering factor, statistics, partitioning, and compare with a scan plan.

Group C — Full scan but no useful Smart Scan

Pattern:

Full scan + cell multiblock physical read OR direct path read + low offload

Action:

Investigate segment size, buffered reads, shared server, passthrough, cell pressure, and operation eligibility.

This is much more useful than trying to enable one Exadata feature everywhere.

28. Before-and-After Validation

Capture before and after:

Elapsed time
DB Time
CPU time
User I/O time
Physical read bytes
Offload eligible bytes
Smart Scan returned bytes
Storage Index bytes saved
Single-block latency
Flash/XRMEM optimized reads
TEMP usage
PGA behavior
Cell CPU
Cell memory
RAC waits

A successful change should improve the target workload without creating a new bottleneck elsewhere. Use multiple comparable executions, record median and tail response times, and include realistic concurrency. SQL elapsed totals for PX and DB Time are not interchangeable with client wall-clock time. Compare plan hash values and results as well as speed, and revert a change that worsens the shared workload.

29. Illustrative Example — Index Prevents an Efficient Smart Scan

Suppose:

Table size = 2 TB
Query returns = 80 million rows
Plan = INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID
Physical reads = very high
Offload eligible = 0
User I/O = dominant

Investigation finds:

Estimated rows = 50,000
Actual rows = 80,000,000
Clustering factor = poor

The index plan was chosen because the cardinality was badly underestimated. After the statistics/cardinality problem is corrected, the plan changes to:

TABLE ACCESS STORAGE FULL

These invented values illustrate the diagnostic pattern; they are not a benchmark or a promise of a particular speedup. Eligible bytes are not necessarily bytes physically read from disk after elimination.

Eligible scan bytes: about 2 TB; some regions skipped; about 90 GB returned

The performance improvement did not come from “turning on Smart Scan.” It came from fixing the reason Oracle chose the wrong access path.

30. Illustrative Example — Smart Scan Is Fine but PGA Is the Bottleneck

Suppose:

Scan = 1.5 TB
Offload eligible = 1.5 TB
Returned = 40 GB
Cells are fast
Elapsed time is still 25 minutes

SQL Monitor shows a huge hash join spilling to TEMP. The path is:

Smart Scan works → 40 GB returned → Hash join spills → TEMP I/O dominates

The correct tuning area is:

PGA/workarea | Join strategy | Cardinality | Parallelism | TEMP

Not Smart Scan.

31. Illustrative Example — Small Table Does Not Smart Scan

Suppose a 40 MB lookup table is scanned repeatedly. Plan:

TABLE ACCESS FULL

Wait:

cell multiblock physical read

You expected cell smart table scan. But the segment is small and Oracle favors buffered reads.

If the table becomes hot in the buffer cache, the scan may already be extremely fast. Trying to force Smart Scan can make performance worse.

The right question is:

Is the SQL actually slow?

Exadata is not a feature checklist.

32. My Exadata Database-Tuning Workflow

Use:

SQL → Amount of data → Access path → Cardinality/selectivity → I/O path → Exadata feature → Memory/PX/RAC → Validate

More specifically:

1. Identify expensive SQL in the correct window
2. Check plan and actual rows
3. Determine single-block versus scan workload
4. Check Smart Scan only when a scan is appropriate
5. Check index selectivity and clustering factor
6. Check partition pruning
7. Check buffer-cache behavior
8. Check PGA/TEMP spills
9. Review HCC for read-mostly data
10. Review Storage Index savings/data organization
11. Review Flash/XRMEM for repeated latency-sensitive reads
12. Check PX and IORM
13. Measure before and after

Final Exadata Tuning Checklist

SQL and Plans

• Top SQL identified from representative period
• Plan changes reviewed
• Cardinality estimates reasonable
• Index access selective enough to justify rowid I/O
• Large scans use appropriate scan path

Smart Scan

• Direct-read path understood
• Full/fast-full scans checked for Smart Scan where appropriate
• Eligible and returned bytes reviewed
• Passthrough/block-I/O reasons investigated where needed

Memory

• Buffer cache sized for workload, not folklore
• Large scans not forced into cache unnecessarily
• PGA over-allocation checked
• TEMP spills understood

Data Layout

• Partition pruning works
• HCC reviewed for read-mostly historical data
• Storage Index savings reviewed
• Data organization supports important predicates where practical

Flash / XRMEM

• High-read segments reviewed using physical/optimized reads
• CELL_FLASH_CACHE KEEP used only where evidence supports it
• XRMEM/RDMA behavior considered for OLTP

Workload Management

• Parallelism appropriate
• Cell CPU/memory healthy under PX
• IORM allocation understood
• RAC waits not the hidden bottleneck

Validation

• Before/after elapsed time captured
• CPU and User I/O compared
• Physical/offload bytes compared
• Cell/RAC impact checked
• No new bottleneck introduced

Conclusion

Exadata tuning is not about enabling as many Exadata features as possible. It is about aligning each workload with the most efficient path.

For selective OLTP:

Index → Buffer Cache/XRMEM/Flash → Low latency

For large scans:

Partition pruning + eligible Smart Scan + suitable compression/data layout

For large joins and aggregations:

Fast scan → Correct cardinality → Enough PGA → Controlled PX → Minimal TEMP spill

The full methodology is:

Part 1: Is the infrastructure healthy?
Part 2: Where did the database time go?
Part 3: Is the workload using the correct Exadata path?
Measure → Change → Validate

A good DBA does not begin by forcing a full scan, shrinking the buffer cache, raising DB_FILE_MULTIBLOCK_READ_COUNT, or setting every object to CELL_FLASH_CACHE KEEP. A good DBA starts with evidence.

Oracle documentation

Use the documentation for your installed release; the current Exadata guide also describes features unavailable in earlier database/software combinations.

• Smart Scan eligible operations
• Diagnosing Smart I/O, passthrough and Storage Index behavior
• Storage Index architecture
• Flash Cache policy and KEEP version requirements
• Manually populating Flash Cache
• V$DB_CACHE_ADVICE: units and advisory status
• V$SQL_WORKAREA_ACTIVE: memory, passes and TEMP allocation
• V$SQLSTATS: SQL and Exadata counters
• DB_FILE_MULTIBLOCK_READ_COUNT
• DBMS_COMPRESSION

• Part 1: Exadata Health Check
• Part 2: Exadata Performance Investigation
• Part 3: How to Tune Oracle Database to Use Exadata Efficiently

Tags: