企业官网建设流程全解析

上一篇【第43篇】Oracle ASM磁盘组使用与维护
下一篇【第45篇】Oracle SQL优化基础

摘要

性能监控是Oracle DBA的核心日常工作之一。数据库运行过程中积累的大量性能数据，是发现瓶颈、预判故障、优化系统的宝贵依据。本文系统介绍Oracle性能监控的三大武器：动态性能视图（V$视图）、自动工作负载仓库（AWR）和统计数据包（STATSPACK），结合关键性能指标的解读与实战案例，帮助DBA建立完整的性能监控体系。

一、性能监控基础概念

1.1 为什么要监控数据库性能

数据库性能问题往往不是突然出现的，而是逐渐积累的：

• 被动响应：等到应用报慢才排查，损失已经产生
• 主动监控：提前发现异常趋势，在用户感知前解决问题

Oracle的性能监控体系分为三个层次：

实时监控（V$视图）→ 短期趋势（AWR/STATSPACK）→ 长期基线（性能基线对比）

1.2 性能问题的常见根因

二、动态性能视图（V$视图）

2.1 核心V$视图体系

Oracle提供数百个V$视图，覆盖实例运行的方方面面。以下是性能监控中最常用的一批：

实例整体状态：

-- 数据库运行状态SELECT*FROMv$instance;-- 数据库开库时间（计算运行时长）SELECTstartup_time,ROUND((SYSDATE-startup_time)*24,2)running_hoursFROMv$instance;

关键性能指标快照：

-- Buffer Cache命中率（目标>95%）SELECTROUND(1-(phy.value/(con.value+con.value+cur.value)),4)*100||'%'hit_ratioFROMv$sysstat phy,v$sysstat con,v$sysstat curWHEREphy.name='physical reads'ANDcon.name='db block gets'ANDcur.name='consistent gets';

等待事件实时监控：

-- 当前活跃会话的等待事件SELECTsid,event,wait_class,state,seconds_in_waitFROMv$sessionWHEREstatus='ACTIVE'ANDtype='USER'ANDwait_class!='Idle'ORDERBYseconds_in_waitDESC;

2.2 重要V$视图详解

V$SESSION——会话状态监控

-- 统计各等待事件的活跃会话数SELECTevent,wait_class,COUNT(*)sessionsFROMv$sessionWHEREstatus='ACTIVE'ANDwait_class!='Idle'GROUPBYevent,wait_classORDERBYsessionsDESC;-- 查找长时间运行的SQLSELECTs.sid,s.serial#, s.username,s.status,s.event,ROUND(s.last_call_et/60,1)running_minutes,t.sql_textFROMv$sessionsJOINv$sqltext tONs.sql_hash_value=t.hash_valueWHEREs.status='ACTIVE'ANDs.last_call_et>300-- 运行超过5分钟ORDERBYs.last_call_etDESC;

V$SYSSTAT——系统统计信息

-- 关键系统统计SELECTname,valueFROMv$sysstatWHEREnameIN('db block gets','consistent gets','physical reads','redo size','sorts (disk)','sorts (memory)','table scans (long tables)','parse count (hard)','parse count (total)','user calls','execute count')ORDERBYname;

关键比率计算：

-- 软解析率（目标>95%）SELECTROUND((1-hard_parse/total_parse)*100,2)soft_parse_ratioFROM(SELECTSUM(DECODE(name,'parse count (hard)',value,0))hard_parse,SUM(DECODE(name,'parse count (total)',value,0))total_parseFROMv$sysstatWHEREnameIN('parse count (hard)','parse count (total)'));-- 磁盘排序率（目标<5%）SELECTROUND(disk_sort/(mem_sort+disk_sort)*100,2)disk_sort_ratioFROM(SELECTSUM(DECODE(name,'sorts (disk)',value,0))disk_sort,SUM(DECODE(name,'sorts (memory)',value,0))mem_sortFROMv$sysstatWHEREnameIN('sorts (disk)','sorts (memory)'));

