PL/SQL Function Result Cache

tom_fu

http://www.oracle-base.com/articles/11g/cross-session-plsql-function-result-cache-11gr1.php
PL/SQL Function Result Cache 我在11.2.0.3测试更新数据后缓存为何没有变化，函数定义中加了RESULT_CACHE RELIES_ON (res_cache_test_tab)的

SQL> SET SERVEROUTPUT ON
SQL> EXEC run_test;
First Loop: 1013 hsecs
Second Loop: 0 hsecs
SQL> pause

SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> SET SERVEROUTPUT OFF
SQL> pause
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> SET SERVEROUTPUT OFF
SQL> pause

SQL> UPDATE res_cache_test_tab
  2  SET    value = value * 10;
SQL> COMMIT;
SQL> pause

SQL> SET SERVEROUTPUT ON
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

26856649

1. SQL> select * from v$version where rownum <=1;
   
2. 
   
3. BANNER
   
4. --------------------------------------------------------------------------------
   
5. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
   
6. 
   
7. SQL>
   
8. CREATE OR REPLACE FUNCTION get_value (p_in IN NUMBER)
   
9.   RETURN NUMBER
   
10.   RESULT_CACHE
    
11. AS
    
12.   l_value res_cache_test_tab.value%TYPE;
    
13. BEGIN
    
14.   SELECT value
    
15.   INTO   l_value
    
16.   FROM   res_cache_test_tab
    
17.   WHERE  id = p_in;
    
18.   
    
19.   -- Pause for 1 second.
    
20.   DBMS_LOCK.sleep(1);
    
21.   
    
22.   RETURN l_value;
    
23. END get_value;
    
24. 17  /
    
25. 
    
26. 函数已创建。
    
27. 
    
28. UPDATE res_cache_test_tab
    
29. SET    value = value * 10;
    
30. 
    
31. 已更新10行。
    
32. 
    
33. COMMIT;
    
34. 
    
35. 
    
36. 提交完成。
    
37. 
    
38. SQL> SQL> EXEC run_test;
    
39. First Loop: 1001 hsecs
    
40. Second Loop: 0 hsecs
    
41. 
    
42. PL/SQL 过程已成功完成。
    
43. 
    
44. UPDATE res_cache_test_tab
    
45. SET    value = value * 10;
    
46. 
    
47. 已更新10行。
    
48. 
    
49. COMMIT;
    
50. 
    
51. 
    
52. 提交完成。
    
53. 
    
54. SQL> SQL> EXEC run_test;
    
55. First Loop: 1002 hsecs
    
56. Second Loop: 0 hsecs
    
57. 
    
58. PL/SQL 过程已成功完成。
    
59. 
    
60. SQL> EXEC run_test;
    
61. First Loop: 0 hsecs
    
62. Second Loop: 0 hsecs
    
63. 
    
64. PL/SQL 过程已成功完成。
    
65. 
    
66. CREATE OR REPLACE  FUNCTION get_value (p_in IN NUMBER)
    
67.   RETURN NUMBER
    
68.   RESULT_CACHE RELIES_ON (res_cache_test_tab)
    
69. AS
    
70.   l_value res_cache_test_tab.value%TYPE;
    
71. BEGIN
    
72.   SELECT value
    
73.   INTO   l_value
    
74.   FROM   res_cache_test_tab
    
75.   WHERE  id = p_in;
    
76.   
    
77.   -- Pause for 1 second.
    
78.   DBMS_LOCK.sleep(1);
    
79.   
    
80.   RETURN l_value;
    
81. END get_value;
    
82. 17  /
    
83. 
    
84. 函数已创建。
    
85. 
    
86. SQL> EXEC run_test;
    
87. First Loop: 1001 hsecs
    
88. Second Loop: 0 hsecs
    
89. 
    
90. PL/SQL 过程已成功完成。
    
91. 
    
92. SQL> EXEC run_test;
    
93. First Loop: 0 hsecs
    
94. Second Loop: 0 hsecs
    
95. 
    
96. PL/SQL 过程已成功完成。
    
97. 
    
98. UPDATE res_cache_test_tab
    
99. SET    value = value * 10;
    
100. 
     
101. 已更新10行。
     
102. 
     
