Server Result Cache应该是Oracle11g新特性中比较令人期待的，很多人都在研究这个特性，比如eygle和yangtingkun都为这个特性写了一系列的文章。
Server Result Cache实际上是两个特性，一个是SQL Result Cache，一个是PL/SQL Function Result Cache。在上一篇文章中简单展示了SQL Result Cache的特性，这里打算说说PL/SQL Function Result Cache。
要在PL/SQL Function中启用result cache特性，只需要在创建function时添加RESULT_CACHE子句即可。另外还有一个RELIES_ON子句，可以指定该function依赖的对象，如果该对象改变则会导致该function缓存结果失效。
创建一个测试函数
NING@11g>create or replace function f_result_cache
2 return int
3 result_cache
4 as
5 l_count int;
6 begin
7 select count(*) into l_count
8 from all_objects;
9 -- pause for 5 second
10 dbms_lock.sleep(5);
11 return l_count;
12 end f_result_cache;
13 /
Function created.
2 return int
3 result_cache
4 as
5 l_count int;
6 begin
7 select count(*) into l_count
8 from all_objects;
9 -- pause for 5 second
10 dbms_lock.sleep(5);
11 return l_count;
12 end f_result_cache;
13 /
Function created.
创建一个procedure调用function来执行测试
NING@11g>CREATE OR REPLACE PROCEDURE run_test AS
2 l_start int;
3 l_count int;
4 BEGIN
5 l_start := DBMS_UTILITY.get_time;
6
7 l_count := f_result_cache;
8
9 DBMS_OUTPUT.put_line('Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
10 END run_test;
11 /
2 l_start int;
3 l_count int;
4 BEGIN
5 l_start := DBMS_UTILITY.get_time;
6
7 l_count := f_result_cache;
8
9 DBMS_OUTPUT.put_line('Time: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
10 END run_test;
11 /
先清空result cache
NING@11g>exec dbms_result_cache.flush;
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
第一次执行测试
NING@11g>set serveroutput on
NING@11g>exec run_test;
Time: 774 hsecs
PL/SQL procedure successfully completed.
NING@11g>exec run_test;
Time: 774 hsecs
PL/SQL procedure successfully completed.
查看result cache的内存报告，可以看到result cache已经缓存了一个plsql block
NING@11g>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 = 1184K bytes (1184 blocks)
Maximum Result Size = 59K bytes (59 blocks)
[Memory]
Total Memory = 101492 bytes [0.115% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 96360 bytes [0.109% of the Shared Pool]
....... Overhead = 63592 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... PLSQL = 1 blocks (1 count)
PL/SQL procedure successfully completed.
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 = 1184K bytes (1184 blocks)
Maximum Result Size = 59K bytes (59 blocks)
[Memory]
Total Memory = 101492 bytes [0.115% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 96360 bytes [0.109% of the Shared Pool]
....... Overhead = 63592 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... PLSQL = 1 blocks (1 count)
PL/SQL procedure successfully completed.
查看统计信息
NING@11g>select * from v$result_cache_statistics;
ID NAME VALUE
---------- ------------------------------ -------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1184
3 Block Count Current 32
4 Result Size Maximum (Blocks) 59
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
ID NAME VALUE
---------- ------------------------------ -------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1184
3 Block Count Current 32
4 Result Size Maximum (Blocks) 59
5 Create Count Success 1
6 Create Count Failure 0
7 Find Count 0
8 Invalidation Count 0
9 Delete Count Invalid 0
10 Delete Count Valid 0
Create Count Success = 1 说明成功的创建了一个缓存结果。
再次执行测试，发现输出的执行时间变为0，说明已经使用result cache直接得到function的结果，而不需要再次执行function。
PL/SQL procedure successfully completed.
NING@11g>exec run_test;
Time: 0 hsecs
PL/SQL procedure successfully completed.
NING@11g>exec run_test;
Time: 0 hsecs
PL/SQL procedure successfully completed.
再次查看统计信息
NING@11g>select * from v$result_cache_statistics;
ID NAME VALUE
---------- ------------------------------ -------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1184
3 Block Count Current 32
4 Result Size Maximum (Blocks) 59
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
ID NAME VALUE
---------- ------------------------------ -------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1184
3 Block Count Current 32
4 Result Size Maximum (Blocks) 59
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
Find Count = 1说明第二次执行测试时使用了缓存的结果。
另外开启一个session来执行，发现时间还是为0，统计信息中Find Count则增加为2，说明其他的session可以共享缓存的结果。
D:\>sqlplus ning/ning@11g
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Sep 6 11:08:22 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
NING@11g>set serveroutput on
NING@11g>exec run_test;
Time: 0 hsecs
PL/SQL procedure successfully completed.
NING@11g>select * from v$result_cache_statistics;
ID NAME VALUE
---------- ------------------------------ -------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1184
3 Block Count Current 32
4 Result Size Maximum (Blocks) 59
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
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Sep 6 11:08:22 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
NING@11g>set serveroutput on
NING@11g>exec run_test;
Time: 0 hsecs
PL/SQL procedure successfully completed.
NING@11g>select * from v$result_cache_statistics;
ID NAME VALUE
---------- ------------------------------ -------------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1184
3 Block Count Current 32
4 Result Size Maximum (Blocks) 59
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
重编译function会导致缓存结果失效
NING@11g>alter function f_result_cache compile;
Function altered.
NING@11g>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 = 1184K bytes (1184 blocks)
Maximum Result Size = 59K bytes (59 blocks)
[Memory]
Total Memory = 101492 bytes [0.115% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 96360 bytes [0.109% of the Shared Pool]
....... Overhead = 63592 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... Invalid = 1 blocks (1 count)
PL/SQL procedure successfully completed.
Function altered.
NING@11g>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 = 1184K bytes (1184 blocks)
Maximum Result Size = 59K bytes (59 blocks)
[Memory]
Total Memory = 101492 bytes [0.115% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 96360 bytes [0.109% of the Shared Pool]
....... Overhead = 63592 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... Invalid = 1 blocks (1 count)
PL/SQL procedure successfully completed.
Result-Cached Functions的一些限制
- 不能是调用者权限(invoke’s right)函数
- 不能是管道表函数(pipelined table function)
- 不能有OUT或者IN OUT类型的参数
- 不能是LOB/REF CURSOR/Collection/Ojbect/Record类型的参数
- 不能是LOB/REF CURSOR/Collection/Ojbect/Record类型的返回值
