优化案例2：select标量子查询且主查询排序
DM技术交流QQ群：940124259
1. 场景描述
华南服务中心的同事微信群上发出一条查询SQL，称带上排序操作执行很慢，需要近8秒的时间返回结果集。
意思比较明确，带上排序才会这么慢的，看似缩小排查范围，仅凭是否存在排序操作判定问题，难免会让人走进死胡同。
幸亏当时SQL不是很复杂，凭借达梦现在的工具ET和执行计划，结合平时的经验推测和技术手段，7分钟远程，准确定位真实的瓶颈点，
将该SQL优化到100毫秒，使同事拍手称赞。
2. 分析过程
2.1 查看原始SQL执行计划
explain
SELECT
a.fwid ,
a.ldid ,
a.mpid ,
a.MYCH ,
a.CH ,
a.fwh ,
a.th ,
a.fh ,
a.dh ,
a.fwlx ,
c.fwgn ,
c.fwgn fwgncode ,
c.jtgn ,
b.cg ,
b.fwhx ,
a.dz ,
a.mp ,
a.ZJZMJ as zmj ,
a.TNMJ ,
a.GTMJ ,
b.fbyt ,
b.FFBYT ,
b.cf ,
b.wsj ,
b.dq ,
b.xq ,
b.nq ,
b.bq ,
c.HTAH ,
c.yzzt ,
c.QQAH ,
c.QQZT ,
REPLACE(a.ch, '夹', '') pxch ,
nvl(c.zyzlzt, 0) zyzlzt ,
a.jzjg ,
c.djzt ,
c.htzt ,
a.jlzt ,
decode(c.sfhq, '0', '0', '1', '1', '0')sfhq ,
decode(c.sfzy, '0', '0', '1', '1', '0')sfzy ,
decode(c.sfgjpt, '0', '0', '1', '1', '0')sfgjpt,
decode(c.sfxjf, '0', '0', '1', '1', '0')sfxjf ,
c.xzzt ,
c.yyzt ,
c.dyzt ,
c.sfzg sfzg ,
mp.dz mpdz ,
c.sffc ,
a.jjmj ,
c.fwyt ,
a.xzqh xzqh_code ,
a.beiz ,
a.fcgbz ,
c.sfbj ,
c.gjptfl ,
a.YSZJZMJ ,
a.YSTNMJ ,
a.YSGTMJ ,
c.djah fwb_djah ,
c.JZMJDJ ,
c.TNMJDJ ,
a.chbdcdyh ,
a.chah ,
a.sfxgchmj ,
c.JCYFWZT ,
c.hbfgsdzt ,
nvl(c.islock, 0) islock ,
c.hqlx hqlx ,
c.DJAH DJAH ,
a.xzqh xzqh ,
C.SFJGBA SFJGBA ,
C.SFCWBA SFCWBA ,
(
select
decode(c.sfcwba, '1', decode(max(cw.balx), '1', '出售(1:1)', '3', '整体转让', '出售(大于1:1)'), '')
from
T_CWZS_CWZSAJB cw,
T_CWZS_CWAJB aj
where
cw.ajxxid =aj.ajxxid
and aj.fwid = a.fwid
and cw.del_flag = '0'
and aj.del_flag ='0'
and cw.jlzt = '1'
)
Sblx ,
a.kfsid kfsid,
c.sfgycq sfgycq
FROM
T_XMGK_FWFSB b,
T_XMGK_MPB mp ,
T_XMGK_FWZTB c,
T_XMGK_FWB a
left join T_XMGK_FCFFWGXB fcf
on
fcf.FWID = a.FWID
and fcf.del_flag = '0'
WHERE
a.FWID = b.FWID
and b.FWID = c.FWID
and a.mpid =mp.mpid
and b.del_flag = '0'
and a.del_flag = '0'
and c.del_flag = '0'
and mp.del_flag = '0'
and a.mpid = '112'
and c.hqlx <> '2'
Order By
CAST(regexp_replace(a.ch, '([0-9]-[0-9])|[^-0-9.]') as int),
CAST(regexp_replace(a.fwh, '([0-9]-[0-9])|[^-0-9.]') as int);
-- 执行计划
/*
1 #NSET2: [169, 1, 3408]
2 #PIPE2: [169, 1, 3408]
3 #PIPE2: [168, 1, 3408]
4 #PRJT2: [2, 1, 3408]; exp_num(75), is_atom(FALSE)
5 #SORT3: [2, 1, 3408]; key_num(2), is_distinct(FALSE), top_flag(0), is_adaptive(0)
6 #HEAP TABLE SCAN: [1, 1, 3408]; table_no(0),
7 #SPL2: [166, 1, 3760]; key_num(2), spool_num(0), is_atom(FALSE), has_variable(0)
8 #PRJT2: [166, 1, 3760]; exp_num(3), is_atom(FALSE)
9 #HAGR2: [166, 1, 3760]; grp_num(1), sfun_num(5); slave_empty(0) keys(DMTEMPVIEW_16784484.AUTOID)
10 #NEST LOOP LEFT JOIN2: [164, 1, 3760]; join condition(AJ.FWID = DMTEMPVIEW_16784484.ORDER_COL2)[with var] partition_keys_num(0) ret_null(0)
11 #HEAP TABLE SCAN: [1, 1, 3408]; table_no(0),
12 #HASH2 INNER JOIN: [81, 1, 352]; KEY_NUM(1); KEY(AJ.AJXXID=CW.AJXXID) KEY_NULL_EQU(0)
13 #SLCT2: [80, 1, 152]; (AJ.DEL_FLAG = '0' AND AJ.FWID = var1)
14 #CSCN2: [80, 550864, 152]; INDEX33569077(T_CWZS_CWAJB as AJ) -- 数据量有点大
15 #SLCT2: [1, 1306, 200]; (CW.DEL_FLAG = '0' AND CW.JLZT = '1')
16 #CSCN2: [1, 2516, 200]; INDEX33569078(T_CWZS_CWZSAJB as CW)
17 #HEAP TABLE: [1, 1, 3408]; table_no(0) full(FALSE), mpp_full(0) autoid(TRUE)
18 #PRJT2: [1, 1, 3408]; exp_num(72), is_atom(FALSE)
19 #INDEX JOIN LEFT JOIN2: [1, 1, 3408] join condition(FCF.DEL_FLAG = '0') ret_null(0)
20 #SLCT2: [1, 1, 3408]; (C.DEL_FLAG = '0' AND C.HQLX <> '2' AND A.FWID = C.FWID)
21 #NEST LOOP INDEX JOIN2: [1, 1, 3408]
22 #SLCT2: [1, 1, 1914]; B.DEL_FLAG = '0'
23 #NEST LOOP INDEX JOIN2: [1, 1, 1914]
24 #MERGE INNER JOIN3: [1, 1, 1410]; KEY_NUM(1); KEY(COL_4 = COL_1) KEY_NULL_EQU(0)
25 #SLCT2: [1, 21, 1266]; A.DEL_FLAG = '0'
26 #BLKUP2: [1, 22, 1266]; INDEX_XMGK_FWB_MPID(A)
27 #SSEK2: [1, 22, 1266]; scan_type(ASC), INDEX_XMGK_FWB_MPID(T_XMGK_FWB as A), scan_range['112','112']
28 #SLCT2: [1, 1, 144]; MP.DEL_FLAG = '0'
29 #CSEK2: [1, 1, 144]; scan_type(UNIQUE), INDEX33568939(T_XMGK_MPB as MP), scan_range['112','112']
30 #CSEK2: [1, 1, 0]; scan_type(UNIQUE), INDEX33568938(T_XMGK_FWFSB as B), scan_range[A.FWID,A.FWID]
31 #CSEK2: [1, 1, 0]; scan_type(UNIQUE), INDEX33568936(T_XMGK_FWZTB as C), scan_range[B.FWID,B.FWID]
32 #BLKUP2: [1, 1, 0]; INDEX_XMGK_FCFFWGXB_FWID(FCF)
33 #SSEK2: [1, 1, 0]; scan_type(ASC), INDEX_XMGK_FCFFWGXB_FWID(T_XMGK_FCFFWGXB as FCF), scan_range[A.FWID,A.FWID]
*/
执行计划解析：
- 首先将主查询中四张表做完连接（三个内连接、一个外连接），把中间结果集放在临时表DMTEMPVIEW_16784484（堆表：支持无序地并发插入）中并且进行编号table_no(0)。
- SPL2操作符下主要是将HEAP TABLE(DMTEMPVIEW_16784484)临时结果集带入标量子查询 T_CWZS_CWZSAJB和T_CWZS_CWAJB连接后进行相关子查询条件aj.fwid= a.fwid，可以理解成子查询平坦化。
- 标量子查询的子计划出现NEST LOOP [with var]字眼，说明存在变量改写后嵌套循环传递的连接方式，特别值得注意它是性能杀手
- 观察操作节点13 SLCT2 AJ.FWID = var1，由HEAP TABLE临时表结果集传入变量值过滤，又因T_CWZS_CWAJB表的数据量有点大56W行，在NEST LOOP连接方式的控制下，
被反复地全表扫描再过滤，加重HEAP TABLE结果集较大的情况下，执行效率慢合乎常理。 - 取出临时表DMTEMPVIEW_16784484按CAST(regexp_replace(a.ch/a.fwh, ‘([0-9]-[0-9])|[^-0-9.]’) as int)排序，目的是按数值方式排序（而不是字符对应的ASCII码表）。
- 排序完成后，将SPL2临时结果集进行AUTOID拼接结果，则全表的最终结果集就可获得SELECT所有查询项的值(包含标量子查询返回的关联列的计算值）。
2.2 ET定位耗时操作符
依据日常经验和执行计划关键点跟踪，大概率认为性能杀手在于标量子查询处，也就是SPL2子计划树。
有时凭借长时间的经验总结，80%问题能够解决，但也会在所谓的经验面前栽跟头。应该客观且科学性的分析问题，此时ET性能定位工具出场。
ET工具使用，建议以会话级的方式设置生效，免得影响生产环境，测试环境另当别论。
-- 执行SQL窗口打开SQL执行消耗监控
/*
普通用户：grant execute on sys.et to xxx;
*/
sf_set_session_para_value('monitor_sql_exec', 1);
call et(执行号);
/*
当时已弄好，忘了截图，此处仅提供方法学习
当时看到执行计划中4号节点PRJT2非常消耗时间，即SPL2右节点存在严重的性能问题，也就是标量子查询。
结合SPL2子计划树中WITH VAR的字眼，更好确定问题点，则应该着重优化标量子查询。
*/
2.3 注释大法
步骤2的跟踪已经接近现实，为了百分之百坚定问题根源，时而利用一下注释方法也挺便捷的，对SELECT中标量子查询括起注释，再次执行查询语句，返回时间极短。 此处不再具体演示，介绍常用方法足够。
3. 解决方法
3.1 HINT功法
1.禁止变量改写，转为HASH LEFT JOIN2，减少SPL2子计划树中标量子查询两表的全表扫描次数。2.执行计划大体方向变化不大，仅是在处理子查询平坦化时，采用哈希连接，操作节点编号10与13发生变化（不启用变量改写）。
select /*+ NO_USE_CVT_VAR */ ...
-- 执行计划
/*
1 #NSET2: [184, 1, 3408]
2 #PIPE2: [184, 1, 3408]
3 #PIPE2: [183, 1, 3408]
4 #PRJT2: [2, 1, 3408]; exp_num(75), is_atom(FALSE)
5 #SORT3: [2, 1, 3408]; key_num(2), is_distinct(FALSE), top_flag(0), is_adaptive(0)
6 #HEAP TABLE SCAN: [1, 1, 3408]; table_no(0),
7 #SPL2: [181, 1, 3760]; key_num(2), spool_num(0), is_atom(FALSE), has_variable(0)
8 #PRJT2: [181, 1, 3760]; exp_num(3), is_atom(FALSE)
9 #HAGR2: [181, 1, 3760]; grp_num(1), sfun_num(5); slave_empty(0) keys(DMTEMPVIEW_16794534.AUTOID)
10 #HASH LEFT JOIN2: [180, 1, 3760]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(DMTEMPVIEW_16794534.ORDER_COL2=AJ.FWID)
11 #HEAP TABLE SCAN: [1, 1, 3408]; table_no(0),
12 #SLCT2: [126, 484137, 352]; AJ.DEL_FLAG = '0'
13 #HASH2 INNER JOIN: [126, 484137, 352]; KEY_NUM(1); KEY(CW.AJXXID=AJ.AJXXID) KEY_NULL_EQU(0)
14 #SLCT2: [1, 1306, 200]; (CW.DEL_FLAG = '0' AND CW.JLZT = '1')
15 #CSCN2: [1, 2516, 200]; INDEX33569078(T_CWZS_CWZSAJB as CW)
16 #CSCN2: [74, 550864, 152]; INDEX33569077(T_CWZS_CWAJB as AJ)
17 #HEAP TABLE: [1, 1, 3408]; table_no(0) full(FALSE), mpp_full(0) autoid(TRUE)
18 #PRJT2: [1, 1, 3408]; exp_num(72), is_atom(FALSE)
19 #INDEX JOIN LEFT JOIN2: [1, 1, 3408] join condition(FCF.DEL_FLAG = '0') ret_null(0)
20 #SLCT2: [1, 1, 3408]; (C.DEL_FLAG = '0' AND C.HQLX <> '2' AND A.FWID = C.FWID)
21 #NEST LOOP INDEX JOIN2: [1, 1, 3408]
22 #SLCT2: [1, 1, 1914]; B.DEL_FLAG = '0'
23 #NEST LOOP INDEX JOIN2: [1, 1, 1914]
24 #MERGE INNER JOIN3: [1, 1, 1410]; KEY_NUM(1); KEY(COL_4 = COL_1) KEY_NULL_EQU(0)
25 #SLCT2: [1, 21, 1266]; A.DEL_FLAG = '0'
26 #BLKUP2: [1, 22, 1266]; INDEX_XMGK_FWB_MPID(A)
27 #SSEK2: [1, 22, 1266]; scan_type(ASC), INDEX_XMGK_FWB_MPID(T_XMGK_FWB as A), scan_range['112','112']
28 #SLCT2: [1, 1, 144]; MP.DEL_FLAG = '0'
29 #CSEK2: [1, 1, 144]; scan_type(UNIQUE), INDEX33568939(T_XMGK_MPB as MP), scan_range['112','112']
30 #CSEK2: [1, 1, 0]; scan_type(UNIQUE), INDEX33568938(T_XMGK_FWFSB as B), scan_range[A.FWID,A.FWID]
31 #CSEK2: [1, 1, 0]; scan_type(UNIQUE), INDEX33568936(T_XMGK_FWZTB as C), scan_range[B.FWID,B.FWID]
32 #BLKUP2: [1, 1, 0]; INDEX_XMGK_FCFFWGXB_FWID(FCF)
33 #SSEK2: [1, 1, 0]; scan_type(ASC), INDEX_XMGK_FCFFWGXB_FWID(T_XMGK_FCFFWGXB as FCF), scan_range[A.FWID,A.FWID]
*/
3.2 改写功法
标量子查询改为左外连接。
SELECT
a.fwid ,
a.ldid ,
a.mpid ,
a.MYCH ,
a.CH ,
a.fwh ,
a.th ,
a.fh ,
a.dh ,
a.fwlx ,
c.fwgn ,
c.fwgn fwgncode ,
c.jtgn ,
b.cg ,
b.fwhx ,
a.dz ,
a.mp ,
a.ZJZMJ as zmj ,
a.TNMJ ,
a.GTMJ ,
b.fbyt ,
b.FFBYT ,
b.cf ,
b.wsj ,
b.dq ,
b.xq ,
b.nq ,
b.bq ,
c.HTAH ,
c.yzzt ,
c.QQAH ,
c.QQZT ,
REPLACE(a.ch, '夹', '') pxch ,
nvl(c.zyzlzt, 0) zyzlzt ,
a.jzjg ,
c.djzt ,
c.htzt ,
a.jlzt ,
decode(c.sfhq, '0', '0', '1', '1', '0')sfhq ,
decode(c.sfzy, '0', '0', '1', '1', '0')sfzy ,
decode(c.sfgjpt, '0', '0', '1', '1', '0')sfgjpt,
decode(c.sfxjf, '0', '0', '1', '1', '0')sfxjf ,
c.xzzt ,
c.yyzt ,
c.dyzt ,
c.sfzg sfzg ,
mp.dz mpdz ,
c.sffc ,
a.jjmj ,
c.fwyt ,
a.xzqh xzqh_code ,
a.beiz ,
a.fcgbz ,
c.sfbj ,
c.gjptfl ,
a.YSZJZMJ ,
a.YSTNMJ ,
a.YSGTMJ ,
c.djah fwb_djah ,
c.JZMJDJ ,
c.TNMJDJ ,
a.chbdcdyh ,
a.chah ,
a.sfxgchmj ,
c.JCYFWZT ,
c.hbfgsdzt ,
nvl(c.islock, 0) islock ,
c.hqlx hqlx ,
c.DJAH DJAH ,
a.xzqh xzqh ,
C.SFJGBA SFJGBA ,
C.SFCWBA SFCWBA ,
-- 改写内容块 --
decode(c.sfcwba, '1', Sblx0, '') Sblx ,
-- 改写内容块 --
a.kfsid kfsid,
c.sfgycq sfgycq
FROM
T_XMGK_FWFSB b,
T_XMGK_MPB mp ,
T_XMGK_FWZTB c,
T_XMGK_FWB a
left join T_XMGK_FCFFWGXB fcf
on
fcf.FWID = a.FWID
and fcf.del_flag = '0'
-- 改写内容块 --
left join
(
select
aj.fwid,
decode(max(cw.balx), '1', '出售(1:1)', '3', '整体转让', '出售(大于1:1)') Sblx0
from
T_CWZS_CWZSAJB cw,
T_CWZS_CWAJB aj
where
cw.ajxxid = aj.ajxxid
and cw.del_flag = '0'
and aj.del_flag = '0'
and cw.jlzt = '1'
group by aj.fwid
) x
on x.fwid = a.fwid
-- 改写内容块 --
WHERE
a.FWID = b.FWID
and b.FWID = c.FWID
and a.mpid =mp.mpid
and b.del_flag = '0'
and a.del_flag = '0'
and c.del_flag = '0'
and mp.del_flag = '0'
and a.mpid = '112'
and c.hqlx <> '2'
Order By
CAST(regexp_replace(a.ch, '([0-9]-[0-9])|[^-0-9.]') as int),
CAST(regexp_replace(a.fwh, '([0-9]-[0-9])|[^-0-9.]') as int);
1068
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
