数据库存储过程优化案例

问题：
系统厂家开发人员，反应数据库一个存储过程执行缓慢，代码如下：
MERGE INTO vaas.mkt_worksheet_allot_data_0712 m
USING (SELECT b.user_id,
DECODE (
'101',
'105',
DECODE ('2',
'2', org_loc1_2_id,
'3', org_loc1_3_id,
'4', org_loc1_4_id,
'5', org_loc1_5_id,
NULL),
DECODE ('2',
'2', org_2_id,
'3', org_3_id,
'4', org_4_id,
'5', org_5_id,
NULL)
)
org_id,
DECODE (
'101',
'105',
DECODE ('2',
'2', org_loc1_2_name,
'3', org_loc1_3_name,
'4', org_loc1_4_name,
'5', org_loc1_5_name,
NULL),
DECODE ('2',
'2', org_2_name,
'3', org_3_name,
'4', org_4_name,
'5', org_5_name,
NULL)
)
org_name
FROM vaas.mkt_worksheet_allot_data_0712 a,
vaas.mkt_user_info b
WHERE a.node_id = '2021070900003248'
AND a.range_id = '2021070900003286'
AND a.m_pretreat_id IS NULL
AND a.user_id = b.user_id) n
ON (m.user_id = n.user_id)
WHEN MATCHED
THEN
UPDATE SET
m.m_pretreat_id = DECODE (n.org_id, NULL, NULL, 'pre123'),
m.m_rule_id = DECODE (n.org_id, NULL, NULL, 'role123'),
m.m_org_level = DECODE (n.org_id, NULL, NULL, '2'),
m.m_org_type = DECODE (n.org_id, NULL, NULL, 'org123'),
m.m_obj_item_value = n.org_id,
m.m_obj_item_value_desc = n.org_name
WHERE m.node_id = '2021070900003248'
AND m.range_id = '2021070900003286'
AND m_pretreat_id IS NULL
AND n.org_id IN
(SELECT c.org_id
FROM triber.mgt_user a,
triber.mgt_org b,
triber.mgt_org c
WHERE a.login_id = 'zhangn103'
AND a.org_id = b.org_id
AND c.org_path LIKE
b.org_path || '%'
AND c.org_level = '2'
AND c.org_status = 1);

COMMIT;

问题排查：
1、 查看SQL的执行计划：

查看表结构及数据量发现，vaas.mkt_worksheet_allot_data_0712（以下称a表）在node_id和user_id上有索引，数据量为90w；vaas.mkt_user_info（以下称为b表）在user_id上有索引，数据量为4000w。
2、查看a表上node_id和range_id的数据分布：

问题原因分析：
正常情况下，对于大数据量的关联查询，CBO会选择hash join。但是由于a表数据量分布及不均匀，且离散度比较低。导致Oracle低估了a的数据量，从而选择nested loops join。
解决方案：
执行node_id和range_id的多列统计；
begin
dbms_stats.gather_table_stats (
ownname => 'VAAS',
tabname => 'MKT_WORKSHEET_ALLOT_DATA_0720',
estimate_percent=> 100,
method_opt => ' FOR COLUMNS (node_id,range_id)',
cascade => TRUE
);
end;
/

收集完多列统计后执行计划如下：

可以看到a表和B表走了hash join，此时存储过程执行正常。