oracle 11g result 整理詳細版

11g推出result cache特性
可以在service 端和client 端 cache 結果集

service 端由兩部分組成
1.sql query result cache（儲存SQl查詢的結果集）
2.pl/sql funcation result cache（存pl/sql 函式結果集）

儲存在shared_pool中
SQL> select pool,name ,bytes/1024/1024 from v$sgastat where upper(name) like '%RESULT%';

POOL         NAME                       BYTES/1024/1024
------------ -------------------------- ---------------
shared pool Result Cache: State Objs        .002719879
shared pool Result Cache: Memory Mgr        .000118256
shared pool Result Cache: Bloom Fltr        .001953125
shared pool Result Cache: Cache Mgr         .000102997

SQL> select name ,sum(bytes) from v$sgastat where name like 'Result Cache%' group by rollup(name);

NAME                       SUM(BYTES)
-------------------------- ----------
Result Cache: Bloom Fltr         2048
Result Cache: Cache Mgr           108
Result Cache: Memory Mgr          124
Result Cache: State Objs         2852
                                 5132

SQL> show user
USER is "SYS"
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> show parameter result

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result              integer     5 ~~~控制單個cache結果可以佔總server result cache大小%
result_cache_max_size                big integer 1248K ~~~~cache的大小，若為0表示禁用
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
SQL>

*一致性當dml,ddl修改result cache 依賴的表，result cache將失效,即使沒有任何實際變化,也可能會失效（比如一個select for update 後commit）
*result cache通過比較sql_text確認是否有可用的result cache可使用，（空格，大小寫可以忽略）
*要是有bind值，bind的值必須完全一致（這是必須的，不同的bind值非常可能會有不同的result cache產生）
*當使用pl/sql函式未指定relies_on字句，使用client resutl cache,result_cache_remote_expiration>0 這些情況結果集無法保證一致

與server result cache有關的引數

result_cache_mode：manual 查詢時候要手動加hint result_cache 通知優化器才可以
auto:優化器會判斷是否將使用已經存在的result cache（用hint產生的result cache）（依據查詢執行的頻率、生成結果的成本以及針對底層資料庫物件更改的頻率。）,另外只有使用hint的時候產生result cache，oracle才會使用
force:儘可能的cache 查詢結果，將result_cache加到select語句中（no_result_cache 可拒絕cache）,既所有不包含no_result_cache hint的select都會使用 result cache

result_cache_max_size ：單位位元組，指定用來作為result cache的shared pool大小，0表示禁用result cache，defalut是oracle根據shared pool大小自動推算出來的(system級修改)
result_cache_max_result:~控制單個cache結果可以佔總server result cache大小單位(%)預設5,取值1-100 (system級修改)
result_cache_remote_expiration :基於遠端物件的結果集的臨時有效期（單位分鐘），遠端物件（比如通過dblink訪問）的結果集無法由於遠端物件的變更而變成失效，所以這個引數定義多久後失效，預設為0表示遠端result cache 被禁止

與client result cache有關的引數
*cient result cache最大缺點無法保證一致性（輪詢的關係）
引數在server上啟用
client_result_cache_lag :指定2次資料庫呼叫之見最大間隔時間（單位毫秒），oracle使用輪詢機制確認clent result cache是否失效，既定期執行資料庫呼叫來檢查資料庫引擎
檢視是否有result cache被執行失效操作，這個引數就是輪詢之間的間隔（也可以認為就是無效的資料在clinet cache中保留的最長時間），oracle還會在每次其它原因執行資料庫呼叫時候
同時檢查result cache的有效性,降低輪詢cost(靜參)
client_result_cache_size:每個cilent process可以用來作為result cache分配記憶體的最大大小（單位位元組），預設0 表示禁用result cache cilent(靜參)

cilent的sqlnet.ora中也可以設定引數
oci_result_cache_max_size:設定後會覆蓋server 端設定的初始化引數cilent_resutl_cache_size（前提是client_result_cache_size>0，表示啟用了result cache client）
oci_result_cache_max_rset_size:指定單個結果集可以最多使用的記憶體大小（單位位元組）
oci_result_cache_max_rset_rows:指定單個result cache可以儲存的最大返回記錄（行數）

