《更多角度看性能优化内部培训》由会员分享，可在线阅读，更多相关《更多角度看性能优化内部培训（61页珍藏版）》请在金锄头文库上搜索。
1、更多角度看性能优化同样的案例，不同的角度由一个AWR报告开始执行次数多逻辑读大性能影响大从应用的角度看：SQL如何产生何以执行如此多次通过设置应用系统的参数，减少SQL执行甚至不自动执行也可避免该性能问题通过分析应用需求，也可以确定逻辑读高显然也是存在问题的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
2、,a.pk_srcbilltypefrompub_messageinfoa,sm_userbwherea.senderman=b.cuserid(+)and(checkman=:1anda.type=1)or(a.type=-1anda.state2and(a.pk_corp=:2ora.pk_corp=0001)and(a.receivedeleteflagisnullora.receivedeleteflag=N)anda.state=0)orderbysenddatedescPlanhashvalue:3486320044-|Id|Operation|Name|Rows|Bytes|Co
3、st(%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|拿到执行计划基本也可定位到问题的根源无索引可用SQLselectcount(checkman)frompub_messa
4、geinfo;-27989130万左右的总数据量，全表扫描因此产生较大逻辑读SQLselectcount(distinctcheckman)frompub_messageinfowherecheckmanisnotnull;-11428checkman的唯一性非常高，设计人员已经建立了索引frompub_messageinfoa,sm_userbwherea.senderman=b.cuserid(+)and(checkman=:1anda.type=1)OR(a.type=-1anda.state2anda.pk_corp=:2)SQL分析的过程也即是业务梳理的过程SQLselectcoun
5、t(*),typefrompub_messageinfogroupbytype;COUNT(*)TYPE-11-1676017011675126-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECTSTATEMENT|1|799|8(13)|00:00:01|1|SORTORDERBY|1|799|8(13)|00:00:01|2|NESTEDLOOPSOUTER|1|799|7(0)|00:00:01|*3|TABLEACCESSBYINDEXROWID|PUB_MESSAGEINFO|1|771|6(0)|00:00:01|4|B
6、ITMAPCONVERSIONTOROWIDS|5|BITMAPOR|6|BITMAPCONVERSIONFROMROWIDS|*7|INDEXRANGESCAN|I_MESSAGEINFO_3|1(0)|00:00:01|8|BITMAPAND|9|BITMAPCONVERSIONFROMROWIDS|*10|INDEXRANGESCAN|I_MESSAGEINFO_2|1(0)|00:00:01|11|BITMAPCONVERSIONFROMROWIDS|*12|INDEXRANGESCAN|I_MESSAGEINFO_3|1(0)|00:00:01|13|TABLEACCESSBYIND
7、EXROWID|SM_USER|1|28|1(0)|00:00:01|*14|INDEXUNIQUESCAN|PK_SM_USER|1|1(0)|00:00:01|-PredicateInformation(identifiedbyoperationid):-3-filter(A.TYPE=(-1)AND(A.PK_CORP=0001ORA.PK_CORP=1007)ANDA.STATE2ORA.TYPE=1ANDCHECKMAN=0001C71000000001GYI0)ANDA.STATE=0AND(A.RECEIVEDELETEFLAGISNULLORA.RECEIVEDELETEFLA
8、G=N)7-access(A.TYPE=(-1)10-access(CHECKMAN=0001C71000000001GYI0)12-access(A.TYPE=1)14-access(A.SENDERMAN=B.CUSERID(+)7-access(A.TYPE=(-1)10-access(CHECKMAN=0001C71000000001GYI0)12-access(A.TYPE=1)全表扫描得到消除18345/17提高：100000%优化前后效果对比不熟悉应用的DBA不是好的性能优化师更深层的思考第三方优化公司建议：通过适当的冗余字段，消除表关联，减少数据访问共同面临的现实问题一些程序员
9、也意识到这个问题并且实际情况的复杂程度要更大得多客户端Application基于安装的客户端基于浏览器的客户端Firewall负载均衡WebServerHTTPServerPlug-inWebServerPlugPlug-in防火墙NodeBNodeAApplicationServerWebContainerEJBContainerApplicationServerWebContainerEJBContainerApplicationServerWebContainerEJBContainerNC系统架构介绍HTTPServer轻量级web应用J2EE架构可以让这类操作充分利用应用服务器甚至客
10、户端资源动车组效应：减少数据库压力减少应用服务器压力减少网络依赖性多级缓存的应用实践，灵活可配置的客户端缓存不清楚系统架构的程序员不可能是好的性能优化师这是我们产品早就提供使用了的机制如果清楚了产品架构就能更好的提供这些机制以及应用PUB里也有关于这个问题的咨询大量采用的方式类标量子查询selectGET_NAME(订单钢种id),GET_NAME(企业内部钢种id),a,b,c,dFROMA延伸问题-标量子查询标量子查询：selecta.object_id,(selectb.object_namefromtest_obj_sbwhereb.object_id=a.object_id)from
11、test_obj1a外连接表关联：selecta.object_id,b.object_namefromtest_obj1a,test_obj_sbwherea.object_id=b.object_id(+)selecttable_name,num_rows,blocksfromuser_tableswheretable_namein(TEST_OBJ_S,TEST_OBJ1);TABLE_NAMENUM_ROWSBLOCKS-TEST_OBJ113176184TEST_OBJ_S115标量子查询与连接查询类filter式执行计划Pub上类似的问题很多其实际造成的影响往往也非常大WAITIN
12、G_SESSIONLOCK_TYPEMODE_REQUESTEDMODE_HELDLOCK_ID1LOCK_ID2-2760None1095TransactionExclusiveExclusive13107293900271261TransactionExclusiveExclusive13107293900271269TransactionExclusiveExclusive13107293900271294TransactionExclusiveExclusive13107293900271317TransactionExclusiveExclusive13107293900271410
13、TransactionExclusiveExclusive13107293900272613TransactionExclusiveExclusive13107293900272664TransactionExclusiveExclusive13107293900272683TransactionExclusiveExclusive13107293900272703TransactionExclusiveExclusive13107293900272716TransactionExclusiveExclusive13107293900272790TransactionExclusiveExcl
14、usive13107293900272796TransactionExclusiveExclusive13107293900272813TransactionExclusiveExclusive13107293900272846TransactionExclusiveExclusive13107293900272862TransactionExclusiveExclusive13107293900272879TransactionExclusiveExclusive13107293900274197TransactionExclusiveExclusive13107293900274321Tr
15、ansactionExclusiveExclusive1310729390027案例分析可怕的锁等待这只是其中的一组，严重时类似的等待与被等待session占据整个连接池，活动线程占据整个集群24个server所有线程池，整个应用集群无法继续提供服务第一步：先找到锁的源头SQLselectsid,id1,id2,blockfromv$lockwhereid1=1310729andid2=390027andblock0;SIDID1ID2BLOCK-276013107293900271第二步：查看其运行状态SQLselectsql_id,event,statusfromv$sessionwher
16、esid=2760;SQL_IDEVENTSTATUS- -nullSQL*NetmessagefromclientINACTIVE该session当前没有做数据库操作数据库层看锁等待ACTIVEExecuteThread:15forqueue:weblogic.kernel.Default(self-tuning)daemonprio=1tid=0x09e5f620nid=.SocketInputStream.socketRead0(NativeMethod).SocketInputStream.read(SocketInputStream.java:129).ns.Packet.recei
17、ve(UnknownSource).ns.DataPacket.receive(UnknownSource).ns.NetInputStream.getNextPacket(UnknownSource).ns.NetInputStream.read(UnknownSource).ns.NetInputStream.read(UnknownSource).ns.NetInputStream.read(UnknownSource)atoracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1099)atoracle.jdbc.d
18、river.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1070)atoracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:478)atoracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)atoracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)atoracle.jdbc.driv
19、er.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)atoracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)atoracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)atoracle.jdbc.driver.OraclePreparedStatement.executeInter
20、nal(OraclePreparedStatement.java:3285)atoracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)-locked(aoracle.jdbc.driver.T4CPreparedStatement)-locked(aoracle.jdbc.driver.T4CConnection)atweblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:1
21、00)atnc.jdbc.framework.crossdb.CrossDBPreparedStatement.executeQuery(CrossDBPreparedStatement.java:103)atnc.bs.gl.voucher.VchfreevalueDMO.queryByVoucherPKs(VchfreevalueDMO.java:560)atnc.bs.gl.voucher.VoucherBO.catVoucherFreeValue(VoucherBO.java:670)atnc.bs.gl.voucher.VoucherBO.queryByConditionVO(Vou
22、cherBO.java:2479)中间件层看锁等待处于等待状态的线程堆栈 ACTIVE ExecuteThread: 17 for queue: weblogic.kernel.Default (self-tuning) daemon prio=1 tid=0x09b0d1c0 nid=0x2565 waiting for monitor entry 0x3c1bc000.0x3c1bde30at java.lang.Throwable.printStackTrace(Throwable.java:462)- waiting to lock (a java.io.PrintStream)at
23、java.lang.Throwable.printStackTrace(Throwable.java:452)at $Proxy22.queryByConditionVO(Unknown Source)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)at sun.reflect.DelegatingMethodAccessorImpl.invoke(Delega
24、tingMethodAccessorImpl.java:25)at java.lang.reflect.Method.invoke(Method.java:585)at n.serv.ServiceDispatcher.invokeBeanMethod(ServiceDispatcher.java:320)at n.serv.ServiceDispatcher.execCall(ServiceDispatcher.java:129)at n.serv.CommonServletDispatcher.doGet(CommonServletDispatcher.java:76)at n.serv.
25、CommonServletDispatcher.doPost(CommonServletDispatcher.java:95)at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:225)at weblogi
26、c.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:127)at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:272)at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java
27、:42)持有锁的线程堆栈此线程正在等待JAVA锁对象因此迟迟不能执行完成STUCKExecuteThread:14forqueue:weblogic.kernel.Default(self-tuning)daemonprio=1tid=0x0a1354e0nid=0x17f3runnable0x3c8cb000.0x3c8cc0b0atjava.io.FileOutputStream.writeBytes(NativeMethod)atjava.io.FileOutputStream.write(FileOutputStream.java:260)atjava.io.BufferedOutpu
28、tStream.write(BufferedOutputStream.java:105)-locked(ajava.io.BufferedOutputStream)atjava.io.PrintStream.write(PrintStream.java:412)-locked(ajava.io.PrintStream)atsun.nio.cs.StreamEncoder$CharsetSE.writeBytes(StreamEncoder.java:336)atsun.nio.cs.StreamEncoder$CharsetSE.implFlushBuffer(StreamEncoder.ja
29、va:404)atsun.nio.cs.StreamEncoder.flushBuffer(StreamEncoder.java:115)-locked(ajava.io.OutputStreamWriter)atjava.io.OutputStreamWriter.flushBuffer(OutputStreamWriter.java:169)atjava.io.PrintStream.write(PrintStream.java:459)-locked(ajava.io.PrintStream)atjava.io.PrintStream.print(PrintStream.java:616
30、)atjava.io.PrintStream.println(PrintStream.java:753)-locked(ajava.io.PrintStream)atjava.lang.Throwable.printStackTrace(Throwable.java:462)-locked(ajava.io.PrintStream)atjava.lang.Throwable.printStackTrace(Throwable.java:452)真正的罪魁祸首Pstack查看对应NID的进程在操作系统中的调用：Thread17(Thread1025264560(LWP2334):这是持有锁的线程
31、#00xffffe410in_kernel_vsyscall()#10xf7fcf41bin_write_nocancel()from/lib/tls/libpthread.so.0#20xf78b57b3inJVM_Write()#30xf75803deinVerifyClassCodesForMajorVersion()#40xf757c347inJava_java_io_FileOutputStream_writeBytes()#50xf187308fin?()#60x095ddd48in?()#70x3d1c3528in?()#80x3d1c3524in?()#90x00000000i
32、n?()不熟悉中间件的系统管理员也难成为好的性能优化师这个问题根本的解决之道是修正操作系统的缺陷线程监控性能监控工具简介谁做什么做多久了消耗资源是什么线程信息中间件层端到端监控以此和JAVATHREADDUMP进行关联快速定位应用系统线程问题活动线程详细信息当前SQL历史SQL中间件层端到端监控联查执行计划中间件层端到端监控案例分析设计角度看优化DB层面的优化手段优化前后对比：8566989/9697880多倍！厂商层面的动作类似的问题光pub里就能找到很多，处理起来也是各显身手SELECT*FROMeh_ladingbillWHERE(nvl(dr,0)=0)and(nvl(dr,0)=0a
33、ndvbillstatus=1andpk_corp=1031andbillnonotin(selectbillcodefromdap_finindexwherepk_corp=1031andnvl(dr,0)=0)从设计的角度来看，eh_ladingbill可以设计一个字段来回写状态或者下游单据id来避免这个问题SQL将简化为：SELECT*FROMeh_ladingbillWHERE(nvl(dr,0)=0)and(nvl(dr,0)=0andvbillstatus=1andpk_corp=1031andxx_flag=1从设计的角度看selectc.pk_produce,b.pk_invm
34、andoc,a.pk_invbasdocfrombd_invbasdoca,bd_invmandocb,bd_producecwherea.pk_invbasdoc=b.pk_invbasdocandb.pk_invmandoc=c.pk_invmandocanda.pk_invcl=0001A8100000000007AXandc.pk_calbody=1146B8100000000003VDanda.invcode=130104990001anda.invcode=130104990001anda.invcode=2011-01-01)and(head.dbilldateselectdis
35、tinct(freevalueid)2fromgl_freevaluea3where(checktype=00010000000000000073and4(checkvaluein(selectpk_cubasdocfrombd_cumandoc7where(pk_corp=1177orpk_corp=0001)9or(checktype=0001A8100000000004QM)10groupbyfreevalueid11havingcount(*)=2ExecutionPlan-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECT
36、STATEMENT|1871|115K|2706(1)|00:00:33|*1|FILTER|2|SORTGROUPBYNOSORT|1871|115K|2706(1)|00:00:33|*3|FILTER|4|INDEXFULLSCAN|I_GL_FREEVALUE4|637K|38M|2706(1)|00:00:33|5|INLISTITERATOR|*6|INDEXRANGESCAN|I_BD_CUMANDOC_1|1|26|1(0)|00:00:01|-Statistics-0recursivecalls0dbblockgets3817525consistentgets0physica
37、lreads3024rowsprocessed案例分析如何兼顾业务灵活性与性能Statistics-0recursivecalls0dbblockgets14412consistentgets0physicalreads0redosize84630bytessentviaSQL*Nettoclient2561bytesreceivedviaSQL*Netfromclient203SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)3024rowsprocessed改写SQL，可以看到性能提升DB层面技术问题OracleCBO目前还未修
38、正的一个缺陷3024 rows processed去掉子查询，虽然结果集多了很多但逻辑读却很小DB层面可采取的技术手段借用此HINT可以绕过该问题DB层面可采取的技术手段统计信息-571recursivecalls2dbblockgets463657consistentgets3physicalreads0redosize246bytessentviaSQL*Nettoclient327bytesreceivedviaSQL*Netfromclient1SQL*Netroundtripsto/fromclient22sorts(memory)0sorts(disk)0rowsprocessed
39、统计信息-667recursivecalls0dbblockgets4654consistentgets838physicalreads0redosize246bytessentviaSQL*Nettoclient327bytesreceivedviaSQL*Netfromclient1SQL*Netroundtripsto/fromclient10sorts(memory)0sorts(disk)0rowsprocessedDB层面可采取的技术手段HINT前后逻辑对比高度灵活的辅助核算设计ASSIDASSIDTYPETYPEVALUEVALUE1type1t111type2t212type1
40、t112type3t312type4t412type5t513type1t113type3t313type4t413type5t513type6t61selectdistinctassidfromtablewheretype=type1ortype=type2groupbyassidhavingcount(*)=2复杂低效的SQL设计的角度看切合实际的用户使用情况调研不精通SQL的设计人员也成不了好的性能优化师二八原则：即支持type无限扩展又能兼顾热点的性能不精通SQL的设计人员也成不了好的性能优化师不熟悉应用的DBA不是好的性能优化师不清楚系统架构的程序员不可能是好的性能优化师不熟悉中间件
41、的系统管理员也难成为好的性能优化师一次硬件规划的案例分析薪资发放日需满足全公司30万人自助查询薪资某银行人力资源管理系统，甲方负责人对系统提出压力测试要求之一：登陆查询假设只有千分之一的并发度，30万人自助查询薪资将额外增加300TPS的运算需求数据库服务器硬件应用服务器硬件网络带宽客户端PC真实的需求是什么？自动批量计算薪资，提供差异列表，供人力专员确认即可薪资计算就是必须的吗快递物流信息查询简历浏览信息查询其他的例子个人信用信息查询客商信用计算自动催款等等性能优化也需要解放思想，与时俱进，改善用户体验并同时降低系统压力数字巨灵金融服务平台数据显示，目前已公布年报并有可比数据的1449家上市
42、公司，去年应收账款余额高达9361.04亿元，同比暴增30.34%。其中，1089家公司应收账款同比增加，翻番的公司有178家之多。与此同时，其他应收款同比增长48.7%，两者占到同期营收收入的8.8%。从绝对量上看，非金融行业的应收账款创出了近三年来的新高。综合企业经营情况来看，创业板上市公司年报暴露出来的应收账款是净利润的3倍多，远远超过主板和中小板。通知业务人员催款从一次购物经历看性能优化显示为荆州市的商家，实际上是属于该市的县城，“快递”了5天需求与实现差异带来的糟糕用户体验同一城市的快递，却要先送到另一个城市索引与执行路径执行计划/索引/以及全局考虑增加运货线路增加索引改变购物方式改
43、变SQL性能优化理论T响应时间=S工作量/V处理速度在系统处理能力不变的情况下，减少每笔交易所需的工作量是提高系统性能唯一行之有效的办法提高执行速度速度执行时间数据库初始化参数调整内存调整：SGA(db_cache_size,shared_pool_size,large_pool_size)PGA（sort_area_size）进程调整：db_writer_processes升级瓶颈硬件减少SQL执行成本执行时间执行成本减少SQL耗费的资源减少SQL执行次数避免SQL执行。性能优化与节能环保硬件厂商的官方数据：服务器功率散热量(BTU）每年耗电（度）每年电费支出（北京电价1.03元/度）M4000*2403268793532036379.9296M5000*27476127546549067454.45283万3万度性能优化利国利民投产硬件资源投产智力资源有社会责任感的企业必将尽量减少硬件资源的投产！负责任的软件供应商也一定会加强软件性能优化方面的投入！在成倍降低系统资源耗费的情况下提供更好的用户体验重视DBA等人力资源注重人员能力提升等
