Series: Part 2 of 3

Part 2 Exadata Performance Investigation

A Practical DBA Guide

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

Introduction

In Part 1 we answered: Is the Exadata infrastructure healthy?

in the time of DB issues (slowness). don’t immediately jumb to find/tune the highest SQL statement. That statement may be the victim, not the cause.

here Part 2, which will help you with database performance investigation.

We will cover the following topics:

• Define the Performance Problem
• AWR — Related Exadata Evidence
• DB Time, AAS, and Wait Classes
• Live Investigation: Wait Events and Active SQL
• Important Exadata Wait Events
• Find the SQL Responsible for the Workload
• Smart Scan: Eligible Bytes, Returned Bytes, and Offload
• Historical SQL Investigation
• Find Large Reads With No Smart Scan Eligibility
• Check the Execution Plan and SQL Monitor
• Check for Plan Changes
• Why Smart Scan Does Not Happen (Falls Back to Passthrough)
• Not Every SQL Should Use Smart Scan
• How an Index Can Prevent Smart Scan
• Flash Cache, XRMEM, and Storage Indexes
• Correlate Database Latency With Cell Metrics
• Check Cell Imbalance and RAC Before Blaming Storage
• scenarios & cases
• Exadata Performance Investigation Workflow

The objective is not simply to find an expensive SQL statement. The objective is to build a cause-and-effect chain that explains where the time went.

1. Define the Performance Problem

Before opening AWR or running performance queries, define the scope of the incident.

• When did the slowdown start and end?
• Is the problem happening now or has it already finished?
• Is the entire database affected, or only one application, service, PDB, RAC instance, or SQL statement?
• Was there a deployment, batch job, maintenance task, or workload change?
• Statistics collection,Data growth?
• Plan change,Parallel workload?
• ..etc. our DBA usually Questions

DBA Tip: Compare the problem period with a similar normal period. A 30-minute incident should not normally be compared with an entire previous day.

2. Start With AWR — Follow the Evidence

For a historical Exadata incident, AWR is normally one of the best starting points.

However, do not read the report from page 1 to the end. Start with the sections that explain DB Time and resource usage.

Database / Workload: DB Time • AAS • Load Profile • Top Foreground Events • Host / Instance CPU

SQL: Elapsed Time • CPU • User I/O • Physical & Logical Reads

Exadata / RAC: Smart I/O Statistics • Flash Cache Statistics • RAC Statistics • Segment Statistics

AWR  →  What Increased?  →  What Used DB Time?  →  Which SQL?  →  Which Subsystem?

3. DB Time, AAS, and Wait Classes

DB Time is approximately the time foreground sessions spend on CPU plus non-idle waits. Average Active Sessions (AAS) helps visualize how much work was active during the incident.

AAS = DB Time / Elapsed Time

For example, if AAS normally runs around 15 and rises to 90 during the incident, something clearly changed.
AAS alone does not tell us what those sessions were doing, so the next step is to identify the dominant wait class.

High DB CPU: SQL CPU • Parsing • PX • Inefficient plan

High User I/O: Smart Scan • Single-block I/O • Flash • Disk • Cell

High Cluster: RAC • Hot blocks • Service placement

High Commit: LGWR • Redo latency • Commit rate

4. Live Investigation: Wait Events and Active SQL

Note: Note: in my troubleshoot routine I frankly using Tanel Poder scripts and method, please try it 🙂

For a live incident, start with the active sessions and current wait events. Remember that system-event views are cumulative from instance startup, so use them as clues and correlate them with the current workload.

SELECT inst_id,
       sid,
       serial#,
       username,
       sql_id,
       event,
       wait_class,
       state,
       seconds_in_wait
FROM gv$session
WHERE status = 'ACTIVE'
  AND type = 'USER'
ORDER BY inst_id, sid;

SELECT inst_id,
       event,
       wait_class,
       total_waits,
       ROUND(time_waited_micro/1000000,2) time_s
FROM gv$system_event
WHERE wait_class <> 'Idle'
ORDER BY time_waited_micro DESC;

