Oracle Performance Engineering Guide: AWR, ASH and SQL Monitor in 19c and 23ai
Production was slow. Users were complaining. The application team blamed the database. I had 30 minutes to find the root cause before the CTO joined the war room call.
I opened an AWR report. Buffer busy waits: 87% of total wait time. But which sessions? Which objects? AWR doesn't tell you. That's when I switched to ASH. Three sessions hammering the same table block. SQL Monitor showed me the exact query execution plan with real-time wait events. Problem identified: missing index on a foreign key. Index created. Performance restored. Crisis averted.
AWR, ASH, and SQL Monitor are Oracle's three essential performance diagnostic tools. AWR gives you the big picture over time. ASH shows you what's happening right now at the session level. SQL Monitor reveals how individual queries execute in real-time. Together, they form the complete performance troubleshooting toolkit that every Oracle DBA needs to master.
This guide covers real production troubleshooting techniques using AWR, ASH, and SQL Monitor in Oracle 19c and 23ai. If you're tired of guessing at performance problems or spending hours digging through logs, these are the diagnostic queries and analysis patterns that actually work.