--简化后
SELECT t5.id,t6.id,T5.merchantApplyRangeId
FROM t5
LEFT JOIN t6
ON T6.id = T5.merchantApplyRangeId
AND T6.ytenant_id = 'hzd092p5'
AND T6.ispotential = 0
WHERE T5.stopstatus = 0
AND (T6.orgId IS NULL OR T6.orgId <> '666666')
AND T6.iRangeType = 1;
1 #NSET2: [233, 2733->855839, 184] ; used times:170.024(ms); n_enter:3471
2 #PRJT2: [233, 2733->855839, 184]; exp_num(3), is_atom(FALSE); INFO_BITS(0); used times:98.419(ms); n_enter:6776
3 #HASH2 INNER JOIN: [233, 2733->855839, 184]; KEY_NUM(1), MEM_USED(80000KB), DISK_USED(0KB) KEY(t6.id=t5.merchantApplyRangeId) KEY_NULL_EQU(0); used times:64005.538(ms); n_enter:10164
4 #SLCT2: [123, 12->856108, 138]; (t6.ytenant_id = 'hzd092p5' AND t6.iRangeType = 1 AND (t6.orgId IS NULL OR t6.orgId <> '666666') AND t6.ispotential = var1), slct_pushdown(0); used times:216.765(ms); n_enter:6776
5 #CSCN2: [123, 866933->866933, 138]; INDEX33555636(t6); btr_scan(1); need_slct(0) prejudge_iescn(0); used times:507.389(ms); n_enter:3388
6 #SLCT2: [107, 21673->866631, 46]; t5.stopstatus = var2, slct_pushdown(1); used times:88.138(ms); n_enter:6776
7 #CSCN2: [107, 866933->866933, 46]; INDEX33555635(t5); btr_scan(1); need_slct(1) prejudge_iescn(0); used times:431.013(ms); n_enter:3388
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
11937 logical reads
0 physical reads
0 direct physical reads
0 redo size
42797454 bytes sent to client
5586 bytes received from client
83 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
0.000 io wait time(ms)
66124.609 exec time(ms)
SQL> et(1305);
OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE INFO1
----- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- ----------------- --------------- -------------------- -----
DLCK 2188 0% 8 0 84 0 0 0 0 NULL NULL 0 NULL
SLCT2 88138 0.13% 7 6 6776 0 0 0 0 NULL NULL 0 NULL
PRJT2 98419 0.15% 6 2 6776 0 0 0 0 NULL NULL 0 NULL
NSET2 170024 0.26% 5 1 3471 0 0 0 0 NULL NULL 0 NULL
SLCT2 216765 0.33% 4 4 6776 0 0 0 0 NULL NULL 0 NULL
CSCN2 431013 0.66% 3 7 3388 0 0 0 0 NULL NULL 0 NULL
CSCN2 507389 0.77% 2 5 3388 0 0 0 0 NULL NULL 0 NULL
HI3 64005538 97.69% 1 3 10164 80000 0 15624 840484 NULL NULL 0 NULL
1、从执行计划中看执行最耗时的部分是第3步的hash inner join,耗时64005.538(ms),由T6表(866933行)和T5表(866933行)全扫之后直接关联,结合et,这部分的HI3操作符计算的hash槽 HASH_USED_CELLS=15624,产生了hash冲突 HASH_CONFLICT=840484,97.69%的耗时都在这里成为主要性能瓶颈点;
2、T5 和 T6 的关联字段都是主键,对于唯一值来说,HASH算法通常会计算出更多的HASH槽,案例中以 T6 构建 HASH槽,T5 进行探测,T6 的 85w 数据仅生成了 15624 个HASH槽,冲突数 840484 意味着平均每个槽内有 53.8 个记录(840484/15624),HASH函数计算生成的槽数越少,每个槽里的记录就越多,那么探测时的链式查找开销就会越大;
旧的 HASH 算法计算出的 HASH 槽较少,导致 HASH 冲突多,性能较差,在2024年2季度版中,ini 参数 HASH_OPT_FLAG 新增取值 2,针对部分操作符,使用优化的 hash 算法计算 hash 槽以及匹配数据,在 HASH_CMP_OPT_FLAG 为 0 的前提下生效。
--使用hint hash_opt_flag(2)
SQL> SELECT /*+ hash_opt_flag(2) */
2 t5.id,t6.id ,T5.merchantApplyRangeId
3 FROM T5
4 LEFT JOIN T6
5 ON T6.id = T5.merchantApplyRangeId
6 AND T6.ytenant_id = 'hzd092p5'
7 AND T6.ispotential = 0
8 WHERE T5.stopstatus = 0
9 AND (T6.orgId IS NULL OR T6.orgId <> '666666')
10 AND T6.iRangeType = 1;
1 #NSET2: [233, 2733->855839, 184] ; used times:130.322(ms); n_enter:3471
2 #PRJT2: [233, 2733->855839, 184]; exp_num(3), is_atom(FALSE); INFO_BITS(0); used times:75.191(ms); n_enter:6776
3 #HASH2 INNER JOIN: [233, 2733->855839, 184]; KEY_NUM(1), MEM_USED(80000KB), DISK_USED(0KB) KEY(t6.id=t5.merchantApplyRangeId) KEY_NULL_EQU(0); INFO_BITS(0x100); used times:890.006(ms); n_enter:10164
4 #SLCT2: [123, 12->856108, 138]; (t6.ytenant_id = 'hzd092p5' AND t6.iRangeType = 1 AND (t6.orgId IS NULL OR t6.orgId <> '666666') AND t6.ispotential = var1), slct_pushdown(0); used times:125.080(ms); n_enter:6776
5 #CSCN2: [123, 866933->866933, 138]; INDEX33555636(t6); btr_scan(1); need_slct(0) prejudge_iescn(0); used times:289.853(ms); n_enter:3388
6 #SLCT2: [107, 21673->866631, 46]; t5.stopstatus = var2, slct_pushdown(1); used times:79.416(ms); n_enter:6776
7 #CSCN2: [107, 866933->866933, 46]; INDEX33555635(t5); btr_scan(1); need_slct(1) prejudge_iescn(0); used times:321.294(ms); n_enter:3388
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
11937 logical reads
0 physical reads
0 direct physical reads
0 redo size
42797454 bytes sent to client
5623 bytes received from client
83 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
0.000 io wait time(ms)
2408.910 exec time(ms)
已用时间: 875.680(毫秒). 执行号:1310.
SQL> et(1310);
OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE INFO1
----- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- ----------------- --------------- -------------------- -----
DLCK 2043 0.11% 8 0 84 0 0 0 0 NULL NULL 0 NULL
PRJT2 75191 3.93% 7 2 6776 0 0 0 0 NULL NULL 0 NULL
SLCT2 79416 4.15% 6 6 6776 0 0 0 0 NULL NULL 0 NULL
SLCT2 125080 6.54% 5 4 6776 0 0 0 0 NULL NULL 0 NULL
NSET2 130322 6.81% 4 1 3471 0 0 0 0 NULL NULL 0 NULL
CSCN2 289853 15.15% 3 5 3388 0 0 0 0 NULL NULL 0 NULL
CSCN2 321294 16.79% 2 7 3388 0 0 0 0 NULL NULL 0 NULL
HI3 890006 46.52% 1 3 10164 80000 0 440333 415775 NULL NULL 0 NULL
HASH_OPT_FLAG=2之后,从et中可见新算法计算的HASH槽数明显增加,总体耗时也从66s降低到2.4s,从执行计划中可以看到第3步HASH2 INNER JOIN算子的执行计划信息中出现了INFO_BITS(0x100)标志位,代表新HASH算法生效,新HASH算法支持 hlo/hro/hls/hfo/afun/spl/cnntb/hi/dist/aagr/hagr 等操作符,在不同场景中均会有所提升。
文章
阅读量
获赞
