result_cache_remote_expiration integer 0

09:48:09 sys@felix SQL>

上面显示的参数中result_cache_mode用于控制Server result cache的模式，该参数有3个可选设置。

(1) 设置auto：则优化器会自动判断是否将查询结果缓存。

(2) 设置manual：则需要通过查询提示result_cache来告诉优化器是否缓存结果。

(3) 设置force ：则尽可能地缓存查询结果（通过提示no_result_cache可以拒绝缓存）

09:52:31 scott@felix SQL>create table felix asselect * from dba_objects;

Table created.

09:53:28 scott@felix SQL>alter systemflush SHARED_POOL;

System altered.

09:53:42 scott@felix SQL>alter system flushBUFFER_CACHE;

System altered.

09:54:06 scott@felix SQL>set autot on;

09:54:25 scott@felix SQL>select count(*) fromfelix;

COUNT(*)

----------

75613

Execution Plan

----------------------------------------------------------

Plan hash value: 2587295606

--------------------------------------------------------------------

| Id |Operation | Name | Rows | Cost (%CPU)| Time |

--------------------------------------------------------------------

| 0 |SELECT STATEMENT | | 1 | 301 (1)| 00:00:04 |

| 1 | SORTAGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| FELIX | 63221 | 301 (1)| 00:00:04 |

--------------------------------------------------------------------

Note

-----

- dynamicsampling used for this statement (level=2)

Statistics

----------------------------------------------------------

70 recursive calls

0 db block gets

1167 consistent gets

1351 physical reads

0 redo size

528 bytes sent via SQL*Net to client

523 bytes received via SQL*Netfrom client

2 SQL*Net roundtrips to/fromclient

5 sorts (memory)

0 sorts (disk)

1 rows processed

09:54:44 scott@felix SQL>

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

09:56:02 scott@felix SQL>show parameterresult_cache_mode

NAME TYPE VALUE

------------------------------------ -----------------------------------

result_cache_mode string MANUAL

09:56:50 scott@felix SQL>

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

09:56:50 scott@felix SQL>select /*+result_cache */ count(*) from felix;

COUNT(*)

----------

75613

Execution Plan

----------------------------------------------------------

Plan hash value: 2587295606

------------------------------------------------------------------------------------------

| Id |Operation | Name | Rows | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------

| 0 |SELECT STATEMENT | | 1 | 301 (1)| 00:00:04 |

| 1 | RESULT CACHE | 1hnnwscv2aj3631n497zczt04j | | | |

| 2 | SORT AGGREGATE | | 1 | | |

| 3 | TABLE ACCESS FULL| FELIX | 63221 | 301 (1)| 00:00:04 |

------------------------------------------------------------------------------------------

Result Cache Information (identified by operationid):

------------------------------------------------------

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

Note

-----

- dynamicsampling used for this statement (level=2)

Statistics

----------------------------------------------------------

4 recursive calls

0 db block gets

1137 consistent gets

1077 physical reads

0 redo size

528 bytes sent via SQL*Net toclient

523 bytes received via SQL*Netfrom client

2 SQL*Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (disk)

1 rows processed