为什么Oracle 12.1.0.2会跳过结果缓存表上的函数调用？-Java 学习之路

我将现实生活中的问题减少到以下测试用例：

DROP TABLE test_users;
CREATE TABLE test_users (
  user_id INTEGER,
  username VARCHAR2(32),
  first_name VARCHAR2(40),
  last_name VARCHAR2(40)
);
ALTER TABLE test_users ADD
(
  CONSTRAINT test_users_pk 
  PRIMARY KEY (user_id)
  USING INDEX
)
/
ALTER TABLE test_users ADD
(
  CONSTRAINT test_users_uq 
  UNIQUE (username)
  USING INDEX
)
/

INSERT INTO test_users VALUES (1, 'A', 'Sneezy', 'Timon');
INSERT INTO test_users VALUES (2, 'B', 'Dopey', 'Simba');
INSERT INTO test_users VALUES (3, 'C', 'Happy', 'Nala');
INSERT INTO test_users VALUES (4, 'D', 'Grumpy', 'Pumbaa');
COMMIT;
CREATE OR REPLACE FUNCTION test_function RETURN test_users.user_id%TYPE IS
    identifier VARCHAR2(32);
    user_id    users.user_id%TYPE;
  BEGIN
    SELECT sys_context('userenv', 'client_identifier') INTO identifier FROM dual;

    SELECT user_id INTO user_id FROM test_users WHERE upper(username) = upper(identifier);
    dbms_output.put_line('TEST_FUNCTION called!');
    RETURN user_id;

END test_function;

-- Testing with disabled result cache
ALTER TABLE test_users RESULT_CACHE (MODE DEFAULT);
DECLARE
  f users.first_name%TYPE;
  last_name  users.last_name%TYPE;
  identifier VARCHAR2(32);
  l_user_id users.user_id%type;
