1. DB Time Much Higher Than DB CPU

Pattern

• DB Time significantly greater than DB CPU

Meaning

Sessions are mostly waiting, not using CPU.

Likely Causes

• Disk I/O bottleneck
• Lock contention
• Network delays

Example

Metric	Value
DB Time	8000 sec
DB CPU	1200 sec

This clearly indicates wait-based performance issues.

---

2. High db file sequential read

Pattern

Top Timed Event shows:

db file sequential read

Meaning

Excessive single-block reads, usually from index access.

Common Causes

• Inefficient index lookups
• Missing indexes
• Poor execution plans
• Nested loop joins scanning large tables

DBA Action

• Review SQL execution plan
• Consider index optimization

---

3. High db file scattered read

Pattern

db file scattered read

Meaning

Large full table scans.

Causes

• Missing indexes
• Poor optimizer statistics
• Reporting queries scanning large tables

Action

• Add indexes
• Partition tables
• Rewrite SQL

---

4. High log file sync

Pattern

Top wait event:

log file sync

Meaning

Sessions waiting for commit operations.

Causes

• Too many commits
• Slow redo log I/O
• Application committing inside loops

Typical Fix

• Batch commits
• Improve storage for redo logs

---

5. enq: TX - row lock contention

Pattern

enq: TX - row lock contention

Meaning

Transactions waiting for row locks.

Causes

• Long running transactions
• Multiple sessions updating the same rows
• Poor application design

DBA Action

• Identify blocking session
• Review application logic

---

6. Very High Buffer Gets for a SQL

Pattern

In SQL ordered by Gets section:

SQL ID	Buffer Gets
xyz123	900M

Meaning

Query reading massive number of blocks.

Causes

• Bad join conditions
• Missing indexes
• Inefficient execution plan

Action

• SQL tuning
• Index creation
• Optimizer statistics update

---

7. High Hard Parse Rate

Pattern

Instance Activity Stats show:

parse count (hard)

Very high relative to executions.

Meaning

Database frequently compiling SQL.

Causes

• Application not using bind variables
• Dynamic SQL generation

Impact

• CPU overhead
• Library cache contention

Solution

Use bind variables.

---

8. Average Active Sessions Higher Than CPU Count

Pattern

Average Active Sessions graph shows values higher than CPU cores.

Example:

CPU cores	16
AAS	45

Meaning

CPU is overloaded.

Causes

• Excessive SQL workload
• Parallel queries
• Poorly tuned queries

Action

• Identify top SQL
• Optimize queries
• Consider CPU scaling

---

9. High direct path read or direct path write

Pattern

direct path read
direct path write

Meaning

Large data operations bypassing buffer cache.

Causes

• Parallel queries
• Large scans
• Data warehouse operations

Interpretation

Often normal for ETL or batch workloads, but problematic in OLTP systems.

---

10. Sudden Spike in DB Time

Pattern

Comparing two AWR snapshots:

Snapshot	DB Time
9–10 AM	800 sec
10–11 AM	9000 sec

Meaning

Workload spike or runaway query.

Investigation

Check:

• SQL ordered by elapsed time
• Session activity
• Application batch jobs

---

The Senior DBA Mental Model

Experienced DBAs essentially map AWR signals → root causes:

AWR Signal	Likely Root Cause
DB Time >> CPU	Wait events
db file sequential read	Index I/O
db file scattered read	Full table scans
log file sync	Commit latency
TX row lock contention	Blocking
High buffer gets	Bad SQL
High hard parses	Missing bind variables
AAS > CPU cores	CPU saturation
direct path read/write	Large scans
Sudden DB time spike	Batch job / bad query

---

Why These Patterns Matter

For experienced DBAs, performance diagnosis becomes a pattern-recognition exercise. Instead of scanning hundreds of report lines, they quickly identify the dominant signal, then trace it back to the responsible SQL or system component.

This disciplined reading of AWR reports transforms troubleshooting from guesswork into a structured investigation grounded in evidence.