Performance Engineering Interview Questions #14

Following are the Performance Engineering Interview Questions Set #14.

Q. 66 What is the difference between ‘db file sequential read’ and ‘db file scattered read’ wait events?

Ans: The ‘db file sequential read’ indicates single-block reads, often associated with index lookups whereas ‘db file scattered read’ involves multi-block reads, typically from full table scans or index fast full scans.

Q. 67 In an AWR report, how would high ‘db file sequential read’ wait to be interpreted?

Ans: It suggests significant time spent on single-block I/O operations, possibly due to inefficient index usage or excessive index range scans.

Q. 68 What might cause high ‘db file scattered read’ wait times?

Ans: A high ‘db file scattered read’ wait time indicates heavy multi-block reads, often due to full table scans. The reasons are missing indexes, large table sizes, or inefficient query plans.

Q. 69 How can you use the AWR report to determine if the database is experiencing I/O bottlenecks?

Ans: High wait times for I/O-related events (e.g. db file sequential read) and high average wait times indicate potential I/O bottlenecks. Additionally, the ‘IO Profile’ section provides I/O throughput metrics.

Q. 70 What is the significance of the ‘Buffer Busy Waits’ event?

Ans: It occurs when multiple sessions attempt to access the same block in the buffer cache, but the block is currently being read or modified by another session. High occurrences suggest contention in the buffer cache.