Result Cache

result cache是用来存储查询sql得到的结果，给之后的重复查询来使用。通过缓存这些结果，oracle能避免那些重复的实际消耗，并且节省了大量的数据库操作，比如排序、合并、物理io和表关联等。result cache是内存里的一块单独区域，要么是SGA里或者客户端应用程序内存里。存放在里面的缓存结果在不同的sql语句或者不同的会话之间是可以共享的，除非缓存结果本身失效了。结果缓存对于应用程序来说是完全透明的，它不需要人为介入，而是直接由oracle内部来进行自动管理和维护。

对于不同的应用系统来说，result cache所带来的收益是不一样的，对于OLAP这种大数据量的分析统计系统来说效果最明显。缓存的最佳选择是查询了大量的数据，但最后却只返回少量的数据，例如数据仓库。除此之外，对于只读或者数据较少变化的SQL来说效果也会更好，因为当数据进行了变化后，相对于的缓存也会失效，而需要重新维护生成新的。

数据库相关配置

oracle与result cache相关的一些初始化参数如下

1
2
3
4
5
6
7
8
9
10
select name, value, isdefault
from v$parameter
where name like 'result_cache%';

NAME                                               VALUE           ISDEFAULT
-------------------------------------------------- --------------- ---------------------------
result_cache_mode                                  MANUAL          TRUE
result_cache_max_size                              42958848        TRUE
result_cache_max_result                            5               TRUE
result_cache_remote_expiration                     0               TRUE

  • result_cache_mode

    决定了哪些查询是可以存储在result cache里的，如果查询有资格放到缓存，则应用会去检查result cache里是否已经存在，如果存在则直接从里面获取数据，如果不存在数据库则会执行查询语句，并返回结果同时将结果放到result cache当中。

    • Manual

      只有带查询hint和表标注的查询结果才会进行缓存，这是默认值

    • Force

      所有的查询都会强制缓存到result cache，但是可以在sql中指定/*+ NO_RESULT_CACHE */来排除当前sql

  • result_cache_max_size

    设定result cache的大小，这部分内存是直接从shared pool中分配但是是单独维护，刷新shared pool并不会刷新result cache

  • result_cache_max_result

    指定对于单个结果来说所占用的内存大小不能超过总result cache大小的百分比，默认值是5，这个参数可以在system或者session级别进行修改

  • result_cache_remote_expiration

    指定那些依赖远端数据库对象的结果集超期时间（分钟），默认是0表示使用远端数据库对象的结果不会被缓存。如果指定的非0值，则对远端数据库对象进行的DML操作并不会使缓存结果失效。

清空结果缓存

1
exec DBMS_RESULT_CACHE.FLUSH;

手动result cache

默认缓存模式为手动表示数据库并不会自动缓存结果，除非使用RESULT_CACHE hint。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
create table t
as
select mod(level,10) id,level*2 amount from dual
connect by level <=50000;

set timing on
set autotrace traceonly

select /*+ result_cache */
id, sum(amount) amount
from t
group by id;

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 47235625

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    10 |    80 |    30   (7)| 00:00:01 |
|   1 |  RESULT CACHE       | 0jqwkxxdxwwby3nq2zva97pa1n |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    10 |    80 |    30   (7)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T                          | 50000 |   390K|    28   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(XB.T); name="select /*+ result_cache */
id, sum(amount) amount
from t
group by id"



Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         92  consistent gets
         84  physical reads
          0  redo size
        554  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

通过上面的这个结果得到了如下信息：

  • 首先在执行计划中看到了一个新的operation，ID=1的“RESULT CACHE”。这是执行计划在执行过程中的最后一步，它告诉我们oracle会缓存之前步骤执行后得到的结果。
  • 在Name那一列，operation为“RESULT CACHE”的那一行多了一个系统生成的标识值，这是一个内部key供系统来在result cache中查询和匹配sql语句,看起来是通过sql文本hash值得到的，因为刷新了cache以后这个key不会变。
  • 执行计划报告中多了一个新的“Result Cache Information”，包含了这个sql所依赖的对象，和生成这个结果的sql文本开头的一部分。

这里总共扫描了5W行数据，92个一致性读，84个物理读，最终获取了10行数据。

