Expensive SQL on Exadata 26.1: Read the Offload Numbers Before You Build the Index

The Exadata ticket we see most often is not about a slow query. It is about a cluster that runs hot for hours, an IORM plan that somebody keeps re-tuning, and a licence conversation about adding cores, while the response times that users actually see are acceptable. Underneath it there are usually a handful of SQL_IDs that X11M storage has made fast enough to ignore and expensive enough to matter. That is the particular shape of expensive SQL on Exadata, and it is why we compute one number, the offload ratio, before we look at a plan, a wait event or an index.

This post is how we work an expensive SQL backlog on the current platform: Exadata System Software 26.1, the 26ai release, with 26.1.1 as the maintenance update in circulation, on X11M storage, running Oracle Database 26ai, with 19c called out wherever the installed base behaves differently. Sample outputs are illustrative and say so. The queries are the point, because the numbers have to be yours.

Why the pack entitlement and the cell software level come before any expensive SQL query

Exadata ships without the Diagnostics Pack or the Tuning Pack. Both are licensed separately, and a SELECT against DBA_HIST_SQLSTAT on an estate that never bought them is a licence event with our name on it. So before any expensive SQL work on an Exadata system, the first two things we read are the pack access parameter and DBA_FEATURE_USAGE_STATISTICS, the second because the delta from takeover date to today is the only proof of who turned what on. The cell release version comes next, since half of what follows depends on it.

SELECT   banner_full FROM v$version;

SELECT   patch_id, patch_type, status, action_time, description
FROM     dba_registry_sqlpatch
ORDER BY action_time DESC FETCH FIRST 5 ROWS ONLY;

SHOW PARAMETER control_management_pack_access;   -- NONE | DIAGNOSTIC | DIAGNOSTIC+TUNING

SELECT   name, detected_usages, currently_used, first_usage_date, last_usage_date
FROM     dba_feature_usage_statistics
WHERE    name IN ('Automatic Workload Repository', 'ADDM', 'SQL Tuning Advisor',
                  'SQL Monitoring and Tuning pages', 'Real-Time SQL Monitoring',
                  'SQL Plan Management', 'Exadata', 'Automatic Indexes')
ORDER BY name;

-- Cell software level, from any database node with cellcli access to the storage grid:
-- dcli -g cell_group -l celladmin "cellcli -e list cell attributes name, releaseVersion, flashCacheMode, memoryCacheMode"

Every expensive SQL query below is marked for what it needs. The AWR and ASH ones want the Diagnostics Pack. The V$SQL, V$SYSSTAT, DBMS_XPLAN, CellCLI and SQL Plan Management ones want Enterprise Edition and nothing else. An unlicensed estate can still find and fix expensive SQL with the second group plus STATSPACK; it just has less history and has to sample V$SESSION itself.

The offload chain is what makes expensive SQL on Exadata a different problem

For expensive SQL on commodity storage, bytes read is bytes read. On Exadata a scan passes through a chain: bytes eligible for offload, bytes the storage index let the cell skip, bytes the smart scan filtered and projected, and finally bytes that crossed the RoCE fabric to the database server. The ratio of the last to the first, subtracted from one, is the offload efficiency, and it is the single number that tells us whether an expensive SQL statement is using the hardware it is running on.

Figure 1. The offload chain for one expensive SQL statement. Above 0.9 the cells are earning their keep; below 0.5 with eligible bytes high, the plan or the predicate has changed and the cells are being used as disks.

SELECT   sql_id,
         child_number,
         plan_hash_value,
         executions,
         ROUND(elapsed_time / NULLIF(executions, 0) / 1000, 1)                        AS avg_elapsed_ms,
         ROUND(io_cell_offload_eligible_bytes / 1024 / 1024 / 1024, 2)                AS elig_gb,
         ROUND(io_interconnect_bytes / 1024 / 1024 / 1024, 2)                         AS interconnect_gb,
         ROUND(io_cell_uncompressed_bytes / 1024 / 1024 / 1024, 2)                    AS uncompressed_gb,
         ROUND(1 - io_interconnect_bytes / NULLIF(io_cell_offload_eligible_bytes, 0), 3) AS offload_eff,
         ROUND(io_cell_offload_returned_bytes / NULLIF(io_cell_offload_eligible_bytes, 0), 3) AS returned_ratio
FROM     v$sql
WHERE    sql_id = '7v3gk9x2m1qa8'
ORDER BY child_number;

-- System-wide, the same story from V$SYSSTAT (cumulative since instance start):
SELECT   name, ROUND(value / 1024 / 1024 / 1024, 1) AS gb
FROM     v$sysstat
WHERE    name IN ('cell physical IO bytes eligible for predicate offload',
                  'cell physical IO bytes saved by storage index',
                  'cell physical IO interconnect bytes returned by smart scan',
                  'cell physical IO interconnect bytes',
                  'physical read total bytes',
                  'cell flash cache read hits',
                  'cell RDMA reads')
ORDER BY name;

The chain only exists on one of the two I/O paths the database server can choose for an expensive SQL statement, and it chooses per segment, per execution. A buffered read pulls 8K blocks into the SGA through cell single block or multiblock physical reads; the cell serves them from XRMEM or flash and evaluates nothing. A direct-path read hands the predicate to the cell, which filters, projects, consults the storage index and decompresses HCC on its own CPUs, then returns rows into the PGA. Smart scan lives on the second path only.

Figure 2. Buffered path and direct path. The cells accelerate whichever the plan hands them, which is why expensive SQL on Exadata is a plan question before it is a storage question.

When offload_eff is poor, or elig_gb is close to zero for expensive SQL that reads a large table, there are exactly three reasons and we check them in order. The segment was not read direct-path, because it sits under the serial direct read threshold or the buffer cache heuristic preferred buffered reads; physical reads direct in V$SESSTAT for the session settles that. The predicate was not offloadable, which the plan's predicate section will show. Or the cells were saturated, which only the cell metrics can say and the database side cannot.

Pulling the expensive SQL list out of AWR by CPU seconds and bytes per row

Once the offload chain is in our heads, the expensive SQL ranking query is built around it. DBA_HIST_SQLSTAT carries per-snapshot deltas that separate working from waiting: CPU, I/O wait, cluster wait, interconnect bytes and offload-eligible bytes. The query below aggregates a window per SQL_ID and plan hash and derives four ratios. We keep all four ORDER BY lines and comment out three, because the same list ordered by elapsed time and by CPU seconds answers two different questions and produces two different top statements.

DEFINE hours = 24

WITH snaps AS (
    SELECT   snap_id, dbid, instance_number
    FROM     dba_hist_snapshot
    WHERE    begin_interval_time >= SYSTIMESTAMP - NUMTODSINTERVAL(&hours, 'HOUR')
),
agg AS (
    SELECT   s.sql_id,
             s.plan_hash_value,
             SUM(s.executions_delta)                          AS executions,
             SUM(s.elapsed_time_delta) / 1e6                  AS elapsed_s,
             SUM(s.cpu_time_delta)     / 1e6                  AS cpu_s,
             SUM(s.iowait_delta)       / 1e6                  AS iowait_s,
             SUM(s.clwait_delta)       / 1e6                  AS cluster_wait_s,
             SUM(s.buffer_gets_delta)                         AS buffer_gets,
             SUM(s.physical_read_bytes_delta)                 AS phys_read_bytes,
             SUM(s.io_offload_elig_bytes_delta)               AS offload_elig_bytes,
             SUM(s.io_interconnect_bytes_delta)               AS interconnect_bytes,
             SUM(s.rows_processed_delta)                      AS rows_processed,
             MAX(s.px_servers_execs_delta)                    AS px_execs,
             COUNT(DISTINCT s.plan_hash_value) OVER (PARTITION BY s.sql_id) AS plan_count
    FROM     dba_hist_sqlstat s
    JOIN     snaps n ON n.snap_id = s.snap_id AND n.dbid = s.dbid AND n.instance_number = s.instance_number
    GROUP BY s.sql_id, s.plan_hash_value
)
SELECT   a.sql_id,
         a.plan_hash_value,
         a.plan_count,
         a.executions,
         ROUND(a.elapsed_s / NULLIF(a.executions, 0) * 1000, 1)        AS avg_elapsed_ms,
         ROUND(a.cpu_s, 1)                                              AS total_cpu_s,
         ROUND(a.iowait_s, 1)                                           AS total_iowait_s,
         ROUND(a.buffer_gets / NULLIF(a.rows_processed, 0), 1)          AS gets_per_row,
         ROUND(a.phys_read_bytes / NULLIF(a.rows_processed, 0) / 1024)  AS kb_read_per_row,
         ROUND(1 - a.interconnect_bytes / NULLIF(a.offload_elig_bytes, 0), 3) AS offload_eff,
         ROUND(a.offload_elig_bytes / 1024 / 1024 / 1024, 1)            AS offload_elig_gb,
         SUBSTR(t.sql_text, 1, 120)                                     AS sql_text
FROM     agg a
LEFT JOIN dba_hist_sqltext t ON t.sql_id = a.sql_id
ORDER BY a.cpu_s DESC                 -- resource cost: the licence-core question
-- ORDER BY a.elapsed_s DESC         -- response time, weighted by how often it runs
-- ORDER BY gets_per_row DESC        -- efficiency: buffer gets per row returned
-- ORDER BY a.plan_count DESC, a.elapsed_s DESC   -- instability: several plans for one SQL_ID
FETCH FIRST 25 ROWS ONLY;

SQL_ID        PLAN_HASH  PLANS EXECS  AVG_ELAPSED_MS TOTAL_CPU_S TOTAL_IOWAIT_S GETS_PER_ROW KB_READ_PER_ROW OFFLOAD_EFF OFFLOAD_ELIG_GB
------------- ---------- ----- ------ -------------- ----------- -------------- ------------ --------------- ----------- ---------------
7v3gk9x2m1qa8 1382190441     3  18240         2418.7     21044.3         9302.1       6120.4          3180.2       0.412          9120.5
9c2h7ftq0zd4b 2011878312     1 1928400            2.9      4177.9          188.4        204.0             0.1                      0.0
a8kx13pm7rw2c  511209835     1   2210         1871.5        212.6         3922.8         14.2            21.6       0.968          2288.7
...

Read the three rows against the chain. The first statement offloads 41 percent of nine terabytes eligible, has three plans in a day, and burns twenty-one thousand CPU seconds; it is expensive SQL by every definition and it is where the day goes. The second runs 1.9 million times at three milliseconds with no eligible bytes at all, because it is an index lookup, and it is still the second-largest CPU consumer on the cluster; ordering by average elapsed would have hidden it on page four. The third offloads 97 percent, uses almost no CPU, and is slow. It is waiting, not working, and ASH will say on what.

Those are the three regions of the picture we carry for expensive SQL. Elapsed time per execution on one axis, resource per row on the other. The slow-and-expensive corner gets the ticket. The fast-and-expensive corner decides how many cores get licensed. The slow-and-cheap corner is a wait problem, and no index will touch it.

Figure 3. Where the three illustrative expensive SQL statements land. The evidence source and the fix family are different in each corner, which is why sorting the list one way is never enough.

SELECT   sql_id,
         sql_plan_hash_value,
         NVL(wait_class, 'ON CPU')                 AS wait_class,
         NVL(event, 'ON CPU')                      AS event,
         COUNT(*) * 10                             AS approx_seconds,   -- DBA_HIST ASH samples every 10 s
         ROUND(100 * RATIO_TO_REPORT(COUNT(*)) OVER (PARTITION BY sql_id), 1) AS pct_of_sql