103. COMMIT;
     
104. 
     
105. 提交完成。
     
106. 
     
107. SQL>
     
108. SQL> EXEC run_test;
     
109. First Loop: 1001 hsecs
     
110. Second Loop: 0 hsecs
     
111. 
     
112. PL/SQL 过程已成功完成。
     
113. 
     
114. SQL> EXEC run_test;
     
115. First Loop: 0 hsecs
     
116. Second Loop: 0 hsecs
     
117. 
     
118. PL/SQL 过程已成功完成。
     
119. 
     
120. SQL>

复制代码

tom_fu

我测试结果更新数据后还是用缓存
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> DROP TABLE res_cache_test_tab PURGE;
SQL>
SQL> exec dbms_result_cache.flush;
SQL> set SERVEROUTPUT ON
SQL> exec 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  = 6336K bytes (6336 blocks)
Maximum Result Size = 316K bytes (316 blocks)
[Memory]
Total Memory = 5352 bytes [0.001% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.001% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
SQL> set SERVEROUTPUT OFF
SQL> CREATE TABLE res_cache_test_tab (
  2    id       NUMBER,
  3    value NUMBER
  4  );
SQL>
SQL> BEGIN
  2    FOR i IN 1 .. 10 LOOP
  3      INSERT INTO res_cache_test_tab VALUES (i, i*10);
  4    END LOOP;
  5    COMMIT;
  6  END;
  7  /
SQL>
SQL> pause

SQL> CREATE OR REPLACE  FUNCTION get_value (p_in IN NUMBER)
  2    RETURN NUMBER
  3    RESULT_CACHE RELIES_ON (res_cache_test_tab)
  4  AS
  5    l_value res_cache_test_tab.value%TYPE;
  6  BEGIN
  7    SELECT value
  8    INTO      l_value
  9    FROM      res_cache_test_tab
10    WHERE  id = p_in;
11
12    -- Pause for 1 second.
13    DBMS_LOCK.sleep(1);
14
15    RETURN l_value;
16  END get_value;
17  /
SQL>
SQL> pause

SQL> CREATE OR REPLACE PROCEDURE run_test AS
  2    l_start NUMBER;
  3    l_loops NUMBER := 10;
  4    l_value res_cache_test_tab.value%TYPE;
  5  BEGIN
  6    l_start := DBMS_UTILITY.get_time;
  7
  8    FOR i IN 1 .. l_loops LOOP
  9      l_value := get_value(i);
10    END LOOP;
11
12    DBMS_OUTPUT.put_line('First Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
13
14    l_start := DBMS_UTILITY.get_time;
15
16    FOR i IN 1 .. l_loops LOOP
17      l_value := get_value(i);
18    END LOOP;
19
20    DBMS_OUTPUT.put_line('Second Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
21  END run_test;
22  /
SQL> pause

SQL>
SQL> SET SERVEROUTPUT ON
SQL> EXEC run_test;
First Loop: 1013 hsecs
Second Loop: 0 hsecs
SQL> pause

SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> SET SERVEROUTPUT OFF
SQL> pause

SQL> UPDATE res_cache_test_tab
  2  SET    value = value * 10;
SQL> COMMIT;
SQL> pause

SQL> SET SERVEROUTPUT ON
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> EXEC run_test;
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> SET SERVEROUTPUT OFF
SQL>

Maclean Liu(刘相兵

SQL> CREATE OR REPLACE  FUNCTION get_value (p_in IN NUMBER)
  2    RETURN NUMBER
  3    RESULT_CACHE RELIES_ON (res_cache_test_tab)
  4  AS
  5    l_value res_cache_test_tab.value%TYPE;
  6  BEGIN
  7    SELECT value
  8    INTO   l_value
  9    FROM   res_cache_test_tab
10    WHERE  id = p_in;
11   
12    -- Pause for 1 second.
13    DBMS_LOCK.sleep(1);
14   
15    RETURN l_value;
16  END get_value;
17  /

Function created.

SQL> EXEC run_test;
First Loop: 1001 hsecs
Second Loop: 0 hsecs

PL/SQL procedure successfully completed.

SQL> UPDATE res_cache_test_tab
  2  SET    value = value * 10;
COMMIT;

10 rows updated.

SQL>
Commit complete.

SQL>
SQL> EXEC run_test;
First Loop: 1001 hsecs
Second Loop: 0 hsecs

PL/SQL procedure successfully completed.

我的测试中没有遇到该问题


请你把get_value  和run_test 在你环境中的DDL 贴出来，以及运行的版本和 使用的USER

tom_fu

[oracle@11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 7 15:53:09 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

SQL> @/home/oracle/a.sql
SQL> SET ARRAYSIZE 2000
SQL>
SQL> DROP TABLE res_cache_test_tab PURGE;
DROP TABLE res_cache_test_tab PURGE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> exec dbms_result_cache.flush;
SQL> set SERVEROUTPUT ON
SQL> exec 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  = 2M bytes (2K blocks)
Maximum Result Size = 102K bytes (102 blocks)
[Memory]
Total Memory = 5352 bytes [0.002% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
SQL> set SERVEROUTPUT OFF
SQL> CREATE TABLE res_cache_test_tab (
  2    id       NUMBER,
  3    value NUMBER
  4  );
SQL>
SQL> BEGIN
  2    FOR i IN 1 .. 10 LOOP
  3      INSERT INTO res_cache_test_tab VALUES (i, i*10);
  4    END LOOP;
  5    COMMIT;
  6  END;
  7  /
SQL> select * from res_cache_test_tab;

        ID      VALUE
---------- ----------
         1         10
         2         20
         3         30
         4         40
         5         50
         6         60
         7         70
         8         80
         9         90
        10        100
SQL> pause

SQL> CREATE OR REPLACE  FUNCTION get_value (p_in IN NUMBER)
  2    RETURN NUMBER
  3    RESULT_CACHE RELIES_ON (res_cache_test_tab)
  4  AS
  5    l_value res_cache_test_tab.value%TYPE;
  6  BEGIN
  7    SELECT value
  8    INTO      l_value
  9    FROM      res_cache_test_tab
10    WHERE  id = p_in;
11  
12    -- Pause for 1 second.
13    DBMS_LOCK.sleep(1);
14  
15    RETURN l_value;
16  END get_value;
17  /
SQL>
SQL> pause

SQL> CREATE OR REPLACE PROCEDURE run_test AS
  2    l_start NUMBER;
  3    l_loops NUMBER := 10;
  4    l_value res_cache_test_tab.value%TYPE;
  5  BEGIN
  6    l_start := DBMS_UTILITY.get_time;
  7  
  8    FOR i IN 1 .. l_loops LOOP
  9      l_value := get_value(i);
10    END LOOP;
11    dbms_output.put_line(l_value );
12    DBMS_OUTPUT.put_line('First Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
13  
14    l_start := DBMS_UTILITY.get_time;
15  
16    FOR i IN 1 .. l_loops LOOP
17      l_value := get_value(i);
18    END LOOP;
19  
20    DBMS_OUTPUT.put_line('Second Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
21  END run_test;
22  /
SQL> pause

SQL>
SQL> SET SERVEROUTPUT ON
SQL> EXEC run_test;

100
First Loop: 1003 hsecs
Second Loop: 0 hsecs
SQL> pause
SQL> EXEC run_test;
100
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> SET SERVEROUTPUT OFF
SQL> pause

SQL> UPDATE res_cache_test_tab SET value = value * 10;
SQL> commit;
SQL> select * from res_cache_test_tab;

        ID      VALUE
---------- ----------
         1        100
         2        200
         3        300
         4        400
         5        500
         6        600
         7        700
         8        800
         9        900
        10       1000
SQL> pause

SQL> SET SERVEROUTPUT ON
SQL> EXEC run_test;
100
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> EXEC run_test;
100
First Loop: 0 hsecs
Second Loop: 0 hsecs
SQL> SET SERVEROUTPUT OFF
SQL>

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
result_cache_max_size                big integer 2M
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
函数定义如上输出所示
SQL> CREATE OR REPLACE PROCEDURE run_test AS
  2    l_start NUMBER;
  3    l_loops NUMBER := 10;
  4    l_value res_cache_test_tab.value%TYPE;
  5  BEGIN
  6    l_start := DBMS_UTILITY.get_time;
  7  
  8    FOR i IN 1 .. l_loops LOOP
  9      l_value := get_value(i);
10    END LOOP;
11    dbms_output.put_line(l_value );
12    DBMS_OUTPUT.put_line('First Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
13  
14    l_start := DBMS_UTILITY.get_time;
15  
16    FOR i IN 1 .. l_loops LOOP
17      l_value := get_value(i);
18    END LOOP;
19  
20    DBMS_OUTPUT.put_line('Second Loop: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
21  END run_test;

Maclean Liu(刘相兵

tom_fu 发表于 2013-7-13 20:31
[oracle@11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 7 15:53:09 2 ...

你不够仔细， 也喜欢忽视细节

仔细看我4楼的问题 和你的操作 你漏掉什么了？

Maclean Liu(刘相兵

http://www.oracle-base.com/artic ... ult-cache-11gr1.php 作者原帖

CONN test/test 这个操作你认为是没意义的？


简单来说 不要用 SYS或SYSTEM去测试这几个result cache的特性， 这个文档也有明确说

tom_fu

多谢！原来不能用sys和system

tom_fu

并不是说sys和system都不行，这个函数缓存我觉得就是有问题

C:\Users\Tom>sqlplus system/oracle@l11g

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 14 09:51:36 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

SQL> show user
USER is "SYSTEM"
SQL> @c:\a.sql
SQL> SET ARRAYSIZE 2000
SQL>
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
result_cache_max_size                big integer 2M
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0
SQL> pause

SQL> select dbms_result_cache.status() from dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------------------------------------------------------------
ENABLED
SQL> exec dbms_result_cache.flush;
SQL> pause
SQL> set autot on exp stat
SQL> select     /*+ RESULT_CACHE */ department_id, AVG(salary)
  2  from hr.employees
  3  group by department_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 1akffwbzpzduf01pch4018m270 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select  /*+ RESULT_CACHE */ department_id, AVG(salary)
from hr.employees
group by department_id"



Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          6  physical reads
          0  redo size
        633  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL> set autot off
SQL> pause

SQL> SET SERVEROUTPUT ON
SQL> exec 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  = 2M bytes (2K blocks)
Maximum Result Size = 102K bytes (102 blocks)
[Memory]
Total Memory = 169328 bytes [0.075% of the Shared Pool]
... Fixed Memory = 5352 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 163976 bytes [0.072% of the Shared Pool]
....... Overhead = 131208 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)
SQL> SET SERVEROUTPUT OFF
SQL> pause

SQL> set autot on exp stat
SQL> select     /*+ RESULT_CACHE */ department_id, AVG(salary)
  2  from hr.employees
  3  group by department_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 1akffwbzpzduf01pch4018m270 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select  /*+ RESULT_CACHE */ department_id, AVG(salary)
from hr.employees
group by department_id"



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        633  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL> set autot off
SQL> pause

SQL> update hr.employees set salary=salary+10;
SQL> commit;
SQL> pause

SQL> set autot on exp stat
SQL> select     /*+ RESULT_CACHE */ department_id, AVG(salary)
  2  from hr.employees
  3  group by department_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 1akffwbzpzduf01pch4018m270 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select  /*+ RESULT_CACHE */ department_id, AVG(salary)
from hr.employees
group by department_id"
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        639  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed
SQL> set autot off
SQL> set autot on exp stat
SQL> select     /*+ RESULT_CACHE */ department_id, AVG(salary)
  2  from hr.employees
  3  group by department_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   1 |  RESULT CACHE       | 1akffwbzpzduf01pch4018m270 |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    11 |    77 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES                  |   107 |   749 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(HR.EMPLOYEES); name="select  /*+ RESULT_CACHE */ department_id, AVG(salary)
from hr.employees
group by department_id"

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        639  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL> set autot off
SQL> pause

Maclean Liu(刘相兵

Results or data sets from objects/tables in the SYS or SYSTEM schema cannot be cached using this feature. Data from CURRVAL and NEXTVAL pseudo columns from database sequences cannot be cached using this feature.


文档的解释 是SYS or SYSTEM不能使用该特性， 实际测试是可以用到，但Oracle Corp不保证没有问题

http://www.oracle.com/technetwor ... che-rac-284280.html