某保险理赔核心系统oracle平迁ob国产化项目，目前已经投产完成，稳定运行。
其中遇到条执行3600s 慢sql，这条sql是 hibernate 拼接出来的语句，如果页面不同选项的话，表顺序，谓词过滤条件内容都会不一样。
目前只针对这条拼接的慢sql进行优化，并无实际多大用处，记录个改法案例，实际优化方案还是建议应用基于业务逻辑优化拼接sql的逻辑。
OB原厂技术、架构交流，性能优化沟通交流可以联系笔者。
慢SQL如下：
SELECT COUNT(*) AS COL_0_0_ FROM GGGGGGG WBUSINESSC0_, VVVVVVV VVVVVVV1_, GGDFFF PRPLBPMCOM2_, SDQQQQQ PRPDCOMPAN3_, YUYUYU WBUSINESSS4_ WHERE WBUSINESSC0_.NODEID=WBUSINESSS4_.ID AND WBUSINESSC0_.TASKID=PRPLBPMCOM2_.BPMMAINID AND WBUSINESSC0_.VALID=1 AND VVVVVVV1_.RISKCODE<>'9999' AND VVVVVVV1_.MAKECOM=PRPDCOMPAN3_.COMCODE AND VVVVVVV1_.MAKECOM=PRPDCOMPAN3_.COMCODE AND WBUSINESSC0_.BUSINESSNO=VVVVVVV1_.PROPOSALNO AND VVVVVVV1_.CLASSCODE=('01') AND ( WBUSINESSC0_.INDATE BETWEEN (TO_DATE('2024-11-20 00:00:00','YYYY-MM-DD HH24:MI:SS')) AND ( TO_DATE('2024-11-24 00:00:00','YYYY-MM-DD HH24:MI:SS'))) AND WBUSINESSC0_.STATE=('1') AND ( EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITEFOUR' OR EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITEFIVE' OR EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITESIX' OR EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITESEVEN' OR EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITETWO' OR EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITETHREE' OR EXISTS (SELECT 1 FROM SDQQQQQ B_ WHERE PRPLBPMCOM2_.COMCODE1=B_.COMCODE START WITH B_.COMCODE IN ('00000000') CONNECT BY PRIOR B_.COMCODE= B_.UPPERCOMCODE AND PRIOR B_.COMCODE <> B_.COMCODE) AND 1=1 AND ( WBUSINESSC0_.RISKCODE IN ('0105' , '0128' , '0103' , '0101' , '0198' , '0199' , '0109' , '0107' , '0108')) AND WBUSINESSS4_.NODENAME='UNDERWRITEONE');
慢SQL执行计划：
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ============================================================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | -------------------------------------------------------------------------------------------------------------- | | |0 |SCALAR GROUP BY | |1 |93712 | | | |1 |└─SUBPLAN FILTER | |2 |93712 | | | |2 | ├─NESTED-LOOP JOIN | |2 |93412 | | | |3 | │ ├─NESTED-LOOP JOIN | |2 |93382 | | | |4 | │ │ ├─NESTED-LOOP JOIN | |2 |93353 | | | |5 | │ │ │ ├─HASH JOIN | |2 |93310 | | | |6 | │ │ │ │ ├─TABLE RANGE SCAN |WBUSINESSS4_(IDX_YUYUYU) |1 |17 | | | |7 | │ │ │ │ └─TABLE RANGE SCAN |WBUSINESSC0_(IDX_VVVVVVV_STATE1) |105 |93283 | | | |8 | │ │ │ └─DISTRIBUTED TABLE RANGE SCAN |PRPLBPMCOM2_(IDX_GGDFFF_BPMMAINID) |1 |27 | | | |9 | │ │ └─DISTRIBUTED TABLE GET |VVVVVVV1_ |1 |18 | | | |10| │ └─DISTRIBUTED TABLE GET |PRPDCOMPAN3_ |1 |18 | | | |11| ├─LIMIT | |1 |27 | | | |12| │ └─SUBPLAN SCAN |VIEW1 |1 |27 | | | |13| │ └─NESTED-LOOP CONNECT BY | |18 |27 | | | |14| │ ├─SUBPLAN SCAN |VIEW2 |1 |5 | | | |15| │ │ └─TABLE GET |B_ |1 |5 | | | |16| │ └─SUBPLAN SCAN |VIEW3 |17 |21 | | | |17| │ └─DISTRIBUTED TABLE RANGE SCAN|B_(IDX_COMPANY_UPPERCOMCODE) |17 |21 | | | |18| ├─LIMIT | |1 |27 | | | |19| │ └─SUBPLAN SCAN |VIEW4 |1 |27 | | | |20| │ └─NESTED-LOOP CONNECT BY | |18 |27 | | | |21| │ ├─SUBPLAN SCAN |VIEW5 |1 |5 | | | |22| │ │ └─TABLE GET |B_ |1 |5 | | | |23| │ └─SUBPLAN SCAN |VIEW6 |17 |21 | | | |24| │ └─DISTRIBUTED TABLE RANGE SCAN|B_(IDX_COMPANY_UPPERCOMCODE) |17 |21 | | | |25| ├─LIMIT | |1 |27 |