5. Important Exadata Wait Events

Common Exadata-related waits include:

• cell smart table scan
• cell smart index scan
• cell single block physical read
• cell single block physical read: flash cache
• cell single block physical read: partner flash cache
• cell single block physical read: xrmem cache / partner xrmem cache
• cell multiblock physical read
• cell interconnect retransmit during physical read

Important: A high cell smart table scan wait does not automatically mean Smart Scan is the problem. A query may process terabytes of data while Exadata still eliminates most of that data before it reaches the database server.

Example:

20 TB scanned at cells → Predicate/column filtering → 200 GB returned to DB nodes

That may still represent highly effective Exadata offload.

The question is:

How much work did the query request, how much did Exadata eliminate, and what performance did the workload achieve?

6. Find the SQL Responsible for the Workload

GV$SQLSTATS is especially useful on Exadata because it includes SQL performance statistics together with Exadata-specific I/O counters.

SELECT inst_id,
       sql_id,
       plan_hash_value,
       executions,
       ROUND(elapsed_time/1000000,1) elapsed_s,
       ROUND(cpu_time/1000000,1) cpu_s,
       ROUND(user_io_wait_time/1000000,1) io_wait_s,
       ROUND(physical_read_bytes/1024/1024/1024,2) read_gb,
       ROUND(io_cell_offload_eligible_bytes/1024/1024/1024,2) eligible_gb,
       ROUND(io_cell_offload_returned_bytes/1024/1024/1024,2) returned_gb
FROM gv$sqlstats
WHERE physical_read_bytes > 1024*1024*1024
ORDER BY elapsed_time DESC
FETCH FIRST 30 ROWS ONLY;

This is much more informative on Exadata than looking only at DISK_READS. However, remember that GV$SQLSTATS is cumulative for the cursor, so executions, last active time, and the incident window still matter.

Always consider:

Executions | Last active time | Plan hash value | Child cursors | Incident window

7. Smart Scan: Eligible Bytes, Returned Bytes, and Offload

For large scan workloads, compare physical reads, offload-eligible bytes, and bytes returned by the storage cells.

500 GB Eligible  →  Storage Cells  →  Predicate / Column Filtering  →  20 GB Returned

Useful system statistics:
SELECT name,
       value
FROM v$sysstat
WHERE name IN (
    'cell physical IO bytes eligible for predicate offload',
    'cell physical IO interconnect bytes returned by smart scan',
    'cell physical IO bytes saved by storage index'
)
ORDER BY name;

A large difference between eligible and returned bytes can indicate that Exadata is eliminating substantial data at the storage layer. However, the values must be interpreted in the context of the SQL statement, selected columns, predicate selectivity, and execution plan.

Do not use a universal offload percentage. A formula such as 1 – returned/eligible can be useful for some scan workloads, but it is not a universal Exadata performance score.

8. Historical SQL Investigation

Current V$ views are not enough for an incident that happened yesterday. Use AWR, but restrict DBA_HIST_SQLSTAT to the actual incident window. Otherwise, you may accidentally analyze the entire AWR retention period.

SELECT s.sql_id,
       s.plan_hash_value,
       ROUND(SUM(s.elapsed_time_delta)/1000000,1) elapsed_s,
       ROUND(SUM(s.cpu_time_delta)/1000000,1) cpu_s,
       ROUND(SUM(s.physical_read_bytes_delta)/1024/1024/1024,2) read_gb,
       ROUND(SUM(s.io_offload_elig_bytes_delta)/1024/1024/1024,2) eligible_gb,
       ROUND(SUM(s.io_offload_return_bytes_delta)/1024/1024/1024,2) returned_gb
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn
  ON sn.dbid = s.dbid
 AND sn.instance_number = s.instance_number
 AND sn.snap_id = s.snap_id
WHERE sn.begin_interval_time >=
      TO_TIMESTAMP('&BEGIN_TIME','YYYY-MM-DD HH24:MI')
  AND sn.end_interval_time <=
      TO_TIMESTAMP('&END_TIME','YYYY-MM-DD HH24:MI')
