递归函数WITH recursive执行过慢

OceanBase
#1

【 使用环境 】生产环境
【 OB or 其他组件 】ob4.2.1
【问题描述】
WITH recursive t(districts_id, parent_id, districts_name,n) AS
(
SELECT districts_id, parent_id, districts_name,1
FROM tbl_districts WHERE districts_id =22
UNION ALL
SELECT m.districts_id, m.parent_id,m. districts_name,n+1
FROM tbl_districts m JOIN t ON m.parent_id = t.districts_id
WHERE n <= 20
)
SELECT count(*) FROM t;

tbl_districts一共1.6w的数据，mysql执行0.19s，ob执行70s

执行计划
执行计划.log (4.9 KB)

================================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|

|0 |SCALAR GROUP BY | |1 |6532 |
|1 |©¸©¤SUBPLAN SCAN |t |2764 |6482 |
|2 | ©¸©¤RECURSIVE UNION ALL| |2764 |6475 |
|3 | ©À©¤TABLE FULL SCAN |tbl_districts|84 |695 |
|4 | ©¸©¤HASH JOIN | |2680 |5773 |
|5 | ©À©¤TABLE FULL SCAN|t |199 |1 |
|6 | ©¸©¤TABLE FULL SCAN|m |16780 |1055 |

Outputs & filters:

0 - output([T_FUN_COUNT()(0x7f21b049d090)]), filter(nil), rowset=256
group(nil), agg_func([T_FUN_COUNT()(0x7f21b049d090)])
1 - output(nil), filter(nil), rowset=256
access(nil)
2 - output([UNION([1])(0x7f21b04841c0)], [UNION([2])(0x7f21b0484410)], [UNION([3])(0x7f21b0484660)], [UNION([4])(0x7f21b04848b0)]), filter(nil), rowset=256
3 - output([tbl_districts.districts_id(0x7f21b0449260)], [tbl_districts.parent_id(0x7f21b044b4b0)], [tbl_districts.districts_name(0x7f21b044bab0)], [1]), filter([cast(tbl_districts.districts_id(0x7f2
1b0449260), DECIMAL(-1, -1))(0x7f21b04497d0) = cast(22, DECIMAL(2, 0))(0x7f21b044a320)(0x7f21b0448b10)]), rowset=256
access([tbl_districts.districts_id(0x7f21b0449260)], [tbl_districts.parent_id(0x7f21b044b4b0)], [tbl_districts.districts_name(0x7f21b044bab0)]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false],
range_key([tbl_districts.districts_id(0x7f21b0449260)]), range(MIN ; MAX)always true
4 - output([column_conv(VARCHAR,utf8mb4_general_ci,length:50,NULL,m.districts_id(0x7f21b04657c0))(0x7f21b04674c0)], [column_conv(VARCHAR,utf8mb4_general_ci,
length:100,NULL,m.parent_id(0x7f21b04639e0))(0x7f21b046e7e0)], [column_conv(VARCHAR,utf8mb4_general_ci,length:150,NULL,m.districts_name(0x7f21b04660e0))(0x7f21b0475b00)],
[column_conv(BIGINT,PS:(1,0),NULL,t.n(0x7f21b0464f30) + 1(0x7f21b0466950))(0x7f21b047ce20)]), filter(nil), rowset=256
equal_conds([m.parent_id(0x7f21b04639e0) = t.districts_id(0x7f21b0463cd0)(0x7f21b0463290)]), other_conds(nil)
5 - output([t.districts_id(0x7f21b0463cd0)], [t.n(0x7f21b0464f30)]), filter([t.n(0x7f21b0464f30) <= 20(0x7f21b04647e0)]), rowset=256
access([t.districts_id(0x7f21b0463cd0)], [t.n(0x7f21b0464f30)])
is_index_back=false, is_global_index=false, filter_before_indexback[false],
range_key(nil), range(MIN ; MAX)
6 - output([m.districts_id(0x7f21b04657c0)], [m.parent_id(0x7f21b04639e0)], [m.districts_name(0x7f21b04660e0)]), filter(nil), rowset=256
access([m.districts_id(0x7f21b04657c0)], [m.parent_id(0x7f21b04639e0)], [m.districts_name(0x7f21b04660e0)]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([m.districts_id(0x7f21b04657c0)]), range(MIN ; MAX)always true
Used Hint:

/*+

*/
Qb name trace:

stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1
stmt_id:2, SET$1 > SET$CEB121DD
stmt_id:3, SEL$2 > SEL$A3D39CCF
stmt_id:4, SEL$3 > SEL$B0E633B8
Outline Data:

/*+
BEGIN_OUTLINE_DATA
FULL(@“SEL$A3D39CCF” “jlsmz”.“tbl_districts”@“SEL$2”)
LEADING(@“SEL$B0E633B8” (“t”@“SEL$3” “jlsmz”.“m”@“SEL$3”))
USE_HASH(@“SEL$B0E633B8” “jlsmz”.“m”@“SEL$3”)
FULL(@“SEL$B0E633B8” “t”@“SEL$3”)
FULL(@“SEL$B0E633B8” “m”@“SEL$3”)
SIMPLIFY_EXPR(@“SEL$2”)
INLINE(@“SET$1”)
OUTER_TO_INNER(@“SEL$3”)
OPTIMIZER_FEATURES_ENABLE(‘4.2.1.0’)
END_OUTLINE_DATA
*/
Optimization Info:

tbl_districts:
table_rows:16780
physical_range_rows:16780
logical_range_rows:16780
index_back_rows:0
output_rows:83
table_dop:1
dop_method:Table DOP
avaiable_index_name:[tbl_districts_districts_id_IDX, tbl_districts_districts_idp_IDX, tbl_districts]
stats version:1710626401441251
dynamic sampling level:0

m:
table_rows:16780
physical_range_rows:16780
logical_range_rows:16780
index_back_rows:0
output_rows:16780
table_dop:1
dop_method:Table DOP
avaiable_index_name:[tbl_districts_districts_id_IDX, tbl_districts_districts_idp_IDX, tbl_districts]
pruned_index_name:[tbl_districts_districts_id_IDX, tbl_districts_districts_idp_IDX]
stats version:1710626401441251
dynamic sampling level:0
Plan Type:
LOCAL
Note:
Degree of Parallelisim is 1 because of table property
Expr Constraints:
1 = 1 result is TRUE

#3

麻烦帮获取下moniotr信息

  1. 确认 sql_plan_monitor 已经打开（sys租户执行）
    show parameters like ‘enable_sql_audit’;
    – 如果 enable_sql_audit = False 则将其打开：
    alter system enable_sql_audit = true;

2 .登录业务租户

  1. 获取执行计划
    explain sql语句;

  2. 设置临时 trace 获取
    SET ob_enable_show_trace=‘ON’;

  3. 再次执行 sql 语句

  4. 获取第五步执行后的trace_id
    select last_trace_id();

  5. 临时关闭monitor数据，防止信息被覆盖（sys租户执行）
    alter system enable_sql_audit = false;

  6. 执行下面的 SQL ，将xxxxx 替换为第6步返回的trace_id ，以获取每个算子的吐行信息
    select plan_line_id, plan_operation, sum(output_rows), sum(STARTS) rescan, min(first_refresh_time) open_time, max(last_refresh_time) close_time, min(first_change_time) first_row_time, max(last_change_time) last_row_eof_time, count(1) from oceanbase.gv$sql_plan_monitor where trace_id = ‘xxxxx’ group by plan_line_id, plan_operation order by plan_line_id;

  7. 恢复 sql_audit 参数
    alter system enable_sql_audit = true;

#4

image
image1920×649 128 KB

#5

OB422版本对 rescan 有优化，可以升级下再试试。

#6

好的