Oracle（138）如何监控数据库性能？

监控数据库性能是确保Oracle数据库高效运行的关键任务。性能监控涉及多个方面，包括系统资源使用情况、等待事件、SQL执行情况等。以下是一些常用的方法和SQL查询示例，帮助你监控Oracle数据库的性能。

1. 监控系统资源使用情况

1.1 检查CPU使用情况

SELECT * FROM v$osstat WHERE stat_name IN ('NUM_CPUS', 'LOAD');

1.2 检查内存使用情况

SELECT * FROM v$sga;
SELECT * FROM v$pgastat;

2. 监控等待事件

等待事件是Oracle性能调优的重要指标，反映了数据库在等待哪些资源。

2.1 当前等待事件

SELECT event, total_waits, time_waited
FROM v$system_event
ORDER BY time_waited DESC;

2.2 活跃会话的等待事件

SELECT sid, event, wait_time, seconds_in_wait
FROM v$session_wait
WHERE wait_time != 0;

3. 监控SQL执行情况

了解哪些SQL语句占用了最多的资源，有助于进行SQL调优。

3.1 最耗资源的SQL语句

SELECT sql_id, executions, cpu_time, elapsed_time, sql_text
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

3.2 SQL执行计划

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', child_number => 0));

4. 监控I/O性能

I/O性能是数据库性能的关键因素之一。

4.1 数据库文件I/O统计信息

SELECT file_name, phyrds, phywrts, readtim, writetim
FROM v$filestat a, dba_data_files b
WHERE a.file# = b.file_id;

4.2 表空间I/O统计信息

SELECT tablespace_name, file_id, read_time, write_time, phy_reads, phy_writes
FROM v$filestat, dba_data_files
WHERE v$filestat.file# = dba_data_files.file_id;

5. 监控锁和并发

锁和并发问题会显著影响数据库性能。

5.1 检查当前锁情况

SELECT session_id, lock_type, mode_held, mode_requested, lock_id1, lock_id2
FROM dba_locks;

5.2 检查会话等待的锁

SELECT blocking_session, sid, wait_class, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

6. 使用AWR（Automatic Workload Repository）报告

AWR报告提供了全面的数据库性能数据。

6.1 生成AWR报告

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/

DECLARE
  l_dbid     NUMBER;
  l_inst_num NUMBER;
  l_btime    DATE;
  l_etime    DATE;
  l_rpt_id   NUMBER;
BEGIN
  SELECT dbid, instance_number INTO l_dbid, l_inst_num FROM v$instance;
  l_btime := SYSDATE - 1/24; -- 1 hour ago
  l_etime := SYSDATE;
  l_rpt_id := DBMS_WORKLOAD_REPOSITORY.awr_report_text (
                l_dbid, l_inst_num, l_btime, l_etime, 0);
  DBMS_OUTPUT.put_line('AWR report ID: ' || l_rpt_id);
END;
/

7. 使用Oracle Enterprise Manager（OEM）

OEM是Oracle提供的图形化管理工具，提供了丰富的监控和管理功能。

7.1 访问OEM

通过浏览器访问OEM控制台，通常URL格式为：

http://<hostname>:<port>/em

8. 使用Statspack

Statspack是Oracle提供的另一种性能监控工具，适用于不使用AWR的环境。

8.1 安装和配置Statspack

@$ORACLE_HOME/rdbms/admin/spcreate.sql

8.2 生成Statspack报告

EXEC STATSPACK.SNAP;

总结

监控Oracle数据库性能需要综合使用系统资源监控、等待事件分析、SQL执行监控、I/O性能监控、锁和并发监控等多种方法。通过使用上述SQL查询和工具（如AWR、OEM、Statspack），可以全面了解数据库的性能状况，及时发现和解决性能问题。