对这个已经缓存到内存的sql重新执行 ，比较下两者之间的结果有何不同。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |    10 |    80 |    30   (7)| 00:00:01 |
|   1 |  RESULT CACHE       | 0jqwkxxdxwwby3nq2zva97pa1n |       |       |            |          |
|   2 |   HASH GROUP BY     |                            |    10 |    80 |    30   (7)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T                          | 50000 |   390K|    28   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(XB.T); name="select /*+ result_cache */
id, sum(amount) amount
from t
group by id"



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

执行计划并没有什么区别，重点看最后的Statistics部分。可以看到在这些执行中oracle本身只做了非常少的工作，逻辑读和物理读都是0。oracle已经意识到这个查询是可以直接从result cache当中获取结果的，所以就直接很容易的返回了预先计算好的答案。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
col name for a40
col cache_id for a40
select name,type,cache_id,row_count 
from v$result_cache_objects
order by creation_timestamp;

NAME                                     TYPE                           CACHE_ID                                  ROW_COUNT
---------------------------------------- ------------------------------ ---------------------------------------- ----------
select /*+ result_cache */               Result                         0jqwkxxdxwwby3nq2zva97pa1n                       10
id, sum(amount) amount
from t
group by id

XB.T                                     Dependency                     XB.T                                              0

通过v$result_cache_objects视图可以看到有两个不同的类型，dependency和result，result类型清晰的表示了之前执行的sql。

同样也可以通过v$result_cache_statistics视图来查到与result cache相关的更加详细的统计信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> select * from v$result_cache_statistics;

        ID NAME                                     VALUE                    CON_ID
---------- ---------------------------------------- -------------------- ----------
         1 Block Size (Bytes)                       1024                          0
         2 Block Count Maximum                      41952                         0
         3 Block Count Current                      256                           0
         4 Result Size Maximum (Blocks)             2097                          0
    	 5 Create Count Success                     1                             0
         6 Create Count Failure                     0                             0
         7 Find Count                               1                             0
         8 Invalidation Count                       0                             0
         9 Delete Count Invalid                     0                             0
        10 Delete Count Valid                       0                             0
        11 Hash Chain Length                        0-1                           0
        12 Find Copy Count                          1                             0
        13 Latch (Share)                            0                             0

这个视图可以看到一些基本信息，缓存的条目和命中。目前block大小为1k，最多有41952个块也就是41952k，与show parameter看到的结果一致。当前使用了256k，单个结果最多能使用2097k，成功创建一个结果（Create Count Success），并且命中过一次缓存（Find Count）。

result cache依赖性

每个查询的结果都依赖一个或多个表，通过V$RESULT_CACHE_DEPENDENCY视图可以查到result cache当中每一个条目所依赖的对象。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT a.id, a.name, listagg(c.object_name) within group (order by 1) AS object_names
  FROM v$result_cache_objects a
  LEFT OUTER JOIN v$result_cache_dependency b
    ON (a.id = b.result_id)
  LEFT OUTER JOIN dba_objects c
    ON (b.object_no = c.object_id)
 WHERE a.type = 'Result'
 GROUP BY a.id, a.name;
 
         ID NAME                                                                   OBJECT_NAMES
---------- ---------------------------------------------------------------------- ------------------------------
       100 select /*+ result_cache */                                             T
           id, sum(amount) amount
           from t
           group by id

依赖性主要是为了保持缓存中数据的完整性，如果所依赖的表被修改了，oracle会修改缓存的条目为失效状态，在原sql重新执行以后则会生成一个新的条目出来。这个特性无法关闭，即使你能容忍数据的不一致。

所以想要使result cache里的条目失效非常简单，只要简单做个更新然后commit即可。

1
2
3
4
5
update t
set id=id
where rownum=1;

commit;

这时重新执行之前的sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
set autotrace traceonly

select /*+ result_cache */
id, sum(amount) amount
from t
group by id;

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

oracle并不会判断你的语句是否造成了数据的变更，可以通过上面的结果看到又重新产生了一致性读，表示这次执行oracle并没有直接采用result cache里存放的结果，而是重新执行了一遍语句，通过v$result_cache_objects查看更详细的信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
col name for a40
select name,type,status,cache_id,row_count 
from v$result_cache_objects
order by creation_timestamp;

