11G result cache缓存结果集的新特性，是一个非常适合只读、读>>写、典型OLTP等系统的功能。
对此做了一些functional的研究，因为有太多的文章介绍这个功能，我想有哪些视图、有哪些功能、有哪些命令、如何开启和关闭、如何调整缓存区的大小、如何查看缓存区的状态都不需要我赘述。即使我赘述一下，也是从其他某地copy paste过来，所以，这里仅仅探究一下一些可能没有太多人注意的地方（如果你已经了解非常清楚，当我没说。。）。
而我所有做的实验都是在11.1.0.6 @ solaris 10 下完成的。
我个人觉得对这个特性值得注意的地方有以下6点：
1. 在当前11g版本中，result_cache_mode没有AUTO模式。
当你baidu "result_cache_mode AUTO"，你会发现很多中文文章都写明这个参数有三个值：FORCE, MANUAL, AUTO.
当你google "result_cache_mode AUTO"，第一篇英文文章from sysdba.wordpress.com的标题就是“No AUTO RESULT_CACHE_MODE”。
其实对于这个问题，我自己在做实验和搜索相关的文档时，也注意到了。
对于Oracle官方文档中，始终没有提及AUTO模式的result_cache_mode。
而大家对于有auto模式的理解来源于这个Oracle error:
SQL> alter system set result_cache_mode=hao;
alter system set result_cache_mode=hao
*
ERROR at line 1:
ORA-00096: invalid value HAO for parameter result_cache_mode, must be from
among FORCE, MANUAL, AUTO
以及yingtingkun大牛的文章也提及了AUTO模式。
而我自己却始终没有看到AUTO模式的特性和作用。
BTW，测试环境为Release 11.1.0.6.0
这里有1篇来自Oracle Forum的Q&A提到了这个参数，显得更加可信一点：
http://forums.oracle.com/forums/thread.jspa?messageID=2190297
Oracle的人提到：
“ I learned from Development that this column is not intended for customer use.
It's either for internal use only, or reserved for future use (I don't know which).
I've asked that it be documented to indicate whatever is correct.
I seems rather silly not to documented it at all.”
也有一篇Oracle的文章提到了AUTO参数的作用：
http://www.oracle.com/technology/global/cn/obe/11gr1_db/perform/rescache/res_cache.htm
“AUTO — 优化器将根据大量因素决定是否缓存结果。
决定因素包括查询执行的频率、生成结果的成本以及针对底层数据库对象更改的频率。”
而我更愿意相信，这是将来的将来的某11g可能会引进的功能。
但当前来看，我并没有看到AUTO的稳定作用。
2.骗人的执行计划告诉你采用了result cache（实际却没有）
SQL> set autotrace on
SQL> select /*+result_cache*/ object_id from test where object_name='TEST';
OBJECT_ID
----------
23851
12178
23994
23955
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
602 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> delete from test where object_name='TEST';
4 rows deleted.
SQL> select /*+result_cache*/ object_id from test where object_name='TEST';
no rows selected
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 158 | 42 (3)| 00:00:01 |
| 1 | RESULT CACHE | b4qhu6vht5axvdhuc2g0s506rt | | | | |
|* 2 | TABLE ACCESS FULL| TEST | 2 | 158 | 42 (3)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME"='TEST')
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(HAOZHU_USER.TEST); parameters=(nls); name="select /*+result_cache*/ object_id from test where object_name='TEST'"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
177 consistent gets
0 physical reads
0 redo size
333 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
执行无数次这个语句，都会发现plan里使用了result cache，但是每次执行的LIO都是177.
于是我们会怀疑，是否每次执行这条语句时，Oracle的recreate了一次result cache呢？
答案是 No。
有一种办法可以确认：
通过查询V$RESULT_CACHE_STATISTICS
我们可以发现，每次执行之后，“Create Count Success”并没有变，说明没有重建新的result cache。
“Find Count”也没有变，说明并没有使用老的result cache。
“Create Count Failure”也没有变，说明甚至并没有去尝试重建result cache。
其实，V$RESULT_CACHE_STATISTICS里的每一个值都没有变，
所以，虽然执行计划显示的是使用了result cache，但是实际上，他仅仅按照常规的执行计划执行了一遍。
其实，这个骗人的执行计划，在Oracle 官档performance tunning里提到过原因：
A query result based on a read-consistent snapshot of data that is older than the latest
committed version of the data will not be cached. If any of the tables used to build a
cached result has been modified in an ongoing transaction in the current session then
the result is never cached.
3. 同一条语句的cache id是相同的。
这个特点是指当同一条语句经过若干次invalidation之后，每次产生的cache id都是相同的。
最直观的表现就是V$RESULT_CACHE_OBJECTS里会有相同cache_id的记录，但其中有的status=Published，
而有的status=Invalid。
接着上面的例子，当我commit了这个delete之后，
再run这个select语句重建一个result cache。
SQL> select CACHE_ID,status from V$RESULT_CACHE_OBJECTS where CACHE_ID='b4qhu6vht5axvdhuc2g0s506rt';
CACHE_ID STATUS
-------------------------------------------------- ---------
b4qhu6vht5axvdhuc2g0s506rt Published
b4qhu6vht5axvdhuc2g0s506rt Invalid
4. result cache跟执行计划是有关的。
曾有人提出说result cache在一般情况下跟执行计划是无关的，
只有当返回的结果对于两种执行计划不一样时才会有不同的result cache。
但经过我的实验，我认为：
result cache跟执行计划是有关的，即使当两种执行计划返回的结果是一样的，
也有可能生成两种result cache。
这取决于Oracle内部是如何认为这两种执行计划是否会返回同样的结果。
这里所谓的“同样的结果”也包括同样的顺序。
这里我举出三种例子来说明执行计划与result cache的关系：
A：语句相同，但执行计划不同，返回结果相同 => 相同的result cache
SQL> alter table test add primary key(object_id);
Table altered.
SQL> select /*+result_cache full(t)*/ count(*) from test t;
COUNT(*)
----------
13314
Execution Plan
----------------------------------------------------------
Plan hash value: 3467505462
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 (3)| 00:00:01 |
| 1 | RESULT CACHE | 9yc82u1dvm44n6zg2z3673cv41 | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| TEST | 12126 | 42 (3)| 00:00:01 |
------------------------------------------------------------------------------------------
SQL> select /*+result_cache index(t)*/ count(*) from test t;
COUNT(*)
----------
13314
Execution Plan
----------------------------------------------------------
Plan hash value: 1580189600
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 (3)| 00:00:01 |
| 1 | RESULT CACHE | 9yc82u1dvm44n6zg2z3673cv41 | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | INDEX FULL SCAN| SYS_C003349 | 12126 | 34 (3)| 00:00:01 |
----------------------------------------------------------------------------------------
B：语句相同，但执行计划不同，返回结果不同 => 不同的result cache
SQL> select /*+result_cache full(test)*/ object_id from test where object_id<10;
OBJECT_ID
----------
5
8
2
6
9
3
7
4
Execution Plan
----------------------------------------------------------
Plan hash value: 217508114
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 104 | 42 (3)| 00:00:01 |
| 1 | RESULT CACHE | fczpjw6rxzd3v0xusgrya270xg | | | | |
|* 2 | TABLE ACCESS FULL| TEST | 8 | 104 | 42 (3)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL> select /*+result_cache index(test)*/ object_id from test where object_id<10;
OBJECT_ID
----------
2
3
4
5
6
7
8
9
Execution Plan
----------------------------------------------------------
Plan hash value: 870918645
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 104 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | gjm87a5p9vdvfgbwvmd5pwfbzg | | | | |
|* 2 | INDEX RANGE SCAN| SYS_C003349 | 8 | 104 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
SQL> l
1* select ID,TYPE,STATUS,HASH,NAME,PIN_COUNT,SCAN_COUNT,ROW_COUNT,LRU_NUMBER,INVALIDATIONS,CACHE_ID from v$result_cache_objects
SQL> /
ID TYPE STATUS HASH NAME
---------- ---------- --------- ---------- --------------------------------------------------------------------------------------------------------------------------------
PIN_COUNT SCAN_COUNT ROW_COUNT LRU_NUMBER INVALIDATIONS CACHE_ID
---------- ---------- ---------- ---------- ------------- ---------------------------------------------------------------------------------------------
0 Dependency Published 1011194414 HAOZHU_USER.TEST
0 0 0 0 0 HAOZHU_USER.TEST
2 Result Published 1507624737 select /*+result_cache index(test)*/ object_id from test where object_id<:> 0 0 8 1 0 d5srq9r200za9ftxqbv27atzn5
1 Result Published 4274661509 select /*+result_cache full(test)*/ object_id from test where object_id<:> 0 0 8 0 0 8kjsht5gg0kd181k5qhj72ujb7
这里通过index range scan的执行计划返回数据的顺序跟走FTS的明显不一致，
于是这里Oracle认为这两种语句不能共享同一个result cache.
这是很好理解的，但是并不能说：如果语句相同，执行计划不同，但返回的结果相同时， 就一定能得到相同的result cache。
这里我的第三个例子就是来推翻这个很多人认为是对的观点。
C: 语句相同，但执行计划不同，返回结果相同 => 不同的result cache
SQL> select /*+result_cache full(test)*/ object_name from test where object_id<3;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
C_OBJ#
Execution Plan
----------------------------------------------------------
Plan hash value: 217508114
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 42 (3)| 00:00:01 |
| 1 | RESULT CACHE | au7zwunrs3km5bsr9bv2vt5yy7 | | | | |
|* 2 | TABLE ACCESS FULL| TEST | 1 | 79 | 42 (3)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL> select /*+result_cache index(test)*/ object_name from test where object_id<3;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
C_OBJ#
Execution Plan
----------------------------------------------------------
Plan hash value: 3369156586
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | 371ysw90pqb54fbz5sfpjtakhk | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 79 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C003349 | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
ID TYPE STATUS HASH NAME
---------- ---------- --------- ---------- --------------------------------------------------------------------------------------------------------------------------------
PIN_COUNT SCAN_COUNT ROW_COUNT LRU_NUMBER INVALIDATIONS CACHE_ID
---------- ---------- ---------- ---------- ------------- ---------------------------------------------------------------------------------------------
0 Dependency Published 1980725647 HAOZHU_USER.TEST
0 0 0 0 0 HAOZHU_USER.TEST
2 Result Published 2786844960 select /*+result_cache index(test)*/ object_name from test where object_id<3
0 0 1 1 0 371ysw90pqb54fbz5sfpjtakhk
1 Result Published 1211243460 select /*+result_cache full(test)*/ object_name from test where object_id<3
0 0 1 0 0 au7zwunrs3km5bsr9bv2vt5yy7
很明显，虽然这两个语句采用的执行计划不同，但他们却能得到相同的结果，
而result cache却不相同。
很容易猜想，这是因为Oracle并没有人那么聪明，他只是按照一定的规则办事，
当他认为同一个语句走不同执行计划时，
如果他能百分之百确定能返回同样的结果，例如前面例子中的count(*)，那他们能共享同一个result cache；
如果他认为走这两种执行计划是有可能得到不一样的结果时，例如不一样的返回顺序，那他们就会产生不同的result cache，
这里也就包含了有可能结果一样，也有可能结果不一样的情况。
5.inline view 可以使用result cache
SQL> select /*+result_cache*/ count(distinct object_name) c from test;
C
----------
10570
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 42 (3)| 00:00:01 |
| 1 | RESULT CACHE | 44jtcbfzbbuxa6a2gs4a9k3rtt | | | | |
| 2 | SORT GROUP BY | | 1 | 66 | | |
| 3 | TABLE ACCESS FULL| TEST | 12126 | 781K| 42 (3)| 00:00:01 |
--------------------------------------------------------------------------------------------------
SQL> select /*+result_cache*/ count(distinct object_id) c from test;
C
----------
13314
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 8 (0)| 00:00:01 |
| 1 | RESULT CACHE | b6bnmc2aq4ssm8khz1x8n3ha5x | | | | |
| 2 | SORT GROUP BY | | 1 | 13 | | |
| 3 | INDEX FAST FULL SCAN| SYS_C003349 | 12126 | 153K| 8 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
SQL> select a.c-b.c d
2 from
3 (select /*+result_cache*/ count(distinct object_id) c from test) a,
(select /*+result_cache*/ count(distinct object_name) c from test) b; 4
D
----------
2744
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 50 (2)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 50 (2)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 8 (0)| 00:00:01 |
| 3 | RESULT CACHE | b6bnmc2aq4ssm8khz1x8n3ha5x | | | | |
| 4 | SORT GROUP BY | | 1 | 13 | | |
| 5 | INDEX FAST FULL SCAN| SYS_C003349 | 12126 | 153K| 8 (0)| 00:00:01 |
| 6 | VIEW | | 1 | 13 | 42 (3)| 00:00:01 |
| 7 | RESULT CACHE | 44jtcbfzbbuxa6a2gs4a9k3rtt | | | | |
| 8 | SORT GROUP BY | | 1 | 66 | | |
| 9 | TABLE ACCESS FULL | TEST | 12126 | 781K| 42 (3)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
6. 绑定变量的不同会产生拥有相同cache_id的result cache，但他们返回不同结果。
在我第3点中，我说“同一条语句的cache id是相同的”。
但是其中有个需要特别指出来的一个观点是：
拥有相同cache_id的result cache不一定是同一个result cache。
SQL> var A number;
SQL> exec :A:=10;
SQL> select /*+result_cache*/ count(*) from test t where object_id<:a>
COUNT(*)
----------
8
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | bz5c1pap87un3947bc9s81pmrz | | | | |
| 2 | SORT AGGREGATE | | 1 | 13 | | |
|* 3 | INDEX RANGE SCAN| SYS_C003349 | 606 | 7878 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL> exec :A:=20;
SQL> select /*+result_cache*/ count(*) from test t where object_id<:a>
COUNT(*)
----------
18
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | bz5c1pap87un3947bc9s81pmrz | | | | |
| 2 | SORT AGGREGATE | | 1 | 13 | | |
|* 3 | INDEX RANGE SCAN| SYS_C003349 | 606 | 7878 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
SQL> /
ID TYPE STATUS HASH NAME
---------- ---------- --------- ---------- --------------------------------------------------------------------------------------------------------------------------------
PIN_COUNT SCAN_COUNT ROW_COUNT LRU_NUMBER INVALIDATIONS CACHE_ID
---------- ---------- ---------- ---------- ------------- ---------------------------------------------------------------------------------------------
0 Dependency Published 1077501375 HAOZHU_USER.TEST
0 0 0 0 0 HAOZHU_USER.TEST
2 Result Published 4131368813 select /*+result_cache*/ count(*) from test t where object_id<:a> 0 0 1 1 0 bz5c1pap87un3947bc9s81pmrz
1 Result Published 3169441897 select /*+result_cache*/ count(*) from test t where object_id<:a> 0 0 1 0 0 bz5c1pap87un3947bc9s81pmrz
在v$result_cache_objects中，我们很清楚的看到有两条相同name相同cache_id的相同status=Published的记录。
这种情况在使用了绑定变量的环境中，应该非常常见。
这种情况，是不是也该归入我的第2点中呢？
《2.骗人的执行计划告诉你采用了result cache（实际却没有）》
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15415488/viewspace-615398/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/15415488/viewspace-615398/
1830
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
