最近经常遇到分层查询SQL的性能问题。结合历史失败案例，我总结了一些常见的connectby场景性能失败类型，并在本文中分享。1、result中的Filtering或者spanningtree中的filtering放在where之后，用来切割结果树生成后的叶子节点；放置在connectby之后，用于在生成树的过程中切割子树。一个经常出现的现象是，在业务逻辑中，过滤前不一定要生成结果树。由于开发者将过滤条件放在不同的位置（在where之后，在connectby之后），导致过滤效果混乱，导致性能低下。下面的SQL就是一个典型的案例。根据用户反馈，生产环境上zzzz.SYS_RC_ROUTE_DETAIL表有3000+条数据，但是SQL语句没有跑出来。work_order='yyyyyyyyy')startwitht.node_type='0'connectbynocyclepriornext_node_id=node_id让客户运行SQL一分钟然后取消，抓取监控报告如下：问题很明显，有很多重复的值nextnodeid=表中的node_id，导致出现大量的结果集。SQL运行一分钟内，connectby还没有完成完整树的制作，已经有3000W+的数据了，所以我们开始思考是否逻辑上需要在构建完完整树后进行过滤。与业务部门沟通后，发现不需要。下面的数据可以测试，3000行数据，但是count(*)会很慢。SQL>createtabletest1asselectmod(rownum,2)id,mod(rownum+1,2)id2fromdualconnectbylevel<=3000;2345678Tablecreated.SQL>settimingonSQL>selectcount(*)fromtest1whereid=0startwithid=0connectbynocyclepriorid=id2;COUNT(*)----------1500Elapsed:00:09:26.88SQL>结果中的filter如上图，耗时9分钟；而生成树中的过滤器只用了0.3秒：SQL>selectcount(*)fromtest1startwithid=0connectbynocyclepriorid=id2andid=0;COUNT(*)----------1500Elapsed:00:00:00.31在很多情况下，两种写法的结果集可能相同，如下：createtabletest2asseselectrownumid,rownum+1id2,rownum+2id3fromdualconnectbylevel<=3000;SQL>selectidfromtest2whereid3<10startwithid=3connectbynocyclepriorid2=id;ID---------12345677rowsselected.SQL>selectidfromtest2startwithid=1connectbynocyclepriorid2=idandid3<10;ID--------12345677rowsselected。但实际上这两种写法在语义上差别很大，结果集也可能不同，如下：SQL>selectidfromtest2whereid3=10startwithid=3connectbynocyclepriorid2=id;ID---------8Elapsed:00:00:00.13SQL>selectidfromtest2startwithid=3connectbynocyclepriorid2=idandid3=10;我D------------3Elapsed:00:00:00.002.CBO估计不准确层次查询SQL语句经常出现的问题是CBO估计返回的结果集有偏差，导致执行计划不准确不准确的表虽然我收集了统计信息，但是CBO对结果集的估计与实际值偏差很大（相差数百甚至数千倍），但这不能完全归咎于CBO。毕竟递归查询有多少层，需要裁剪多少数据？综合起来，结果确实无法估量。针对CBO估计不准确的问题，我们考虑了结果集比较特殊的参数，在SQL文本中进行了区分，将特殊参数的识别应用到带提示的修改后的SQL中运行，通过提示指定返回的结果集。这种情况不同于普通的数据倾斜，无法通过基线给出不涉及应用修改的解决方案。3.并行处理层次查询的SQL直接使用parallel的hint，会遇到并行序列化的问题，即不能真正并行。对于一些重要且耗时的层次查询，可以考虑重写SQL实现PIPELINEDTABLEFUNCTION。以下脚本测试参考了陈焕生童鞋的博客以及oracle相关文档(DocID2168864.1)：droptablet1;--t1with100,000rowscreatetablet1asselectrownumid,lpad(rownum,10,'0')v1,trunc((rownum-1)1)/100,rpad(rownum,100)paddingfromdualconnectbylevel<=100000;begindbms_stats.gather_table_stats(user,'T1');结束;/select/*+monitor*/count(*)from(selectCONNECT_BY_ROOTltrim(id)root_id,CONNECT_BY_ISLEAFis_leaf,levelast1_level,a.v1fromt1astartwitha.id<=1000connectbyNOCYCLEid=priorid+1000);createorreplacepackagerefcur_pkgASTYPER_RECISRECORD(row_idROWID);TYPErefcur_tISREFCURSORRETURNR_REC;END;/createorreplacepackageconnect_by_parallelas/*Naviagatesashallowhiearchyinparallel,wherewedoatreewalkforeachroot*/CURSORC1(p_rowidROWID)IS--Cursordoneforeachsubtree.ThisselectisprovidedbythecustomerselectCONNECT_BY_ROOTltrim(id)root_id,CONNECT_BY_ISLEAFis_leaf,levelast1_level,a.v1fromt1astartwithrowid=p_rowidconnectbyNOCYCLEid=priorid+1000;TYPET1_TABisTABLEOFC1%ROWTYPE;FUNCTIONtreeWalk(p_refrefcur_pkg.refcur_t)RETURNT1_TABPIPELINEDPARALLEL_ENABLE(PARTITIONp_refBYANY);ENDconnect_by_parallel;/createorreplacepackagebodyconnect_by_parallelasFUNCTIONtreeWalk(p_refrefcur_pkg.refcur_t)RETURNT1_TABPIPELINEDPARALLEL_ENABLE(PARTITIONp_refBYANY)ISin_recp_ref%ROWTYPE;BEGINexecuteimmediate'altersessionset"_old_connect_by_enabled"=true';LOOP--foreachrootFETCHp_refINTOin_rec;EXITWHENp_ref%NOTFOUND;FORc1recINc1(in_rec.row_id)LOOP--retrierowowsofsubtreePIPEROW(c1rec);ENDLOOP;ENDLOOP;executeimmediate'altersessionset"_old_connect_by_enabled"=false';RETURN;ENDtreeWalk;ENDconnect_by_parallel;/SELECT/*+monitor*/COUNT(*)FROMTABLE(connect_by_parallel(RS*OR.tree+al((a100)*/rowidFROMt1aWHEREid<=100)))b;层次查询的SQL在整个SQL优化场景中所占比例比较小，但是这类SQL优化往往比较麻烦。本文分享的三个案例是在实战中总结出来的，对Oracle分层查询的SQL优化有很大的参考意义，尤其是陈焕生提供的并行案例，含金量很高。有兴趣的童鞋可以测试一下。笔者介绍姜健，云趣网络科技人联合创始人，OracleACE，11gOCM，多年Oracle设计、管理和实施经验，精通数据库优化、OracleCBO和并行原理。云帆鹰眼监控核心设计师和开发者，资深PythonWeb开发者。
