上一篇【第43篇】Oracle ASM磁盘组使用与维护下一篇【第45篇】Oracle SQL优化基础摘要性能监控是Oracle DBA的核心日常工作之一。数据库运行过程中积累的大量性能数据是发现瓶颈、预判故障、优化系统的宝贵依据。本文系统介绍Oracle性能监控的三大武器动态性能视图V$视图、自动工作负载仓库AWR和统计数据包STATSPACK结合关键性能指标的解读与实战案例帮助DBA建立完整的性能监控体系。一、性能监控基础概念1.1 为什么要监控数据库性能数据库性能问题往往不是突然出现的而是逐渐积累的被动响应等到应用报慢才排查损失已经产生主动监控提前发现异常趋势在用户感知前解决问题Oracle的性能监控体系分为三个层次实时监控V$视图→ 短期趋势AWR/STATSPACK→ 长期基线性能基线对比1.2 性能问题的常见根因问题类型典型症状常用诊断工具CPU瓶颈CPU使用率持续高位V$SESSION, AWR Top事件I/O瓶颈等待事件以I/O为主V$FILESTAT, AWR内存不足大量物理读Buffer命中率低V$BUFFER_POOL_STATISTICS锁争用大量enq等待事件VL O C K , V LOCK, VLOCK,VSESSIONSQL效率差Top SQL消耗大量资源V$SQL, AWR Top SQL网络延迟SQL*Net等待事件突出V$SESSION_WAIT二、动态性能视图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.valuecon.valuecur.value)),4)*100||%hit_ratioFROMv$sysstat phy,v$sysstat con,v$sysstat curWHEREphy.namephysical readsANDcon.namedb block getsANDcur.nameconsistent gets;等待事件实时监控-- 当前活跃会话的等待事件SELECTsid,event,wait_class,state,seconds_in_waitFROMv$sessionWHEREstatusACTIVEANDtypeUSERANDwait_class!IdleORDERBYseconds_in_waitDESC;2.2 重要V$视图详解V$SESSION——会话状态监控-- 统计各等待事件的活跃会话数SELECTevent,wait_class,COUNT(*)sessionsFROMv$sessionWHEREstatusACTIVEANDwait_class!IdleGROUPBYevent,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_valuet.hash_valueWHEREs.statusACTIVEANDs.last_call_et300-- 运行超过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_sortdisk_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.readtimfs.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.block0ANDl2.block1ANDl1.id1l2.id1ANDl1.id2l2.id2;-- 获取被锁对象详情SELECTs.sid,s.username,o.object_name,o.object_type,l.mode_held,l.mode_requested,l.blockFROMv$sessionsJOINv$locked_object loONs.sidlo.session_idJOINdba_objects oONlo.object_ido.object_idJOINv$locklONs.sidl.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(retention43200,-- 保留时间分钟30天43200interval30-- 采集间隔分钟);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_timeSYSDATE-1ORDERBYsnap_id;-- 生成HTML格式AWR报告SELECT*FROMTABLE(dbms_workload_repository.awr_report_html(l_dbid(SELECTdbidFROMv$database),l_inst_num1,l_bid12345,-- 起始快照IDl_eid12350-- 结束快照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等待类型诊断指引等待类型含义处理方向CPU timeCPU执行时间非等待关注执行效率、算法db file sequential read单块读索引扫描检查索引使用情况db file scattered read多块读全表扫描检查是否缺索引log file syncLGWR将日志写入磁盘I/O优化、减少提交频率buffer busy waits缓冲区竞争调整PCTFREE分区热块enq: TX-row lock contention行锁争用检查长事务和锁等待③ SQL StatisticsSQL统计AWR报告包含多个SQL排序视角SQL ordered by Elapsed Time总耗时最多的SQLSQL ordered by CPU TimeCPU消耗最多的SQLSQL ordered by Gets逻辑读最多的SQLSQL ordered by Reads物理读最多的SQLSQL ordered by Executions执行次数最多的SQL3.4 AWR对比分析-- 对比两个时间段的性能差异$ORACLE_HOME/rdbms/admin/awrddrpt.sql-- 选择两组快照范围AWR会生成差异报告四、STATSPACK4.1 STATSPACK与AWR的区别特性AWRSTATSPACK版本支持Oracle 10g及以上Oracle 8i及以上许可要求需要Diagnostics Pack许可免费包含在基础版中功能丰富度非常丰富基础功能安装方式自动安装手动安装数据存储SYS.WRH$_*PERFSTAT.*结论若企业持有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 usernamePERFSTAT;配置自动采集快照创建定时任务-- 连接为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 StatisticsSQL统计-- Memory Statistics内存统计五、实战案例数据库性能突降诊断案例背景某生产数据库在工作日下午14:00-16:00出现明显性能下降应用响应时间从200ms增加到3000ms需要快速定位原因。诊断步骤步骤1查看当前等待事件-- 实时查看活跃会话等待SELECTevent,COUNT(*)cntFROMv$sessionWHEREstatusACTIVEANDwait_class!IdleGROUPBYeventORDERBYcntDESC;输出结果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.sidl.sidANDl.block1-- 正在阻塞其他会话ORDERBYs.last_call_etDESC;步骤3获取阻塞SQL-- 获取持有锁的会话正在执行的SQLSELECTs.sid,sq.sql_textFROMv$sessionsJOINv$sqlsqONs.sql_hash_valuesq.hash_valueWHEREs.sid456;-- 假设阻塞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处理方案-- 临时处置终止长时间持有锁的会话ALTERSYSTEMKILLSESSION456, 12345IMMEDIATE;-- 长期方案-- 1. 排查业务逻辑避免长时间持有行锁-- 2. 减少批处理事务的批次大小-- 3. 确保UPDATE语句使用索引避免锁行过多六、建立持续性能监控体系6.1 关键指标监控脚本-- 一键性能健康检查SELECT Buffer Cache 命中率 titleFROMdualUNIONALLSELECTROUND((1-(SELECTvalueFROMv$sysstatWHEREnamephysical reads)/(SELECTvalueFROMv$sysstatWHEREnamedb block gets)(SELECTvalueFROMv$sysstatWHEREnameconsistent gets))*100,2)||%FROMdualUNIONALLSELECT 当前活跃会话数 FROMdualUNIONALLSELECTTO_CHAR(COUNT(*))FROMv$sessionWHEREstatusACTIVEUNIONALLSELECT 前5个等待事件 FROMdualUNIONALLSELECTevent||: ||cntFROM(SELECTevent,COUNT(*)cntFROMv$sessionWHEREstatusACTIVEANDwait_class!IdleGROUPBYeventORDERBYcntDESC)WHEREROWNUM5;6.2 AWR基线管理-- 创建性能基线在系统状态良好时建立BEGINdbms_workload_repository.create_baseline(start_snap_id12345,end_snap_id12360,baseline_nameNormal_Workday_Jan2024);END;/-- 查看已有基线SELECTbaseline_id,baseline_name,start_snap_id,end_snap_idFROMdba_hist_baseline;-- 基于基线生成对比报告$ORACLE_HOME/rdbms/admin/awrblmig.sql七、总结工具最佳使用场景核心价值V$视图实时诊断、紧急故障秒级响应直击现象AWR定期分析、性能趋势历史对比发现规律STATSPACK无Diagnostics Pack许可低成本替代方案性能监控不是一次性工作而是需要建立持续监控→定期分析→主动优化的闭环。建议DBA建立性能基线在系统正常时记录关键指标的正常范围设置告警阈值Buffer命中率低于95%、活跃会话超过N个时自动告警定期审查AWR每周回顾AWR趋势识别性能变化问题记录归档将每次性能事件的诊断过程和解决方案记录在案上一篇【第43篇】Oracle ASM磁盘组使用与维护下一篇【第45篇】Oracle SQL优化基础参考资料Oracle Database Performance Tuning Guide 11g Release 2Oracle Database Administrator’s Guide 11g Release 2Oracle Database Reference 11gV$视图完整列表Oracle MOS Note: AWR Report Interpretation Guide