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;