NC性能优化理论与实践用友软件股份有限公司性能定义性能优化概述性能是系统在指定工作负载下的表现，通常通过系统响应时间、吞吐量及可用性来衡量系统性能的好坏生产系统中性能通常通过不同业务阶段用户操作的响应时间及系统资源的使用情况来直观反映性能优化理论T响应时间=S工作量/V处理速度1、减小S2、增加V在系统处理能力不变的情况下，减少每笔交易所需的工作量是提高系统性能唯一行之有效的办法网络优化减小S合并远程调用、减少不必要的前台操作、裁剪VO压缩增加V优化网络基础设施多线程延迟加载(lazyload)并不能提高性能SQL优化减小S--减少所需访问的数据块优化业务模型通过余额模型可避免扫描大量历史数据连接池避免重复建立连接的工作量批量模型，减少SQL执行次数合理利用索引通过索引可大幅较少数据库扫描的数据块（S），从而大幅提高响应（T）优化SQL，使SQL实际需要访问的数据量得到降低借鸡下蛋前台缓存模型减少后台工作量增加V--提高处理速度升级硬件、优化参数如：将数据放入cache性能优化概述性能优化概述数据库系统：参数设置、统计信息等中间件参数设置：JVM参数连接池事务管理客户端：客户端JRE参数，客户端操作系统资源资源其他：应用及数据库平台操作系统、硬件系统、网络应用程序相关影响NC系统性能的因素性能优化概述影响NC系统性能的因素NC系统最初状态其他10%客户端系统10%中间件10%数据库35%应用程序35%•系统架构•数据库参数、优化器设置•应用服务器JVM参数•客户端JRE参数实例级优化•应用算法•SQL优化、索引调整•需求优化、设计调整应用级优化优化前：150M左右优化后：数百KSQL_IDbg5v796nyx0b2,childnumber0-------------------------------------selecta.pk_messageinfo,a.senderman,b.user_name,a.checkman,a.pk_corp,a.type,a.state,a.url,a.title,a.content,a.senddate,a.priority,a.dealdate,a.billid,a.billno,a.pk_billtype,a.pk_srcbilltype,a.pk_busitype,a.actiontype,a.titlecolorfrompub_messageinfoa,sm_userbwherea.senderman=b.cuserid(+)and(checkman=:1and(a.typein(3,4,5,6))and(a.receivedeleteflagisnullora.receivedeleteflag='N')anda.state=0)orderbysenddatedescPlanhashvalue:657918639-------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||||4055(100)|||1|SORTORDERBY||2|1534|4055(1)|00:00:49||2|NESTEDLOOPSOUTER||2|1534|4054(1)|00:00:49||*3|TABLEACCESSFULL|PUB_MESSAGEINFO|2|1478|4053(1)|00:00:49||4|TABLEACCESSBYINDEXROWID|SM_USER|1|28|1(0)|00:00:01||*5|INDEXUNIQUESCAN|PK_SM_USER|1||1(0)|00:00:01|-------------------------------------------------------------------------------------------------selecta.pk_messageinfo,a.senderman,b.user_name,a.checkman,a.pk_corp,a.type,a.state,a.url,a.title,a.content,a.senddate,a.priority,a.dealdate,a.billid,a.billno,a.pk_billtype,a.pk_srcbilltype,a.pk_busitype,a.actiontype,a.titlecolorfrompub_messageinfoa,sm_userbwherea.senderman=b.cuserid(+)and(((checkman=:1anda.type=1)or(a.type=-1anda.state<>2and(a.pk_corp=:2ora.pk_corp='0001')))and(a.receivedeleteflagisnullora.receivedeleteflag='N')anda.state=0)orderbysenddatedescPlanhashvalue:3486320044---------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------|0|SELECTSTATEMENT||||2411(100)|||1|SORTORDERBY||264|184K|2411(1)|00:00:29||*2|HASHJOINOUTER||264|184K|2410(1)|00:00:29||*3|TABLEACCESSFULL|PUB_MESSAGEINFO|264|176K|2366(1)|00:00:29||4|TABLEACCESSFULL|SM_USER|10145|277K|43(0)|00:00:01|一条数据由数据库到中间件再到客户端将产生的网络流量执行计划-------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost|-------------------------------------------------------------------|0|SELECTSTATEMENT||15|1590|11||*1|FILTER||||||2|TABLEACCESSFULL|BD_BDINFO|15|1590|5||3|NESTEDLOOPS||802|67368|6||*4|INDEXRANGESCAN|I_BD_ACCSUBJ|765|32130|4||*5|INDEXRANGESCAN|I_BD_SUBJASS|1|42|1|-------------------------------------------------------------------统计信息----------------------------------------------------------0recursivecalls0dbblockgets412764consistentgets0physicalreads0redosize1861bytessentviaSQL*Nettoclient250bytesreceivedviaSQL*Netfromclient3SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)16rowsprocessed执行计划--------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost|--------------------------------------------------------------------|0|SELECTSTATEMENT||22|2816|33||*1|HASHJOINSEMI||22|2816|33||2|TABLEACCESSFULL|BD_BDINFO|292|30952|5||3|VIEW|VW_NSO_1|2346|51612|27||4|NESTEDLOOPS||2346|192K|27||*5|INDEXRANGESCAN|I_BD_ACCSUBJ|765|32130|4||*6|INDEXRANGESCAN|I_BD_SUBJASS|3|126|1|--------------------------------------------------------------------统计信息----------------------------------------------------------0recursivecalls0dbblockgets1525consistentgets0physicalreads0redosize1946bytessentviaSQL*Nettoclient250bytesreceivedviaSQL*Netfromclient3SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)16rowsprocessed412764consistentgets412764*8192/1024/1024=3224.72MB1525consistentgets1525*8192/1024/1024=11.91MB邮储项目对应信息：5843169consistentgets45649.75MB6917consistentgets54.04MB未索引字段条件查询的控制SQL>selectbdcode,bdnamefromufnc5610.bd_bdinfo;已选择292行。统计信息----------------------------------------------------------0recursivecalls0dbblockgets27consistentgets0physicalreads0redosize6563bytessentviaSQL*Nettoclient375bytesreceivedviaSQL*Netfromclient21SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)292rowsprocessed