NAME                                     TYPE                           STATUS                      CACHE_ID                                  ROW_COUNT
---------------------------------------- ------------------------------ --------------------------- ---------------------------------------- ----------
XB.T                                     Dependency                     Published                   XB.T                                              0
select /*+ result_cache */               Result                         Invalid                     0jqwkxxdxwwby3nq2zva97pa1n                       10
id, sum(amount) amount
from t
group by id

select /*+ result_cache */               Result                         Published                   0jqwkxxdxwwby3nq2zva97pa1n                       10
id, sum(amount) amount
from t
group by id

这里STATUS列很清楚的表示了之前的那个条目变成了失效状态，而之后生成了一个cache_id一样的新条目，状态是published状态，也就是当前可用的状态。

缓存使用计数

缓存了结果以后，可以通过V$RESULT_CACHE_STATISTICS视图来查看缓存具体被使用的次数，但是这里显示的数据都代表的result cache的整体统计信息，无法获取单个条目的情况。

这个视图的结果可以给我们用来验证一些实验，通过PL/SQL循环调用sql，查看缓存使用了多少次

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select value from v$result_cache_statistics
where name='Find Count';

VALUE
--------------------
106

declare
	n int;
begin
	for i in 1..100 loop
		select /*+ result_cache */ count(1) into n from t;
	end loop;
end;
/

PL/SQL procedure successfully completed.

重新检查find count的计数

1
2
3
4
5
6
select value from v$result_cache_statistics
where name='Find Count';

VALUE
--------------------
205

计数增加了99，这是因为第一次是创建新的缓存条目，而剩下的99次都是直接使用这个缓存条目的结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
col name for a40
select name,type,status,cache_id,row_count 
from v$result_cache_objects
order by creation_timestamp;

NAME                                     TYPE                           STATUS                      CACHE_ID                                  ROW_COUNT
---------------------------------------- ------------------------------ --------------------------- ---------------------------------------- ----------
XB.T                                     Dependency                     Published                   XB.T                                              0
select /*+ result_cache */               Result                         Invalid                     0jqwkxxdxwwby3nq2zva97pa1n                       10
id, sum(amount) amount
from t
group by id

select /*+ result_cache */               Result                         Published                   0jqwkxxdxwwby3nq2zva97pa1n                       10
id, sum(amount) amount
from t
group by id

SELECT /*+ result_cache */ COUNT(1) FROM Result                         Published                   6khry4nkgn7r918q26w75v28td                        1
 T

性能比较

在前面提到oracle建议是那些读取大量数据但是返回少量数据的情况最适合result cache，接下来会通过几个例子来量化性能的提升到底有多少，对于不同的场景来说提升的幅度有多大。

简单数据求和

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
SQL> exec DBMS_RESULT_CACHE.FLUSH;

PL/SQL procedure successfully completed.

SQL> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

SQL> declare
  2  n int;
  3  begin
  4  for i in 1..100 loop
  5  select
  6  sum(amount) amount into n
  7  from t;
  8  end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

SQL> declare
  2  n int;
  3  begin
  4  for i in 1..100 loop
  5  select /*+ result_cache */
  6  sum(amount) amount into n
  7  from t;
  8  end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> exec runstats_pkg.rs_stop(1000);
