Oracle performance monitoring tools

Oracle performance monitoring is essential for identifying bottlenecks in SQL, memory, I/O, CPU, and network layers. In enterprise environments, you typically combine Oracle-native tools + OS tools + infrastructure monitoring to get a full picture.

Below is a practical, production-focused guide to Oracle performance monitoring tools.

1. Oracle native performance monitoring tools (core tools)

🔹 1. AWR (Automatic Workload Repository)

Most important Oracle performance tool.

It captures:

Top SQL
Wait events
CPU usage
I/O latency
Memory usage trends

👉 Used for historical performance analysis (baseline vs current)

🔹 2. ASH (Active Session History)

Real-time session-level monitoring:

Shows:

What sessions are doing right now
Blocking sessions
Wait events per session

👉 Best for diagnosing live performance issues

🔹 3. ADDM (Automatic Database Diagnostic Monitor)

Oracle’s automated performance advisor.

It identifies:

Root causes of performance problems
SQL bottlenecks
Memory and I/O issues

👉 Think of it as “Oracle performance AI”

🔹 4. Oracle Enterprise Manager (OEM)

Oracle Enterprise Manager is a full enterprise monitoring platform.

It provides:

Real-time dashboards
SQL monitoring
Alerts and notifications
RAC cluster monitoring
Capacity planning

👉 Best for enterprise-wide Oracle monitoring

🔹 5. V$ dynamic performance views

Low-level real-time views:

Examples:

V$SESSION → active sessions
V$SYSTEM_EVENT → system wait events
V$SQL → top SQL queries
V$IOSTAT → I/O statistics

👉 Used by DBAs for deep troubleshooting

2. SQL performance monitoring tools

🔹 SQL Tuning Advisor

Suggests indexes
Rewrites queries
Improves execution plans

🔹 SQL Access Advisor

Recommends indexing strategies
Improves query access paths

🔹 SQL Monitor

Real-time execution tracking
Shows long-running SQL

3. RAC-specific monitoring tools

If using Oracle RAC:

🔹 Cluster Health Monitor (CHM)

Monitors cluster nodes
Detects node-level issues

🔹 Global Cache Service stats

Tracks:

gc buffer busy
interconnect latency
cache fusion efficiency

🔹 CRSCTL / SRVCTL tools

Used for:

Cluster services status
Node health
RAC configuration

4. Operating system monitoring tools (Linux / AIX)

Linux tools:

🔹 iostat

Disk I/O performance
Latency per device

🔹 vmstat

CPU + memory pressure
Swap usage

🔹 sar

Historical performance trends

🔹 top / htop

CPU and process usage

AIX tools:

🔹 nmon (very important)

CPU, memory, disk, network in one view

🔹 vmstat / iostat / lparstat

AIX-specific performance metrics

5. Storage monitoring tools

🔹 ASM monitoring

If using Oracle ASM:

Disk group performance
Rebalance activity
I/O distribution

🔹 NVMe / disk tools

nvme-cli (Linux)
SAN monitoring tools

6. Advanced enterprise monitoring tools

🔹 1. Grafana + Prometheus (modern stacks)

Real-time dashboards
Custom Oracle metrics integration

🔹 2. Third-party monitoring tools

Dynatrace
Datadog
New Relic
SolarWinds

👉 Provide full-stack observability (DB + app + infra)

🔹 3. Cloud monitoring (OCI/AWS/Azure)

For Oracle Cloud:

OCI Monitoring (metrics + alarms)
Performance Hub (Oracle Cloud DB tool)

7. Key performance metrics to monitor

🔴 CPU metrics

CPU usage per session
Run queue length

🔴 Memory metrics

SGA hit ratio
PGA usage
Swap activity (must be near zero)

🔴 I/O metrics

IOPS
latency (ms)
redo log write time
temp file usage

🔴 SQL metrics

Top SQL by CPU
Top SQL by I/O
Execution time

🔴 Wait events (most important in Oracle)

db file sequential read
db file scattered read
log file sync
gc buffer busy (RAC)
enq: TX - row lock

8. Monitoring strategy (enterprise approach)

✔ Step 1: Baseline creation

Capture AWR snapshots during peak hours

✔ Step 2: Real-time monitoring setup

ASH + OEM dashboards

✔ Step 3: OS + DB correlation

Match Linux/AIX metrics with Oracle wait events

✔ Step 4: Alerting system

CPU threshold alerts
I/O latency alerts
blocking session alerts

✔ Step 5: Continuous tuning loop

Identify top SQL weekly
Optimize queries
Monitor improvements

9. Common monitoring mistakes

🔴 Only monitoring CPU

Misses real bottlenecks (usually I/O or SQL)

🔴 Ignoring wait events

Oracle performance is mostly wait-event driven

🔴 Not using AWR baseline

No comparison → no tuning direction

🔴 No SQL-level monitoring

80% of issues come from bad SQL

10. Best practices summary

✔ Use AWR for historical analysis
✔ Use ASH for real-time debugging
✔ Use OEM for enterprise dashboards
✔ Monitor wait events first (not CPU)
✔ Track top SQL continuously
✔ Correlate OS + DB metrics
✔ Set proactive alerts
✔ Always maintain performance baseline

Final takeaway

Oracle performance monitoring is not a single tool—it is a layered observability stack combining AWR, ASH, OEM, OS tools, and SQL-level diagnostics.

The most effective DBA strategy is:

Identify slow SQL (first priority)
Correlate wait events (second priority)
Fix I/O or memory bottlenecks (third)
Validate improvements via AWR comparison