Tag: tag

DBA_HIST_ACTIVE_SESS_HISTORY query Oracle ASH session history

DBA_HIST_ACTIVE_SESS_HISTORY: Complete Guide to Oracle ASH Analysis

Posted on by Sanjeeva Kumar

Introduction Have you ever been asked to explain why your Oracle database was slow last Tuesday at 2:30 PM? Or needed to identify which SQL statements were causing CPU spikes during last night’s batch run? This is where DBA_HIST_ACTIVE_SESS_HISTORY becomes your most powerful diagnostic tool. Recently, we faced a production issue where users reported intermittent Read more about DBA_HIST_ACTIVE_SESS_HISTORY: Complete Guide to Oracle ASH Analysis[…]

Oracle Database Memory Monitoring Guide

Posted on by Sanjeeva Kumar

Introduction Database memory management is crucial for maintaining optimal performance in Oracle databases. Inefficient memory usage can lead to slower query execution, increased I/O operations, and poor application response times. This comprehensive guide provides database administrators and developers with practical tools and techniques for monitoring and optimizing Oracle database memory components. Why Memory Monitoring Matters Read more about Oracle Database Memory Monitoring Guide[…]

Oracle Index Unique Scan — B-tree path from root to single rowid

Optimizer Access Paths: index unique scan

Posted on by Sanjeeva Kumar

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 Index Fast Full Scan — multiblock I/O across all leaf blocks, no table access

Oracle Index Fast Full Scan: When the Optimizer Reads the Entire Index

Posted on by Sanjeeva Kumar

Last Updated: June 2026Tested On: Oracle 19c Your AWR shows INDEX FAST FULL SCAN on a query that runs every 5 minutes. The index exists. The query looks fine. But something feels off — why is Oracle reading the entire index instead of doing a range scan? Sound familiar? This post explains exactly what Oracle Read more about Oracle Index Fast Full Scan: When the Optimizer Reads the Entire Index[…]

Oracle direct path read buffer cache bypass PGA

Oracle Direct Path Read: Complete Performance Guide with Real Examples

Posted on by Sanjeeva Kumar

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[…]

Oracle index full scan traversal path through B-tree index structure from root to leaf blocks

Oracle Index Full Scan: When and Why It Fires

Posted on by Sanjeeva Kumar

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[…]