===============================================================================================
RunStats report : 24-AUG-2020 16:35:16
===============================================================================================
-----------------------------------------------------------------------------------------------
1. Summary timings
-----------------------------------------------------------------------------------------------
Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
TIMER cpu time (hsecs)                                             18            0          -18
TIMER elapsed time (hsecs)                                        862          795          -67
Comments:
1) Run2 was 7.8% quicker than Run1
2) Run2 used 7.8% less CPU time than Run1
-----------------------------------------------------------------------------------------------
2. Statistics report
-----------------------------------------------------------------------------------------------
Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
STAT  no work - consistent read gets                            8,900           89       -8,811
STAT  table scan blocks gotten                                  8,900           89       -8,811
STAT  consistent gets                                           9,200           92       -9,108
STAT  consistent gets from cache                                9,200           92       -9,108
STAT  consistent gets pin                                       9,200           92       -9,108
STAT  consistent gets pin (fastpath)                            9,200           92       -9,108
STAT  session logical reads                                     9,200           92       -9,108
LATCH cache buffers chains                                     18,401          351      -18,050
STAT  session uga memory                                            0    1,178,784    1,178,784
STAT  table scan disk non-IMC rows gotten                   5,000,000       50,000   -4,950,000
STAT  table scan rows gotten                                5,000,000       50,000   -4,950,000
STAT  session pga memory                                   -4,718,592    1,179,648    5,898,240
STAT  logical read bytes from cache                        75,366,400      753,664  -74,612,736
-----------------------------------------------------------------------------------------------
3. Latching report
-----------------------------------------------------------------------------------------------
Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
LATCH total latches used                                       28,549       10,743      -17,806
Comments:
1) Run2 used 62.4% fewer latches than Run1
-----------------------------------------------------------------------------------------------
4. Time model report
-----------------------------------------------------------------------------------------------
Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
TIME  PL/SQL execution elapsed time                             6,330        6,263          -67
TIME  parse time elapsed                                          350          234         -116
TIME  sql execute elapsed time                                255,629       40,530     -215,099
TIME  DB time                                                 256,752       41,251     -215,501
TIME  DB CPU                                                  257,054       41,508     -215,546

都是重复了100次，第一次没有使用result cache，而第二次进行了缓存，从总的时间上来看第二次快了7.8%，消耗了更少的cpu。在statistics报告里面，一些一致性读的部分大量减少，RUN1出现了很多CBC latch争用，总latch数量RUN2会少了62.4%，总的来说对于这种简单语句，效果并没有预想的那么明显。

复杂语句

创建两张测试表，每张表大概50W数据

1
2
3
4
5
6
7
create table t1 as select * from dba_objects;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
commit;

create table t2 as select * from t1;

循环执行sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
SQL> set serveroutput on
SQL> exec DBMS_RESULT_CACHE.FLUSH;

PL/SQL procedure successfully completed.

SQL>
SQL> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

SQL> declare
  2  n int;
  3  begin
  4  for i in 1..100 loop
  5  select  count(1) into n from t1,t2 where t1.object_name=t2.object_name;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

SQL> declare
  2  n int;
  3  begin
  4  for i in 1..100 loop
  5  select /*+ result_cache */ count(1) into n from t1,t2 where t1.object_name=t2.object_name;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> exec runstats_pkg.rs_stop(1000);
