Oracle 11g新特性:Server Result Cache测试

« Oracle 11g新特性:Result Cache之一 | Blog首页 | 解决防火墙等服务（ICS）不能启动问题 »

现在我们来看一下Server Result Cache的作用。

首先创建一张测试表：

SQL> connect eygle/eygle
Connected.
SQL> create table eygle as select * from dba_objects;

Table created.

在以前版本中，我们第一次执行该SQL可以看到consistent gets和physical reads大致相同：

SQL> set autotrace on
SQL> select count(*) from eygle;

COUNT(*)
----------
15993

Execution Plan
----------------------------------------------------------
Plan hash value: 3602634261

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EYGLE | 14489 | 64 (0)| 00:00:01 |
--------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
282 consistent gets
217 physical reads
0 redo size
420 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

再次执行同样查询时，由于数据Cache在内存中，physical reads会减少到0.
但是consistent gets仍然不变：

SQL> select count(*) from eygle;

COUNT(*)
----------
15993

Execution Plan
----------------------------------------------------------
Plan hash value: 3602634261

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EYGLE | 14489 | 64 (0)| 00:00:01 |
--------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
221 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

现在我们来看看在Server Result Cache下，Oracle的行为。
首先在result_cache_mode参数设置为MANUAL时：

SQL> show parameter result_cache_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode string MANUAL

我们需要在SQL中手工指定Cache，这需要通过加入一个hints来实现，这个hints是result_cache：

SQL> select /*+ result_cache */ count(*) from eygle;

COUNT(*)
----------
15993

Execution Plan
----------------------------------------------------------
Plan hash value: 3602634261

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 (0)| 00:00:01 |
| 1 | RESULT CACHE | 76rwwyazv6t6c39f1d8rrqh8rb | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| EYGLE | 14489 | 64 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=1; dependencies=(EYGLE.EYGLE);
attributes=(single-row); name="select /*+ result_cache */ count(*) from eygle"

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
280 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
416 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以76rwwyazv6t6c39f1d8rrqh8rb名称创建。

那么在接下来的查询中，这个Result Cache就可以被利用：

SQL> select /*+ result_cache */ count(*) from eygle;

COUNT(*)
----------
15993

Execution Plan
----------------------------------------------------------
Plan hash value: 3602634261

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 (0)| 00:00:01 |
| 1 | RESULT CACHE | 76rwwyazv6t6c39f1d8rrqh8rb | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| EYGLE | 14489 | 64 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=1; dependencies=(EYGLE.EYGLE);
attributes=(single-row); name="select /*+ result_cache */ count(*) from eygle"

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
416 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的查询中，consistent gets减少到0，直接访问结果集，不再需要执行SQL查询。
这就是Result Cache的强大之处。

我们可以通过查询v$result_cache_memory视图来看Cache的使用情况：

SQL> select * from V$RESULT_CACHE_MEMORY
2 where FREE='NO';

ID CHUNK OFFSET FRE OBJECT_ID POSITION
---------- ---------- ---------- --- ---------- ----------
0 0 0 NO 0 0
1 0 1 NO 1 0

通过V$RESULT_CACHE_STATISTICS可以查询Result Cache的统计信息：

SQL> select * from V$RESULT_CACHE_STATISTICS;

ID NAME VALUE
---------- ------------------------------ ----------
1 Block Size (Bytes) 1024
2 Block Count Maximum 992
3 Block Count Current 32
4 Result Size Maximum (Blocks) 49
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

10 rows selected.

V$RESULT_CACHE_OBJECTS记录了Cache的对象：

SQL> SELECT ID,TYPE,NAME,BLOCK_COUNT,ROW_COUNT FROM V$RESULT_CACHE_OBJECTS;

ID TYPE NAME BLOCK_COUNT ROW_COUNT
---------- ---------- ------------------------------ ----------- ----------
0 Dependency EYGLE.EYGLE 1 0
1 Result select /*+ result_cache */ cou 1 1
nt(*) from eygle

一个新的系统包被引入，DBMS_RESULT_CACHE可以用于执行关于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 = 992K bytes (992 blocks)
Maximum Result Size = 49K bytes (49 blocks)
[Memory]
Total Memory = 100836 bytes [0.059% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 95704 bytes [0.056% of the Shared Pool]
....... Overhead = 62936 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.