V$FILESTAT——数据文件I/O统计

-- 数据文件I/O统计（找出I/O热点文件）SELECTdf.file#, df.name,fs.phyrdsreads,fs.phywrts writes,fs.readtim read_time_cs,-- 单位：百分之一秒fs.writetim write_time_cs,ROUND(fs.readtim/NULLIF(fs.phyrds,0),4)avg_read_ms,ROUND(fs.writetim/NULLIF(fs.phywrts,0),4)avg_write_msFROMv$datafile dfJOINv$filestat fsONdf.file# = fs.file#ORDERBY(fs.readtim+fs.writetim)DESC;

V$SQL——SQL执行统计

-- Top 10 高负载SQL（按物理读排序）SELECTsql_id,elapsed_time,cpu_time,buffer_gets,disk_reads,executions,ROUND(elapsed_time/NULLIF(executions,0)/1000000,2)avg_elapsed_sec,ROUND(disk_reads/NULLIF(executions,0),0)avg_disk_reads,SUBSTR(sql_text,1,80)sql_previewFROMv$sqlORDERBYdisk_readsDESCFETCHFIRST10ROWSONLY;-- Top 10 耗时SQL（按总消耗时间排序）SELECTsql_id,ROUND(elapsed_time/1000000,2)total_elapsed_sec,executions,ROUND(elapsed_time/NULLIF(executions,0)/1000000,4)avg_sec,SUBSTR(sql_text,1,80)sql_previewFROMv$sqlORDERBYelapsed_timeDESCFETCHFIRST10ROWSONLY;

V$LOCK——锁等待分析

-- 查看当前锁等待情况SELECTl1.sid waiter,l2.sid holder,l1.typelock_type,l1.id1,l1.id2FROMv$lockl1,v$lockl2WHEREl1.block=0ANDl2.block=1ANDl1.id1=l2.id1ANDl1.id2=l2.id2;-- 获取被锁对象详情SELECTs.sid,s.username,o.object_name,o.object_type,l.mode_held,l.mode_requested,l.blockFROMv$sessionsJOINv$locked_object loONs.sid=lo.session_idJOINdba_objects oONlo.object_id=o.object_idJOINv$locklONs.sid=l.sidORDERBYs.sid;

三、AWR（自动工作负载仓库）

3.1 AWR基础配置

AWR是Oracle 10g引入的自动性能数据采集框架，每隔一定时间（默认60分钟）自动拍摄系统性能快照，保留期限默认8天。

-- 查看AWR当前配置SELECTsnap_interval,retention,EXTRACT(HOURFROMsnap_interval)snap_hours,EXTRACT(DAYFROMretention)retention_daysFROMdba_hist_wr_control;-- 修改AWR设置（快照间隔30分钟，保留30天）BEGINdbms_workload_repository.modify_snapshot_settings(retention=>43200,-- 保留时间（分钟），30天=43200interval=>30-- 采集间隔（分钟）);END;/-- 手动创建AWR快照SELECTdbms_workload_repository.create_snapshot()snap_idFROMdual;-- 查看已有快照SELECTsnap_id,begin_interval_time,end_interval_timeFROMdba_hist_snapshotORDERBYsnap_idDESCFETCHFIRST20ROWSONLY;

3.2 生成AWR报告

-- 方式一：使用官方脚本（推荐）-- 在SQL*Plus中执行：@$ORACLE_HOME/rdbms/admin/awrrpt.sql-- 按提示选择报告格式（html/text）、时间范围-- 方式二：使用DBMS_WORKLOAD_REPOSITORY包-- 首先查找报告所需的快照IDSELECTsnap_id,TO_CHAR(begin_interval_time,'YYYY-MM-DD HH24:MI')snap_timeFROMdba_hist_snapshotWHEREbegin_interval_time>=SYSDATE-1ORDERBYsnap_id;-- 生成HTML格式AWR报告SELECT*FROMTABLE(dbms_workload_repository.awr_report_html(l_dbid=>(SELECTdbidFROMv$database),l_inst_num=>1,l_bid=>12345,-- 起始快照IDl_eid=>12350-- 结束快照ID));