===============================================================================================
RunStats report : 25-AUG-2020 10:49:54
===============================================================================================
-----------------------------------------------------------------------------------------------
1. Summary timings
-----------------------------------------------------------------------------------------------
Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
TIMER cpu time (hsecs)                                          8,617           88       -8,529
TIMER elapsed time (hsecs)                                    122,975        1,281     -121,694
Comments:
1) Run2 was 99% quicker than Run1
2) Run2 used 99% less CPU time than Run1
-----------------------------------------------------------------------------------------------
2. Statistics report
-----------------------------------------------------------------------------------------------
Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
LATCH object queue header operation                             1,753          732       -1,021
LATCH query server process                                      1,059           12       -1,047
LATCH session allocation                                        1,185           20       -1,165
LATCH interrupt manipulation                                    1,235           26       -1,209
LATCH cp srv type state latch                                   1,231           16       -1,215
STAT  process last non-idle time                                1,230           13       -1,217
LATCH service drain list                                        1,230           13       -1,217
LATCH Consistent RBA                                            1,266           13       -1,253
LATCH OS process                                                1,300            5       -1,295
LATCH channel operations parent latch                           1,404           31       -1,373
LATCH client/application info                                   1,894           37       -1,857
LATCH process queue                                             2,107           45       -2,062
LATCH query server freelists                                    2,202           47       -2,155
LATCH lgwr LWN SCN                                              2,449           20       -2,429
STAT  calls to kcmgcs                                           2,704           31       -2,673
LATCH post/wait queue                                           2,791           34       -2,757
LATCH OS process allocation                                     3,050           24       -3,026
LATCH parallel query alloc buffer                               3,385           73       -3,312
LATCH session idle bit                                          4,097           81       -4,016
STAT  scheduler wait time                                       4,310           22       -4,288
STAT  non-idle wait time                                        4,370           23       -4,347
LATCH redo writing                                              5,174          269       -4,905
LATCH SGA Logging Log Latch                                     5,811           57       -5,754
LATCH JS Sh mem access                                          6,149           65       -6,084
LATCH redo allocation                                           6,168           54       -6,114
STAT  CPU used by this session                                  8,620           92       -8,528
STAT  recursive cpu usage                                       8,620           91       -8,529
STAT  CPU used when call started                                8,625           92       -8,533
LATCH archive destination                                       9,407          457       -8,950
LATCH Real-time descriptor latch                               10,709            0      -10,709
LATCH checkpoint queue latch                                   22,123        5,154      -16,969
LATCH SQL memory manager workarea list latch                   27,954          275      -27,679
STAT  non-idle wait count                                      32,206          357      -31,849
LATCH messages                                                 34,303          803      -33,500
LATCH shared pool                                              45,485          607      -44,878
LATCH process queue reference                                  81,350        1,411      -79,939
LATCH simulator hash latch                                    144,019        1,787     -142,232
LATCH active service list                                     157,454        1,654     -155,800
LATCH JS queue state obj latch                                332,004        3,456     -328,548
LATCH enqueue hash chains                                     708,314        7,930     -700,384
STAT  no work - consistent read gets                        2,275,500       22,755   -2,252,745
STAT  table scan blocks gotten                              2,275,500       22,755   -2,252,745
STAT  consistent gets                                       2,278,200       22,782   -2,255,418
STAT  consistent gets from cache                            2,278,200       22,782   -2,255,418
STAT  consistent gets pin                                   2,278,200       22,782   -2,255,418
STAT  consistent gets pin (fastpath)                        2,278,200       22,782   -2,255,418
STAT  session logical reads                                 2,278,200       22,782   -2,255,418
STAT  session pga memory                                   -3,145,728    1,114,112    4,259,840
LATCH cache buffers chains                                  4,619,781       47,490   -4,572,291
STAT  session pga memory max                               61,407,232    1,114,112  -60,293,120
STAT  session uga memory max                               62,780,264    1,056,048  -61,724,216
STAT  table scan disk non-IMC rows gotten                 116,846,400    1,168,464 -115,677,936
STAT  table scan rows gotten                              116,846,400    1,168,464 -115,677,936
STAT  logical read bytes from cache                      ############  186,630,144 ############
-----------------------------------------------------------------------------------------------
3. Latching report
-----------------------------------------------------------------------------------------------
Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
LATCH total latches used                                    6,274,587       74,319   -6,200,268
Comments:
1) Run2 used 98.8% fewer latches than Run1
-----------------------------------------------------------------------------------------------
4. Time model report
-----------------------------------------------------------------------------------------------
Type  Name                                                       Run1         Run2         Diff
----- -------------------------------------------------- ------------ ------------ ------------
TIME  PL/SQL compilation elapsed time                           2,332            0       -2,332
TIME  PL/SQL execution elapsed time                             7,196        4,566       -2,630
TIME  hard parse elapsed time                                   2,824            0       -2,824
TIME  parse time elapsed                                        5,084          342       -4,742
TIME  DB CPU                                               86,246,712      923,657  -85,323,055
TIME  sql execute elapsed time                            129,287,077    1,145,411 -128,141,666
TIME  DB time                                             129,291,774    1,146,272 -128,145,502
-----------------------------------------------------------------------------------------------

可以看到对于这种访问大数据最后返回数据量又小的情况，性能提升的效果就非常明显。未使用result cache的情况花费了120s，而使用result cache的情况则使用了1s多，CPU的消耗也减少了99%，而在一些具体的统计指标比如latch事件的等待、一致性读的构造和内存分配等情况下耗费的资源都少的多。

总的来说最大的性能提升方面在于避免了大量重复的数据库工作，直接从结果中获取数据，然而对于源表经常要进行修改的情况可能也会导致更负面的效果。

Oracle
#result cache
Result Cache
https://www.xbdba.com/2020/08/27/result-cache/
作者
xbdba
发布于
2020年8月27日
许可协议