搜索
更多角度看性能优化李奎阳同样的案例，不同的角度●由一个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,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|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|●拿到执行计划基本也可定位到问题的根源——无索引可用SQLselectcount(checkman)frompub_messageinfo;---------------279891———30万左右的总数据量，全表扫描因此产生较大逻辑读SQLselectcount(distinctcheckman)frompub_messageinfowherecheckmanisnotnull;-----------------------11428————checkman的唯一性非常高，设计人员已经建立了索引frompub_messageinfoa,sm_userbwherea.senderman=b.cuserid(+)and((checkman=:1anda.type=1)OR(a.type=-1anda.state2anda.pk_corp=:2))●SQL分析的过程也即是业务梳理的过程SQLselectcount(*),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|BITMAPCONVERSIONTOROWIDS|||||||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|TABLEACCESSBYINDEXROWID|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='0001'ORA.PK_CORP='1007')ANDA.STATE2ORA.TYPE=1ANDCHECKMAN='0001C71000000001GYI0')ANDA.STATE=0AND(A.RECEIVEDELETEFLAGISNULLORA.RECEIVEDELETEFLAG='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)★全表扫描得到消除优化前：150M左右优化后：80K~480K18345/17提高：100000%●优化前后效果对比●不熟悉应用的DBA不是好的性能优化师更深层的思考●第三方优化公司建议：通过适当的冗余字段，消除表关联，减少数据访问共同面临的现实问题●一些程序员也意识到这个问题并且实际情况的复杂程度要更大得多客户端Application基于安装的客户端基于浏览器的客户端Firewall负载均衡WebServerHTTPServerPlug-inWebServerPlugPlug-in防火墙NodeBNodeAApplicationServerWebContainerEJBContainerApplicationServerWebContainerEJBContainerApplicationServerWebContainerEJBContainerDatabaseServerDatabaseServerBusinessDataWebServer集群应用服务器集群数据库服务器集群NC系统架构介绍InternetHTTP/HTTPSClientHTTPServer轻量级web应用●J2EE架构可以让这类操作充分利用应用服务器甚至客户端资源动车组效应：●减少数据库压力●减少应用服务器压力●减少网络依赖性多级缓存的应用实践，灵活可配置的客户端缓存●不清楚系统架构的程序员不可能是好的性能优化师这是我们产品早就提供使用了的机制如果清楚了产品架构就能更好的提供这些机制以及应用●PUB里也有关于这个问题的咨询●大量采用的方式●类标量子查询selectGET_NAME(订单钢种id),GET_NAME(企业内部钢种id),a,b,c,dFROMA延伸问题--标量子查询标量子查询：selecta.object_id,(selectb.object_namefromtest_obj_sbwhereb.object_id=a.object_id)fromtest_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上类似的问题很多其实际造成的影响往往也非常大WAITING_SESSIONLOCK_TYPEMODE_REQUESTEDMODE_HELDLOCK_ID1LOCK_ID2------------------------------------------------------------------------------------------------2760None1095TransactionExclusiveExclusive13107293900271261TransactionExclusiveExclusive13107293900271269TransactionExclusiveExclusive13107293900271294TransactionExclusiveExclusive13107293900271317TransactionExclusiveExclusive13107293900271410TransactionExclusiveExclusive13107293900272613TransactionExclusiveExclusive13107293900272664TransactionExclusiveExclusive13107293900272683TransactionExclusiveExclusive13107293900272703TransactionExclusiveExclusive13107293900272716TransactionExclusiveExclusive13107293900272790TransactionExclusiveExclusive13107293900272796TransactionExclusiveExclusive13107293900272813TransactionExclusiveExclusi