数据库性能分析报告.doc

人口、执法办案数据库性能分析报告目 录1版本信息22概述33检查操作系统和数据库状态33.1检查操作系统占用情况和数据库配置33.2部署Statspack性能分析工具34Statspack分析报告44.1物理IO分析44.2负载指标54.3TOP 5 Timed Events64.4TOP 5 SQL（物理读）64.5其他SQL94.6表扫描分析95总结及建议101 概述2008年7月20日接分局反应小型机状态异常有死机情况发生，我方工程师在周六只用TOPAS对IO情况进行了监控，并未改变服务器的任何设置。在集成商的后续处理操作中我方工程师进行了全力配合。2008年7月21日了解了具体情况，按照数据库无法连接但小机基本正常的情况，首先加大了操作系统的“每用户连接数”参数。随后，我公司工程师立即开始从以下几方面分析处理该问题。2 检查操作系统和数据库状态此次事件涉及区两台数据库服务器。项目服务器1服务器2机器名称Jlp_sjserver1Jlp_sjserver2操作系统AIX 5.2AIX 5.2IP地址*数据库Oracle Oracle 文件系统类型文件系统文件系统数据库用户oracleoracleHOME目录/home/oracle/home/oracleSIDjlputf8JLPZFBAjlputf8JLPZFBA字符集UTF8ZHS16GBKUTF8ZHS16GBK应用系统人口系统协同平台人口系统协同平台2.1 检查操作系统占用情况和数据库配置2008年7月21日，登录小型机通过TOPAS工具查看，可以看到hdisk2、hdisk3使用率均达到100%，即系统I/O存在瓶颈。注：小机频繁切换的问题与I/O问题应该关系不大，建议对操作系统、硬件等进行全面的诊断。2.2 部署Statspack性能分析工具针对本次问题，初步判断数据库性能存在一定的问题，故我方工程师在人口库和执法办案数据库部署了Statspack性能分析工具。3 Statspack分析报告我公司工程师对人口和执法办案数据库从2008年7月21日10:03:05至10:11:27时间段（即I/O最繁忙的时间段）的STATSPACK Report作了简要分析。3.1 物理IO分析以2008年7月21日为例，物理IO趋势图如下所示：分析：从趋势图中我们可以看出，人口系统的物理读高峰在9.8万左右，而执法办案数据库的物理读在284万左右，约为人口系统的29倍，故*坡数据库I/O问题主要由执法办案数据库引起。3.2 负载指标执法办案Load Profile Per Second Per Transaction - - Redo size: 17,347.86 7,120.71 Logical reads: 32,669.80 13,409.84 Block changes: 122.69 50.36 Physical reads: 2,461.47 1,010.35 Physical writes: 12.62 5.18 User calls: 260.98 107.12 Parses: 38.06 15.62 Hard parses: 3.99 1.64 Sorts: 9.40 3.86 Logons: 0.02 0.01 Executes: 134.30 55.12 Transactions: 2.44 % Blocks changed per Read: 0.38 Recursive Call %: 41.78 Rollback per transaction %: 70.24 Rows per Sort: 53.28人口系统Load Profile Per Second Per Transaction - - Redo size: 11,057.20 12,668.65 Logical reads: 14,379.50 16,475.13 Block changes: 38.09 43.65 Physical reads: 134.11 153.65 Physical writes: 5.99 6.87 User calls: 109.26 125.19 Parses: 16.14 18.49 Hard parses: 6.41 7.35 Sorts: 2.46 2.82 Logons: 0.02 0.02 Executes: 18.23 20.89 Transactions: 0.87 % Blocks changed per Read: 0.26 Recursive Call %: 23.32 Rollback per transaction %: 1.15 Rows per Sort: 307.27从上述两表可以看到：1. 执法办案数据库，物理读为19.23MB/s2. 人口系统数据库，物理读为1.05MB/s结论：执法办案数据库物理读过高。3.3 TOP 5 Timed Events执法办案Top 5 Timed Events % TotalEvent Waits Time (s) Ela Time- - - -db file scattered read 114,643 871 42.91buffer busy waits 71,366 667 32.86db file sequential read 30,834 156 7.70CPU time 152 7.49enqueue 41 117 5.75从Top 5 Timed Events可以得知，db file scattered read（文件离散读）通常与全表扫描有关。需要通过检查来确定全表扫描是否必需的来进行调整。Buffer busy waits，当缓冲区以一种非共享方式或者如正在被读入到缓冲时，就会出现该等待。该值不应该大于1%，先需要与db file scattered read联合处理，如不是热块造成可考虑使用反转索引或更小的block。3.4 TOP 5 SQL（物理读）SQL ordered by Reads for DB: JLPZFBA Instance: JLPZFBA Snaps: 1 -2- End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value- - - - - - - 225,597 53 4,256.5 18.3 4.96 201.82 4070633241Module: aspnet_wp.exeSELECT COUNT(*) as RecCount FROM message WHERE (IsDel = 0) AND (shijian 0 AND (INSTR(BuTiShi, :b1) = 0 OR BuTiShi is null) AND WanChengRen is null 145,335 39 3,726.5 11.8 3.99 200.18 3165106039Module: aspnet_wp.exeSELECT * FROM (SELECT * FROM Message WHERE IsDel = 0 AND (TRIM(WanChengRen) IS NULL) AND INSTR(DX_USERBUMENROLE,;:|:b1|,;:) 0 AND (INSTR(BuTiShi,:b1) = 0 OR BuTiShi is null) ORDER BY ShiJian DESC) WHERE ROWNUM 11 85,887 60 1,431.5 7.0 2.99 197.29 784299083Module: aspnet_wp.exeINSERT INTO MQ_MESSAGE (IIDD, USER_ID, USER_NAME, TITLE, CONTENT, URL, SRC_User_ID, SRC_USER_NAME, SRC_DEP_ID, SRC_DEP_Name, SEND_TIME, MESSA 85,831 5 17,166.2 6.9 2.69 195.73 3186094732Module: LSAMQ.MQListenWinService.exeUPDATE MQ_Message Set IsShow=0 where JieJingBH=:b1 And Sys_Src_ID= 002 49,750 1 49,750.0 4.0 1.69 114.22 1006745214Module: LSAMQ.MQListenWinService.exeInsert into Message(DANWEI,ISDEL,LIANJIE,SHIJIAN,BIAOTI,DUIXIANG,TIANFAREN,FROMBIAO,TIANFASHIJIAN,IIDD,FROMIIDD,LEIXING,ANJIANID) values(:DANWEI,:ISDEL,:LIANJIE,:SHIJIAN,:BIAOTI,:DUIXIANG,:TIANFAREN,:FROMBIAO,:TIANFASHIJIAN,:IIDD,:FROMIIDD,:LEIXING,:ANJIANID)TOP1 SQL：hash_value=4070633241SELECT COUNT(*) as RecCount FROM lsa_jiulongpo.message WHERE (IsDel = 0) AND (shijian 0 AND (INSTR(BuTiShi, :b1) = 0 OR BuTiShi is null) AND WanChengRen is null分析：该sql语句对message表存在全表扫描，会造成大量的物理读。 建议：根据应用情况，选择过滤量最大的列建立索引或函数索引。TOP3 SQL：hash_value=784299083INSERT INTO MQ_MESSAGE (IIDD, USER_ ID, USER_NAME, TITLE, CONTENT, URL, SRC_User_ID, SRC_USER_NAME, SRC_DEP_ID, SRC_DEP_Name, SEND_TIME, MESSA GEDATE, ANJIANID)VALUES (:b1, :b13, :b12, | :b2 | :b11 | | :b3, :b4, :b10 | :b5, :b9, GetUser_XM_ByBH(:b9), :b8, GetBuMeni_MC_ByBuMenID(:b8), :b6, SYSDATE, :b7)分析：两个函数中含有查询，且存在全表扫描。 建议：优化SQL减少物理读。TOP4 SQL：hash_value=784299083UPDATE lsa_jiulongpo.MQ_Message Set IsShow = 0 where JieJingBH = :b1 And Sys_Src_ID = 002分析：更新数据时对MQ_Message存在全表扫描。 建议：为JieJingBH或Sys_Src_ID建立B-Tree索引或位图索引。3.5 其他SQLSELECT WFFZRY_ZP AS BUSINESS_TABLE_NAME, to_char(a.time_stamp, yyyymmddhh24miss) AS Time_Stamp, a.KEYVALUE as RYBH, 1 as ZPBH, (select xingming | .JPG from lsa_jiulongpo.xs_xianyiren where bianhao = replace(a.keyvalue, R, X) and rownum to_date(20080721094418, yyyy-mm-dd hh24miss)分析：UPLOADIMG存在全表扫描。 建议：为JieJingBH或Sys_Src_ID建立B-Tree索引或位图索引。以上仅为部分SQL，其他类似SQL不一一列举。3.6 表扫描分析以7月21日为例：No.NameValue1table scans (long tables)15,0602table scans (short tables)1,068,366从上表可以