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提到了這個引數，顯得更加可信一點：

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引數的作用：

“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（實際卻沒有）》

11G result cache新特性的一些發現和個人見解

相關文章