Wait events in AWR

In an Oracle AWR (Automatic Workload Repository) report, wait events indicate where database sessions spend time while not executing. These are crucial for identifying performance bottlenecks. Below is a categorized list of common wait events, with cause, resolution, and examples.

---

🔹 1. DB Time Related Wait Events

Wait Event	Cause	Resolution
DB CPU	Time spent on CPU by Oracle processes.	Indicates work being done. High DB CPU may need SQL/query tuning.
sql*net message from client	Idle wait - session waiting for client request.	Normal. No action needed unless excessive.
sql*net message to client	Idle wait - sending data to client.	Normal. No action needed.

📘 Example: A session shows 80% time in sql*net message from client. This is idle time waiting on the app/user.

---

🔹 2. Concurrency Wait Events

Wait Event	Cause	Resolution
enq: TX - row lock contention	DML operations waiting on row locks held by other sessions.	Tune application to reduce contention. Commit/rollback blockers.
buffer busy waits	Contention for buffers in the buffer cache (multiple sessions).	Tune hot blocks, increase freelists, or partition data.
gc buffer busy acquire/release	RAC: Block contention across nodes.	Tune SQL or change data placement/access pattern.

📘 Example: enq: TX - row lock contention seen due to two sessions updating the same row at the same time.

---

🔹 3. I/O Related Wait Events

Wait Event	Cause	Resolution
db file sequential read	Single-block reads (e.g., index lookups).	Check for inefficient index access. Tune SQL.
db file scattered read	Multi-block reads (e.g., full table scans).	Check for unselective queries or missing indexes.
direct path read/write	Bypassing buffer cache (e.g., large table scans, parallel ops).	Usually expected in batch jobs. Tune if excessive.

📘 Example: High db file sequential read in top SQL → indicates many index lookups. May benefit from full scan if cost-effective.

---

🔹 4. Configuration Wait Events

Wait Event	Cause	Resolution
log file sync	Session waiting for redo flush during COMMIT.	Optimize commit frequency. Check I/O latency.
log file switch (checkpoint incomplete)	Log switch cannot occur as checkpoint not completed.	Increase redo log size. Improve checkpointing.
log buffer space	Log buffer is full; redo not yet written.	Increase redo log buffer size. Reduce commit frequency.

📘 Example: Frequent log file sync indicates slow disk I/O or too many commits.

---

🔹 5. Network Wait Events

Wait Event	Cause	Resolution
SQL*Net more data to/from client	Transferring large amounts of data to/from client.	Optimize network, reduce row size, or use pagination.
SQL*Net break/reset to client	Communication problem (timeout, error).	Review client app error handling and timeout settings.

📘 Example: Large result sets causing SQL*Net more data from client waits.

---

🔹 6. RAC-Specific Wait Events

Wait Event	Cause	Resolution
gc cr request	Global cache request for consistent read.	Optimize queries to reduce block transfers.
gc current block busy	Contention on current blocks in RAC.	Review hot blocks, consider partitioning.
gcs log flush sync	Global cache services syncing redo logs.	Review interconnect latency and redo config.

📘 Example: RAC system with high gc cr request → indicates inter-instance block shipping due to bad data locality.

---

🔹 7. Other Common Wait Events

Wait Event	Cause	Resolution
latch: cache buffers chains	Contention accessing buffer cache.	Tune hot blocks, increase buffer cache.
cursor: pin S wait on X	Library cache concurrency issues.	Review cursor sharing settings. May need to reduce parsing.
read by other session	One session is reading blocks another session will use.	Investigate parallelism or contention.

📘 Example: latch: cache buffers chains might suggest a "hot block" due to a small table with frequent access.

---

📌 How to Read Wait Events in AWR

• Top 5 Timed Events: Shows most impactful waits (in time) during the snapshot interval.
• Wait Class Summary: Categorizes wait events (e.g., I/O, concurrency).
• SQL ordered by Elapsed Time / Wait Time: Pinpoints expensive queries.

---

✅ General Resolution Strategy

1. Find Top Waits in AWR (Top 5 Timed Events).
2. Drill Down to SQL using SQL ordered by... sections.
3. Check Execution Plans for inefficient operations.
4. Tune SQL / Schema / Configuration based on findings.
5. Use tools: ASH, SQL Monitor, SQL Plan Baselines, coe_xfr_sql_profile.sql (for fixing plans).

---