BEGIN
  dbms_output.put_line('setting the session identifier to A (Sneezy, Timon):');
  dbms_session.set_identifier('A');
  l_user_id := test_function();
  dbms_output.put_line('function call in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = test_function();
  dbms_output.put_line(f || ' ' || last_name);
  dbms_output.put_line('variable use in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = l_user_id;
  dbms_output.put_line(f || ' ' || last_name);
  dbms_output.put_line('----');
  dbms_output.put_line('setting the session identifier to B (Dopey Simba):');
  dbms_session.set_identifier('B');
  l_user_id := test_function();
  dbms_output.put_line('function call in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = test_function();
  dbms_output.put_line(f || ' ' || last_name);
  dbms_output.put_line('variable use in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = l_user_id;
  dbms_output.put_line(f || ' ' || last_name);

END;
/

-- Testing with enabled result cache
ALTER TABLE test_users RESULT_CACHE (MODE FORCE);
DECLARE
  f users.first_name%TYPE;
  last_name  users.last_name%TYPE;
  identifier VARCHAR2(32);
  l_user_id users.user_id%type;
BEGIN
  dbms_output.put_line('setting the session identifier to A (Sneezy, Timon):');
  dbms_session.set_identifier('A');
  l_user_id := test_function();
  dbms_output.put_line('function call in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = test_function();
  dbms_output.put_line(f || ' ' || last_name);
  dbms_output.put_line('variable use in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = l_user_id;
  dbms_output.put_line(f || ' ' || last_name);
  dbms_output.put_line('----');
  dbms_output.put_line('setting the session identifier to B (Dopey Simba):');
  dbms_session.set_identifier('B');
  l_user_id := test_function();
  dbms_output.put_line('function call in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = test_function();
  dbms_output.put_line(f || ' ' || last_name);
  dbms_output.put_line('variable use in WHERE criteria:');
  SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = l_user_id;
  dbms_output.put_line(f || ' ' || last_name);

END;
/

索引可能是必要的，也可能不是必需的 . 这里的想法是当前用户的名字在会话标识符中 . 测试功能将会话标识符中的用户名转换为用户ID . 用户名可以（理论上）更改，并用作登录名 . 用户ID永远不会改变，因此是表的PK .

困扰我的是，当打开结果缓存时，并不总是调用此语句的WHERE条件中的函数：

SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = test_function();

第一个PL / SQL块产生以下结果：

setting the session identifier to A (Sneezy, Timon):
TEST_FUNCTION called!
function call in WHERE criteria:
TEST_FUNCTION called!
Sneezy Timon
variable use in WHERE criteria:
Sneezy Timon
----
setting the session identifier to B (Dopey Simba):
TEST_FUNCTION called!
function call in WHERE criteria:
TEST_FUNCTION called!
Dopey Simba
variable use in WHERE criteria:
Dopey Simba

第二个块产生这个：

setting the session identifier to A (Sneezy, Timon):
TEST_FUNCTION called!
function call in WHERE criteria:
TEST_FUNCTION called!
Sneezy Timon
variable use in WHERE criteria:
Sneezy Timon
----
setting the session identifier to B (Dopey Simba):
TEST_FUNCTION called!
function call in WHERE criteria:
Sneezy Timon
variable use in WHERE criteria:
Dopey Simba

如您所见，TEST_FUNCTION的调用次数减少，结果错误 . 我理解结果缓存的方式，用户表应该是一个完美的候选人 . 许多SELECT，很少DML . 除非我将函数调用放在WHERE标准中，否则一切正常 . 如果我调用该函数，将结果保存在变量中并在WHERE标准中使用它，一切都很好 .

这是为什么？这是一个错误还是一个功能？事实上该函数使用来自会话标识符的数据是主要问题吗？或者通常不会为整个表打开结果缓存？

编辑：在阅读了一些答案之后，我尝试将该函数显式声明为缓存结果，如下所示：

CREATE OR REPLACE FUNCTION test_function(identifier VARCHAR2 DEFAULT sys_context('userenv', 'client_identifier'))
    RETURN test_users.user_id%TYPE result_cache relies_on(test_users) IS
    user_id test_users.user_id%TYPE;
BEGIN
    SELECT user_id INTO user_id FROM test_users WHERE upper(username) = upper(identifier);
    dbms_output.put_line('TEST_FUNCTION called!');
    RETURN user_id;
END test_function;

这与下面评论中的Oracle文档示例非常相似 .

可悲的是，这没有帮助 . 使用或不使用parantheses调用函数对我没有任何影响（但请参阅下面的评论） . 我发现始终获得预期结果的唯一方法是禁用表的结果缓存 .

3 回答

SELECT 
        S.ST_NM, 
        FPS.PRTCPNT_TYP_CD,
        FPS.IVD_UNQ_PRSN_CNT IVD,
        FPS.NIVD_UNQ_PRSN_CNT NonIVD,
        FPS.UNQ_PRSN_CNT Total,
        SUM(CASE WHEN FBPS.CASE_TYP_CD = 'F' AND FBPS.ST_CD <> 'ZZ000' THEN FBPS.FV_ADD_CNT ELSE 0 END) IVDAdded,
        SUM(CASE WHEN FBPS.CASE_TYP_CD = 'F' AND FBPS.ST_CD <> 'ZZ000' THEN FBPS.FV_RMVL_CNT ELSE 0 END) IVDRemoved,
        SUM(CASE WHEN FBPS.CASE_TYP_CD = 'F' AND FBPS.ST_CD <> 'ZZ000' THEN FBPS.FV_ADD_CNT - FBPS.FV_RMVL_CNT ELSE 0 END) IVDNet,
        SUM(CASE WHEN FBPS.CASE_TYP_CD = 'N' AND FBPS.ST_CD <> 'ZZ000' THEN FBPS.FV_ADD_CNT ELSE 0 END) NonIVDAdded,
        SUM(CASE WHEN FBPS.CASE_TYP_CD = 'N' AND FBPS.ST_CD <> 'ZZ000' THEN FBPS.FV_RMVL_CNT ELSE 0 END) NonIVD Removed,
        SUM(CASE WHEN FBPS.CASE_TYP_CD = 'N' AND FBPS.ST_CD <> 'ZZ000' THEN FBPS.FV_ADD_CNT - FBPS.FV_RMVL_CNT ELSE 0 END) NonIVDNet
from
        STATE  S
        LEFT JOIN FCR_PRSN_SUMRY FPS ON FPS.PRTCPNT_TYP_CD IN ('CH','CP','NP','PF','ZZ')
                    and FPS.FV_IND = 'Y' 
                    AND FPS.SERIES_CD = 'FV002'
                    AND FPS.ST_CD = S.ST_CD 
        LEFT JOIN FCR_BATCH_PRSN_SUMRY FBPS ON FBPS.ST_CD = FPS.ST_CD AND FBPS.PRCSD_DT = FPS.PRCSD_DT
        LEFT JOIN MI_CALENDAR MI ON MI.PRCSD_DT = FPS.PRCSD_DT  AND PREV_CAL_MTH_STRT_DT  = :START_DATE
                                                                AND PREV_CAL_MTH_END_DT    = :END_DATE
WHERE S.ST_CD IN (SELECT ST_CD 
                             FROM ADMN_ST_GRP 
                             WHERE ST_GRP_ID = 'STDZZ')                                                             
GROUP BY        
        S.ST_NM, FPS.PRTCPNT_TYP_CD,
        FPS.IVD_UNQ_PRSN_CNT,
        FPS.NIVD_UNQ_PRSN_CNT,
        FPS.UNQ_PRSN_CNT
ORDER BY S.ST_NM;

回复于 2024-05-08T13:08:23+08:00

问题不在于 TEST_USERS 表上的缓存 . 问题是 TEST_FUNCTION 函数的结果正在被缓存，更改会话标识符不会使这些结果无效 .

要避免此问题，请先将 TEST_FUNCTION 的定义更改为：

CREATE OR REPLACE FUNCTION test_function ( identifier VARCHAR2 DEFAULT sys_context('userenv', 'client_identifier') ) RETURN test_users.user_id%TYPE IS
    --identifier VARCHAR2(32);
    user_id    test_users.user_id%TYPE;
  BEGIN
    --SELECT sys_context('userenv', 'client_identifier') INTO identifier FROM dual;

    SELECT user_id INTO user_id FROM test_users WHERE upper(username) = upper(identifier);
    dbms_output.put_line('TEST_FUNCTION called!');
    RETURN user_id;

END test_function;

然后，当您在 WHERE 子句中使用它时，请调用它：

SELECT first_name, last_name 
INTO f, last_name 
FROM test_users WHERE user_id = test_function;

重要提示：请注意我没有使用 test_function() （即，请注意没有括号） .

为什么括号很重要？我不知道 . 我不认为他们应该这样做 . 但这适用于我的12.1.0.2实例 .

运行您发布的测试用例的变体，我在最后得到这些结果：

function call in WHERE criteria with no parens...: Dopey Simba
function call in WHERE criteria with parens...: Sneezy Timon

回复于 2024-05-08T13:08:23+08:00

-3
似乎Oracle正在看到这两个调用
```
SELECT first_name, last_name INTO f, last_name FROM test_users WHERE user_id = test_function();
```
并且考虑到，由于查询"look"相同，它可以在第二次调用中重用第一次调用的结果 . 由于 test_function() 不是确定性的，因此这个假设不正确（可能是一个错误，Oracle不应该假设函数是确定性的，除非在函数声明中使用了 DETERMINISTIC 子句） .

当您使用显式参数绑定时，Oracle理解不仅查询必须是等效的，参数值也必须相同才能使结果缓存有效 . 因此，如果您没有其他限制，则使用显式参数编写查询可能是一个可行的选择 .

无论如何，我个人不会为结果缓存而烦恼，查询太简单了;你可以依靠好的旧缓冲区缓存来加速这些查询，只要你在 USER_ID 中有一个索引，很可能就是这种情况（你说它毕竟是PK） . 随着查询变得更加复杂，结果缓存将更有用，因为在这些情况下，Oracle将能够绕过执行计划的更大子树 .
回复于 2024-05-08T13:08:23+08:00

为什么Oracle 12.1.0.2会跳过结果缓存表上的函数调用？

3 回答

相关问题