GROUP BY s.sql_id, s.plan_hash_value
ORDER BY SUM(s.elapsed_time_delta) DESC
FETCH FIRST 30 ROWS ONLY;

9. Find Large Reads With No Smart Scan Eligibility

A useful follow-up is to find statements that read a significant amount of data but show no offload eligibility during the same incident window. These are investigation candidates, not automatically bad SQL.

SELECT s.sql_id,
       s.plan_hash_value,
       ROUND(SUM(s.physical_read_bytes_delta)/1024/1024/1024,2) read_gb,
       ROUND(SUM(s.io_offload_elig_bytes_delta)/1024/1024/1024,2) eligible_gb,
       ROUND(SUM(s.elapsed_time_delta)/1000000,1) elapsed_s
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn
  ON sn.dbid = s.dbid
 AND sn.instance_number = s.instance_number
 AND sn.snap_id = s.snap_id
WHERE sn.begin_interval_time >=
      TO_TIMESTAMP('&BEGIN_TIME','YYYY-MM-DD HH24:MI')
  AND sn.end_interval_time <=
      TO_TIMESTAMP('&END_TIME','YYYY-MM-DD HH24:MI')
GROUP BY s.sql_id, s.plan_hash_value
HAVING SUM(s.physical_read_bytes_delta) > 10*1024*1024*1024
   AND NVL(SUM(s.io_offload_elig_bytes_delta),0) = 0
ORDER BY SUM(s.physical_read_bytes_delta) DESC
FETCH FIRST 50 ROWS ONLY;

A highly selective index-based SQL may legitimately have zero offload eligibility. The next step is to inspect its execution plan.

These are investigation candidates, not automatically bad SQL.

10. Check the Execution Plan and SQL Monitor

For a cached SQL statement, inspect the actual execution plan and I/O statistics:

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

Operations such as TABLE ACCESS STORAGE FULL and INDEX STORAGE FAST FULL SCAN are particularly interesting on Exadata. However, seeing STORAGE in the plan means that storage-level processing may be possible; it does not by itself prove that useful offload occurred.

Check:

• E-Rows versus A-Rows
• Buffers
• Physical reads
• Predicates
• Plan hash value
• Join order
• Partition pruning
• Access method
• Parallel execution

A major E-Rows versus A-Rows mismatch may explain a bad access path before any Exadata-specific tuning.

DBMS_XPLAN note: ALLSTATS LAST requires execution statistics to have been collected for the cursor; otherwise detailed A-Rows information may not be available.

For long-running or parallel SQL, SQL Monitor can provide a much clearer execution-level view of time, I/O, waits, PX activity, and Exadata offload information.

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(
           sql_id       => '&SQL_ID',
           type         => 'TEXT',
           report_level => 'ALL'
       )
FROM dual;

11. Check for Plan Changes

When SQL was fast yesterday and slow today, compare plans before touching Exadata settings.

SELECT sql_id,
       plan_hash_value,
       MIN(snap_id) first_snap,
       MAX(snap_id) last_snap,
       SUM(executions_delta) executions
FROM dba_hist_sqlstat
WHERE sql_id = '&SQL_ID'
GROUP BY sql_id, plan_hash_value
ORDER BY first_snap;

Ask:

• Did an index access replace a scan?
• Did a scan replace a good selective index?
• Did join order change?
• Did parallelism change?
• Did partition pruning disappear?
• Did statistics change?

A plan change can completely change the Exadata I/O path.

12. Why Smart Scan Does Not Happen (Falls Back to Passthrough)

Suppose the plan shows TABLE ACCESS STORAGE FULL but the expected offload benefit is missing. Do not simply conclude that Smart Scan is broken. Investigate why the operation is running in passthrough or block-I/O mode.

• Operation or predicate is not offloadable
• Smart Scan was disabled by configuration or session settings
• Block-I/O mode is being used
• Storage cell resource pressure or low memory
• Encryption or version-specific conditions
• Other CELLSRV or software conditions

