Oracle数据库需重点监控的实用关键指标有哪些？

Hey there! When setting up monitoring for an Oracle database, focusing on the right metrics can save you from headaches down the line—here are the key categories and specific indicators I’d prioritize from my experience:

These are the backbone of identifying bottlenecks and keeping your database running smoothly:

• Wait Events: The gold standard for Oracle performance tuning. Keep an eye on high-frequency waits like:
  • db file sequential read: Tied to single-block reads (often linked to inefficient index usage or table access)
  • db file scattered read: Linked to full table scans—too many of these mean you might need better indexing
  • latch free & enqueue: These point to concurrency issues (e.g., lock contention between transactions)
    Quick check query:

  SELECT event, total_waits, time_waited 
  FROM v$system_event 
  WHERE event LIKE '%read%' OR event IN ('latch free', 'enqueue');
  

• High-Impact SQL: Track queries that drain CPU, IO, or time. Use the v$sqlarea view to spot top offenders by elapsed time, disk reads, or execution count:

  SELECT sql_id, 
         elapsed_time/1000000 AS elapsed_sec, 
         cpu_time/1000000 AS cpu_sec, 
         disk_reads, executions 
  FROM v$sqlarea 
  ORDER BY elapsed_time DESC 
  FETCH FIRST 10 ROWS ONLY;
  

• Instance Throughput: Measure overall database load with transactions per second and logical reads per second. Grab these from v$sysstat:

  SELECT name, value/100 AS per_sec 
  FROM v$sysstat 
  WHERE name IN ('user commits', 'user rollbacks', 'session logical reads');
  

  (Note: Divide by your sampling interval in seconds for accurate per-second rates)

Don’t let CPU, memory, or storage become a bottleneck:

• CPU Usage: Monitor database-level CPU consumption via v$sysstat's CPU used by this session, and cross-reference with OS tools like top to ensure Oracle isn’t hogging resources.
• Memory (SGA/PGA):
  • Buffer Cache Hit Ratio: Aim for 90%+—a lower ratio means too many physical reads. Check it with:

    SELECT 1 - (phy.value / (cur.value + con.value)) AS buffer_cache_hit_ratio 
    FROM v$sysstat cur, v$sysstat con, v$sysstat phy 
    WHERE cur.name = 'db block gets' 
      AND con.name = 'consistent gets' 
      AND phy.name = 'physical reads';
    

  • PGA Usage: Track if your PGA_AGGREGATE_TARGET is sufficient using v$pgastat—if total PGA allocated consistently exceeds the target, you’ll need to adjust the parameter.
• I/O Latency: Slow storage kills performance. Use v$filestat to check average read/write times (anything over 20ms is a red flag):

  SELECT file_name, avg_read_time, avg_write_time 
  FROM v$filestat fs 
  JOIN dba_data_files df ON fs.file# = df.file_id;
  

Blocked sessions and rogue connections can grind your app to a halt:

• Active Sessions: Keep tabs on sessions marked ACTIVE in v$session—long-running active sessions often mean slow queries or uncommitted transactions:

  SELECT username, status, machine, program 
  FROM v$session 
  WHERE status = 'ACTIVE' AND username IS NOT NULL;
  

• Blocking Locks: Detect transactions that are blocking others (a common cause of app timeouts):

  SELECT blocking_session, sid, serial#, username, object_name 
  FROM v$session s 
  JOIN dba_objects o ON s.row_wait_obj# = o.object_id 
  WHERE blocking_session IS NOT NULL;
  

• Idle Sessions: Clean up sessions that have been inactive for hours (e.g., over 3600 seconds) to free up resources.

Running out of space is one of the most preventable database disasters:

• Tablespace Usage: Monitor percentage used for each tablespace—set alerts before they hit 85-90%:

  SELECT tablespace_name, 
         round((used_space/total_space)*100,2) AS usage_percent 
  FROM (
    SELECT tablespace_name, 
           SUM(bytes) AS total_space, 
           SUM(bytes - NVL(free_bytes,0)) AS used_space 
    FROM dba_tablespaces ts 
    LEFT JOIN (
      SELECT tablespace_name, SUM(bytes) AS free_bytes 
      FROM dba_free_space GROUP BY tablespace_name
    ) fs ON ts.tablespace_name = fs.tablespace_name 
    GROUP BY tablespace_name
  );
  

• Redo Log Switch Frequency: If redo logs switch more than once every 15-30 minutes, they’re too small—this causes unnecessary overhead. Check recent switches with:

  SELECT member, first_time, next_time 
  FROM v$log_history 
  ORDER BY first_time DESC 
  FETCH FIRST 10 ROWS ONLY;
  

Don’t skip these—they’re your safety net when things go wrong:

• Backup Success: Verify that RMAN backups complete successfully using v$rman_backup_job_details:

  SELECT start_time, end_time, status, elapsed_seconds 
  FROM v$rman_backup_job_details 
  ORDER BY start_time DESC 
  FETCH FIRST 5 ROWS ONLY;
  

• Archive Log Health: Ensure archive logs are being generated and applied correctly (if using Data Guard), and that the archive directory doesn’t run out of space.

Adjust thresholds based on your specific workload and business needs—what’s normal for a small app might be a red flag for a high-traffic system!

内容的提问来源于stack exchange，提问作者Kohan95