南通系统性能调优与评估报告

南通社保系统性能调优与诊断报告共 21 页 / 第 1 页南通社保系统性能调优与诊断报告东软集团股份有限公司2013 年 12 月南通社保系统性能调优与诊断报告共 21 页 / 第 2 页目 录1、 系统性能问题与解决概述 .32、 系统运行现状与性能问题分析 .32.1 从系统错误日志角度分析 32.2 从系统参数角度分析 42.3 从系统资源角度分析 42.4 从应用程序效率角度分析 .53、 优化方法与结果 .53.1 资源调优 .53.2 软件调优 .74、 下一步系统优化建议 .84.1 资源调整 .84.2 应用调整 .95、 系统维护建议 .95.1 空间方面 95.2 操作系统资源方面 95.3 系统应用阻塞处理 106、 业务模块 SQL 语句调优 .106.1 解决放大镜弹出窗口录入查询慢问题 .106.2 灵活就业人员实收分配 .146.3 解决养老应付核定慢问题 .156.4 加快每个业务提交记录日志时间 .15南通社保系统性能调优与诊断报告共 21 页 / 第 3 页南通社保系统性能调优与诊断报告共 21 页 / 第 4 页1、 系统性能问题与解决概述自 2013 年 10 月以来，南通核心业务系统各模块，尤其是批量业务相继报告越来越慢，尤其养老应付核定更是慢的离谱，由自 2012 年 5 月系统上线的 12 分钟到13 年 11 月份的 12 小时。因此东软公司特就此现象组织专门队伍对业务及服务器运行情况进行跟踪分析，总体评估是南通社保系统硬件平台资源无法高性能地满足业务高峰时段的办公要求，调整与改造已经迫在眉睫。同时由于一年多的数据积累，以及新区县的并入，无论从业务量到数据量都有了较快的增长，这样对原有的个别语句和模式提出了更高要求，通过对系统资源的调整，程序的优化使得多数主要业务已经完全满足要求。2、 系统运行现状与性能问题分析南通业务系统的主数据库运行在两台 16cores ，64g 内存的 ibm570 上，目前运行核三 3.0 系统，主要包括五险和和劳动就业等，几乎所有的业务系统都运行在这两台服务器上，另外还有两台 ibm550 运行医院的托管 his，数据库的版本为 10.2.0.5，运行的是 oracle rac。业务系统性能出现严重问题的原因有很多，需要逐个的来排查，主要有以下几个方面：2.1 从系统错误日志角度分析 是否有硬件错误？#errpt 未发现系统有明显的硬件错误，初步排除硬件故障问题。 是否有系统网络丢包现象？#netstat -in未发现有丢包现象，排除。南通社保系统性能调优与诊断报告共 21 页 / 第 5 页 是否有数据库的严重错误？通过 oracle alert 日志等分析，未发现严重的系统错误日志。 是否有数据库 bug？经发现有 oracle 系统的 bug，但经过在 oracle support 网站查询该错误，发现仅仅是一个垃圾输出问题，并不影响系统性能。因此也排除这个问题。2.2 从系统参数角度分析 操作系统参数#prtconf经分析未发现明显的参数问题。 数据库参数sqlshow parameter经排查，数据库参数设置基本合理，没有大的问题。 2.3 从系统资源角度分析 通过系统工具 topas 观察发现系统资源明显短缺主要体现在内存和 I/O 资源匮乏上，以下几点说明：1、通过操作系统工具观察高峰时段发现，db1 64g 内存几乎耗尽，一般剩余几 M 或十几 M，db2 略有剩余。2、db1 系统交换空间使用了 18g ，占整体 32g 的 57%，db2 还好，但也有 20%的swap 使用率，这对数据库类型业务来说是极其糟糕的，明显体现内存不足，一般理想业务系统不可超过 10%，否则用户感觉系统很慢。3、磁盘一直保持繁忙，cpu 等待 I/O 情况很多。4、db1 cpu 利用率高峰一般在 70-80%左右，db2 也能达到 60-70%，虽然没有体现cpu 严重资源不足， 但 cpu 利用率一旦过 70%，前台的响应速度也会受到影响。 通过统计发现两台数据库服务器会话过多，严重影响资源利用南通社保系统性能调优与诊断报告共 21 页 / 第 6 页#ps -ef|grep ora|wc发现两台 db 每个的会话连接数都达到 1200 上下，而激活会话不过十几个到几十个，多余的连接占用内存资源，数据库也需要维护。 通过观察发现 db1 比 db2 上多出两个资源使用来源一个是数据库复制软件 dsg，动态实时做复制，另外一个是在 db1 节点上还有另外一个数据库在运行，版本是 oracle9i，主要是老库，用来备用查询老数据，它的 sga 区占 4g，有 100 个左右的连接，占用空间有 2T。 通过观察数据库表空间发现资源不足通过观察发现业务用户的两个主要的表空间 data 和 idx 都已经接近 90%，这对I/O 数据的访问带来很大的影响，一般理想情况应该不超过 60%。2.4 从应用程序效率角度分析通过数据库 awr 报告分析，数据库内部慢的等待事件如下图：如上图抓拍的等待视图所示，对系统事件等待情况进行分析，发现系统等待事件最多的是 cpu，这说明 cpu 的运算量比较大，需要重点分析。其次单块读占 50%多。说明等待事件主要是索引和表 rowid 读。这些读动作其实都是正常 I/O 操作，而存储的南通社保系统性能调优与诊断报告共 21 页 / 第 7 页读写性能直接影响这个指标，也影响到系统的响应速度。一般来说为了减少上述等待事件，都采取两个动作，一个是调整索引访问路径，使用合理的索引，消除不必要的索引，消除索引碎片，调整索引和表的存储参数等。二是调整存储性能，消除硬件瓶颈。3、 优化方法与结果3.1 资源调优主要在以下三方面 消除多余数据库连接通过减少应用服务器中间件连接池的连接数配置，由原来的每台 1200 左右连接，减少到现在的每台 600-700 个连接，预计每台因此释放的内存资源至少有 5g 以上。这在很大程度上缓解的内存资源的使用，也会使 oracle 对 swap 区的使用大大减少，调整后资源如下图，db1 的 swap 区由原来 57%下降到 29%。南通社保系统性能调优与诊断报告共 21 页 / 第 8 页 表空间调整由于快到年终结转，单位应收核定等，会产生大量数据，经协调临时增加部分空间，已达到缓解的目的，由原来的表空间利用率 89%下降的 81%，这样短缺内基本够用，但 i/o 性能问题还是不能得到充分缓解。迁移3.2 软件调优由于现场主要反馈的是业务模块运行较慢，模拟测试调优比较困难，所以我们基本采用让业务人员实际运行业务我们后台监控运行的方式来进行，同时在运行结束后我们再利用 oracle 的 awr 报告反馈的运行期间各种统计信息，进一步分析到底哪个包、哪个过程、哪个语句消耗资源，消耗什么资源。软件调优根据现场运行情况和业务周期来进行：南通社保系统性能调优与诊断报告共 21 页 / 第 9 页12 月 17-21 日，主要对通用查询、到账、通用税票、业务日志进行分析排查和解决12 月 23-28 日，主要针对征集、养老核定、到账实收排查和解决第一周涉及到所有应用问题的分析和排查，具体过程如下：第一周解决的问题：1、在通用查询和个别性能低下语句的排查中，发现了很多前台传参数和后台调用类型不匹配的问题，主要是 number 型和 varchar 型的不匹配，这样导致数据库执行计划的改变，不能用上索引，执行效率低下。通过调整后性能明显改观。2、发现一些不能正确利用索引的现象比如一些给定索引条件在外层，内层语句条件无法使用，通过调整获得明显改善。3、发现有些功能没有用上，但语句还关联这些视图，如到账模块，南通没用上压缩表，但却关联相关条件，导致性能大幅下降。通过调整好，性能大幅得到改善。第一周遗留的问题：1、涉及灵活就业人员单位编号的查询问题跟踪发现，同样的 sql，在输入不同 aab001 单位编号时执行计划是不同的，对于像灵活就业人员这种大单位（有好几个是 6-7 万人） ，无论是 ab01，还是 ab07、ab09等，它们的数据量产生的太大，正常关联这些表的 aab001 的查询是无法走索引的，而其他单位则可以。因此无法做进一步调优。2、到账实收跟踪到账实收性能问题比较棘手，一个是偶尔一天才有几笔业务，有时一天都没有，因此需跟业务人员定好时间才能进行。另外，跟踪发现总共有四个语句是影响性能的关键，在内循环中，执行量比较大，等待事件比较多，如果得到改善那将大大提高效率，但这些语句的执行计划没问题，因此比较郁闷。第二周涉及到上一周应用问题继续分析和排查以及新业务的跟踪，具体过程如下：1、征集南通社保系统性能调优与诊断报告共 21 页 / 第 10 页征集告诉我们的时候，都说是比较慢，每每发现启动后都有大量的 row lock，但后来我们发现很多时候 oracle rac 的两个节点都有相同这些业务锁，而且还有很多 gc cr request 等锁，这说明是两个会话在争夺资源，在杀掉这些资源锁后，我们让业务人员再做一次业务，发现很快，30 分钟做完。经分析，发现很多时候在系统高峰阶段，资源比较匮乏，业务人员在做批量业务时感觉比较慢，甚至前台界面都灰掉，他们就关掉界面，然后再另开一个界面接着做，这样又一次新提交产生，锁就出现了，我们上周就发现业务人员连续提交了三次相同的征集动作，导致大量的行锁存在。解决办法是批量最好等资源不忙时做，如果真的无法出来，让我们后台人员先清掉占用资源然后再做。否则会堆积很多锁更加重了系统的资源消耗。2、养老应付核定这个问题一开始也是没好的解决办法，就是慢。但跟开发沟通后，打算现场跟踪调试，结果发现，循环体有个最重要的条件字段在系统内未启用，每次支付核定时BAE074 值默认都为0 ，这样导致大量的相同的值，但 oracle 并未给出全表扫描，使用的是索引，因此给大家的感觉执行计划没问题，实际上每次读取的数据量却是非常大的，因此，很慢。后来对这些 0 重新按日期做 update，系统由原来的 12 小时提高到半小时。3、实收划账（第一周遗留问题）这个在这周有点眉目了，经开发确认，程序中存在在一次实收操作多个灵活就业人员时，内循环中更新每个人的 AB09 时使用的 BAZ002 是相同的。在实收下一个人时，由于传入参数只有一个，并且是相同的 BAZ002，.这样如果一次实收人数较多时，同一次业务操作多次循环， AB09 会被重复更新。因此导致每次循环多读取、跟新数据。系统变慢了，现在程序正在更新测试。 。 。 ，下周应该解决了。第二周遗留的问题灵活就业人员单位人员再细化分成小单位，以保证相关模块的快速查询。南通社保系统性能调优与诊断报告共 21 页 / 第 11 页4、 下一步系统优化建议目前系统处于平台资源基本饱和工作情况，我们对影响性能的 top-sql 做了分析调优工作，但目前代码的总体优化率已经很高了，个别语句的调整空间不大了，不能解决整体性能问题。为了能使系统能维持工作，特提出以下几点调优建议：4.1 资源调整 尽快完成对 db1 上 oracle9i 的迁移工作目前通过调整使得系统存储资源剩余有所提升，但利用率仍然达到 81%多，这样的情况对系统整体性能仍然面临考验，尤其 2014 年 1 月份，春节前可能提前做批量业务，这对系统资源又是一次冲击。因此，尽快完成 oracle9i 老库的迁移工作，以使系统资源得到充分改善，db1 节点能够释放 4g 的物理内存，同时 2T 的存储空间可以加入到业务的 data 和 idx 表空间，这样对整体系统性能的提升有着明显的好处，而且对下月的批量业务也就没有担心的必要了。 着眼未来，希望能更新设备，缓解目前各种平台资源的不足的问题。4.2 应用调整将灵活就业人员单位分拆。这个问题是个核心问题，如果分拆成功，那么无论对通用查询还是其他对业务模块，凡是涉及灵活就业单位查询的模块将有非常大的好处，整体性能将得到进一步改善，I/O 性能有个进一步提升。南通社保系统性能调优与诊断报告共 21 页 / 第 12 页5、 系统维护建议5.1 空间方面以下几方面容易导致系统出现异常，需定时监控： 各表空间使用率监控通过 em，或其他命令，空间满，业务只能读，不能写。 操作系统各文件系统日志使用情况df -g，主要是根文件系统，如果满，则操作系统出现异常。 数据库软件安装文件系统这里如果产生大量的 oracle trace 文件，会导致数据 hang 数据库归档归档满也会导致数据库 hang。5.2 操作系统资源方面使用 topas 或 nmon 等工具，随时监控系统资源，包括 oracle 进程内的所有进程对系统资源的损耗。如果发现高耗 cpu、内存、I/O、网络、 swap 区等，及时监控，分析来源，如发现可疑进程，及时记录，以便排查，特殊情况下可杀掉，以释放资源。5.3 系统应用阻塞处理 大量锁堵塞情况，应对措施：第一步，查询有拥有大量锁的 sid，将其杀掉南通社保系统性能调优与诊断报告共 21 页 / 第 13 页select * from gv$lock where lmode0 and type in (TM,TX);如果还不行，一个一个重启应用，对于不正常的连接，oracle 会回滚事务 查找大批耗 I/O 全表扫描的对象 select inst_id,target,count(*) tt from gv$session_longops group by inst_id,sid,target order by inst_id,tt ;根据情况可以，影响大则对应清除或加上索引。6、 业务模块 sql 语句调优6.1 解决放大镜弹出窗口录入查询慢问题去除视图内的子查询，使执行时不再全表扫描原 SQL:create or replace view v_ac01_ae10 asselect C.AAE044 AAE044,C.AAB999 AAB999,AC01.BAZ001 BAZ001,AC01.BAZ002 BAZ002,AC01.AAC001 AAC001,AC01.AAC001 AAC999,AC01.AAC058 AAC058,AC01.AAE135 AAE135,AC01.AAC003 AAC003,AC01.AAC004 AAC004,AC01.AAC005 AAC005,AC01.AAC006 AAC006,AC01.AAC007 AAC007,南通社保系统性能调优与诊断报告共 21 页 / 第 14 页AC01.AIC162 AIC162,AC01.BAE039 BAE039,AC01.AAC084 AAC084,AC01.AAC009 AAC009,AC01.AAC010 AAC010,AC01.AAC011 AAC011,AC01.BAC024 BAC024,AC01.AAC012 AAC012,AC01.AAC013 AAC013,AC01.AAC014 AAC014,AC01.AAC015 AAC015,AC01.AAC017 AAC017,AC01.AAC020 AAC020,AC01.AAE013 AAE013,AC01.AAB301 AAB301,AC01.AAB034 AAB034,AC01.AKC021 AKC021,AC01.AAC033 AAC033,AC01.BAC057 BAC057,AC01.BAC058 BAC058,AC01.BAC059 BAC059,AC01.BAC060 BAC060,AC01.BAC061 BAC061,AC01.BAC062 BAC062,AC01.AAC028 AAC028,AC01.BAC064 BAC064,AC01.BAC065 BAC065,AC01.AAZ500 AAZ500,AC01.BAB302 BAB302,AC01.BAB303 BAB303,AC01.BAC066 BAC066,AC01.BAC067 BAC067,南通社保系统性能调优与诊断报告共 21 页 / 第 15 页AC01.BAC130 BAC130,AC01.BAC131 BAC131,AC01.BAC132 BAC132,AC01.BAC140 BAC140,AC01.BAB305 BAB305,AC01.BAB306 BAB306,AC01.BAB307 BAB307,AC01.BAB304 BAB304,AC01.BAC141 BAC141,AC01.AAA027 AAA027,AC01.BAC068 BAC068,AC01.BAC082 BAC082,AC01.BAC083 BAC083,AC01.BAC084 BAC084,AC01.BAC152 BAC152,AC01.BAC085 BAC085,AC01.BAC086 BAC086,AC01.AAE011 AAE011,AC01.AAE036 AAE036,AC01.BAC134 BAC134,AC01.AAC019 AAC019,AC01.BAC020 BAC020,AC01.BAC087 BAC087,AC01.BAC088 BAC088,AC01.BAC089 BAC089,AC01.BAC157 BAC157,AC01.BAC170 BAC170,AC01.BAC133 BAC133,AC01.BAC056 BAC056,AC01.BAC173 BAC173from ac01,(select distinct AE10.AAE044,AE10.AAB999,AC02.AAC001南通社保系统性能调优与诊断报告共 21 页 / 第 16 页from AE10,AC02where AE10.AAB001 = AC02.AAB001) Cwhere AC01.AAC001 = C.AAC001AND AC01.BAC062 36.2 灵活就业人员实收分配在一次实收操作多个灵活就业人员时，内循环中更新每个人的 AB09 时使用的BAZ002 是相同的。在实收下一个人时，由于传入参数只有一个，并相同的BAZ002，.这样如果一次实收人数较多时，同一次业务操作多次循环， AB09 会被重复更新。UPDATE AB09SET BAE159 = :B1, BAZ002 = PKG_DATA_TYPE.FUN_GETBAZ002南通社保系统性能调优与诊断报告共 21 页 / 第 19 页WHERE EXISTS (SELECT 1FROM AB07WHERE AB07.BAZ001 = AB09.BAE999AND AB07.BAZ002 = PKG_DATA_TYPE.FUN_GETBAZ002 )AND EXISTS (SELECT 1FROM AB14WHERE AB14.BAE203 = AB09.BAE203AND AB14.BAZ002 = PKG_DATA_TYPE.FUN_GETBAZ002)AND NVL(BAE159, 0) = 1以下 SQL 默认执行计划走 IDX_AB07_AAB001 索引，通过加入强制索引 /*+index(ab07 IDX_AB07_TMP)*/ 使读取数据量变小SELECT /*+index(ab07 IDX_AB07_TMP)*/SUM(AAE020) INTO N_欠费金额 FROM AB07 WHERE (BAB221, -核定流水号AAZ010, -缴费主体编号BAE165, -缴费主体类型AAB001, -参保组织编号AAE002, -结算期AAE003, -费款所属期AAE140, -险种类型BAE060, -缴费对象AAA157, -款项AAA115, -缴费类型AAC084, -离退休