server result測試
SSQL> create user xh identified by a831115
2 ;

User created.

SQL> grant dba to xh
2 ;

Grant succeeded.

SQL> conn xh/a831115
Connected.
SQL> create table xht as select * from all_objects;

Table created.

SQL> alter system flush buffer_cache;

System altered.
SQL> set autotrace trace
SQL> select count(*) from xht;

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   278   (1)| 00:00:04 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| XHT | 63554 |   278   (1)| 00:00:04 |
-------------------------------------------------------------------

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

Statistics
----------------------------------------------------------
          4 recursive calls
          0 db block gets
       1062 consistent gets
        994 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        420 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 xht;

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   278   (1)| 00:00:04 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| XHT | 63554 |   278   (1)| 00:00:04 |
-------------------------------------------------------------------

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

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        998 consistent gets~~~~~~~~~~~~~~
          0 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        420 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 /*+result_cache*/ count(*) from xht;

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

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

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

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

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xht"

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

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        998 consistent gets
          0 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        420 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 /*+result_cache*/ count(*) from xht;

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

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

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

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

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xht"

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
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> execute dbms_result_cache.flush~~~~~~~~~~~~~~~~~~~~~~清除result cache(存sga shared pool中)

PL/SQL procedure successfully completed.

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

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

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

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

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

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xht"

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

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        998 consistent gets
          0 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        420 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 dbms_result_cache.status from dual;

STATUS
--------------------------------------------------------------------------------
ENABLED

SQL> set serveroutput on
SQL> execute 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 = 1248K bytes (1248 blocks)
Maximum Result Size = 62K bytes (62 blocks)
[Memory]
Total Memory = 98632 bytes [0.065% of the Shared Pool]
... Fixed Memory = 5132 bytes [0.003% of the Shared Pool]
... Dynamic Memory = 93500 bytes [0.062% of the Shared Pool]
....... verhead = 60732 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.

SQL> select * from v$result_cache_memory where free='NO';

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

SQL> col name for a40
SQL> select * from v$result_cache_statistics;

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

SQL> select id,type,name,block_count,row_count from v$result_cache_objects;

        ID TYPE       NAME                                     BLOCK_COUNT
---------- ---------- ---------------------------------------- -----------
ROW_COUNT
----------
         0 Dependency XH.XHT                                             1
         0

         1 Result     select /*+result_cache*/ count(*) from x           1
                      ht
         1

下面看下auto情況

SQL> alter system set result_cache_mode=AUTO ~~動態
2 ;

System altered.

SQL> execute dbms_result_cache.flush

PL/SQL procedure successfully completed.

SQL> alter system flush buffer_cache;

System altered.

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

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

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

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

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

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xht"

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

Statistics
----------------------------------------------------------
          4 recursive calls
          0 db block gets
       1062 consistent gets
        994 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        420 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 xht; 不加 HINT

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

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

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

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

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; type=AUTO; dependencies=(XH.XHT); attributes=(single-row)
; name="select count(*) from xht"

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

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          0 consistent gets~~~~~~~~~~~~~~~~~~~~~(要多查幾次才行，因為auto自動根據查詢頻率等判斷是否有必要使用生成的result cache)
          0 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> create table xht2 as select * from all_objects;

Table created.

SQL> select count(*) from xht2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483

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

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

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

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; type=AUTO; dependencies=(XH.XHT2); attributes=(single-row
); name="select count(*) from xht2"

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

Statistics
----------------------------------------------------------
         28 recursive calls
          0 db block gets
       1068 consistent gets
        993 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        420 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 xht2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483

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

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

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

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; type=AUTO; dependencies=(XH.XHT2); attributes=(single-row
); name="select count(*) from xht2"

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

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        998 consistent gets~~~~~~~~~~~~~~~~~~~~~~~~~~~
          0 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        420 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 xht2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483

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

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

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

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; type=AUTO; dependencies=(XH.XHT2); attributes=(single-row
); name="select count(*) from xht2"

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

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        998 consistent gets~~~~~~~~~~~~~~~~~~~~但沒有使用
          0 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>

QL> select /*+result_cache*/count(*) from xht2; ~~~必須建立這個result cache時用HINT 才可以

Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483

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

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

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

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; dependencies=(XH.XHT2); attributes=(single-row); name="se
lect /*+result_cache*/count(*) from xht2"

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

