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

28/28南通社保系统性能调优与诊断报告东软集团股份有限公司2013年12月

目录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

系统性能问题与解决概述自2013年10月以来，南通核心业务系统各模块，尤其是批量业务相继报告越来越慢，尤其养老应付核定更是慢的离谱，由自2012年5月系统上线的12分钟到13年11月份的12小时。因此东软公司特就此现象组织专门队伍对业务及服务器运行情况进行跟踪分析，总体评估是南通社保系统硬件平台资源无法高性能地满足业务高峰时段的办公要求，调整与改造已经迫在眉睫。同时由于一年多的数据积累，以及新区县的并入，无论从业务量到数据量都有了较快的增长，这样对原有的个别语句和模式提出了更高要求，通过对系统资源的调整，程序的优化使得多数主要业务已经完全满足要求。系统运行现状与性能问题分析南通业务系统的主数据库运行在两台16cores，64g内存的ibm570上，目前运行核三3.0系统，主要包括五险和和劳动就业等，几乎所有的业务系统都运行在这两台服务器上，另外还有两台ibm550运行医院的托管his，数据库的版本为10.2.0.5，运行的是oraclerac。业务系统性能出现严重问题的原因有很多，需要逐个的来排查，主要有以下几个方面：2.1从系统错误日志角度分析是否有硬件错误？#errpt未发现系统有明显的硬件错误，初步排除硬件故障问题。是否有系统网络丢包现象？#netstat-in未发现有丢包现象，排除。是否有数据库的严重错误？通过oraclealert日志等分析，未发现严重的系统错误日志。是否有数据库bug？经发现有oracle系统的bug，但经过在oraclesupport网站查询该错误，发现仅仅是一个垃圾输出问题，并不影响系统性能。因此也排除这个问题。2.2从系统参数角度分析操作系统参数#prtconf经分析未发现明显的参数问题。数据库参数sql>showparameter经排查，数据库参数设置基本合理，没有大的问题。2.3从系统资源角度分析通过系统工具topas观察发现系统资源明显短缺主要体现在内存和I/O资源匮乏上，以下几点说明：1、通过操作系统工具观察高峰时段发现，db164g内存几乎耗尽，一般剩余几M或十几M，db2略有剩余。2、db1系统交换空间使用了18g，占整体32g的57%，db2还好，但也有20%的swap使用率，这对数据库类型业务来说是极其糟糕的，明显体现内存不足，一般理想业务系统不可超过10%，否则用户感觉系统很慢。3、磁盘一直保持繁忙，cpu等待I/O情况很多。4、db1cpu利用率高峰一般在70-80%左右，db2也能达到60-70%，虽然没有体现cpu严重资源不足，但cpu利用率一旦过70%，前台的响应速度也会受到影响。通过统计发现两台数据库服务器会话过多，严重影响资源利用#ps-ef|grepora|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操作，而存储的读写性能直接影响这个指标，也影响到系统的响应速度。一般来说为了减少上述等待事件，都采取两个动作，一个是调整索引访问路径，使用合理的索引，消除不必要的索引，消除索引碎片，调整索引和表的存储参数等。二是调整存储性能，消除硬件瓶颈。优化方法与结果3.1资源调优主要在以下三方面消除多余数据库连接通过减少应用服务器中间件连接池的连接数配置，由原来的每台1200左右连接，减少到现在的每台600-700个连接，预计每台因此释放的内存资源至少有5g以上。这在很大程度上缓解的内存资源的使用，也会使oracle对swap区的使用大大减少，调整后资源如下图，db1的swap区由原来57%下降到29%。表空间调整由于快到年终结转，单位应收核定等，会产生大量数据，经协调临时增加部分空间，已达到缓解的目的，由原来的表空间利用率89%下降的81%，这样短缺内基本够用，但i/o性能问题还是不能得到充分缓解。迁移3.2软件调优由于现场主要反馈的是业务模块运行较慢，模拟测试调优比较困难，所以我们基本采用让业务人员实际运行业务我们后台监控运行的方式来进行，同时在运行结束后我们再利用oracle的awr报告反馈的运行期间各种统计信息，进一步分析到底哪个包、哪个过程、哪个语句消耗资源，消耗什么资源。软件调优根据现场运行情况和业务周期来进行：12月17-21日，主要对通用查询、到账、通用税票、业务日志进行分析排查和解决12月23-28日，主要针对征集、养老核定、到账实收排查和解决第一周涉及到所有应用问题的分析和排查，具体过程如下：第一周解决的问题：1、在通用查询和个别性能低下语句的排查中，发现了很多前台传参数和后台调用类型不匹配的问题，主要是number型和varchar型的不匹配，这样导致数据库执行计划的改变，不能用上索引，执行效率低下。通过调整后性能明显改观。2、发现一些不能正确利用索引的现象比如一些给定索引条件在外层，内层语句条件无法使用，通过调整获得明显改善。3、发现有些功能没有用上，但语句还关联这些视图，如到账模块，南通没用上压缩表，但却关联相关条件，导致性能大幅下降。通过调整好，性能大幅得到改善。第一周遗留的问题：1、涉及灵活就业人员单位编号的查询问题跟踪发现，同样的sql，在输入不同aab001单位编号时执行计划是不同的，对于像灵活就业人员这种大单位（有好几个是6-7万人），无论是ab01，还是ab07、ab09等，它们的数据量产生的太大，正常关联这些表的aab001的查询是无法走索引的，而其他单位则可以。因此无法做进一步调优。2、到账实收跟踪到账实收性能问题比较棘手，一个是偶尔一天才有几笔业务，有时一天都没有，因此需跟业务人员定好时间才能进行。另外，跟踪发现总共有四个语句是影响性能的关键，在内循环中，执行量比较大，等待事件比较多，如果得到改善那将大大提高效率，但这些语句的执行计划没问题，因此比较郁闷。第二周涉及到上一周应用问题继续分析和排查以及新业务的跟踪，具体过程如下：1、征集征集告诉我们的时候，都说是比较慢，每每发现启动后都有大量的rowlock，但后来我们发现很多时候oraclerac的两个节点都有相同这些业务锁，而且还有很多gccrrequest等锁，这说明是两个会话在争夺资源，在杀掉这些资源锁后，我们让业务人员再做一次业务，发现很快，30分钟做完。经分析，发现很多时候在系统高峰阶段，资源比较匮乏，业务人员在做批量业务时感觉比较慢，甚至前台界面都灰掉，他们就关掉界面，然后再另开一个界面接着做，这样又一次新提交产生，锁就出现了，我们上周就发现业务人员连续提交了三次相同的征集动作，导致大量的行锁存在。解决办法是批量最好等资源不忙时做，如果真的无法出来，让我们后台人员先清掉占用资源然后再做。否则会堆积很多锁更加重了系统的资源消耗。2、养老应付核定这个问题一开始也是没好的解决办法，就是慢。但跟开发沟通后，打算现场跟踪调试，结果发现，循环体有个最重要的条件字段在系统内未启用，每次支付核定时BAE074值默认都为‘0’，这样导致大量的相同的值，但oracle并未给出全表扫描，使用的是索引，因此给大家的感觉执行计划没问题，实际上每次读取的数据量却是非常大的，因此，很慢。后来对这些0重新按日期做update，系统由原来的12小时提高到半小时。3、实收划账（第一周遗留问题）这个在这周有点眉目了，经开发确认，程序中存在在一次实收操作多个灵活就业人员时，内循环中更新每个人的AB09时使用的BAZ002是相同的。在实收下一个人时，由于传入参数只有一个，并且是相同的BAZ002，.这样如果一次实收人数较多时，同一次业务操作多次循环，AB09会被重复更新。因此导致每次循环多读取、跟新数据。系统变慢了，现在程序正在更新测试。。。，下周应该解决了。第二周遗留的问题灵活就业人员单位人员再细化分成小单位，以保证相关模块的快速查询。下一步系统优化建议目前系统处于平台资源基本饱和工作情况，我们对影响性能的top-sql做了分析调优工作，但目前代码的总体优化率已经很高了，个别语句的调整空间不大了，不能解决整体性能问题。为了能使系统能维持工作，特提出以下几点调优建议：4.1资源调整尽快完成对db1上oracle9i的迁移工作目前通过调整使得系统存储资源剩余有所提升，但利用率仍然达到81%多，这样的情况对系统整体性能仍然面临考验，尤其2014年1月份，春节前可能提前做批量业务，这对系统资源又是一次冲击。因此，尽快完成oracle9i老库的迁移工作，以使系统资源得到充分改善，db1节点能够释放4g的物理内存，同时2T的存储空间可以加入到业务的data和idx表空间，这样对整体系统性能的提升有着明显的好处，而且对下月的批量业务也就没有担心的必要了。着眼未来，希望能更新设备，缓解目前各种平台资源的不足的问题。4.2应用调整将灵活就业人员单位分拆。这个问题是个核心问题，如果分拆成功，那么无论对通用查询还是其他对业务模块，凡是涉及灵活就业单位查询的模块将有非常大的好处，整体性能将得到进一步改善，I/O性能有个进一步提升。系统维护建议5.1空间方面以下几方面容易导致系统出现异常，需定时监控：各表空间使用率监控通过em，或其他命令，空间满，业务只能读，不能写。操作系统各文件系统日志使用情况df-g，主要是根文件系统，如果满，则操作系统出现异常。数据库软件安装文件系统这里如果产生大量的oracletrace文件，会导致数据hang数据库归档 归档满也会导致数据库hang。 5.2操作系统资源方面使用topas或nmon等工具，随时监控系统资源，包括oracle进程内的所有进程对系统资源的损耗。如果发现高耗cpu、内存、I/O、网络、swap区等，及时监控，分析来源，如发现可疑进程，及时记录，以便排查，特殊情况下可杀掉，以释放资源。5.3系统应用阻塞处理大量锁堵塞情况，应对措施：第一步，查询有拥有大量锁的sid，将其杀掉select*fromgv$lockwherelmode>0and

