Oracle使用shared pool来cache result：
使用Query Result Cache的好处：
1.避免parse，节省cpu资源
2.减少latch使用
3.减少shared pool内存的使用
4.减少io使用
result_cache_max_size
可以使用参数result_cache_max_size来启用result cache.
result_cache_mode
MANUAL:必须使用result_cache hint.
FORCE:使用no_result_cache忽略result cache.
Restriction:
1.dictionary and temporary table
2.sequence currval and nextval pseudo columns
3.SQL functions....
4.Non-deterministic PL/SQL functions
相关视图:
v$result_cache_objects
v$result_cache_dependency
V$result_Cache_Memory
v$result_cache_statistics
SQL> show parameter result_cache_mode
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
result_cache_mode string MANUAL
#manaual模式下，需要添加result_cache提示
SQL> show parameter result_cache_max_size
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
result_cache_max_size big integer 832K
SQL> select /*+ result_cache */ count(*) from jane.t;
COUNT(*)
----------
4
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | bx3wh4vsutxmndt5jx4v2ntq89 | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T | 4 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(JANE.T); attributes=(single-row); name="select /*+ result_cache */ count(*) from jane.t"
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
73 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
426 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
#第一次使用，可以从查看V$result_cache_objects，发现该语句已经缓存了
SQL> /
COUNT(*)
----------
4
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | bx3wh4vsutxmndt5jx4v2ntq89 | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T | 4 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(JANE.T); attributes=(single-row); name="select /*+ result_cache */ count(*) from jane.t"
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
426 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
#本次查询已经使用了result cache，没有消耗io
SQL>
SQL>alter system set result_cache_mode='FORCE' scope=both;
系统已更改。
#Force模式下，自动缓存结果
SQL> select count(*) from jane.t;
COUNT(*)
----------
4
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | bx3wh4vsutxmndt5jx4v2ntq89 | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T | 4 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(JANE.T); attributes=(single-row); name="select count(*) from jane.t"
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
73 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
426 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
#第一次执行，缓存结果，并没有添加result_cache提示
SQL> /
COUNT(*)
----------
4
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | RESULT CACHE | bx3wh4vsutxmndt5jx4v2ntq89 | | | |
| 2 | SORT AGGREGATE | | 1 | | |
| 3 | TABLE ACCESS FULL| T | 4 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; dependencies=(JANE.T); attributes=(single-row); name="select count(*) from jane.t"
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
426 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>
#可以看到没有添加提示仍然使用了result cache;
SQL> select /*+ no_result_cache */ count(*) from jane.t;
COUNT(*)
----------
4
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 4 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
426 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
#添加no_result_cache提示，不再使用result cache
#数据更新会导致cache内容失效
SQL> insert into jane.t values(1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select status from v$result_cache_objects where cache_id='bx3wh4vsutxmndt5jx4v2ntq89';
STATUS
------------------
Invalid
#report
SQL> set serveroutput on size 99999
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 = 832K bytes (832 blocks)
Maximum Result Size = 41K bytes (41 blocks)
[Memory]
Total Memory = 335804 bytes [0.276% of the Shared Pool]
... Fixed Memory = 7780 bytes [0.006% of the Shared Pool]
... Dynamic Memory = 328024 bytes [0.270% of the Shared Pool]
....... verhead = 65880 bytes
....... Cache Memory = 256K bytes (256 blocks)
........... Unused Memory = 19 blocks
........... Used Memory = 237 blocks
............... Dependencies = 43 blocks (43 count)
............... Results = 194 blocks
................... SQL = 194 blocks (194 count)
PL/SQL 过程已成功完成。
#刷新
SQL> exec dbms_result_cache.flush;
