运维 OB

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 团队成员，长于缺点剖析、性能提升，团体博客：[简书 | 轻松的鱼]