3.3 解读AWR报告关键章节

AWR报告包含丰富的性能信息，以下是重点关注的章节：

① Load Profile（负载概述）

Load Profile Per Second Per Transaction ~~~~~~~~~~ ---------- --------------- Redo size: 123,456.78 45,678.90 Logical reads: 8,901.23 3,456.78 Block changes: 234.56 89.01 Physical reads: 45.67 17.89 Physical writes: 12.34 4.56 User calls: 567.89 212.34 Parses: 123.45 46.12 Hard parses: 2.34 0.88

关键指标解读：

• Hard parses/s < 5：超过此值说明存在大量硬解析，需检查绑定变量使用
• Logical reads/s：反映数据库活动量的基本指标
• Physical reads/s：高值表示Buffer Cache命中率低

② Top 5 Timed Events（核心诊断入口）

Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) DB Time Wait Class --------------------------------- -------- ---------- --------- ---------- CPU time 1,234 65.2% db file sequential read 45,678 234.5 12.4% User I/O log file sync 12,345 89.3 4.7% Commit db file scattered read 5,678 45.6 2.4% User I/O read by other session 1,234 23.4 1.2% Concurrency

等待类型诊断指引：

③ SQL Statistics（SQL统计）

AWR报告包含多个SQL排序视角：

• SQL ordered by Elapsed Time：总耗时最多的SQL
• SQL ordered by CPU Time：CPU消耗最多的SQL
• SQL ordered by Gets：逻辑读最多的SQL
• SQL ordered by Reads：物理读最多的SQL
• SQL ordered by Executions：执行次数最多的SQL

3.4 AWR对比分析

-- 对比两个时间段的性能差异@$ORACLE_HOME/rdbms/admin/awrddrpt.sql-- 选择两组快照范围，AWR会生成差异报告

四、STATSPACK

4.1 STATSPACK与AWR的区别

结论：若企业持有Oracle Diagnostics Pack许可证，首选AWR；若没有许可或使用旧版Oracle，使用STATSPACK。

4.2 STATSPACK安装与配置

# 以SYS用户登录安装STATSPACKsqlplus / as sysdba# 执行安装脚本（创建PERFSTAT用户和相关对象）@$ORACLE_HOME/rdbms/admin/spcreate.sql# 按提示输入PERFSTAT密码和表空间# 验证安装SELECT username FROM dba_users WHERE username='PERFSTAT';

配置自动采集快照（创建定时任务）：

-- 连接为PERFSTAT用户CONNECTperfstat/password-- 手动创建快照测试EXECUTEstatspack.snap;-- 配置自动采集（每小时一次）VARIABLE jobno NUMBER;BEGINDBMS_JOB.SUBMIT(:jobno,'statspack.snap;',SYSDATE,'SYSDATE + (1/24)'-- 每1小时执行);COMMIT;END;/

4.3 生成STATSPACK报告

-- 查看已有快照SELECTsnap_id,snap_timeFROMstats$snapshotORDERBYsnap_idDESC;-- 生成报告@$ORACLE_HOME/rdbms/admin/spreport.sql-- 输入起始和结束快照ID-- STATSPACK报告结构与AWR类似，包含：-- Instance Activity Stats（实例活动统计）-- Wait Events（等待事件）-- SQL Statistics（SQL统计）-- Memory Statistics（内存统计）

五、实战案例：数据库性能突降诊断

案例背景

某生产数据库在工作日下午14:00-16:00出现明显性能下降，应用响应时间从200ms增加到3000ms，需要快速定位原因。

诊断步骤

步骤1：查看当前等待事件

-- 实时查看活跃会话等待SELECTevent,COUNT(*)cntFROMv$sessionWHEREstatus='ACTIVE'ANDwait_class!='Idle'GROUPBYeventORDERBYcntDESC;

输出结果：

EVENT CNT ---------------------------------- ---- enq: TX - row lock contention 35 db file sequential read 12 CPU 8

