为了测试方便，将RESULT_CACHE_MODE设置为AUTO模式。
设置参数为AUTO后，第二次执行SQL已经使用了RESULT CACHE。下面来看RESULT CACHE的第一个特点，由于RESULT CACHE所使用的内存来自SGA，因此RESULT CACHE的结果对于所有的SESSION都是可见的。SQL> ALTER SESSION SET RESULT_CACHE_MODE = AUTO; 会话已更改。 SQL> SET AUTOT ON SQL> SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT /*+ RESULT_CAC T" 统计信息 ---------------------------------------------------------- 56 recursive calls 0 db block gets 1019 consistent gets 1007 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
下面启动第二个SQLPLUS SESSION：
由于SELECT COUNT(*) FROM T的结果已经保存在SGA中，其他的SESSION也是可以使用这个缓存结果集的。RESULT CACHE是满足一致性读的，而且当缓存表数据发生变化的时候，Oracle会自动INVALIDATE缓存结果集：SQL> SET SQLPROMPT 'SQL2> ' SQL2> SET AUTOT ON SQL2> ALTER SESSION SET RESULT_CACHE_MODE = AUTO; 会话已更改。 SQL2> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 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，但是这个时候如果从第二个会话进行查询：SQL> DELETE T WHERE ROWNUM = 1; 已删除 1 行。 SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68323 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 1011 consistent gets 0 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
由于会话1没有提交，修改对于会话2来说是不可见的。因此会话2仍然可以使用缓存结果集。SQL2> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
将会话1的修改回滚，则会话1又可以利用RESULT CACHE。SQL> ROLLBACK; 回退已完成。 SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
由于进行DDL操作，Oracle自动INVALIDATE了RESULT CACHE结果集，可以通过V$RESULT_CACHE_OBJECTS观察到：SQL> CREATE INDEX IND_T_OBJECT_ID ON T(OBJECT_ID); 索引已创建。 SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT COU 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 1011 consistent gets 0 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> ALTER TABLE T MODIFY OBJECT_ID NOT NULL; 表已更改。 SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 3841213438 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 44 (3)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IND_T_OBJECT_ID | 68324 | 44 (3)| 00:00:01 | --------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 204 recursive calls 0 db block gets 187 consistent gets 151 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed
从最后几个查询可以看出，RESULT CACHE忽略SQL语句的执行计划，即使执行计划发生了变化，只有最终获取的结果是一样的，Oracle仍然会使用RESULT CACHE。SQL> SELECT ID, TYPE, STATUS FROM V$RESULT_CACHE_OBJECTS 2 WHERE NAME = 'SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T'; ID TYPE STATUS ---------- ---------- --------- 1 Result Invalid 最后看一下Oracle RESULT CACHE的另外一个特点，是否使用RESULT_CACHE与Oracle的执行计划无关： SQL> SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 3841213438 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 44 (3)| 00:00:01 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | INDEX FAST FULL SCAN| IND_T_OBJECT_ID | 68324 | 44 (3)| 00:00:01 | --------------------------------------------------------------------------------------------- Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT /*+ RESULT_CAC T" 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 158 consistent gets 0 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 3841213438 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 44 (3)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IND_T_OBJECT_ID | 68324 | 44 (3)| 00:00:01 | --------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 158 consistent gets 0 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SELECT /*+ FULL(T) */ COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 2966233522 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 282 (1)| 00:00:04 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T | 68324 | 282 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT /*+ FROM T" 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SELECT /*+ INDEX(T) */ COUNT(*) FROM T; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 2391220499 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 153 (1)| 00:00:02 | | 1 | RESULT CACHE | 1t707xy2juqfq1xgx9dvy4bcqj | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | INDEX FULL SCAN| IND_T_OBJECT_ID | 68324 | 153 (1)| 00:00:02 | ---------------------------------------------------------------------------------------- Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; type=AUTO; dependencies=(YANGTK.T); attributes=(single-row); name="SELECT /*+ ) FROM T" 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SELECT COUNT(*) FROM T A; COUNT(*) ---------- 68324 执行计划 ---------------------------------------------------------- Plan hash value: 3841213438 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 44 (3)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IND_T_OBJECT_ID | 68324 | 44 (3)| 00:00:01 | --------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 158 consistent gets 0 physical reads 0 redo size 338 bytes sent via SQL*Net to client 377 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
注意当执行计划不同导致结果不同时，Oracle使用RESULT CACHE可能会得到不正确的结果。
