AWR & Diagnostics

AWR reports are simultaneously the most powerful and most misread performance tool in Oracle’s arsenal. Most DBAs jump straight to Top SQL, copy the SQL_ID, and start tuning — skipping the sections that tell them why that SQL became a problem in the first place. This case study walks through a realistic AWR from a production Exadata system, section by section, using a deliberately constructed problematic scenario. Every number is fabricated for illustrative purposes; every diagnostic conclusion is grounded in real Oracle internals. ...

Performance diagnostics without a methodology is archaeology — you dig until you find something interesting, not necessarily something important. Oracle’s AWR and ASH provide an extraordinarily detailed record of database activity, but interpreting them requires a structured approach. This article presents a top-down methodology that moves from macro to micro: DB Time → Wait Profile → Top SQL → Root Cause. The Foundation: DB Time DB Time is the total time all sessions spent working in the database (on CPU or waiting for database resources) during a snapshot period. It is the single most important number in the AWR report. ...

Oracle SQL Monitor is the most powerful single-query diagnostic tool in the Oracle performance toolkit. Unlike DBMS_XPLAN.DISPLAY_CURSOR, which gives you a static execution plan, SQL Monitor captures real-time runtime statistics at every step of the execution plan — rows processed, memory used, temporary I/O, CPU time, and elapsed time per operation. For parallel queries, it shows the workload distribution across parallel server processes. When SQL Monitor Activates Automatically SQL Monitor begins monitoring a SQL statement automatically when either: ...