黄远邦-复杂而有趣的ORACLE优化案例集锦

复杂而有趣的ORACLE优化案例集锦 黄远邦 13701026113 [e-mail] 关于我• 北京中亦科技资深ORACLEDBA• 擅长数据中心大规模ORACLE运维• 擅长ORACLESQL优化、综合性能优化• 擅长ORACLE综合故障诊断• 开发了多款ORACLE运维实用工具内容摘要RAC优化案例1之SQL优化RAC优化案例2之SQL改写优化RAC优化案例3之实例优化RAC优化案例4之操作系统和IO优化RAC优化案例5之架构优化SQL优化案例1（不看SQL语句直接优化SQL）判断SQL效率是否有问题StatNameStatementPerExecutionElapsedTime(ms)2,919,39714,241CPUTime(ms)1,168,2705,699Executions205N/ABufferGets2.05E+0799,939.1DiskReads75,393368ParseCalls1850.9Rows2,21011UserI/OWaitTime(ms)312,852N/AClusterWaitTime(ms)443,562N/AApplicationWaitTime(ms)0N/A分析执行计划找到执行计划的问题点Id=20的步骤占据了整个cost18672中的18480,该步骤是瓶颈！虽然是全表扫描，但是该表应用过滤条件后返回3308K行，330万条，没问题？找到执行计划的问题点id=9和id=19的步骤做hashjoin,1行和330万行表关联，返回1行(id=8),即小结果集和大结果集关联返回小结果集，应该走嵌套昀合适，没走，那就是不满足嵌套连接的条件，即内部表TB2缺少索引！找到谓词部分和连接条件即可定位问题提出解决方案并测试createindexidx_tb2_idontb2(id)onlinetablespace&tablespace_name;优化效果对比指标原SQL语句优化后执行时间140.1逻辑读99,9395348内容摘要RAC优化案例1之SQL优化RAC优化案例2之SQL改写优化RAC优化案例3之实例优化RAC优化案例4之操作系统和IO优化RAC优化案例5之架构优化SQL优化案例2之SQL等价改写问题描述：程序效率低下，无法达到上线要求,执行时间超过5天未结束SQL语句：原SQL语句较长，将该复杂SQL简化为下列模型Select*fromAwhere(A.idinx_subqueryora.idnotiny_subquery)and(A.idinz_subqueryora.idnotinw_subquery)思考下这个SQL写法会有什么问题？SQL效率低下原因分析Select*fromAwhere(A.idinx_subqueryora.idnotiny_subquery)and(A.idinz_subqueryora.idnotinw_subquery)对于(or)and(or)的写法，oracle查询转换是无能为力的优化器原理上只能按照下列方法实现1）A表取一条记录必须去探测子查询x/y/z/w2）A表再取一条记录再去探测子查询3）直到A表所有记录探测完成，SQL执行计划表连接只能是filter,而无法利用hashantijoinA表满足条件的记录数达到14万条，需要探测子查询14万次每探测一次都需要把各个子查询重新执行一次！这就是为什么执行时间超过5天都没有结束的原因！如何优化？Select*fromAwhere(A.idinx_subqueryorA.idnotiny_subquery)and(A.idinz_subqueryorA.idnotinw_subquery)X是y都来自同一张表，且x是y的子集z是w都来自同一张表，且z是w的子集画图是SQL等价改写很好的习惯(A.idinx_subqueryornotiny_subquery)的执行过程XYAIN的结果NOTIN的结果最终满足条件的结果！A.idnotin(y_subqueryminusx_subquery)的结果见绿色部分XIN的结果xYminusxyA绿色为最终结果，与前面写法结果一致！结果比较XIN的结果xYminusxyAXYIN的结果NOTIN的结果最终满足条件的结果！A昀终写法Select*fromAwhere(A.idinx_subqueryorA.idnotiny_subquery)and(A.idinz_subqueryorA.idnotinw_subquery)Select*fromAwhereA.idnotin(y_subqueryminusx_subquery)andA.idnotin(w_subqueryminusz_subquery)A可以先后两次与子查询的结果做hashantijoin,只需要各扫描一次！而不是扫描14万次子查询，效率极大提升！优化效果比对优化前，运行时间超过5天仍未结束。优化后，运行时间为10分钟。内容摘要RAC优化案例1之SQL优化RAC优化案例2之SQL改写优化RAC优化案例3之实例优化RAC优化案例4之操作系统和IO优化RAC优化案例5之架构优化RAC优化案例4之实例调优现象执行计划完全一样情况下，批量时快时慢正常2小时，慢则长达10小时根据时间分解原则，时间主要消耗在RACglobalcache环节Gc单次等待达到几百毫秒！RAC优化案例4之实例调优私网流量仅1M/秒，时间都消耗到哪了？RAC优化案例4之实例调优对Globalcache各个环节的时间进一步分解按照公式计算Avgglobalcachecrblockreceivetime(ms)=Avgglobalcachecrblockbuildtime(ms)+Avgglobalcachecrblocksendtime(ms)+Avgglobalcachecrblockflushtime(ms)+Avgmessagesentqueuetimeonksxp(ms)+其他？87 显然不等于 0.0 + 0.0 + 3.1 + 0.2  还差了80毫秒哪去了？ 这里的其他还包括什么？  通过上述分析，我们发现： (1)节点1发送至节点2的指标正常 (2)节点1收到节点1的消息后处理指标正常 (3)节点2返回消息至节点1的指标正常 时间都消耗发送队列中的等待了！但是节点1到节点2的总响应时间却是异常的这说明，有1个环节肯定是异常的，就是“消息从进入队列”到“被发送出去”之间的间隔，如果RAC出现流量控制，则会导致该问题。继续检查，如下所示：比较分析可以看到：（1）其中Avgmessagesentqueuetime(ms)指标就表示“消息从进入队列”到“被发送出去”之间的间隔,不过这个不是原因，该指标本身体现的是一个结果的指标。（2）异常时候的消息流控指标即指标“%offlowcontrolledmessages:”达到16.28%,而正常时候该指标只有0.08！（3）我们通过多次不同时间点比较，均有相同结论，当流控指标较大时，就会出现问题。什么时候出现RAC私网流量控制RAC里的流控，可以简单理解为机场中经常出现的流量控制，原理相同，下面情况下可能导致流控(1)天气出现异常，即私网网络链路不顺畅(2)目标机场负载较大等，即RAC对端节点负载很高(3)两地服务能力不对等，即两个节点节点传输配置有差异具体到我们此处的情况，私网传输指标正常，节点负载也非常低..因此，昀可能的就是两端服务能力不对等。而与RACGC传输能力不对等的也就是LMS进程了，该进程负责节点之间消息和BLOCK的传输和处理。当两个节点LMS进程个数不一致时，可能出现流量控制。因此，继续检查两个节点的LMS进程个数。进一步确认证据节点1：WedJul2416:19:222013LMS5:0GCSshadowscancelled,0closed,0Xwsurvived节点2：WedJul2416:21:182013LMS3startedwithpid=16,OSid=17694842atelevatedpriority可以看到：节点1有6个LMS进程，而节点2只有4个LMS进程。ORACLE中，如果没有设置gcs_server_processes参数来指定LMS进程的个数，那么数据库默认将以CPU个数来进行计算，以决定起多少个LMS进程。由于检查，未发现设置该参数，因此需要继续检查CPU个数。找到导致奇怪性能问题的原因节点1：节点2：可以看到,两个节点CPU个数不一样节点1逻辑CPU128，节点2为78.昀终性能问题解决方案在调整两个节点CPU个数一致并重启数据库后，两个节点LMS进程个数一致。调整完成后，原来约一周一次的GC缓慢问题，不再出现。其他解决方案CPU不一致情况下，需设置gcs_server_processes参数为一致内容摘要RAC优化案例1之SQL优化RAC优化案例2之SQL改写优化RAC优化案例3之实例优化RAC优化案例4之操作系统和IO优化RAC优化案例5之架构优化XX系统交易偶尔超时案例分享环境AixOracle11.2.0.3HDS存储现象偶尔出现交易超时（1秒即超时）往数据库Insert一条记录达到10秒钟通过技术手段具体到问题时刻进行深入分析插入的会话在等待什么资源呢？可以看到，多个会话在26秒到30秒期间都在等待commit提交返回，被会话995阻塞，会话995显然是oraclelgwr后台进程!阻塞的源头又在等什么呢？可以看到,lgwr正在将logbuffer中的redo持久化到硬盘中来进程无法获得CPU时间片？CPU很闲，lgwr进程属于CPU实时优先级，而不是共享级别，因此排除那么只剩下IO慢了，进一步通过采集sar中的IO响应时间来验证即可验证IO偶尔出现性能问题（为什么是偶尔超时）可以看到IO时间（等待+服务)在出现问题时响应时间剧增Avgwait时间即IO等待时间达到67毫秒，严重时达到150毫秒Avgserv响应时间一直6毫秒，问题在哪？这说明，瓶颈不在存储响应和san环境传输上，而是在OS级IO在OS队列里压根没有发出去。如果方向为检查存储性能，那方向就错了！案例总结解决将磁盘属性queue_depth从2调整到8后交易失败次数大幅减少如何进一步解决?案例总结进一步解决将redo在线迁移到新的RAID组后问题彻底解决原理Queue_depth空间换性能吃透原理对优化是多么的重要内容摘要RAC优化案例1之SQL优化RAC优化案例2之SQL改写优化RAC优化案例3之实例优化RAC优化案例4之操作系统和IO优化RAC优化案例5之架构优化RAC优化案例5之架构优化现象-RAC冗余性失效– 5月20日下午16点1分左右，XX系统数据库RAC集群节点2所在的P595发生硬件故障，导致节点2LPAR不可用。– 但从16点1分开始，应用程序无法连接到数据库RAC集群中存活的节点1.ORACLE数据库RAC集群未能发挥高可用架构的作用.需要核查问题原因，以便对系统高可用架构提出改进意见。故障分析详细过程-故障分析思路集群中一个节点宕掉，其他节点无法对外提供服务，通常情况下，是因为当中的集群软件没有完成对整个集群状态、数据的重组，因此数据未达到一致，所以集群其他节点无法对外提供服务。这个环境中用到的集群软件有：ORACLERAC/ORACLECRS/IBMHACMP因此，需要分别查看这三个集群是否完成了重组、重新配置。确认ORACLERAC是否完成重组• 查看一节点数据库alert日志,可以看到，RAC集群在16点1分32秒就完成了重组。可排除该问题。TueMay2016:01:30BEIST2014Reconfigurationstarted(oldinc8,newinc10)Listofnodes:0GlobalResourceDirectoryfrozen......TueMay2016:01:32BEIST2014SubmittedallGCS