Statistics
----------------------------------------------------------
          4 recursive calls
          0 db block gets
       1066 consistent gets
          0 physical reads
          0 redo size
        420 bytes sent via SQL*Net to client
        420 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 xht2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1880265483

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

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

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

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT2 | 67364 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; type=AUTO; dependencies=(XH.XHT2); attributes=(single-row
); name="select count(*) from xht2"

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
        420 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

關於sys object

SQL> set autotrace trace exp
SQL> select /*+result_cache*/ count(*) from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |     7   (0)| 00:00:01 |
-------------------------------------------------------------------
sys object沒建立result cache

SQL> select /*+result_cache*/ count(*) from xh.xht;

Execution Plan
----------------------------------------------------------
Plan hash value: 1787396970

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

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

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

| 2 | SORT AGGREGATE | | 1 | |
|

| 3 | TABLE ACCESS FULL| XHT | 63554 | 278 (1)|
00:00:04 |

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

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

1 - column-count=1; dependencies=(XH.XHT); attributes=(single-row); name="sel
ect /*+result_cache*/ count(*) from xh.xht"

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

可以看到並不支援sys object ，這也很明顯所有基表，資料字典都屬於sys object 如果對他們可以result cache 那麼將會用非常大的記憶體

pl/sql函式的result cache，指令碼來自troubleshooting oracle performance

SQL> show user
USER is "XH"
SQL> CREATE TABLE t
2 AS
3 SELECT rownum AS id, rpad('*',100,'*') AS pad
4 FROM dual
5 CONNECT BY level <= 10000;

Table created.

CREATE OR REPLACE FUNCTION f(p IN NUMBER)
RETURN NUMBER
RESULT_CACHE RELIES_ON (t)
IS
l_ret NUMBER;
BEGIN
SELECT count(*) INTO l_ret
FROM t
WHERE id = p;
RETURN l_ret;
END;
/

*RELIES_ON 比較關鍵，如果沒有表變化後，result cache不會失效，這將會造成不一致

SQL> SQL> execute dbms_result_cache.bypass(bypass_mode => TRUE, session => TRUE)

PL/SQL procedure successfully completed.

SQL> SET TIMING ON
SQL> SELECT count(f(1)) FROM t;
SET TIMING OFF

COUNT(F(1))
-----------
10000

Elapsed: 00:00:13.57 ~~~執行用了13秒
SQL> SQL> execute dbms_result_cache.bypass(bypass_mode => FALSE, session => TRUE)

PL/SQL procedure successfully completed.

SQL> SET TIMING ON
SQL> SELECT count(f(1)) FROM t;
SET TIMING OFF

COUNT(F(1))
-----------
10000

Elapsed: 00:00:00.37 ~~~~使用pl/sql 函式 result cache後用了0.37S
SQL> SQL>

SQL> INSERT INTO t VALUES (-1, 'invalidate...');
COMMIT;

1 row created.

SQL>
Commit complete

由於有 RELIES_ON，所以pl/sql函式依賴的表失效了
SQL> select * from v$result_cache_objects where status='Invalid';

ID TYPE STATUS BUCKET_NO HASH NAME NAMES CREATION_ CREATOR_UID DEPEND_COUNT BLOCK_COUNT SCN COLUMN_COUNT PIN_COUNT SCAN_COUNT ROW_COUNT ROW_SIZE_MAX ROW_SIZE_MIN ROW_SIZE_AVG BUILD_TIME LRU_NUMBER OBJECT_NO INVALIDATIONS SPACE_OVERHEAD SPACE_UNUSED CACHE_ID CACHE_KEY
---------- ---------- --------- ---------- ---------- ------------------------------------------------------------------------------------------------------------------
-------------- ----- --------- ----------- ------------ ----------- ---------- ------------ ---------- ---------- ---------- ------------ ------------ ------------ ---------- ---------- ---------- ------------- -------------- ------------ --------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------
2 Result Invalid 858 1404027738 "XH"."F"::8."F"#fac892c7867b54c6 #1

