Report Overview and Core Metrics

An Automatic Workload Repository (AWR) report requires a minimum of two snapshots to generate meaningful delta statistics. These snapshots must belong to the same database instance without an intermediate restart. The report calculates metrics by comparing the end snapshot values against the start snapshot values, reflecting the workload intensity over the elapsed time window.

Key Time Metrics:

• Elapsed Time: The actual wall-clock duration between snapshots.
• DB Time: Represents the total time spent by all foreground sessions executing database calls. This encompasses CPU time, I/O wait time, and other non-idle wait events. High DB Time indicates heavy system load but does not necessarily correlate linearly with response time perceived by the user.
• Average Active Sessions (AAS): Calculated as DB Time / Elapsed Time. An AAS greater than the nummber of CPUs suggests queuing or saturation. For example, if DB Time accumulates significantly faster than real time passes, the system is heavily loaded beyond its processing capacity.

Memory Configuration and Load Profile

SGA Parameters

Memory management strategies include ASMM (SGA_TARGET), AMM (MEMORY_TARGET), and MSMM. In ASMM/AMM configurations, components like the Buffer Cache and Shared Pool fluctuate dynamically. Persistent shrinking of the Shared Pool can trigger row cache locks due to locking during shrink operations. Conversely, continuous growth often indicates insufficient sizing for current parsing demands.

Throughput and Transaction Analysis

The Load Profile section provides insights into system throughput normalized per second and per transaction:

• Redo Generation: Indicates DML frequency. High redo volume pressures LGWR and archiving I/O.
• Logical Reads: Reflects the volume of data accessed from the buffer cache. Excessive reads increase CPU usage and potential latch contention on cache buffers chains.
• Physical I/O: Disk reads and writes. While disk speed varies, excessive physical reads often suggest missing indexes or undersized cache.
• Parsing Activity: Hard parses should ideally remain below 20 per second to avoid library cache mutex contention. Soft parse percentages above 95% are generally desirable.

Instance Efficiency Percentages

Most efficiency metrics aim for 100%. Below thresholds indicate potential optimization targets:

Buffer Management Details: Inversions of BUFFER NOWAIT % reveal contention for free buffers. If this drops, envestigate inefficient SQL or insufficient cache size. The formula relies on v$waitstat counts compared against session logical reads.

Shared Pool Health: A low Library Hit ratio often stems from memory fragmentation or insufficient free memory. Ensuring adequate free space prevents objects from being aged out prematurely. Binding variables is critical here to reduce hard parsing.

Wait Event Analysis

Wait classes categorize why processes pause execution. Focus on high-frequency and long-duration waits.

Foreground Waits:

• Concurrency: Latch misses (e.g., library cache, shared pool) or Enqueue locks (enq: TX - row lock).
• User I/O: Direct block reads (db file sequential/scattered read) or direct path operations.
• Commit: Often dominated by log file sync if commits are frequent and log buffer flushing is slow.

Background Waits: These reflect internal maintenance activities like checkpointing (db file parallel write), control file updates (control file parallel write), and recovery tasks.

Time Model Statistics

This hierarchy breaks down where database time is spent. Since metrics accumulate, totals may exceed 100% due to nesting.

• SQL Execute Elapsed: Time running queries.
• Parse Time: Split into successful and failed parses. High failure rates may indicate Out Of Shared Memory errors (ORA-04031).
• PL/SQL: Compilation overhead vs. execution time.
• Connection Management: Costs associated with opening/closing sessions; high values suggest connection pooling issues.

SQL Performance Profiling

AWR identifies top consumers using several sorting criteria:

1. By Elapsed Time: Identifies queries consuming the most total resources. Good for spotting long-running batch jobs.
2. By CPU Time: Highlights computation-heavy statements requiring tuning of algorithms or indexes.
3. By Logical Reads: Pinpoints queries taxing the Buffer Cache. Reducing these usually improves overall responsiveness.
4. By Physical Reads: Indicates IO bottlenecks potentially solvable via caching or storage improvements.
5. By Parsing Calls: Reveals applications suffering from lack of bind variables or frequent plan changes.
6. By Version Count: High version counts (VERSION_COUNT > 20) indicate optimizer environment changes preventing cursor sharing, leading to unnecessary hard parsing.

System and Resource Statistics

Operating System Metrics

Compare OS stats with database stats to isolate root causes.

• Load Average: High OS load alongside high AAS confirms resource saturation.
• CPU Utilization: Differentiate between User Time (database work) and System Time (kernel overhead).
• I/O Wait: High IOWAIT_TIME points to storage subsystem limitations regardless of database configuration.

PGA and Shared Pool Advisories

These sections simulate parameter adjustments to predict impact.

• PGA Advisory: Helps determine optimal PGA_AGGREGATE_TARGET to minimize disk sorts (sorts (disk)). Look for minimal spill count predictions.
• SGA Targets: Adjusting SGA_TARGET can influence DB Time via buffering. Larger caches generally reduce physical reads but require careful memory management.
• Shared Pool Advisory: Estimates library cache hit rates at different sizes. Insufficient pool size leads to more reloads.

Instance Recovery and Checkpoints

Review FAST_START_MTTR_TARGET settings against actual recovery times. High checkpoint activity (checkpoint incomplete waits) implies log files are too small relative to commit frequency, forcing synchronous checkpoints that stall users.

RAC-Specific Indicators

For Real Application Clusters, interconnect performance and cache synchronization are vital.

High gc cr multi block request wait times often signal network saturation or contention on global cache service latches.

Segment-Level Stats

Drill down to specific tables or indexes to identify hot spots.

• Table Scans: Full scans on large tables are visible here; optimize with indexes.
• Block Changes: High rates of DML on a single object suggest contention opportunities.
• Direct Path Reads/Writes: Significant volumes imply either bulk operations or parallel query usage.

Resource Limits

Monitor Resource Limit Stats to detect if constraints on processes or sessions are hitting ceilings (LIMIT column). Values approaching the limit indicate the need for profile adjustment.

Dictionary Cache

Row cache contention manifests as row cache lock waits. High miss ratios in dc_sequences or dc_objects suggest frequent metadata changes or locking issues. Verify sequence usage patterns.

Mutex Activity

Introducing finer-grained locking in modern vesrions reduces latch contention. High sleep counts on mutexes like KKS: Cursor Stat may indicate heavy parsing or binding mismatches.