typein('TM','TX');如果还不行，一个一个重启应用，对于不正常的连接，oracle会回滚事务

查找大批耗I/O全表扫描的对象

selectinst_id,target,count(*)ttfromgv$session_longopsgroupbyinst_id,sid,targetorderbyinst_id,tt;根据情况可以，影响大则对应清除或加上索引。业务模块sql语句调优6.1解决放大镜弹出窗口录入查询慢问题去除视图内的子查询，使执行时不再全表扫描原SQL:createorreplaceviewv_ac01_ae10asselectC.AAE044AAE044,C.AAB999AAB999,AC01.BAZ001BAZ001,AC01.BAZ002BAZ002,AC01.AAC001AAC001,AC01.AAC001AAC999,AC01.AAC058AAC058,AC01.AAE135AAE135,AC01.AAC003AAC003,AC01.AAC004AAC004,AC01.AAC005AAC005,AC01.AAC006AAC006,AC01.AAC007AAC007,AC01.AIC162AIC162,AC01.BAE039BAE039,AC01.AAC084AAC084,AC01.AAC009AAC009,AC01.AAC010AAC010,AC01.AAC011AAC011,AC01.BAC024BAC024,AC01.AAC012AAC012,AC01.AAC013AAC013,AC01.AAC014AAC014,AC01.AAC015AAC015,AC01.AAC017AAC017,AC01.AAC020AAC020,AC01.AAE013AAE013,AC01.AAB301AAB301,AC01.AAB034AAB034,AC01.AKC021AKC021,AC01.AAC033AAC033,AC01.BAC057BAC057,AC01.BAC058BAC058,AC01.BAC059BAC059,AC01.BAC060BAC060,AC01.BAC061BAC061,AC01.BAC062BAC062,AC01.AAC028AAC028,AC01.BAC064BAC064,AC01.BAC065BAC065,AC01.AAZ500AAZ500,AC01.BAB302BAB302,AC01.BAB303BAB303,AC01.BAC066BAC066,AC01.BAC067BAC067,AC01.BAC130BAC130,AC01.BAC131BAC131,AC01.BAC132BAC132,AC01.BAC140BAC140,AC01.BAB305BAB305,AC01.BAB306BAB306,AC01.BAB307BAB307,AC01.BAB304BAB304,AC01.BAC141BAC141,AC01.AAA027AAA027,AC01.BAC068BAC068,AC01.BAC082BAC082,AC01.BAC083BAC083,AC01.BAC084BAC084,AC01.BAC152BAC152,AC01.BAC085BAC085,AC01.BAC086BAC086,AC01.AAE011AAE011,AC01.AAE036AAE036,AC01.BAC134BAC134,AC01.AAC019AAC019,AC01.BAC020BAC020,AC01.BAC087BAC087,AC01.BAC088BAC088,AC01.BAC089BAC089,AC01.BAC157BAC157,AC01.BAC170BAC170,AC01.BAC133BAC133,AC01.BAC056BAC056,AC01.BAC173BAC173fromac01,(selectdistinctAE10.AAE044,AE10.AAB999,AC02.AAC001fromAE10,AC02whereAE10.AAB001=AC02.AAB001)CwhereAC01.AAC001=C.AAC001ANDAC01.BAC062<>'3';新SQL：reateorreplaceviewv_ac01_ae10asselectdistinctae10.AAE044AAE044,ae10.AAB999AAB999,AC01.BAZ001BAZ001,AC01.BAZ002BAZ002,AC01.AAC001AAC001,AC01.AAC999AAC999,AC01.AAC058AAC058,AC01.AAE135AAE135,AC01.AAC003AAC003,AC01.AAC004AAC004,AC01.AAC005AAC005,AC01.AAC006AAC006,AC01.AAC007AAC007,AC01.AIC162AIC162,AC01.BAE039BAE039,AC01.AAC084AAC084,AC01.AAC009AAC009,AC01.AAC010AAC010,AC01.AAC011AAC011,AC01.BAC024BAC024,AC01.AAC012AAC012,AC01.AAC013AAC013,AC01.AAC014AAC014,AC01.AAC015AAC015,AC01.AAC017AAC017,AC01.AAC020AAC020,AC01.AAE013AAE013,AC01.AAB301AAB301,AC01.AAB034AAB034,AC01.AKC021AKC021,AC01.AAC033AAC033,AC01.BAC057BAC057,AC01.BAC058BAC058,AC01.BAC059BAC059,AC01.BAC060BAC060,AC01.BAC061BAC061,AC01.BAC062BAC062,AC01.AAC028AAC028,AC01.BAC064BAC064,AC01.BAC065BAC065,AC01.AAZ500AAZ500,AC01.BAB302BAB302,AC01.BAB303BAB303,AC01.BAC066BAC066,AC01.BAC067BAC067,AC01.BAC130BAC130,AC01.BAC131BAC131,AC01.BAC132BAC132,AC01.BAC140BAC140,AC01.BAB305BAB305,AC01.BAB306BAB306,AC01.BAB307BAB307,AC01.BAB304BAB304,AC01.BAC141BAC141,AC01.AAA027AAA027,AC01.BAC068BAC068,AC01.BAC082BAC082,AC01.BAC083BAC083,AC01.BAC084BAC084,AC01.BAC152BAC152,AC01.BAC085BAC085,AC01.BAC086BAC086,AC01.AAE011AAE011,AC01.AAE036AAE036,AC01.BAC134BAC134,AC01.AAC019AAC019,AC01.BAC020BAC020,AC01.BAC087BAC087,AC01.BAC088BAC088,AC01.BAC089BAC089,AC01.BAC157BAC157,AC01.BAC170BAC170,AC01.BAC133BAC133,AC01.BAC056BAC056,AC01.BAC173BAC173fromac01,AC02,ae10whereAC01.AAC001=ac02.AAC001andac02.aab001=ae10.AAB001ANDAC01.BAC062<>'3'6.2灵活就业人员实收分配在一次实收操作多个灵活就业人员时，内循环中更新每个人的AB09时使用的BAZ002是相同的。在实收下一个人时，由于传入参数只有一个，并相同的BAZ002，.这样如果一次实收人数较多时，同一次业务操作多次循环，AB09会被重复更新。UPDATEAB09SETBAE159=:B1,BAZ002=PKG_DATA_TYPE.FUN_GETBAZ002WHEREEXISTS(SELECT1FROMAB07WHEREAB07.BAZ001=AB09.BAE999ANDAB07.BAZ002=PKG_DATA_TYPE.FUN_GETBAZ002)ANDEXISTS(SELECT1FROMAB14WHEREAB14.BAE203=AB09.BAE203ANDAB14.BAZ002=PKG_DATA_TYPE.FUN_GETBAZ002)ANDNVL(BAE159,'0')='1'以下SQL默认执行计划走IDX_AB07_AAB001索引，通过加入强制索引/*+index(ab07IDX_AB07_TMP)*/使读取数据量变小SELECT/*+index(ab07IDX_AB07_TMP)*/SUM(AAE020)INTON_欠费金额FROMAB07WHERE(BAB221,--核定流水号AAZ010,--缴费主体编号BAE165,--缴费主体类型AAB001,--参保组织编号AAE002,--结算期AAE003,--费款所属期AAE140,--险种类型BAE060,--缴费对象