Oracle11g PL/SQL Result Cache-优快云博客

本文链接：https://blog.youkuaiyun.com/staricqxyz/article/details/8314053

本文介绍Oracle11g中如何利用PL/SQL函数的result_cache特性来缓存函数结果，减少重复计算，提高系统性能。通过示例展示了缓存的创建、查询及清除过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle11g中提供了标记PL/SQL函数以指明应该缓存其结果的能力,这样下次使用同一参数值调用函数时就可以进行查找而不必重新计算了。缓存是系统级的,这样调用该函数的所有会话都会因缓存的返回值而收益,可以使用函数声明中的result_cache选项激活PL/SQL函数缓存。

清空缓存

SQL> SET serveroutput ON
SQL> EXEC dbms_result_cache.FLUSH;

PL/SQL PROCEDURE successfully completed.

SQL> ALTER system FLUSH shared_pool;

System altered.

SQL> EXEC dbms_result_cache.memory_report;
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block SIZE          = 1K bytes
Maximum Cache SIZE  = 2080K bytes (2080 blocks)
Maximum RESULT SIZE = 104K bytes (104 blocks)
[Memory]
Total Memory = 5352 bytes [0.003% OF the Shared Pool]
... Fixed Memory = 5352 bytes [0.003% OF the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% OF the Shared Pool]

PL/SQL PROCEDURE successfully completed.

创建一个函数,使用result_cache表示函数结果缓存,使用relies_on指定结果依赖的表或视图

SQL> CREATE OR REPLACE FUNCTION f_user(userno IN pls_integer)
  2  RETURN pls_integer
  3  result_cache relies_on (t_user)
  4  IS
  5  v_count pls_integer;
  6  BEGIN
  7    SELECT COUNT(*) INTO v_count FROM t_user WHERE userid = userno;
  8    RETURN v_count;
  9  END;
 10  /

FUNCTION created.

SQL> SELECT username,f_user(userid) counts FROM t_user WHERE username='tom';

USERNAME     COUNTS
-------- ----------
tom               1

确认内存分配情况

SQL> EXEC dbms_result_cache.memory_report;
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block SIZE          = 1K bytes
Maximum Cache SIZE  = 2080K bytes (2080 blocks)
Maximum RESULT SIZE = 104K bytes (104 blocks)
[Memory]
Total Memory = 165472 bytes [0.086% OF the Shared Pool]
... Fixed Memory = 5352 bytes [0.003% OF the Shared Pool]
... Dynamic Memory = 160120 bytes [0.083% OF the Shared Pool]
....... Overhead = 127352 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 3 blocks
............... Dependencies = 2 blocks (2 COUNT)
............... Results = 1 blocks
................... PLSQL   = 1 blocks (1 COUNT)

PL/SQL PROCEDURE successfully completed.

访问v$result_cache_statistics视图,Create Count Success为1则表示结果缓存被成功创建1次,可以看到这里为1

SQL> SELECT * FROM v$result_cache_statistics;

        ID NAME                           VALUE
---------- ------------------------------ ----------
         1 Block SIZE (Bytes)             1024
         2 Block COUNT Maximum            2080
         3 Block COUNT CURRENT            32
         4 RESULT SIZE Maximum (Blocks)   104
         5 CREATE COUNT Success           1
         6 CREATE COUNT Failure           0
         7 Find COUNT                     1
         8 Invalidation COUNT             0
         9 DELETE COUNT Invalid           0
        10 DELETE COUNT Valid             0
        11 Hash Chain LENGTH              1

        ID NAME                           VALUE
---------- ------------------------------ ----------
        12 Find Copy COUNT                1

12 ROWS selected.

再次调用同一个函数,如果Find Count为2说明结果缓存被成功找到一次,可以看到Find Count为2

SQL> SELECT username,f_user(userid) counts FROM t_user WHERE username='tom';

USERNAME     COUNTS
-------- ----------
tom               1

SQL> SELECT * FROM v$result_cache_statistics;

        ID NAME                           VALUE
---------- ------------------------------ ----------
         1 Block SIZE (Bytes)             1024
         2 Block COUNT Maximum            2080
         3 Block COUNT CURRENT            32
         4 RESULT SIZE Maximum (Blocks)   104
         5 CREATE COUNT Success           1
         6 CREATE COUNT Failure           0
         7 Find COUNT                     2
         8 Invalidation COUNT             0
         9 DELETE COUNT Invalid           0
        10 DELETE COUNT Valid             0
        11 Hash Chain LENGTH              1

        ID NAME                           VALUE
---------- ------------------------------ ----------
        12 Find Copy COUNT                2

12 ROWS selected.

再调用一次相同函数Find Count为3

SQL> SELECT username,f_user(userid) counts FROM t_user WHERE username='tom';

USERNAME     COUNTS
-------- ----------
tom               1

SQL> SELECT * FROM v$result_cache_statistics;

        ID NAME                           VALUE
---------- ------------------------------ ----------
         1 Block SIZE (Bytes)             1024
         2 Block COUNT Maximum            2080
         3 Block COUNT CURRENT            32
         4 RESULT SIZE Maximum (Blocks)   104
         5 CREATE COUNT Success           1
         6 CREATE COUNT Failure           0
         7 Find COUNT                     3
         8 Invalidation COUNT             0
         9 DELETE COUNT Invalid           0
        10 DELETE COUNT Valid             0
        11 Hash Chain LENGTH              1

        ID NAME                           VALUE
---------- ------------------------------ ----------
        12 Find Copy COUNT                3

12 ROWS selected.

访问v$result_cache_objects,scan_count这个字段值大于等于1则表示结果被缓存使用

SQL> SELECT TYPE,namespace,STATUS,scan_count,name FROM v$result_cache_objects;

TYPE       NAMES STATUS    SCAN_COUNT NAME
---------- ----- --------- ---------- --------------------------------------------------
Dependency       Published          0 ZWC.T_USER
Dependency       Published          0 ZWC.F_USER
RESULT     PLSQL Published          3 "ZWC"."F_USER"::8."F_USER"#34ca7ad889ae0101 #1

清空缓存

SQL> ALTER system FLUSH shared_pool;

System altered.

SQL> EXEC dbms_result_cache.FLUSH;

PL/SQL PROCEDURE successfully completed.

SQL> SELECT TYPE,namespace,STATUS,scan_count,name FROM v$result_cache_objects;

no ROWS selected

SQL> SELECT * FROM v$result_cache_statistics;

        ID NAME                                               VALUE
---------- -------------------------------------------------- ----------
         1 Block SIZE (Bytes)                                 1024
         2 Block COUNT Maximum                                2080
         3 Block COUNT CURRENT                                0
         4 RESULT SIZE Maximum (Blocks)                       104
         5 CREATE COUNT Success                               0
         6 CREATE COUNT Failure                               0
         7 Find COUNT                                         0
         8 Invalidation COUNT                                 0
         9 DELETE COUNT Invalid                               0
        10 DELETE COUNT Valid                                 0
        11 Hash Chain LENGTH                                  0

        ID NAME                                               VALUE
---------- -------------------------------------------------- ----------
        12 Find Copy COUNT                                    0

12 ROWS selected.