在不使用函数结果缓存的情况下,Oracle在同一查询(事务？)中调用函数时是否可以重用函数的结果？
我正在使用的应用程序严重依赖于Oracle功能.许多查询最终会多次执行完全相同的函数.
一个典型的例子是：
SELECT my_package.my_function(my_id),my_package.my_function(my_id) / 24,my_package.function_also_calling_my_function(my_id) FROM my_table WHERE my_table.id = my_id;
我注意到Oracle总是执行这些函数中的每一个,而不是意识到在同一个查询中只是一秒钟之前调用了相同的函数.函数中的某些元素可能会被缓存,从而导致返回速度稍快.这与我的问题无关,因为我想避免整个第二次或第三次执行.
假设这些函数是相当耗费资源的,并且这些函数可以调用更多函数,将它们的结果基于相当大且经常更新的表(一百万条记录,每小时更新1000次更新).因此,无法使用Oracle的功能结果缓存.
即使数据经常变化,我希望这些函数的结果在从同一查询中调用时也是相同的.
Oracle是否可以重用这些函数的结果以及如何重用？我使用的是Oracle11g和Oracle12c.
-- Takes 200 ms
SELECT test_package.testSpeed('STANDARD','REGEXP_COUNT')
FROM dual;
-- Takes 400ms
SELECT test_package.testSpeed('STANDARD','REGEXP_COUNT'),test_package.testSpeed('STANDARD','REGEXP_COUNT')
FROM dual;
使用功能：
CREATE OR REPLACE PACKAGE test_package IS
FUNCTION testSpeed (p_package_name VARCHAR2,p_object_name VARCHAR2)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY test_package IS
FUNCTION testSpeed (p_package_name VARCHAR2,p_object_name VARCHAR2)
RETURN NUMBER
IS
ln_total NUMBER;
BEGIN
SELECT SUM(position) INTO ln_total
FROM all_arguments
WHERE package_name = 'STANDARD'
AND object_name = 'REGEXP_COUNT';
RETURN ln_total;
END testSpeed;
END;
/
解决方法
添加内联视图和ROWNUM以防止Oracle将查询重写到单个查询块并多次执行这些函数.
示例功能和演示问题
create or replace function wait_1_second return number is
begin
execute immediate 'begin dbms_lock.sleep(1); end;';
-- ...
-- Do something here to make caching impossible.
-- ...
return 1;
end;
/
--1 second
select wait_1_second() from dual;
--2 seconds
select wait_1_second(),wait_1_second() from dual;
--3 seconds
select wait_1_second(),wait_1_second(),wait_1_second() from dual;
简单的查询更改不起作用
这两种方法都需要2秒,而不是1秒.
select x,x
from
(
select wait_1_second() x from dual
);
with execute_function as (select wait_1_second() x from dual)
select x,x from execute_function;
强制Oracle以特定顺序执行
很难告诉Oracle“单独执行此代码,不要对其执行任何谓词推送,合并或其他转换”.每个优化都有提示,但它们很难使用.有几种方法可以禁用这些转换,添加额外的ROWNUM通常是最简单的方法.
--Only takes 1 second
select x,x
from
(
select wait_1_second() x,rownum
from dual
);
很难确切地看到函数的评估位置.但是这些解释计划显示了ROWNUM如何使内联视图单独运行.
explain plan for select x,x from (select wait_1_second() x from dual); select * from table(dbms_xplan.display(format=>'basic')); Plan hash value: 1388734953 --------------------------------- | Id | Operation | Name | --------------------------------- | 0 | SELECT STATEMENT | | | 1 | FAST DUAL | | --------------------------------- explain plan for select x,x from (select wait_1_second() x,rownum from dual); select * from table(dbms_xplan.display(format=>'basic')); Plan hash value: 1143117158 --------------------------------- | Id | Operation | Name | --------------------------------- | 0 | SELECT STATEMENT | | | 1 | VIEW | | | 2 | COUNT | | | 3 | FAST DUAL | | ---------------------------------