FROM     dba_hist_active_sess_history
WHERE    sql_id IN ('7v3gk9x2m1qa8', '9c2h7ftq0zd4b', 'a8kx13pm7rw2c')
AND      sample_time >= SYSTIMESTAMP - INTERVAL '24' HOUR
GROUP BY sql_id, sql_plan_hash_value, wait_class, event
ORDER BY sql_id, approx_seconds DESC;

The Exadata event names do most of the diagnosis for expensive SQL. cell smart table scan and cell smart index scan mean the offload path is in use and the time is real scan time. cell single block physical read means the statement is walking an index or doing row-by-row lookups through the buffer cache; on X11M that is served from XRMEM in tens of microseconds, so a large count is a plan problem, not a storage problem. cell multiblock physical read on a big table is a buffered scan, which is the first of the three reasons offload stops.

enq: TX row lock contention, log file sync, gc buffer busy acquire and the IORM waits all belong to the slow-and-cheap corner of the expensive SQL picture. Nothing in the indexing section applies to them.

Without the Diagnostics Pack, V$SQLSTATS carries the same expensive SQL counters for whatever is still in the shared pool, and a ten-second sampler over V$SESSION is a legal, if crude, ASH. We have run estates that way for months. It works, and it is a reason to have the pack conversation with the client's licensing contact rather than a reason to query DBA_HIST_* anyway.

The plan the cells executed: STORAGE FULL, storage() predicates and the estimate gap

With the offload ratio and the wait profile in hand, the expensive SQL plan is read for two specific things rather than for its cost column: whether the access operations are storage-aware, and whether the predicate reached the cell. DBMS_XPLAN shows both, provided the call asks for actual statistics and the predicate section, and the storage() line beneath a TABLE ACCESS STORAGE FULL is the clearest single signal that offload is possible for that step.

SELECT   *
FROM     TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
             sql_id          => '7v3gk9x2m1qa8',
             cursor_child_no => NULL,
             format          => 'ALLSTATS LAST +PREDICATE +OUTLINE +NOTE'));

-- Illustrative fragment (not a real plan):
-- |   4 |     TABLE ACCESS STORAGE FULL | ORDER_LINES | 1 |  12M |  2.1M | 00:00:38.4 |  4116K | ...
-- Predicate Information:
--    4 - storage("OL"."POST_DATE">=:B1 AND "OL"."STATUS"='OPEN')
--        filter("OL"."POST_DATE">=:B1 AND "OL"."STATUS"='OPEN')
-- Note:
--    - this is an adaptive plan
--    - Degree of Parallelism is 8 because of table property

TABLE ACCESS STORAGE FULL rather than TABLE ACCESS FULL says the optimizer knows the segment is on Exadata storage. The storage() predicate says the filter can be evaluated on the cell; when a predicate appears only as filter(), it cannot, and the cause is nearly always a function wrapped around the column, a datatype the cell does not evaluate, or an operator outside the offloadable set. Then E-Rows against A-Rows, twelve million estimated against 2.1 million actual, is the estimate gap that pushed the optimizer toward a hash join and a temp spill the expensive SQL did not need.

The third reason offload stops for expensive SQL, cell saturation, is invisible from V$ views. It lives on the cells, and CellCLI through dcli is how we read it across the grid in one pass.

# From a database node with dcli configured for the cell group
dcli -g cell_group -l celladmin "cellcli -e list metriccurrent where name like 'FC_IO_RQ_R.*' or name like 'FC_IO_RQ_R_MISS.*' or name like 'SIO_IO_SI_SAVED.*' or name like 'DB_IO_WT_SM_RQ.*' or name like 'CD_IO_TM_R_SM_RQ.*'"

# Per-database IORM view: who is waiting on whom
dcli -g cell_group -l celladmin "cellcli -e list metriccurrent where objectType='IORM_DATABASE' and name like 'DB_IO_.*_SM.*'"

# What the IORM plan actually says (flash cache and XRMEM quotas included since 24.x)
dcli -g cell_group -l celladmin "cellcli -e list iormplan detail"

# Exadata System Software 26.1: Exascale volume performance in iostat style, JSON optional
# edvstat -i 5 -c 6 --json

Two of those metrics would survive if we were allowed only two on a dashboard. FC_IO_RQ_R_MISS climbing while FC_IO_RQ_R stays flat is a flash cache that has lost the working set, and the usual suspect is a scan that started caching itself after a CELL_FLASH_CACHE attribute or a KEEP policy changed. DB_IO_WT_SM_RQ is the average IORM wait per small request for one database; when it is not close to zero for the database being tuned, the expensive SQL in question is losing to a neighbour, and no plan change on this side fixes that.

What moved for expensive SQL between 19c on earlier racks and 26ai on X11M

An estate that upgraded its cells to 26.1 but still runs 19c on the database side sees only half of the changes below, so each row is pinned to the layer that carries it. Most of them alter what expensive SQL evidence means rather than how it is collected, which is the reason to know them before reading numbers, not after.

The row that changes expensive SQL work the most on new racks is the mixed-hardware one. Once X11 storage servers sit beside X11M in the same rack, "the storage is fast" is no longer one statement, and a single SQL_ID can look healthy on partitions that landed on XRMEM and poor on partitions that did not. Oracle's Exadata System Software 26.1 announcement covers the cell-side changes; the X11 storage server announcement covers the tiering hardware.

An index on Exadata competes with a smart scan, so it is tested invisible first

Elsewhere, an index is the reflex answer to expensive SQL with a selective predicate on a big table. On Exadata the alternative to the index is not a slow full scan. It is a smart scan with a storage index, reading only the 1 MB regions that can contain the value and filtering them on the cell, and for a statement returning thousands of rows from a range that scan often wins. For a lookup that returns one row by key twenty thousand times a second, the index wins, and XRMEM makes it win by a wide margin. Most large tables carry both kinds of expensive SQL, so the workload shape decides, not the table.

Figure 4. Index or offload for expensive SQL, by workload shape. The Exadata-specific trap is an index that helped on commodity storage and defeats smart scan here.

The test costs nothing and we run it before every index change on Exadata. Make the candidate index invisible, run the expensive SQL with actual statistics, and compare elapsed, buffer gets and cell bytes against the indexed run. The optimizer treats an invisible index as absent, so the comparison is the real one and the application sees no change.

-- Baseline with the index in play
ALTER SESSION SET STATISTICS_LEVEL = ALL;
SELECT /* ix_test_visible */ ol.order_id, ol.sku, ol.qty, ol.line_total
FROM   order_lines ol
WHERE  ol.post_date >= DATE '2026-08-01'
AND    ol.status    =  'OPEN';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST +PREDICATE'));

-- Hide the index from the optimizer without dropping it; DML still maintains it
ALTER INDEX ix_order_lines_post_date INVISIBLE;

SELECT /* ix_test_invisible */ ol.order_id, ol.sku, ol.qty, ol.line_total
FROM   order_lines ol
WHERE  ol.post_date >= DATE '2026-08-01'
AND    ol.status    =  'OPEN';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST +PREDICATE'));

-- Put it back either way; the decision comes from the two plans, not from this session
ALTER INDEX ix_order_lines_post_date VISIBLE;

For expensive SQL the comparison is never the optimizer cost. It is A-Time on the top line, Buffers and Reads per row returned, and, from V$SQL for the two SQL_IDs, offload efficiency and interconnect bytes. When the scan wins, the index becomes a drop candidate, and since 12.2 the database has tracked whether anything else depends on it without a trace being set.

