This content originally appeared on DEV Community and was authored by Pranav Bakare
ASH (Active Session History)
Think of ASH like a CCTV camera recording live activity inside the database. It captures what each active session is doing at any given second — which SQL_ID is running, what wait events are happening, and if there’s any blocking session. For example, if a PL/SQL batch job is running now and users complain that the system has slowed down, ASH can immediately show that the job is waiting on a row lock in a specific table or consuming heavy I/O. This makes ASH the first tool to check for real-time bottlenecks.
AWR (Automatic Workload Repository)
AWR is more like a daily ledger or periodic report that the database maintains automatically. It takes snapshots every hour and records workload history — top SQL queries, CPU usage, I/O patterns, and execution plans. For example, if a data migration procedure ran fine yesterday but became slow today, AWR reports can compare the two snapshots and reveal that the execution plan changed, or the query started doing full table scans instead of using indexes. AWR is best for analyzing past performance issues and long-term trends.
ADDM (Automatic Database Diagnostic Monitor)
ADDM acts like a financial auditor or expert advisor who reviews the ledger (AWR snapshots) and gives clear recommendations. Instead of just showing numbers, it tells you the impact and what actions to take. For example, ADDM may analyze an AWR report and highlight: “SQL_ID xyz is consuming 75% of DB time, suggest creating an index on column A to improve performance by 50%.” This makes ADDM extremely useful for a PL/SQL developer because it converts raw data into actionable tuning advice that directly addresses performance bottlenecks.
This content originally appeared on DEV Community and was authored by Pranav Bakare
Pranav Bakare | Sciencx (2025-08-26T17:18:28+00:00) ASH | AWR | ADDM in Oracle Database. Retrieved from https://www.scien.cx/2025/08/26/ash-awr-addm-in-oracle-database/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.