Oracle 26ai AWR Enhancements for DBAs

 AWR used to be simple when most databases were single-instance, single-tenant, and all important workload stayed on the primary database.

That world is mostly gone.

Today, one CDB may host multiple PDBs. Reporting may run on Active Data Guard. Read traffic may be pushed toward True Cache. Replication may be handled through GoldenGate or XStream. When performance breaks, the first question is no longer just “what was the top wait event on the database?”

The better question is:

Which container, which workload, which replica, and which component actually caused the pain?

That is where the Oracle 26ai AWR enhancements become useful. They do not magically tune the database, but they reduce one common DBA problem: missing diagnostic data from the place where the issue happened.

Why AWR Needed to Change

In older environments, AWR was often treated as a primary database diagnostic tool. You generated the report, checked DB Time, top SQL, wait events, IO stats, and moved from there.

That worked reasonably well when the primary handled everything.

But in multitenant and offloaded architectures, this approach creates blind spots. A PDB can suffer from poor SQL while the CDB-level report looks acceptable. A standby can be overloaded by reporting queries while the primary AWR shows nothing unusual. Replication can lag because of apply-side SQL, disk latency, or memory pressure, but the normal AWR report may not clearly separate replication workload from application workload.

Oracle has started closing these gaps by making AWR more aligned with real deployment patterns: PDB-level diagnostics, ADG standby snapshots, True Cache visibility, and replication-focused sections.

Oracle 26ai AWR Enhancements in Multitenant

The most useful change for many DBAs is around PDB-level AWR collection.

Oracle documentation states that AWR_PDB_AUTOFLUSH_ENABLED has a default value of TRUE, which means automatic AWR snapshots are enabled by default for all PDBs in a CDB. Oracle also documents that automatic snapshot operations are enabled by default for a CDB and, in Oracle Database 26ai, for a PDB.

That sounds small, but operationally it matters.

Earlier, a DBA could troubleshoot a PDB performance issue only to realize that PDB-level AWR was never enabled. The incident would then move into guesswork: ASH if available, SQL monitor if still present, application timestamps, OS metrics, and whatever was left in memory.

Now the expectation is different. PDBs are first-class performance units.

Useful checks:

show con_name

select name, value
from v$parameter
where name = 'awr_pdb_autoflush_enabled';

select snap_id, dbid, instance_number, begin_interval_time, end_interval_time
from dba_hist_snapshot
order by snap_id desc
fetch first 10 rows only;

For a specific PDB, connect into the PDB and validate snapshot behavior:

alter session set container = SALES_PDB;

select snap_id, begin_interval_time, end_interval_time
from dba_hist_snapshot
order by snap_id desc
fetch first 10 rows only;

The production impact is clear: when one PDB is noisy, you do not want to diagnose only from the CDB root. Consolidated environments hide problems. A batch job in one PDB can create IO pressure, CPU starvation, library cache pressure, or temp usage that hurts other tenants.

The trade-off is also real. More snapshots mean more diagnostic data. More diagnostic data means more SYSAUX usage. DBAs should monitor retention and space growth instead of assuming "default-enabled" means "free."

select occupant_name, space_usage_kbytes/1024 mb
from v$sysaux_occupants
where occupant_name like '%AWR%'
order by space_usage_kbytes desc;
fetch first 10 rows only;

Active Data Guard AWR: Finally Closer to Real Standby Troubleshooting

Active Data Guard is often sold as a way to offload read workload. In reality, many standby systems slowly become reporting platforms, ETL sources, dashboard backends, and ad-hoc query zones.

Then someone says: "The query is slow on standby but fast on primary." That is where DBAs need standby-side evidence.

Oracle AI Database 26ai documents AWR for Active Data Guard with automatic snapshots at CDB root and all PDBs, and Oracle states it is enabled by default for Active Data Guard in 26ai. Oracle’s observability blog also describes 26ai as delivering out-of-the-box AWR for ADG standby workloads, reducing older operational overhead around standby diagnostics.

This helps because standby performance problems are not always SQL tuning problems. Common causes include:

• redo apply competing with reporting queries
• storage latency on standby
• stale or different optimizer behavior
• read consistency waits
• heavy parallel queries
• lag-sensitive application reads

Useful standby checks:

select database_role, open_mode
from v$database;

select name, value, unit
from v$dataguard_stats
where name in ('transport lag','apply lag');

select process, status, thread#, sequence#
from v$managed_standby
order by process;

AWR on standby gives DBAs a cleaner way to separate “bad SQL” from “standby workload pressure.” That distinction matters. Tuning the SQL may help, but if redo apply is blocked by IO or CPU saturation, the real fix is capacity, workload isolation, or scheduling.

True Cache Monitoring: Useful, But Do Not Treat Cache as Magic

Oracle True Cache is designed as an in-memory, consistent cache for Oracle Database workloads, helping offload read activity from the primary database. Oracle introduced True Cache with Oracle Database 23ai, and Oracle’s current True Cache guide is published under Oracle AI Database 26ai.

AWR support for True Cache is useful because cache layers are often dangerous when they become invisible. If the application says “database is slow,” the real issue may be cache miss behavior, routing, memory pressure, or fallback to primary.

A DBA should not only ask whether True Cache is configured. The better questions are:

• Is the cache actually serving the workload?
• Is the application falling back to primary too often?
• Is memory sized correctly?
• Are read-mostly SQL patterns really suitable for cache offload?

Useful direction for monitoring:

select * from v$true_cache_stat;

The exact columns and usefulness will depend on version and RU level, so DBAs should validate against their installed Oracle documentation and not blindly copy scripts between estates.

The operational warning is simple: cache reduces pressure only when the workload matches the design. If the application sends mixed read-write behavior, uses session-specific logic heavily, or frequently misses cache, True Cache may add another diagnostic layer instead of reducing complexity.

Image Idea:
Diagram showing Application -> Connection Pool -> True Cache for read-only workload, with fallback arrow to Primary Database. Add labels: “cache hit,” “fallback,” “primary pressure,” and “AWR visibility.”

Replication Reporting: Better Visibility for GoldenGate and XStream

Replication problems are rarely explained by one metric.

GoldenGate lag may be caused by extract, pump, network, trail file IO, replicat apply SQL, locking, missing indexes, or target-side resource pressure. Traditional database AWR may show waits, but not always in a way that clearly tells the replication story.

Oracle GoldenGate documentation for 26ai describes AWR report sections for replication, including Replication Top Wait Events, with foreground and background process wait events aggregated by event type and Oracle GoldenGate SQL module.

That is useful because replication workload should not be mixed blindly with application workload. A top SQL statement from Replicat has a different operational meaning from an application SQL_ID. One may require target index tuning. Another may require application tuning. Another may require changing batch size or parallelism.

Typical checks still matter:

select event, total_waits, time_waited
from v$system_event
where event like '%GoldenGate%'
   or event like '%Streams%'
order by time_waited desc;

For database-side replication sessions:

select sid, serial#, program, module, event, wait_class, seconds_in_wait
from v$session
where program like '%replicat%'
   or program like '%extract%'
   or module like '%GoldenGate%'
order by seconds_in_wait desc;

AWR does not replace GoldenGate commands or Microservices UI. It complements them. DBAs still need to check trail files, checkpoints, lag, discard files, and target-side constraints.

Image Idea:
Diagram showing Source DB -> Extract -> Trail Files -> Replicat -> Target DB. Add AWR boxes around Extract SQL, Replicat SQL, wait events, and resource usage. Label: “Replication lag is not one metric.”

New Diagnostic Views DBAs Should Notice

Oracle 26ai also adds or highlights diagnostic views that help DBAs go deeper during troubleshooting.

DBA_HIST_SNAPSHOT_DETAILS displays table details for snapshots in the Workload Repository and is available starting with Oracle AI Database 26ai.

V$SQL_HISTORY displays SQL statements tracked by SQL history monitoring, but it is populated only when SQL_HISTORY_ENABLED is set to TRUE.

Example:

select sql_id, elapsed_time, cpu_time, rows_processed
from v$sql_history
order by elapsed_time desc
fetch first 20 rows only;

For optimizer environment investigations:

select name, value
from dba_hist_optimizer_env_details
where optimizer_env_hash_value = :hash_value
order by name;

These views are not a replacement for ASH, SQL Monitor, trace files, or execution plan history. But they give DBAs more breadcrumbs, especially when the problem has already passed and nobody captured live evidence.

The limitation is retention and configuration. If SQL history is not enabled, or AWR retention is too short, you still lose visibility.

Image Idea:
Diagram showing troubleshooting timeline: Incident Time -> SQL History -> AWR Snapshot Details -> ASH/AWR Report -> Root Cause. Label missing data points as “lost if not enabled or retained.”

Production Failure Scenarios

Scenario 1: Slow Report on ADG Standby

A reporting query runs in 3 minutes on primary but takes 25 minutes on standby. The application team blames the optimizer.

AWR on standby shows high IO waits and redo apply pressure during the same window. Data Guard stats show apply lag increasing. The SQL is not perfect, but it is not the only issue.

Fix may involve moving the report window, limiting parallelism, tuning standby storage, or separating heavy reporting from redo apply-sensitive periods.

Scenario 2: One PDB Creates CDB-Wide Noise

CPU spikes at CDB level, but the business impact is reported only by one application. PDB-level AWR shows a batch SQL consuming most DB time inside one PDB.

Without PDB snapshots, the DBA may chase system-wide symptoms. With PDB AWR, the noisy tenant becomes visible.

Scenario 3: GoldenGate Lag Misread as Network Lag

Replication lag increases. Network team finds no issue. AWR replication section shows Replicat-related wait events and top SQL on the target.

Root cause: missing index on target table after schema change.

Fix: create the required index, validate Replicat apply rate, and add schema drift checks.

Scenario 4: True Cache Does Not Reduce Primary Load

A new True Cache deployment is expected to reduce primary reads. Primary load remains high.

Cache monitoring shows many reads still going to primary due to routing and workload pattern mismatch. The fix is not “increase cache memory” blindly. The connection routing and SQL eligibility need review.

Mini Case Study: The Standby Was Not Slow, It Was Busy

A finance reporting workload was moved to an ADG standby to protect the primary OLTP system. For the first few weeks, everything looked fine. Then month-end processing started.

Symptoms:

• reports timing out
• apply lag increasing
• storage latency spikes
• no major issue visible in primary AWR

Initial assumption was bad SQL. But standby-side diagnostics showed that reporting queries and redo apply were fighting for the same IO bandwidth. A few parallel reports were consuming resources at exactly the wrong time.

Fix:

• reduced parallel degree for selected reports
• moved month-end report schedule away from peak redo apply
• added standby-specific AWR review after batch windows
• monitored apply lag and top standby SQL together

Lesson:

A standby is not free capacity. It is a recovery system doing read workload. Treat it casually and it will remind you during the worst possible window.

DBA Insights

The biggest mistake DBAs make with AWR is treating it as a report generator instead of a diagnostic system.

AWR is only useful when snapshots exist at the right level, retention covers the incident window, and the DBA knows which workload they are analyzing.

For Oracle 26ai AWR enhancements, I would watch these areas carefully:

• SYSAUX growth after enabling more PDB-level snapshots
• snapshot interval and retention standards across CDBs
• ADG standby AWR availability before moving reporting workload
• Replicat SQL visibility during replication lag
• True Cache hit behavior before claiming primary offload success
• monitoring dashboards that still show only CDB-level averages

Averages are dangerous in multitenant systems. AWR becoming more PDB-aware is a good thing, but DBAs must update their troubleshooting habits too.

FAQs

Is PDB-level AWR enabled by default in Oracle 26ai?

Yes. Oracle documentation states that AWR_PDB_AUTOFLUSH_ENABLED defaults to TRUE, enabling automatic AWR snapshots for PDBs by default.

Can I use AWR on Active Data Guard standby?

Yes. Oracle 26ai supports automatic AWR snapshots for Active Data Guard at CDB root and PDB levels, and Oracle documents this as enabled by default for ADG in 26ai.

Does AWR replace GoldenGate monitoring?

No. AWR helps correlate database waits, SQL, and resource usage for replication workload. GoldenGate lag, trail files, checkpoints, discard files, and process health still need GoldenGate-side monitoring.

Should I increase AWR retention after these enhancements?

Usually yes, but carefully. More PDB and standby snapshots can increase repository usage. Monitor SYSAUX and define retention based on your incident review window.

Is True Cache always useful for read workload?

No. It helps when workload routing and SQL patterns fit the cache model. If the application frequently falls back to primary or uses unsuitable access patterns, the benefit may be limited.

Conclusion

Oracle 26ai AWR enhancements make AWR more useful for how Oracle databases are actually deployed today: multitenant, replicated, standby-readable, and cache-assisted.

For DBAs, the value is not just automation. The value is evidence.

PDB-level snapshots help avoid CDB-level guesswork. ADG AWR helps troubleshoot where reporting queries really run. True Cache monitoring gives visibility into read offload behavior. Replication sections make GoldenGate and XStream workload easier to separate from normal application SQL.

This is a strong improvement in Oracle’s diagnostic stack. But it still needs DBA discipline: validate snapshots, watch SYSAUX, review retention, and make sure monitoring follows the workload.

AWR is getting smarter. DBAs still need to ask the right questions.