→发现35个会话在等待行锁！锁争用是主因。

步骤2：找出锁源

-- 找出持有锁的会话SELECTDISTINCTs.sid,s.serial#, s.username, s.status,s.last_call_et wait_sec,s.sql_hash_value,s.program,s.machineFROMv$sessions,v$locklWHEREs.sid=l.sidANDl.block=1-- 正在阻塞其他会话ORDERBYs.last_call_etDESC;

步骤3：获取阻塞SQL

-- 获取持有锁的会话正在执行的SQLSELECTs.sid,sq.sql_textFROMv$sessionsJOINv$sqlsqONs.sql_hash_value=sq.hash_valueWHEREs.sid=456;-- 假设阻塞SID为456

步骤4：检查历史AWR

-- 拉取故障时间段的AWR快照SELECTsnap_id,TO_CHAR(begin_interval_time,'HH24:MI')snap_timeFROMdba_hist_snapshotWHEREbegin_interval_timeBETWEENTO_DATE('2024-01-15 13:00','YYYY-MM-DD HH24:MI')ANDTO_DATE('2024-01-15 17:00','YYYY-MM-DD HH24:MI')ORDERBYsnap_id;-- 生成故障时段AWR报告@$ORACLE_HOME/rdbms/admin/awrrpt.sql

步骤5：处理方案

-- 临时处置：终止长时间持有锁的会话ALTERSYSTEMKILLSESSION'456, 12345'IMMEDIATE;-- 长期方案：-- 1. 排查业务逻辑，避免长时间持有行锁-- 2. 减少批处理事务的批次大小-- 3. 确保UPDATE语句使用索引，避免锁行过多

六、建立持续性能监控体系

6.1 关键指标监控脚本

-- 一键性能健康检查SELECT'=== Buffer Cache 命中率 ==='titleFROMdualUNIONALLSELECTROUND((1-(SELECTvalueFROMv$sysstatWHEREname='physical reads')/(SELECTvalueFROMv$sysstatWHEREname='db block gets')+(SELECTvalueFROMv$sysstatWHEREname='consistent gets'))*100,2)||'%'FROMdualUNIONALLSELECT'=== 当前活跃会话数 ==='FROMdualUNIONALLSELECTTO_CHAR(COUNT(*))FROMv$sessionWHEREstatus='ACTIVE'UNIONALLSELECT'=== 前5个等待事件 ==='FROMdualUNIONALLSELECTevent||': '||cntFROM(SELECTevent,COUNT(*)cntFROMv$sessionWHEREstatus='ACTIVE'ANDwait_class!='Idle'GROUPBYeventORDERBYcntDESC)WHEREROWNUM<=5;

6.2 AWR基线管理

-- 创建性能基线（在系统状态良好时建立）BEGINdbms_workload_repository.create_baseline(start_snap_id=>12345,end_snap_id=>12360,baseline_name=>'Normal_Workday_Jan2024');END;/-- 查看已有基线SELECTbaseline_id,baseline_name,start_snap_id,end_snap_idFROMdba_hist_baseline;-- 基于基线生成对比报告@$ORACLE_HOME/rdbms/admin/awrblmig.sql

七、总结

性能监控不是"一次性"工作，而是需要建立持续监控→定期分析→主动优化的闭环。建议DBA：

1. 建立性能基线：在系统正常时记录关键指标的正常范围
2. 设置告警阈值：Buffer命中率低于95%、活跃会话超过N个时自动告警
3. 定期审查AWR：每周回顾AWR趋势，识别性能变化
4. 问题记录归档：将每次性能事件的诊断过程和解决方案记录在案

上一篇【第43篇】Oracle ASM磁盘组使用与维护
下一篇【第45篇】Oracle SQL优化基础

参考资料

• Oracle Database Performance Tuning Guide 11g Release 2
• Oracle Database Administrator’s Guide 11g Release 2
• Oracle Database Reference 11g（V$视图完整列表）
• Oracle MOS Note: AWR Report Interpretation Guide