1.问题描述

下面这个 SQL 执行超过 1000 秒……

本文用这个例子，谈谈标量子查询慢的原因和优化方法。

selectrq.processinstid processinstid,rq.question_id questionId,rq.question_no questionNo,to_char(rq.rev_start_date, 'yyyy-MM-dd') revStartDate,(selecte.namefromewheree.category_code = 'REV_SOURCE'and e.code = rq.rev_source) revSource,(selecte.namefromewheree.category_code = 'QUESTION_TYPE'and e.code = rq.question_type) questionType,rq.question_summary questionSummary,rq.question_desc questionDesc,to_char(rq.question_discover_date, 'yyyy-MM-dd') questionDiscoverDate,rq.aud_project_type audProjectType,(selectd.dept_namefromdwhered.dept_id = rq.check_dept) checkDept,(selectto_char(wm_concat(distinct(k.org_name)))fromo,kwhereo.question_id = rq.question_idand o.ASC_ORG = k.org_idand o.REFORM_TYPE = '0') ascOrg,(selectto_char(wm_concat(distinct(k.dept_name)))fromo,fnd_dept_t kwhereo.question_id = rq.question_idand o.MAIN_REV_DEPT = k.dept_idand o.REFORM_TYPE = '0') mainRevDept,(selecte.namefromewheree.category_code = 'REV_FINISH_STATE'and e.code = rq.rev_finish_state) revFinishState,to_char(rq.compliance_date, 'yyyy-MM-dd') complianceDATEfromrqleft join REM_QUESTION_PLAN_T t on rq.question_id = t.question_idleft join fnd_org_t org on t.ASC_ORG = org.org_idwhere1 = 1and rq.asc_org is nulland (t.asc_org in (selectf.org_idfromfwheref.org_type = 'G')or rq.created_by_org in (selectf.org_idfromfwheref.org_type = 'G'))and rq.company_type = 'G';

2.分析过程

执行计划如下：

===========================================================|ID|OPERATOR|NAME|EST. ROWS|COST|-----------------------------------------------------------|0 |SUBPLAN FILTER||6283|788388847||1 | SUBPLAN FILTER||6283|1325483||2 |HASH OUTER JOIN ||8377|210530||3 |TABLE SCAN|RQ|7966|77932||4 |TABLE SCAN|T|152919|59150||5 |TABLE SCAN|F|440|2763||6 |TABLE SCAN|F|440|2763||7 | TABLE SCAN|E(SYS_C0011218)|1|92||8 | TABLE SCAN|E(SYS_C0011218)|1|92||9 | TABLE GET|D|1|46||10| SCALAR GROUP BY||1|62483||11|NESTED-LOOP JOIN||1|62483||12|TABLE SCAN|O|1|62468||13|TABLE GET|K|1|28||14| SCALAR GROUP BY||1|62483||15|NESTED-LOOP JOIN||1|62483||16|TABLE SCAN|O|1|62468||17|TABLE GET|K|1|27||18| TABLE SCAN|E(SYS_C0011218)|1|92|===========================================================

每个子算子的成本都不高，但总成本很高！

下面结合 SQL 语法语义进行解读。

首先，这个 SQL 从语法上分两部分：

因此，这个 SQL 的执行逻辑是（也就是执行计划里的 0 号SUBPLAN FILTER算子）：

为了定位 SQL 到底慢在哪一步？让我们继续拆解。

SQL 中 10、14 两个算子对应的标量子查询如下，还可以再拆解 SQL，单独只做一次 、k表的关联查询（如下标黄部分）要 200 毫秒：

selectxxx,(selectto_char(wm_concat(distinct(k.org_name)))fromREM_QUESTION_PLAN_T o,fnd_org_t kwhereo.question_id = rq.question_idand o.ASC_ORG = k.org_idand o.REFORM_TYPE = '0') ascOrg,(selectto_char(wm_concat(distinct(k.dept_name)))fromREM_QUESTION_PLAN_T o,fnd_dept_t kwhereo.question_id = rq.question_idand o.MAIN_REV_DEPT = k.dept_idand o.REFORM_TYPE = '0') mainRevDept,xxxfrom t(外部查询，结果有 13 万行);

3.结论

标量子查询的执行计划只能是循环嵌套连接，也就是SUBPLAN FILTER算子（等同于NESTED-LOOP JOIN执行逻辑），它的执行效率取决于两个因素：

因此只有当外部查询结果集不大，并且子查询的关联字段有高效索引时，执行效率才高。如果关联字段没有索引，优化器也没法像JOIN语法一样使用HASH JOIN算子，执行效率很差。

在上面这个慢 SQL 中，有两个标量子查询不只和外表关联，它内部还有关联查询，所以即使关联字段有索引，子查询单次执行的效率也受限，再加上要执行 13 万次，这个耗时就长了。所以这个 SQL 只能改写成LEFT JOIN来优化，这也是标量子查询的标准优化方法。

4.优化方案

这个 SQL 的标量子查询中有聚合函数，应该先GROUP BY聚合后再和外表关联，SQL（局部）改写如下：

with t1 as (selecto.question_id,to_char(wm_concat(distinct(k.org_name))) as org_namefromREM_QUESTION_PLAN_T o,fnd_org_t kwhereo.ASC_ORG = k.org_idand o.REFORM_TYPE = '0'group byo.question_id),t2 as (selecto.question_id,to_char(wm_concat(distinct(k.dept_name))) as dept_namefromREM_QUESTION_PLAN_T o,fnd_dept_t kwhereo.MAIN_REV_DEPT = k.dept_idand o.REFORM_TYPE = '0'group byo.question_id)selectxxx,t1.org_name as ascOrg,t2.dept_name as mainRevDept,xxxfrom t(外部查询，结果有 13 万行)left join t1 on t.question_id=t1.question_idleft join t2 on t.question_id=t2.question_id;

改写后的执行计划如下（变成了使用HASH OUTER JOIN算法），可以看到。

成本 7.88 亿降到了 365 万，执行耗时降到 10 秒！

=============================================================|ID|OPERATOR|NAME|EST. ROWS|COST|-------------------------------------------------------------|0 |SUBPLAN FILTER||6318|3653489||1 | MERGE GROUP BY||6318|1636701||2 |SORT||6318|1632074||3 |SUBPLAN FILTER||6318|1613799||4 |HASH OUTER JOIN||8424|492531 ||5 |HASH OUTER JOIN||8377|331672 ||6 |MERGE OUTER JOIN||7966|198317 ||7 |TABLE SCAN|RQ|7966|77932||8 |SUBPLAN SCAN|T2|2351|119098 ||9 |MERGE GROUP BY||2351|119062 ||10|SORT||2352|118658 ||11|HASH JOIN||2352|113818 ||12|TABLE SCAN |K|22268|8614||13|TABLE SCAN |O|76460|60075||14|TABLE SCAN|T|152919|59150||15|SUBPLAN SCAN|T1|76415|118014 ||16|HASH JOIN||76415|116865 ||17|TABLE SCAN|K|7033|2721||18|TABLE SCAN|O|76460|60075||19|TABLE SCAN|F|440|2763||20|TABLE SCAN|F|440|2763||21| TABLE SCAN|E(SYS_C0011218)|1|92||22| TABLE SCAN|E(SYS_C0011218)|1|92||23| TABLE GET|D|1|46||24| TABLE SCAN|E(SYS_C0011218)|1|92|=============================================================

作者：胡呈清，爱可生 DBA 团队成员，擅长故障分析、性能优化，个人博客：[简书 | 轻松的鱼]