Useful evidence can come from the AWR Smart I/O Statistics section, specific passthrough wait events where available, and cell statistics related to predicate-offload passthrough.

SELECT name, value
FROM v$sysstat
WHERE name LIKE 'cell%passthru%'
   OR name LIKE 'cell%predicate offload%'
ORDER BY name;

The better question is not “Did Smart Scan work?” but “If Smart Scan did not work as expected, why?”

13. Not Every SQL Should Use Smart Scan

A selective OLTP query may correctly use:

INDEX RANGE SCAN → TABLE ACCESS BY INDEX ROWID → Single-block I/O

That workload benefits from:

Buffer Cache | XRMEM | Smart Flash Cache | Low single-block latency | RAC locality

A large analytical query may benefit from:

Full scan → Direct path → Smart Scan → Predicate/column offload → Storage Index

The objective is not to make every SQL use Smart Scan.

The objective is to choose the most efficient access path.

14. How an Index Can Prevent Smart Scan

An ordinary index range scan can keep a SQL statement on the single-block path.

That is often correct.

But if the query retrieves a large percentage of a table through huge numbers of rowid lookups, the index can become more expensive than a Smart Scan.

Look for:

INDEX RANGE SCAN → TABLE ACCESS BY INDEX ROWID

combined with:

Large physical reads + High elapsed/User I/O + Zero/low offload eligibility

Then evaluate:

• Predicate selectivity
• Returned row count
• Clustering factor
• Table size
• Number of table blocks visited
• Plan estimates versus actual rows
• Partition pruning
• Potential Smart Scan filtering

Do not drop or hide the index merely to “enable Exadata.” Compare both acce

15. Flash Cache, XRMEM, and Storage Indexes

As I mentioned previously, not every SQL statement should use Smart Scan. Large analytical scans may benefit from Smart Scan and Storage Indexes, while selective OLTP index lookups often depend more on single-block latency, Flash Cache, XRMEM, and the buffer cache.

Large Scan: Smart Scan • Offload

Index Lookup: Single-block I/O

OLTP: Flash • XRMEM • Latency

DW: Throughput • Storage Index

SELECT name,
       value
FROM v$sysstat
WHERE name LIKE 'cell%'
   OR name LIKE 'physical read%optimized%'
ORDER BY name;

Useful statistics include cell physical IO bytes eligible for predicate offload, cell physical IO bytes saved by storage index, cell flash cache read hits, and RDMA/XRMEM statistics where supported.

16. Correlate Database Latency With Cell Metrics

One of the strongest Exadata troubleshooting techniques is comparing database-side latency with storage-cell latency during the same incident window.

Scenario 1: DB latency 20 ms • Cell latency 3 ms • Investigate above the physical disk layer

Scenario 2: DB latency 20 ms • Cell latency 19 ms • Storage becomes a stronger suspect

Scenario 3: One cell is slow • Compare peers and investigate imbalance

Scenario 4: All cells are busy • Check workload pressure and IORM

In Part 1, we covered CellDisk metrics in detail. During performance investigation, the important difference is that we now correlate those metrics with the exact AWR incident window.

Small reads:

Request rate: CD_IO_RQ_R_SM_SEC

Throughput: CD_IO_BY_R_SM_SEC

Latency: CD_IO_TM_R_SM_RQ

Large reads:

Request rate: CD_IO_RQ_R_LG_SEC

Throughput: CD_IO_BY_R_LG_SEC

Latency: CD_IO_TM_R_LG_RQ

AWR 10:00-11:00  <->  Cell METRICHISTORY 10:00-11:00

17. Check Cell Imbalance and RAC Before Blaming Storage

Exadata is a distributed system, so peer comparison is extremely powerful. If two cells scan around 5 GB/s and one cell is around 900 MB/s, investigate the slower cell rather than treating the platform as one black box.