如果沒有relies_on那麼將將獲得失效的結果集合
SQL> CREATE OR REPLACE FUNCTION f(p IN NUMBER)
2    RETURN NUMBER
3    RESULT_CACHE
4 IS
5    l_ret NUMBER;
6 BEGIN
7    SELECT count(*) INTO l_ret
8    FROM t
9    WHERE id = p;
10    RETURN l_ret;
11 END;
12 /

Function created.

SQL> SELECT f(-1) FROM dual;

F(-1)
----------
2

SQL> SQL> INSERT INTO t VALUES (-1, 'invalidate...');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT f(-1) FROM dual;

F(-1)
----------
2 ~~~~返回了失效的結果集合

PL/SQL 函式限制
1.out,in out 函式不行
2.定義invoker's right函式不行
3.管道化表函式不行
4.匿名壞中用函式不行
5.in中型別lob,ref cursor,object和物件型別函式不行

管理包
SQL> desc dbms_result_cache
PROCEDURE BYPASS~~~~~~~~~~~~~~~~臨時禁用result cache
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
BYPASS_MODE                    BOOLEAN                 IN
SESSION                        BOOLEAN                 IN     DEFAULT
FUNCTION FLUSH RETURNS BOOLEAN ~~~~~~~~~清除所有 result cache
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
RETAINMEM                      BOOLEAN                 IN     DEFAULT
RETAINSTA                      BOOLEAN                 IN     DEFAULT
PROCEDURE FLUSH ~~~~~~~~~清除所有 result cache
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
RETAINMEM                      BOOLEAN                 IN     DEFAULT
RETAINSTA                      BOOLEAN                 IN     DEFAULT
FUNCTION INVALIDATE RETURNS NUMBER 讓給定資料object的所有result cache失效
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER                          VARCHAR2                IN
NAME                           VARCHAR2                IN
PROCEDURE INVALIDATE 讓給定資料object的所有result cache失效
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNER                          VARCHAR2                IN
NAME                           VARCHAR2                IN
FUNCTION INVALIDATE RETURNS NUMBER 讓給定資料object的所有result cache失效
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID                      BINARY_INTEGER          IN
PROCEDURE INVALIDATE 讓給定資料object的所有result cache失效
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_ID                      BINARY_INTEGER          IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER 讓一個給定的快取條目失效
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
ID                             BINARY_INTEGER          IN
PROCEDURE INVALIDATE_OBJECT 讓一個給定的快取條目失效
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
ID                             BINARY_INTEGER          IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER 讓一個給定的快取條目失效
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
CACHE_ID                       VARCHAR2                IN
PROCEDURE INVALIDATE_OBJECT 讓一個給定的快取條目失效
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
CACHE_ID                       VARCHAR2                IN
PROCEDURE MEMORY_REPORT 記憶體使用報告
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
DETAILED                       BOOLEAN                 IN     DEFAULT
FUNCTION STATUS RETURNS VARCHAR2 顯示result 狀態

相關view
v$result_cache_objects
v$result_cache_memory
v$result_cache_statistics
v$result_cache_dependency

關於失效

create table t1 (a int);

declare
begin
for i in 1..10000 loop
insert into t1 values(i);
end loop;
commit;
end;

SQL> execute dbms_stats.gather_table_stats('XH','T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11

SQL> set autotrace trace
SQL> select /*+result_cache*/ * from t1;

10000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

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

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

| 0 | SELECT STATEMENT | | 10000 | 30000 | 7
(0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T1 | 10000 | 30000 | 7
(0)| 00:00:01 |

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

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

1 - column-count=1; dependencies=(XH.T1); name="select /*+result_cache*/ * fr
om t1"

Statistics
----------------------------------------------------------
        229 recursive calls
          0 db block gets
        735 consistent gets
         27 physical reads
       1268 redo size
     136879 bytes sent via SQL*Net to client
       7746 bytes received via SQL*Net from client
        668 SQL*Net roundtrips to/from client
          5 sorts (memory)
          0 sorts (disk)
      10000 rows processed

SQL>

SQL> select status,row_count,scan_count from v$result_cache_objects where cache_id='6d9qvk74vzy5p77k3gwt4j4gcy';

STATUS ROW_COUNT SCAN_COUNT
--------- ---------- ----------
Published 10000 0

SQL> select /*+result_cache*/ * from t1;