之前投给It168的一篇文章中简单点评了一下Oracle11g的这个新特性，当时没有软件所以没有给出例子。本文主要是实际的测试演示一下server result cache。
Server result cache是在shared pool中为查询结果开辟一块专用的缓存，通过新引入的一个初始化参数来控制该cache的大小：result_cache_max_size。如果result_cache_max_size=0则表示禁用该特性。参数result_cache_max_result则控制单个缓存结果可以占总的Server result cache大小的百分比。另外还有一个初始化参数用于控制Server result cache的模式：result_cache_mode，如果取值为auto，则优化器会自动判断是否将查询结果缓存；如果取值为manual（这是默认值），则需要通过查询提示result_cache来告诉优化器是否缓存结果；如果取值为force，则系统会尽可能的缓存查询结果，当然，即使参数取值为force，也可以通过提示no_result_cache来拒绝缓存结果。在itpub关于该特性的讨论中，有人提到，文档中result_cache_mode只有manual/force两个取值。这个可能是文档的bug，通过给参数赋一个非法值得到的报错信息中明白的说明有三个取值：
SQL> alter session set result_cache_mode=a;
ERROR:
ORA-00096: invalid value A for parameter result_cache_mode, must be from among FORCE, MANUAL, AUTO
ERROR:
ORA-00096: invalid value A for parameter result_cache_mode, must be from among FORCE, MANUAL, AUTO
Oracle11g还引入了一个package专门用于管理server result cache：dbms_result_cache
清空server result cache
SQL> exec dbms_result_cache.flush
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
查看server result cache的内存使用报告
SQL> set serveroutput on
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 = 1184K bytes (1184 blocks)
Maximum Result Size = 59K bytes (59 blocks)
[Memory]
Total Memory = 5132 bytes [0.006% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
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 = 1184K bytes (1184 blocks)
Maximum Result Size = 59K bytes (59 blocks)
[Memory]
Total Memory = 5132 bytes [0.006% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL procedure successfully completed.
通过动态性能视图v$result_cache_statistics可以知道server result cache的使用统计信息。
SQL> select * from v$result_cache_statistics;
ID NAME VALUE
---------- ------------------------------ ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1184
3 Block Count Current 0
4 Result Size Maximum (Blocks) 59
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
10 rows selected.
ID NAME VALUE
---------- ------------------------------ ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 1184
3 Block Count Current 0
4 Result Size Maximum (Blocks) 59
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
10 rows selected.
其他相关动态性能视图：
v$result_cache_objects
v$result_cache_memory
v$result_cache_dependency
下面我们看一个实际的例子
SQL> set autotrace on
SQL> select /*+ result_cache */ count(*) from test;
COUNT(*)
----------
11865
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1)
1 0 RESULT CACHE OF '7rkq2h8sb7kdg5sjxw69z8c45g'
2 1 SORT (AGGREGATE)
3 2 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=12024)
Statistics
----------------------------------------------------------
337 recursive calls
0 db block gets
291 consistent gets
0 physical reads
0 redo size
352 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ result_cache */ count(*) from test;
COUNT(*)
----------
11865
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1)
1 0 RESULT CACHE OF '7rkq2h8sb7kdg5sjxw69z8c45g'
2 1 SORT (AGGREGATE)
3 2 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=12024)
Statistics
----------------------------------------------------------
337 recursive calls
0 db block gets
291 consistent gets
0 physical reads
0 redo size
352 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
注意到执行计划中的RESULT CACHE OF ‘7rkq2h8sb7kdg5sjxw69z8c45g’，说明已经缓存前面两步的结果。
再来看result cache的内存使用情况
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 = 1184K bytes (1184 blocks)
Maximum Result Size = 59K bytes (59 blocks)
[Memory]
Total Memory = 103528 bytes [0.112% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.107% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 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 = 103528 bytes [0.112% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.107% of the Shared Pool]
....... Overhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 30 blocks
........... Used Memory = 2 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 1 blocks
................... SQL = 1 blocks (1 count)
PL/SQL procedure successfully completed.
查看v$result_cache_statistics
SQL> 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说明成功的缓存了一笔记录
再次执行该查询
SQL> select /*+ result_cache */ count(*) from test;
COUNT(*)
----------
11865
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1)
1 0 RESULT CACHE OF '7rkq2h8sb7kdg5sjxw69z8c45g'
2 1 SORT (AGGREGATE)
3 2 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=12024)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
352 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
COUNT(*)
----------
11865
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1)
1 0 RESULT CACHE OF '7rkq2h8sb7kdg5sjxw69z8c45g'
2 1 SORT (AGGREGATE)
3 2 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=12024)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
352 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
注意到这里consistent gets=0，也就是该查询甚至不产生逻辑读了，也就是直接从result cache中获得了查询结果。
查看v$result_cache_statistics
SQL> 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，说明上次缓存的结果被后续的查询应用了一次。如果继续执行该查询，可以发现该统计信息相应增加。
当然，这个例子中，缓存了整个查询的结果，实际上也可以缓存执行计划中某一步或者某几步的查询结果。例如，下面的例子中，就缓存了子查询的结果
SQL> select count(*) from (select /*+result_cache*/* from test where object_id<100);
COUNT(*)
----------
98
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=48 Card=237)
3 2 RESULT CACHE OF 'fbbc5y53mwuj75buth9d2vwkkn'
4 3 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=237 Bytes=37446)
Statistics
----------------------------------------------------------
716 recursive calls
4 db block gets
403 consistent gets
0 physical reads
0 redo size
350 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
23 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from (select /*+result_cache*/* from test where object_id<100);
COUNT(*)
----------
98
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=48 Card=237)
3 2 RESULT CACHE OF 'fbbc5y53mwuj75buth9d2vwkkn'
4 3 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=237 Bytes=37446)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
350 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
COUNT(*)
----------
98
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=48 Card=237)
3 2 RESULT CACHE OF 'fbbc5y53mwuj75buth9d2vwkkn'
4 3 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=237 Bytes=37446)
Statistics
----------------------------------------------------------
716 recursive calls
4 db block gets
403 consistent gets
0 physical reads
0 redo size
350 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
23 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from (select /*+result_cache*/* from test where object_id<100);
COUNT(*)
----------
98
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=48 Card=237)
3 2 RESULT CACHE OF 'fbbc5y53mwuj75buth9d2vwkkn'
4 3 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=48 Card=237 Bytes=37446)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
350 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Result Cache对于OLTP应该是没有意义,因为下次执行结果就不一样了,也怎么cache?
[…] Result Cache，一个是PL/SQL Function Result Cache。在上一篇文章中简单展示了SQL Result Cache的特性，这里打算说说PL/SQL Function Result […]