• Physical storage or Flash problem
• Cell CPU or memory pressure
• Network/RDMA issue
• Data imbalance or rebalance activity
• Different hardware or maintenance state

Also check RAC. A slow application can be caused by Cache Fusion contention even when Exadata storage is completely healthy.

SELECT inst_id,
       event,
       total_waits,
       ROUND(time_waited_micro/1000000,1) time_s
FROM gv$system_event
WHERE wait_class = 'Cluster'
ORDER BY time_waited_micro DESC;

If Cluster wait time is high while User I/O is normal, investigate hot blocks, service placement, instance affinity, and global-cache traffic before changing Flash Cache or Smart Scan settings.

18. Examples

1 — Smart Scan Query Looks Slow

Suppose AWR shows cell smart table scan as a top wait. The plan contains TABLE ACCESS STORAGE FULL. The SQL reads 3 TB, 3 TB is offload eligible, and only 120 GB is returned. Cell throughput is high, with no disk or network errors.

3 TB Read  →  3 TB Eligible  →  Smart Scan  →  120 GB Returned

Interpretation: Smart Scan is probably doing useful work. The query is simply processing a large amount of data. The tuning question becomes how to reduce the amount of data processed through better predicates, partition pruning, HCC, data organization, materialized results, or parallelism strategy.

2 — Index SQL Suddenly Slow

Suppose an OLTP SQL normally runs in 5 ms but now takes 150 ms. The plan uses INDEX RANGE SCAN followed by TABLE ACCESS BY INDEX ROWID, and offload eligible bytes are zero.

Index Range Scan  →  ROWID Access  →  Single-Block I/O  →  Flash / XRMEM / RAC

Interpretation: zero offload eligibility is expected. Investigate single-block latency, Flash Cache, XRMEM, buffer cache, RAC block transfers, clustering factor, cardinality, and execution-plan changes. Do not force a full scan merely to make the SQL use Smart Scan.

Key principle: The objective is not to make every SQL statement use Smart Scan. The objective is to choose the most efficient access path for that workload.

19. Exadata Performance Investigation Workflow

Symptom  →  DB Time  →  Waits  →  SQL / Plan  →  I/O Path  →  Cell / RAC  →  Root Cause

This workflow prevents random tuning. Start with the user symptom, identify where DB Time went, identify the SQL or workload responsible, and then correlate database evidence with Exadata and RAC metrics.

Final Investigation Checklist

☐ Incident window and scope identified

☐ AWR problem period compared with a normal period

☐ DB Time, AAS, and dominant wait class understood

☐ Top SQL and execution plan identified

☐ Estimated versus actual rows checked

☐ Smart Scan eligibility and returned bytes reviewed where relevant

☐ Passthrough investigated when offload is unexpectedly low

☐ Flash/XRMEM and single-block latency reviewed for OLTP

☐ Cell metrics matched to the same incident window

☐ Cells compared with each other

☐ RAC waits and IORM considered before blaming storage

Conclusion

Exadata performance troubleshooting is not simply “find the top SQL and change the plan.” It is a correlation exercise.

User Symptom  →  DB Time  →  Wait Events  →  SQL / Plan  →  Exadata Evidence  →  Root Cause

Once we know where the time is being spent, we can tune intelligently.

In Part 3 — How to Tune Oracle Database to Use Exadata Efficiently
we will move from diagnosis to optimization and examine Smart Scan, indexes, Storage Indexes, HCC, Flash Cache KEEP, parallel execution, PGA, DB_FILE_MULTIBLOCK_READ_COUNT, statistics, and IORM.

Articals in the same Series:

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

Other Articals

🔖 Next: Part 1/3 – Oracle Exadata Patching (Oracle Homes)

🔖 Next: Part 2/3 – Oracle Exadata Patching (RoCE Switches)

🔖 Next: Part 3/3 – Oracle Exadata Patching (Cell Storage)

🔖 Next: Part 4/5 – Oracle Exadata Patching (KVM Host)

🔖 Next: Part 5/5 – Oracle Exadata Patching (KVM Guest)

Tags: