OB一哥们找我优化条SQL，反馈在OceanBase存储过程执行时间很慢，需要626秒才能出结果，安排。
-- 原SQL：
INSERT INTO insurance_stat_sx
(id,
stat_date,
cal_num,
underwrite_num,
veh_num,
effect_num,
effect_money,
unit_code,
life_agent_id,
life_agent_name,
sx_unit_code,
sx_unit_name,
sx_dept_group_code,
sx_dept_group_name,
sx_branch_code,
sx_branch_name,
unit_name,
dept_group_code,
dept_group_name,
dept_code,
dept_name,
section_code,
section_name,
client_type,
app_type)
SELECT t_seq_common.nextval AS id,
'2023-05-15',
cal_num,
underwrite_num,
veh_num,
effect_num,
effect_money,
unit_code,
life_agent_id,
life_agent_name,
sx_unit_code,
sx_unit_name,
sx_dept_group_code,
sx_dept_group_name,
sx_branch_code,
sx_branch_name,
unit_name,
dept_group_code,
dept_group_name,
dept_code,
dept_name,
section_code,
section_name,
client_type,
app_type
FROM (SELECT SUM(cal_num) AS cal_num,
SUM(underwrite_num) AS underwrite_num,
SUM(veh_num) AS veh_num,
SUM(effect_num) AS effect_num,
SUM(effect_money) AS effect_money,
unit_code,
life_agent_id,
life_agent_name,
sx_unit_code,
sx_unit_name,
sx_dept_group_code,
sx_dept_group_name,
sx_branch_code,
sx_branch_name,
unit_name,
dept_group_code,
dept_group_name,
dept_code,
dept_name,
section_code,
section_name,
client_type,
app_type
FROM (SELECT log.unit_code,
log.life_agent_id,
log.life_agent_name,
log.sx_unit_code,
log.sx_unit_name,
log.sx_dept_group_code,
log.sx_dept_group_name,
log.sx_branch_code,
log.sx_branch_name,
log.unit_name,
log.dept_group_code,
log.dept_group_name,
log.dept_code,
log.dept_name,
log.section_code,
log.section_name,
SUM(CASE
WHEN log.oper_type = 2 THEN
1
ELSE
0
END) cal_num,
SUM(CASE
WHEN log.oper_type = 3 THEN
1
ELSE
0
END) underwrite_num,
COUNT(DISTINCT(registration_number)) veh_num,
0 effect_num,
0 effect_money,
log.client_type,
log.app_type
FROM insurance_log log
WHERE log.life_agent_id IS NOT NULL
AND log.create_time >=
TO_DATE('2023-05-15', 'yyyy-mm-dd')
AND log.create_time <
TO_DATE('2023-05-16', 'yyyy-mm-dd')
AND log.app_type IS NOT NULL
AND log.client_type IS NOT NULL
GROUP BY log.unit_code,
log.life_agent_id,
log.life_agent_name,
log.sx_unit_code,
log.sx_unit_name,
log.sx_dept_group_code,
log.sx_dept_group_name,
log.sx_branch_code,
log.sx_branch_name,
log.unit_name,
log.dept_group_code,
log.dept_group_name,
log.dept_code,
log.dept_name,
log.section_code,
log.section_name,
log.client_type,
log.app_type
UNION ALL
SELECT log.unit_code,
log.life_agent_id,
log.life_agent_name,
log.sx_unit_code,
log.sx_unit_name,
log.sx_dept_group_code,
log.sx_dept_group_name,
log.sx_branch_code,
log.sx_branch_name,
log.unit_name,
log.dept_group_code,
log.dept_group_name,
log.dept_code,
log.dept_name,
log.section_code,
log.section_name,
0 cal_num,
0 underwrite_num,
0 veh_num,
COUNT(p.policyapplication_pk) effect_num,
SUM(NVL(po.underwritten_premium, 0)) effect_money,
log.client_type,
log.app_type
FROM policyapplication p,
vehicleinformation v,
productselection pr,
policypayment po,
(SELECT unit_code,
policy_id,
TO_CHAR(create_time, 'yyyy-mm-dd') create_time,
life_agent_id,
life_agent_name,
sx_unit_code,
sx_unit_name,
sx_dept_group_code,
sx_dept_group_name,
sx_branch_code,
sx_branch_name,
unit_name,
dept_group_code,
dept_group_name,
dept_code,
dept_name,
section_code,
section_name,
client_type,
app_type
FROM insurance_log
WHERE policy_status = '3'
AND oper_type = 7
AND life_agent_id IS NOT NULL
AND app_type IS NOT NULL
AND client_type IS NOT NULL
GROUP BY unit_code,
policy_id,
TO_CHAR(create_time, 'yyyy-mm-dd'),
life_agent_id,
life_agent_name,
sx_unit_code,
sx_unit_name,
sx_dept_group_code,
sx_dept_group_name,
sx_branch_code,
sx_branch_name,
unit_name,
dept_group_code,
dept_group_name,
dept_code,
dept_name,
section_code,
section_name,
client_type,
app_type) log
WHERE p.policyapplication_pk = v.policyapplication_fk
AND v.vehicleinformation_pk = pr.vehicleinformation_fk
AND pr.productselection_pk = po.productselection_fk
AND p.policy_status = '3'
AND log.policy_id = p.policyapplication_pk
AND log.create_time >= '2023-05-15'
AND log.create_time < '2023-05-16'
GROUP BY log.unit_code,
log.life_agent_id,
log.life_agent_name,
log.sx_unit_code,
log.sx_unit_name,
log.sx_dept_group_code,
log.sx_dept_group_name,
log.sx_branch_code,
log.sx_branch_name,
log.unit_name,
log.dept_group_code,
log.dept_group_name,
log.dept_code,
log.dept_name,
log.section_code,
log.section_name,
log.client_type,
log.app_type)
GROUP BY unit_code,
life_agent_id,
life_agent_name,
sx_unit_code,
sx_unit_name,
sx_dept_group_code,
sx_dept_group_name,
sx_branch_code,
sx_branch_name,
unit_name,
dept_group_code,
dept_group_name,
dept_code,
dept_name,
section_code,
section_name,
client_type,
app_type) tmp;
SQL整体返回0行数据，所以insert into 也是0行，insurance_log 表 12亿行数据。
当时OB哥们给到我的时候我再忙其他事情，没有要执行计划，粗略扫了一下SQL大致的写法。
得知了 insurance_log 表 12 亿行数据以后，让他加个并行hint 试试看速度。
下面SQL除了加了并行 HINT ，后面无任何修改。
select /*+ USE_PX PARALLEL(8)*/
t_seq_common.nextval as id,
--to_char('2023-05-15', 'yyyy-mm-dd') as stat_date,
'2023-05-15',
cal_num,
underwrite_num,
veh_num,
effect_num,
effect_money,
unit_code,
life_agent_id,
life_agent_name... 省略后面SQL
并行 hint 加完以后只需要 281s 就能出结果，当时我也忙其他事情，没继续优化下去。
但是这哥们领导不依不饶，还得继续让他优化，没办法只能帮忙仔细看看了。
缓慢节点：
SELECT log.unit_code,
log.life_agent_id,
log.life_agent_name,
log.sx_unit_code,
log.sx_unit_name,
log.sx_dept_group_code,
log.sx_dept_group_name,
log.sx_branch_code,
log.sx_branch_name,
log.unit_name,
log.dept_group_code,
log.dept_group_name,
log.dept_code,
log.dept_name,
log.section_code,
log.section_name,
SUM(CASE
WHEN log.oper_type = 2 THEN
1
ELSE
0
END) cal_num,
SUM(CASE
WHEN log.oper_type = 3 THEN
1
ELSE
0
END) underwrite_num,
COUNT(DISTINCT(registration_number)) veh_num,
0 effect_num,
0 effect_money,
log.client_type,
log.app_type
FROM insurance_log log
WHERE log.life_agent_id IS NOT NULL
AND log.create_time >=
TO_DATE('2023-05-15', 'yyyy-mm-dd')
AND log.create_time <
TO_DATE('2023-05-16', 'yyyy-mm-dd')
AND log.app_type IS NOT NULL
AND log.client_type IS NOT NULL
GROUP BY log.unit_code,
log.life_agent_id,
log.life_agent_name,
log.sx_unit_code,
log.sx_unit_name,
log.sx_dept_group_code,
log.sx_dept_group_name,
log.sx_branch_code,
log.sx_branch_name,
log.unit_name,
log.dept_group_code,
log.dept_group_name,
log.dept_code,
log.dept_name,
log.section_code,
log.section_name,
log.client_type,
log.app_type
UNION ALL
SELECT log.unit_code,
log.life_agent_id,
log.life_agent_name,
log.sx_unit_code,
log.sx_unit_name,
log.sx_dept_group_code,
log.sx_dept_group_name,
log.sx_branch_code,
log.sx_branch_name,
log.unit_name,
log.dept_group_code,
log.dept_group_name,
log.dept_code,
log.dept_name,
log.section_code,
log.section_name,
0 cal_num,
0 underwrite_num,
0 veh_num,
COUNT(p.policyapplication_pk) effect_num,
SUM(NVL(po.underwritten_premium, 0)) effect_money,
log.client_type,
log.app_type
FROM policyapplication p,
vehicleinformation v,
productselection pr,
policypayment po,
(SELECT unit_code,
policy_id,
TO_CHAR(create_time, 'yyyy-mm-dd') create_time,
life_agent_id,
life_agent_name,
sx_unit_code,
sx_unit_name,
sx_dept_group_code,
sx_dept_group_name,
sx_branch_code,
sx_branch_name,
unit_name,
dept_group_code,
dept_group_name,
dept_code,
dept_name,
section_code,
section_name,
client_type,
app_type
FROM insurance_log
WHERE policy_status = '3'
AND oper_type = 7
AND life_agent_id IS NOT NULL
AND app_type IS NOT NULL
AND client_type IS NOT NULL
GROUP BY unit_code,
policy_id,
TO_CHAR(create_time, 'yyyy-mm-dd'),
life_agent_id,
life_agent_name,
sx_unit_code,
sx_unit_name,
sx_dept_group_code,
sx_dept_group_name,
sx_branch_code,
sx_branch_name,
unit_name,
dept_group_code,
dept_group_name,
dept_code,
dept_name,
section_code,
section_name,
client_type,
app_type) log
WHERE p.policyapplication_pk = v.policyapplication_fk
AND v.vehicleinformation_pk = pr.vehicleinformation_fk
AND pr.productselection_pk = po.productselection_fk
AND p.policy_status = '3'
AND log.policy_id = p.policyapplication_pk
AND log.create_time >= '2023-05-15'
AND log.create_time < '2023-05-16'
GROUP BY log.unit_code,
log.life_agent_id,
log.life_agent_name,
log.sx_unit_code,
log.sx_unit_name,
log.sx_dept_group_code,
log.sx_dept_group_name,
log.sx_branch_code,
log.sx_branch_name,
log.unit_name,
log.dept_group_code,
log.dept_group_name,
log.dept_code,
log.dept_name,
log.section_code,
log.section_name,
log.client_type,
log.app_type;
union all 上面这段SQL insurance_log 表12亿数据，返回0行。
union all 下面这段SQL policyapplication p 1.9亿、vehicleinformation v 1.9亿 、productselection pr 2.7亿、policypayment po 4430万、log 内联视图 2025W，关联后返回0行。
这么大的数据量关联，慢也是正常，但是知道数据量以后就好办了。
SQL改写 + hint 干预方案 ：
with x_log as (
select /*+ USE_PX PARALLEL(6)*/ *
from insurance_log log
where log.life_agent_id is not null
and log.app_type is not null
and log.client_type is not null
and log.create_time >= to_date('2023-05-15', 'yyyy-mm-dd')
and log.create_time < to_date('2023-05-16', 'yyyy-mm-dd')
)
select
t_seq_common.nextval as id,
--to_char('2023-05-15', 'yyyy-mm-dd') as stat_date,
'2023-05-15',
cal_num,
underwrite_num,
veh_num,
effect_num,
effect_money,
unit_code,
life_agent_id,
life_agent_name,
sx_unit_code,
sx_unit_name,
sx_dept_group_code,
sx_dept_group_name,
sx_branch_code,
sx_branch_name,
unit_name,
dept_group_code,
dept_group_name,
dept_code,
dept_name,
section_code,
section_name,
client_type,
app_type
from
(
select
sum(cal_num) as cal_num,
sum(underwrite_num) as underwrite_num,
sum(veh_num) as veh_num,
sum(effect_num) as effect_num,
sum(effect_money) as effect_money,
unit_code,
life_agent_id,
life_agent_name,
sx_unit_code,
sx_unit_name,
sx_dept_group_code,
sx_dept_group_name,
sx_branch_code,
sx_branch_name,
unit_name,
dept_group_code,
dept_group_name,
dept_code,
dept_name,
section_code,
section_name,
client_type,
app_type
from
(
select /*+ USE_PX PARALLEL(4)*/ *
log.unit_code,
log.life_agent_id,
log.life_agent_name,
log.sx_unit_code,
log.sx_unit_name,
log.sx_dept_group_code,
log.sx_dept_group_name,
log.sx_branch_code,
log.sx_branch_name,
log.unit_name,
log.dept_group_code,
log.dept_group_name,
log.dept_code,
log.dept_name,
log.section_code,
log.section_name,
sum(
case
when log.oper_type = 2 then 1
else 0
end
) cal_num,
sum(
case
when log.oper_type = 3 then 1
else 0
end
) underwrite_num,
count(distinct(registration_number)) veh_num,
0 effect_num,
0 effect_money,
log.client_type,
log.app_type
from
x_log log
group by
log.unit_code,
log.life_agent_id,
log.life_agent_name,
log.sx_unit_code,
log.sx_unit_name,
log.sx_dept_group_code,
log.sx_dept_group_name,
log.sx_branch_code,
log.sx_branch_name,
log.unit_name,
log.dept_group_code,
log.dept_group_name,
log.dept_code,
log.dept_name,
log.section_code,
log.section_name,
log.client_type,
log.app_type
union all
select /*+ USE_PX PARALLEL(5)
PQ_DISTRIBUTE(p hash, hash)
PQ_DISTRIBUTE(v hash, hash)
PQ_DISTRIBUTE(pr hash, hash)
PQ_DISTRIBUTE(po hash, hash)
PQ_DISTRIBUTE(log hash, hash)
*/
log.unit_code,
log.life_agent_id,
log.life_agent_name,
log.sx_unit_code,
log.sx_unit_name,
log.sx_dept_group_code,
log.sx_dept_group_name,
log.sx_branch_code,
log.sx_branch_name,
log.unit_name,
log.dept_group_code,
log.dept_group_name,
log.dept_code,
log.dept_name,
log.section_code,
log.section_name,
0 cal_num,
0 underwrite_num,
0 veh_num,
count(p.policyapplication_pk) effect_num,
sum(nvl(po.underwritten_premium, 0)) effect_money,
log.client_type,
log.app_type
from
policyapplication p,
vehicleinformation v,
productselection pr,
policypayment po,
(
select /*+ USE_PX PARALLEL(5)*/
unit_code,
policy_id,
to_char(create_time, 'yyyy-mm-dd') create_time,
life_agent_id,
life_agent_name,
sx_unit_code,
sx_unit_name,
sx_dept_group_code,
sx_dept_group_name,
sx_branch_code,
sx_branch_name,
unit_name,
dept_group_code,
dept_group_name,
dept_code,
dept_name,
section_code,
section_name,
client_type,
app_type
from
x_log log
where
policy_status = '3'
and oper_type = 7
group by
unit_code,
policy_id,
to_char(create_time, 'yyyy-mm-dd'),
life_agent_id,
life_agent_name,
sx_unit_code,
sx_unit_name,
sx_dept_group_code,
sx_dept_group_name,
sx_branch_code,
sx_branch_name,
unit_name,
dept_group_code,
dept_group_name,
dept_code,
dept_name,
section_code,
section_name,
client_type,
app_type
) log
where
p.policyapplication_pk = v.policyapplication_fk
and v.vehicleinformation_pk = pr.vehicleinformation_fk
and pr.productselection_pk = po.productselection_fk
and p.policy_status = '3'
and log.policy_id = p.policyapplication_pk
group by
log.unit_code,
log.life_agent_id,
log.life_agent_name,
log.sx_unit_code,
log.sx_unit_name,
log.sx_dept_group_code,
log.sx_dept_group_name,
log.sx_branch_code,
log.sx_branch_name,
log.unit_name,
log.dept_group_code,
log.dept_group_name,
log.dept_code,
log.dept_name,
log.section_code,
log.section_name,
log.client_type,
log.app_type
)
group by
unit_code,
life_agent_id,
life_agent_name,
sx_unit_code,
sx_unit_name,
sx_dept_group_code,
sx_dept_group_name,
sx_branch_code,
sx_branch_name,
unit_name,
dept_group_code,
dept_group_name,
dept_code,
dept_name,
section_code,
section_name,
client_type,
app_type
) tmp;
最终上面SQL 27s 就能跑出结果。
这个案例从始至终没有看过执行计划 (OB的执行计划我也看不懂，看了也是白看)。
当具备一定优化理论知识之后，我们可以不看执行计划，直接根据 SQL 写法和表的数据量来判断是否走 NL 还是 HASH，
然后一直这样进行下去直到 SQL 语句中所有表都关联完毕，如果大家长期采用此方法进行锻炼，久而久之，你自己的脑袋就是 CBO。
标签：code,group,log,name,dept,sx,SQL,优化,14 From： https://www.cnblogs.com/yuzhijian/p/17407171.html