Index unique scan is the fastest access path Oracle’s optimizer can choose — it returns exactly one row using a single B-tree traversal, with zero ambiguity. If you see INDEX UNIQUE SCAN in an execution plan, that part of the query is already optimal. This post shows the execution plan on a primary key lookup, Read more about Optimizer Access Paths: index unique scan[…]
Oracle direct path read fires when the optimizer decides a full table scan should bypass the buffer cache and read directly into PGA memory. Seeing it as a top wait event in AWR doesn’t automatically mean a problem — but understanding the threshold, the parameters, and the monitoring queries determines whether it’s working for you Read more about Oracle Direct Path Read: Complete Performance Guide with Real Examples[…]
A production AWR report flagged a query with an INDEX FULL SCAN operation consuming significant I/O. The developer assumed it was an index range scan gone wrong. It was not. The optimizer had a reason — and understanding that reason is what separates reactive troubleshooting from deliberate tuning. Tested on: Oracle Database 19c (19.28) · Read more about Oracle Index Full Scan: When and Why It Fires[…]