SELECT   u.owner, u.name AS index_name,
         u.total_access_count, u.total_exec_count, u.total_rows_returned,
         u.bucket_0_access_count           AS zero_row_accesses,
         u.bucket_1000_plus_access_count   AS wide_range_accesses,
         u.last_used,
         ROUND(s.bytes / 1024 / 1024 / 1024, 1) AS size_gb
FROM     dba_index_usage u
JOIN     dba_segments s ON s.owner = u.owner AND s.segment_name = u.name
WHERE    u.owner = 'PAYROLL'
ORDER BY u.total_access_count ASC, size_gb DESC;

-- Partitioned tables with mixed workloads: keep the key index, let range scans offload per partition,
-- and consider a zone map (EE on Exadata) where the range column correlates with a filter column
CREATE MATERIALIZED ZONEMAP zm_order_lines_post_date
    REFRESH FAST ON COMMIT
    AS SELECT SYS_OP_ZONE_ID(rowid), MIN(post_date), MAX(post_date), MIN(status), MAX(status)
       FROM   order_lines
       GROUP BY SYS_OP_ZONE_ID(rowid);

Two cautions before an index leaves an expensive SQL review as a drop. DBA_INDEX_USAGE is flushed on a schedule and counts accesses rather than executions per second, so a lookup index carrying the OLTP path can show a modest count next to a reporting index that scanned a billion rows once; the buckets matter more than the totals. And an index that a foreign key or a unique constraint depends on is not a candidate whatever the usage says, because the constraint is why it exists.

Pin first, patch second, engineer third: SQL Plan Management as the expensive SQL rollback path

The order of operations on an expensive SQL change is set by what can be undone in one call. SQL Plan Management is Enterprise Edition with no pack attached and fully reversible, so when an expensive SQL statement has flipped to a bad plan, loading the good plan from the cursor cache or from AWR as a fixed baseline restores the old behaviour immediately and separates the emergency from the engineering. A hint delivered through a SQL patch is the second rung and equally reversible. Statistics, rewrites and index decisions come after both, made without the pager going off.

Figure 5. One expensive SQL change per iteration, verified against the same SQL_ID in the next AWR interval, with the baseline as the rollback.

-- Load the known-good plan from AWR into a fixed baseline (Diagnostics Pack needed to read AWR;
-- use LOAD_PLANS_FROM_CURSOR_CACHE when the good child is still in the shared pool)
DECLARE
    n PLS_INTEGER;
BEGIN
    n := DBMS_SPM.LOAD_PLANS_FROM_AWR(
             begin_snap      => 48120,
             end_snap        => 48124,
             basic_filter    => q'[sql_id = '7v3gk9x2m1qa8' AND plan_hash_value = 1382190441]',
             fixed           => 'YES',
             enabled         => 'YES');
    DBMS_OUTPUT.PUT_LINE('plans loaded: ' || n);
END;
/

-- Verify: the baseline exists, is fixed, and the next executions report it in the plan Note section
SELECT   sql_handle, plan_name, origin, enabled, accepted, fixed, last_executed
FROM     dba_sql_plan_baselines
WHERE    signature = (SELECT exact_matching_signature FROM v$sql WHERE sql_id = '7v3gk9x2m1qa8' AND ROWNUM = 1);

-- Rollback: drop the baseline and the optimizer is free again
DECLARE
    n PLS_INTEGER;
BEGIN
    n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => 'SQL_9f2a7c1d4e83b005', plan_name => 'SQL_PLAN_9y8m7f2g1hqd5');
END;
/

-- Second rung: a hint without a code change, via SQL patch (EE, no pack); undone with DROP_SQL_PATCH
DECLARE
    p VARCHAR2(128);
BEGIN
    p := DBMS_SQLDIAG.CREATE_SQL_PATCH(
             sql_id    => '7v3gk9x2m1qa8',
             hint_text => 'FULL(@"SEL$1" "OL"@"SEL$1") PARALLEL(4)',
             name      => 'patch_7v3gk9x2m1qa8_full_scan');
END;
/

