Oracle包级缓存引发实时数据不一致的解决方案咨询

针对连接池复用会话导致包级缓存残留旧数据的问题，以下是几个可靠的优化方案，均满足"仅修改函数所在包或第三方SQL"的限制：

方案1：显式调用缓存清空过程（最直接可靠） #

操作步骤： #

1. 修改包结构，新增缓存清空的存储过程：

CREATE OR REPLACE PACKAGE pack_test AS
    -- 定义缓存集合类型（根据实际数据结构调整）
    TYPE t_costly_cache IS TABLE OF your_data_type;
    g_costly_cache t_costly_cache;

    FUNCTION fn_costly(p_type VARCHAR2, p_attr VARCHAR2) RETURN VARCHAR2;
    -- 新增缓存清空过程
    PROCEDURE clear_cache;
END pack_test;
/

CREATE OR REPLACE PACKAGE BODY pack_test AS
    PROCEDURE clear_cache IS
    BEGIN
        IF g_costly_cache IS NOT NULL THEN
            g_costly_cache.DELETE;
        END IF;
    END clear_cache;

    FUNCTION fn_costly(p_type VARCHAR2, p_attr VARCHAR2) RETURN VARCHAR2 IS
    BEGIN
        -- 缓存为空时重新加载数据
        IF g_costly_cache IS NULL OR g_costly_cache.COUNT = 0 THEN
            SELECT your_data_columns BULK COLLECT INTO g_costly_cache 
            FROM your_source_table;
        END IF;

        -- 根据入参匹配返回数据（示例逻辑，按需调整）
        FOR idx IN g_costly_cache.FIRST .. g_costly_cache.LAST LOOP
            IF g_costly_cache(idx).type = p_type 
               AND g_costly_cache(idx).attr = p_attr THEN
                RETURN g_costly_cache(idx).value;
            END IF;
        END LOOP;
        RETURN NULL;
    END fn_costly;
END pack_test;
/

2. 修改第三方执行的SQL，在查询前先调用清空缓存的过程：

BEGIN
    pack_test.clear_cache;
END;
/
SELECT
    pack_test.fn_costly('Person','Name'),
    pack_test.fn_costly('Person','Age'),
    pack_test.fn_costly('Person', 'Gender'),
    pack_test.fn_costly('Person', 'Address')
FROM DUAL;

优缺点： #

• ✅ 完全可靠，确保每次SQL执行前缓存彻底清空，重新加载最新数据
• ✅ 实现简单，无额外依赖
• ⚠️ 需要修改第三方SQL，增加一次轻量级过程调用

方案2：用会话上下文标记缓存有效性（无需显式清空调用） #

通过会话上下文标记当前SQL执行的唯一标识，函数自动校验缓存是否属于当前执行周期，不一致则刷新缓存。

操作步骤： #

1. 修改包结构，增加缓存标记逻辑：

CREATE OR REPLACE PACKAGE pack_test AS
    TYPE t_costly_cache IS TABLE OF your_data_type;
    g_costly_cache t_costly_cache;
    g_cache_tag VARCHAR2(32); -- 缓存对应的执行标记

    FUNCTION fn_costly(p_type VARCHAR2, p_attr VARCHAR2) RETURN VARCHAR2;
END pack_test;
/

CREATE OR REPLACE PACKAGE BODY pack_test AS
    -- 获取当前会话的执行标记
    FUNCTION get_current_exec_tag RETURN VARCHAR2 IS
    BEGIN
        RETURN SYS_CONTEXT('USERENV', 'CLIENT_INFO');
    END get_current_exec_tag;

    FUNCTION fn_costly(p_type VARCHAR2, p_attr VARCHAR2) RETURN VARCHAR2 IS
        v_current_tag VARCHAR2(32);
    BEGIN
        v_current_tag := get_current_exec_tag();
        
        -- 标记不匹配/缓存为空时，刷新缓存
        IF g_cache_tag IS NULL OR g_cache_tag != v_current_tag 
           OR g_costly_cache IS NULL OR g_costly_cache.COUNT = 0 THEN
            IF g_costly_cache IS NOT NULL THEN
                g_costly_cache.DELETE;
            END IF;
            SELECT your_data_columns BULK COLLECT INTO g_costly_cache 
            FROM your_source_table;
            g_cache_tag := v_current_tag;
        END IF;

        -- 匹配返回数据逻辑（同方案1）
        FOR idx IN g_costly_cache.FIRST .. g_costly_cache.LAST LOOP
            IF g_costly_cache(idx).type = p_type 
               AND g_costly_cache(idx).attr = p_attr THEN
                RETURN g_costly_cache(idx).value;
            END IF;
        END LOOP;
        RETURN NULL;
    END fn_costly;
END pack_test;
/

2. 修改第三方SQL，在查询前设置唯一执行标记：

BEGIN
    -- 用SYS_GUID生成唯一标记，确保每次SQL执行标记不同
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_GUID());
END;
/
SELECT
    pack_test.fn_costly('Person','Name'),
    pack_test.fn_costly('Person','Age'),
    pack_test.fn_costly('Person', 'Gender'),
    pack_test.fn_costly('Person', 'Address')
FROM DUAL;

优缺点： #

• ✅ 无需显式调用清空过程，函数自动判断是否刷新缓存
• ✅ 同一条SQL内的多次函数调用仍能复用缓存，保留性能优势
• ⚠️ 需要修改第三方SQL，增加标记设置逻辑

方案对比（vs 你的临时方案） #

你之前用HH24MI的方案存在明显缺陷：如果同一分钟内有两次SQL执行，第二次会复用第一次的缓存，即使中间数据已经更新，完全无法保证数据准确性。

上述两个方案均能确保每次独立SQL执行都加载最新数据，同时保留包级缓存对同SQL内多次函数调用的性能优化，是完全可靠的替代方案。

内容的提问来源于stack exchange，提问作者user1140840