问题概述
SQL中自定义函数被调用次数过多导致性能差
解决方案
利用result_cache减少函数被调用次数
(注意：使用result_cache要格外小心，有其他解决方案的时候尽量用其他解决方案)
优化案例
下面SQL要2个多小时才能跑完
INSERT INTO a
(c65)
(SELECT test_get_out_contract_number(t.plan_po_number,
t.project_number,
t.org_id)
FROM x t);x表一共有4768314行数据
SQL> select count(*) from x;
COUNT(*)
----------
4768314函数的传参列组合基数为2485行
SQL> select count(*)
2 from (
3 select t.plan_po_number, t.project_number, t.org_id
4 from x t
5 group by t.plan_po_number, t.project_number, t.org_id);
COUNT(*)
----------
2485SELECT后面有自定义函数的优化方法 分享过改写SQL加速SQL查询性能
SQL> INSERT INTO a
2 (c65)
3 (SELECT (select test_get_out_contract_number(t.plan_po_number,
4 t.project_number,
5 t.org_id)
6 from dual)
7 FROM x t);
4768314 rows inserted
Executed in 25.72 seconds除了改写SQL之外，还可以对函数启用result_cache特性来加速SQL性能
查看result_cache相关参数
SQL> select name,display_value from v$parameter where name like '%result_cache%';
NAME DISPLAY_VALUE
------------------------------ --------------------------------------------------------------------------------
result_cache_mode MANUAL
result_cache_max_size 2G
result_cache_max_result 5
result_cache_remote_expiration 0
client_result_cache_size 0
client_result_cache_lag 3000
6 rows selected
修改函数定义，对函数启用RESULT_CACHE特性RETURN VARCHAR2 RESULT_CACHE IS
CREATE OR REPLACE FUNCTION test_get_out_contract_number(p_plan_po_number IN VARCHAR2
,p_project_number IN VARCHAR2
,p_org_id IN NUMBER) RETURN VARCHAR2 RESULT_CACHE IS
l_return VARCHAR2(240);
BEGIN
IF p_plan_po_number IS NOT NULL THEN
SELECT pha.attribute1
INTO l_return
FROM po_headers_all pha
WHERE pha.type_lookup_code = 'PLANNED'
AND pha.attribute_category = 'CONTRACT'
AND pha.segment1 = p_plan_po_number
AND pha.org_id = p_org_id
AND pha.attribute1 IS NOT NULL
AND ROWNUM = 1;
ELSIF p_project_number IS NOT NULL THEN
SELECT pha.attribute1
INTO l_return
FROM po_distributions_all pda
,po_headers_all pha
,pa_projects_all ppa
WHERE pha.po_header_id = pda.po_header_id
AND pda.project_id = ppa.project_id
AND pha.type_lookup_code = 'PLANNED'
AND pha.attribute_category = 'CONTRACT'
AND ppa.segment1 = p_project_number
AND rownum = 1;
ELSE
l_return := NULL;
END IF;
RETURN l_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END test_get_out_contract_number;
原始SQL在不改写的情况下也可以26秒跑完
SQL> INSERT INTO a
2 (c65)
3 (SELECT test_get_out_contract_number(t.plan_po_number,
4 t.project_number,
5 t.org_id)
6 FROM x t);
4768314 rows inserted
Executed in 26.404 seconds
查看V$RESULT_CACHE_OBJECTS视图信息
SQL> select id,
2 type,
3 status,
4 name,
5 namespace,
6 scan_count,
7 row_count,
8 row_size_max
9 from V$RESULT_CACHE_OBJECTS
10 where name like upper('%test_get_out_contract_number%')
11 and rownum <= 10;
ID TYPE STATUS NAME NAMESPACE SCAN_COUNT ROW_COUNT ROW_SIZE_MAX
---------- ---------- --------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ------------
34502 Dependency Published APPS.TEST_GET_OUT_CONTRACT_NUMBER 0 0 0
1214 Result Published "APPS"."TEST_GET_OUT_CONTRACT_NUMBER"::8."TEST_GET_OUT_CONTRACT_NUMBER"#8b39c259 PLSQL 9 1 6
382 Result Published "APPS"."TEST_GET_OUT_CONTRACT_NUMBER"::8."TEST_GET_OUT_CONTRACT_NUMBER"#8b39c259 PLSQL 2274 1 14
2415 Result Published "APPS"."TEST_GET_OUT_CONTRACT_NUMBER"::8."TEST_GET_OUT_CONTRACT_NUMBER"#8b39c259 PLSQL 0 1 21
1561 Result Published "APPS"."TEST_GET_OUT_CONTRACT_NUMBER"::8."TEST_GET_OUT_CONTRACT_NUMBER"#8b39c259 PLSQL 6224 1 2
1591 Result Published "APPS"."TEST_GET_OUT_CONTRACT_NUMBER"::8."TEST_GET_OUT_CONTRACT_NUMBER"#8b39c259 PLSQL 2 1 14
1367 Result Published "APPS"."TEST_GET_OUT_CONTRACT_NUMBER"::8."TEST_GET_OUT_CONTRACT_NUMBER"#8b39c259 PLSQL 158 1 16
274 Result Published "APPS"."TEST_GET_OUT_CONTRACT_NUMBER"::8."TEST_GET_OUT_CONTRACT_NUMBER"#8b39c259 PLSQL 8387 1 2
77654 Result Published "APPS"."TEST_GET_OUT_CONTRACT_NUMBER"::8."TEST_GET_OUT_CONTRACT_NUMBER"#8b39c259 PLSQL 5 1 2
469 Result Published "APPS"."TEST_GET_OUT_CONTRACT_NUMBER"::8."TEST_GET_OUT_CONTRACT_NUMBER"#8b39c259 PLSQL 80 1 15
10 rows selected