Verification is the expensive SQL ranking query that found the statement, filtered to its SQL_ID, one snapshot later. avg_elapsed_ms, total_cpu_s and gets_per_row have to move in the right direction on a comparable execution count, and offload_eff has to rise if the change was about the scan path. If executions collapsed too, the application changed and nothing was measured. The before and after rows go into the ticket, because they are the answer to the next question about whether the expensive SQL change paid for itself.

SELECT   n.snap_id,
         TO_CHAR(n.begin_interval_time, 'DD-MON HH24:MI')                    AS interval_start,
         s.plan_hash_value,
         s.executions_delta                                                 AS execs,
         ROUND(s.elapsed_time_delta / NULLIF(s.executions_delta, 0) / 1000, 1) AS avg_elapsed_ms,
         ROUND(s.cpu_time_delta / 1e6, 1)                                   AS cpu_s,
         ROUND(s.buffer_gets_delta / NULLIF(s.rows_processed_delta, 0), 1)  AS gets_per_row,
         ROUND(1 - s.io_interconnect_bytes_delta / NULLIF(s.io_offload_elig_bytes_delta, 0), 3) AS offload_eff
FROM     dba_hist_sqlstat  s
JOIN     dba_hist_snapshot n ON n.snap_id = s.snap_id AND n.dbid = s.dbid AND n.instance_number = s.instance_number
WHERE    s.sql_id = '7v3gk9x2m1qa8'
AND      n.begin_interval_time >= SYSTIMESTAMP - INTERVAL '3' DAY
ORDER BY n.snap_id, s.plan_hash_value;

That verification query is also how a few habits got retired. Raising the degree of parallelism on expensive SQL whose hash join spilled to temp made it spill on more slaves and finish later. An index added to speed up a report turned an eight-second smart scan into a forty-second index range scan with millions of cell single block physical reads, and came out again the next day. CELL_FLASH_CACHE KEEP on a large table evicted the working set the OLTP path relied on, and the latency complaint arrived before the report finished.

Index rebuilds helped exactly as often as they happened to refresh the statistics that were the real problem, so statistics get gathered first now and rebuilds need a demonstrated reason. And flushing the shared pool to reset a plan throws away the evidence and starts a hard-parse storm on every node, when a baseline does the same job for free.

The questions an expensive SQL change has to answer before it ships on Exadata

The expensive SQL method above condenses to a short set of questions, and a change that cannot answer all of them waits. Is the pack entitlement confirmed for every view the evidence came from? Is the statement expensive by elapsed time, by resource per row, or by both, and does the fix match the corner it sits in? What is the offload efficiency now, and if it is low, which of the three reasons applies? Does the plan show storage() predicates on the large-table steps, and is the estimate gap explained?

Then the index questions. Was the candidate index tested invisible against the smart scan on the real statement? Does anything else, including a constraint, depend on an index proposed for removal? Is there a fixed baseline or a SQL patch in place as the rollback, with its DROP call written into the change record? And which snapshot, with which execution count, will prove the expensive SQL improved? When those have answers, the change goes.

Working this with MinervaDB

MinervaDB delivers Oracle consulting, 24x7 consultative support and remote DBA for Oracle Database, Exadata and Oracle Cloud, and expensive SQL work on Exadata is a standing part of it: AWR and cell-metric health checks that rank by resource cost as well as elapsed time, index estate reviews that test every candidate against offload before it ships, 19c to 26ai upgrade assessments that capture baselines before the compatibility change, and IORM plan reviews for consolidated racks where one database's batch is another's latency problem. We work alongside your DBAs and your Oracle Support agreement, and product defects go to Oracle with our evidence package attached under your CSI.

Test every query, baseline, patch and index change here on a non-production system with a representative workload before it reaches production, keep RMAN backups and a rehearsed restore path current so that every change has a rollback, and maintain your disaster recovery posture throughout. The expensive SQL method holds across Exadata generations. Which of these moves pays on your workload is something only your own AWR and cell metrics can tell you.