企业培训_更多角度看性能优化内部培训

更多角度看性能优化 李奎阳 同样的案例 不同的角度 由一个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 拿到执行计划基本也可定位到问题的根源 无索引可用 SQL selectcount checkman frompub messageinfo 279891 30万左右的总数据量 全表扫描因此产生较大逻辑读SQL selectcount 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分析的过程也即是业务梳理的过程 SQL selectcount 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 OR A PK CORP 1007 AND A STATE 2OR A TYPE 1AND CHECKMAN 0001C71000000001GYI0 AND A STATE 0AND A RECEIVEDELETEFLAG ISNULLOR A 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 全表扫描得到消除 18345 17提高 100000 优化前后效果对比 不熟悉应用的DBA不是好的性能优化师 更深层的思考 第三方优化公司建议 通过适当的冗余字段 消除表关联 减少数据访问 共同面临的现实问题 一些程序员也意识到这个问题 并且实际情况的复杂程度要更大得多 客户端 Application 基于安装的客户端 基于浏览器的客户端 Firewall 负载均衡 WebServer HTTPServer Plug in WebServer Plug Plug in 防火墙 NodeB NodeA ApplicationServer WebContainer EJBContainer ApplicationServer WebContainer EJBContainer ApplicationServer WebContainer EJBContainer NC系统架构介绍 HTTPServer 轻量级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 2760None1095TransactionExclusiveExclusive13107293900271261TransactionExclusiveExclusive13107293900271269TransactionExclusiveExclusive13107293900271294TransactionExclusiveExclusive13107293900271317TransactionExclusiveExclusive13107293900271410TransactionExclusiveExclusive13107293900272613TransactionExclusiveExclusive13107293900272664TransactionExclusiveExclusive13107293900272683TransactionExclusiveExclusive13107293900272703TransactionExclusiveExclusive13107293900272716TransactionExclusiveExclusive13107293900272790TransactionExclusiveExclusive13107293900272796TransactionExclusiveExclusive13107293900272813TransactionExclusiveExclusive13107293900272846TransactionExclusiveExclusive13107293900272862TransactionExclusiveExclusive13107293900272879TransactionExclusiveExclusive13107293900274197TransactionExclusiveExclusive13107293900274321TransactionExclusiveExclusive1310729390027 案例分析 可怕的锁等待 这只是其中的一组 严重时类似的等待与被等待session占据整个连接池 活动线程占据整个集群24个server所有线程池 整个应用集群无法继续提供服务 第一步 先找到锁的源头SQL selectsid id1 id2 blockfromv lockwhereid1 1310729andid2 390027andblock0 SIDID1ID2BLOCK 276013107293900271第二步 查看其运行状态SQL selectsql id event statusfromv sessionwheresid 2760 SQL IDEVENTSTATUS nullSQL NetmessagefromclientINACTIVE 该session当前没有做数据库操作 数据库层看锁等待 ACTIVE ExecuteThread 15 forqueue weblogic kernel Default self tuning daemonprio 1tid 0 x09e5f620nid 0 x1f20runnable 0 x3c5c5000 0 x3c5c5f30 SocketInputStream socketRead0 NativeMethod SocketInputStream read SocketInputStream java 129 ns Packet receive 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 driver 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 driver 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 executeInternal 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 100 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 VoucherBO java 2479 中间件层看锁等待 处于等待状态的线程堆栈 ACTIVE ExecuteThread 17 forqueue weblogic kernel Default self tuning daemonprio 1tid 0 x09b0d1c0nid 0 x2565waitingformonitorentry 0 x3c1bc000 0 x3c1bde30 atjava lang Throwable printStackTrace Throwable java 462 waitingtolock ajava io PrintStream atjava lang Throwable printStackTrace Throwable java 452 at Proxy22 queryByConditionVO UnknownSource atsun reflect NativeMethodAccessorImpl invoke0 NativeMethod atsun reflect NativeMethodAccessorImpl invoke NativeMethodAccessorImpl java 39 atsun reflect DelegatingMethodAccessorImpl invoke DelegatingMethodAccessorImpl java 25 atjava lang reflect Method invoke Method java 585 n serv ServiceDispatcher invokeBeanMethod ServiceDispatcher java 320 n serv ServiceDispatcher execCall ServiceDispatcher java 129 n serv CommonServletDispatcher doGet CommonServletDispatcher java 76 n serv CommonServletDispatcher doPost CommonServletDispatcher java 95 atjavax servlet http HttpServlet service HttpServlet java 763 atjavax servlet http HttpServlet service HttpServlet java 856 atweblogic servlet internal StubSecurityHelper ServletServiceAction run StubSecurityHelper java 225 atweblogic servlet internal StubSecurityHelper invokeServlet StubSecurityHelper java 127 atweblogic servlet internal ServletStubImpl execute ServletStubImpl java 272 atweblogic servlet internal TailFilter doFilter TailFilter java 26 atweblogic servlet internal FilterChainImpl doFilter FilterChainImpl java 42 持有锁的线程堆栈 此线程正在等待JAVA锁对象因此迟迟不能执行完成 STUCK ExecuteThread 14 forqueue weblogic kernel Default self tuning daemonprio 1tid 0 x0a1354e0nid 0 x17f3runnable 0 x3c8cb000 0 x3c8cc0b0 atjava io FileOutputStream writeBytes NativeMethod atjava io FileOutputStream write FileOutputStream java 260 atjava io BufferedOutputStream 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 java 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 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 这是持有锁的线程 00 xffffe410in kernel vsyscall 10 xf7fcf41bin write nocancel from lib tls libpthread so 0 20 xf78b57b3inJVM Write 30 xf75803deinVerifyClassCodesForMajorVersion 40 xf757c347inJava java io FileOutputStream writeBytes 50 xf187308fin 60 x095ddd48in 70 x3d1c3528in 80 x3d1c3524in 90 x00000000in 不熟悉中间件的系统管理员也难成为好的性能优化师 这个问题根本的解决之道是修正操作系统的缺陷 线程监控 性能监控工具简介 谁 做什么 做多久了 消耗资源是什么 线程信息 中间件层端到端监控 以此和JAVATHREADDUMP进行关联快速定位应用系统线程问题 活动线程详细信息 当前SQL 历史SQL 中间件层端到端监控 联查执行计划 中间件层端到端监控 案例分析 设计角度看优化 DB层面的优化手段 优化前后对比 8566989 9697880多倍 厂商层面的动作 类似的问题光pub里就能找到很多 处理起来也是各显身手 SELECT FROMeh ladingbillWHERE nvl dr 0 0 and nvl dr 0 0andvbillstatus 1andpk corp 1031 andbillnonotin selectbillcodefromdap finindexwherepk corp 1031 andnvl dr 0 0 从设计的角度来看 eh ladingbill可以设计一个字段来回写状态或者下游单据id来避免这个问题 SQL将简化为 SELECT FROMeh ladingbillWHERE nvl dr 0 0 and nvl dr 0 0andvbillstatus 1andpk corp 1031 andxx flag 1 从设计的角度看 selectc pk produce b pk invmandoc a pk invbasdocfrombd invbasdoca bd invmandocb bd producecwherea pk invbasdoc b pk invbasdocandb pk invmandoc c pk invmandocanda pk invcl 0001A8100000000007AX andc pk calbody 1146B8100000000003VD anda invcode 130104990001 anda invcode 130104990089 orderbya invcode Id Operation Name Rows Bytes Cost CPU Time 0 SELECTSTATEMENT 1 160 44 0 00 00 01 1 TABLEACCESSBYINDEXROWID BD PRODUCE 1 63 1 0 00 00 01 2 NESTEDLOOPS 1 160 44 0 00 00 01 3 NESTEDLOOPS 1 97 43 0 00 00 01 4 TABLEACCESSBYINDEXROWID BD INVBASDOC 1 55 2 0 00 00 01 5 INDEXRANGESCAN I BD INVBAS 1 1 1 0 00 00 01 6 TABLEACCESSBYINDEXROWID BD INVMANDOC 102 4284 41 0 00 00 01 7 INDEXRANGESCAN I INVMAN TEST 102 1 0 00 00 01 8 INDEXRANGESCAN I BD PRODUCE1 1 1 0 00 00 01 案例分析 理解业务逻辑与SQL优化 这类问题金煜基本上瞧一眼就能迅速给出解决方案 熟悉业务逻辑令SQL优化柳暗花明 bd invbasdoc bd invmandoc bd produce 档案编码 类别 库存组织 a pk invbasdoc b pk invbasdoc c pk calbody 1146B8100000000003VD b pk invmandoc c pk invmandoc a pk invcl 0001A8100000000007AX anda invcode 130104990001 anda invcode 130104990089 2万 200万 500万 业务逻辑关系 不熟悉业务逻辑的二次开发人员也别想成好的性能优化师 隐含的条件 Pk corp 1146 库存单据查询实际案例 FROMic general hhead ic general bbodywherehead cgeneralhid body cgeneralhidand head dr 0ANDbody dr 0ANDhead cbilltypecode 4D andbody bonroadflag N andbody pk corp 1404 and 1 1 AND head dbilldate 2011 01 01 and head dbilldate 2011 01 20 and 1 1 原来的逻辑读达到 136582consistentgets 只需增加 andhead pk corp 1404 逻辑读将为 2938consistentgets提高 4648 PUB上的类似案例 模拟原始SQL及其执行情况 逻辑读 9262734耗时 6分51秒 简单优化后执行时间 21秒逻辑读变为 339469262734 33946 27286 selectdistinctLENGTH B OBJECT NAME asLENfromTEST OBJ1b隐含的业务逻辑 当 B OBJECT NAME LEN时 substr a object name 1 LEN b object nameLOOP selecta b object namefromTEST OBJECTa TEST OBJ1bwheresubstr a object name 1 LEN b object nameandlength b object name LEN 人肉优化器的不足 假设count distinctLENGTH B OBJECT NAME 为10 那么需要循环10次 将对表访问10次更好的方式 优化设计 数据库厂商增加 optimizer like join rewrite 优化思路解析 SQL selectdistinct freevalueid 2fromgl freevaluea3where checktype 00010000000000000073 and4 checkvaluein selectpk cubasdocfrombd cumandoc7where pk corp 1177 orpk corp 0001 9or checktype 0001A8100000000004QM 10groupbyfreevalueid11havingcount 2ExecutionPlan Id Operation Name Rows Bytes Cost CPU Time 0 SELECTSTATEMENT 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 0recursivecalls0dbblockgets3817525consistentgets0physicalreads3024rowsprocessed 案例分析 如何兼顾业务灵活性与性能 Statistics 0recursivecalls0dbblockgets14412consistentgets0physicalreads0redosize84630bytessentviaSQL Nettoclient2561bytesreceivedviaSQL Netfromclient203SQL Netroundtripsto fromclient1sorts memory 0sorts disk 3024rowsprocessed 改写SQL 可以看到性能提升 DB层面技术问题 OracleCBO目前还未修正的一个缺陷 3024rowsprocessed 去掉子查询 虽然结果集多了很多但逻辑读却很小 DB层面可采取的技术手段 借用此HINT可以绕过该问题 DB层面可采取的技术手段 统计信息 571recursivecalls2dbblockgets463657consistentgets3physicalreads0redosize246bytessentviaSQL Nettoclient327bytesreceivedviaSQL Netfromclient1SQL Netroundtripsto fromclient22sorts memory 0sorts disk 0rowsprocessed 统计信息 667recursiveca