开启result_cache后，sql的查询结果被缓存到shared_pool中，再次执行相同的查询时，直接读取结果，不需要执行该sql了，可以通过两种方式启用：
1. 加提示 RESULT_CACHE
SQL> SELECT /*+ RESULT_CACHE */ department_id, AVG(salary)
2 FROM hr.employees
3 GROUP BY department_id;
12 rows selected.
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 | 3w0d0687vphfr3gvhgbcpfg89s | | | | |
| 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
853 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed
修改查询中涉及到的表的result_cache属性为force：
SQL> ALTER TABLE hr.employees RESULT_CACHE (MODE FORCE);
Table altered.
SQL> SELECT department_id, AVG(salary)
2 FROM hr.employees
3 GROUP BY department_id;
12 rows selected.
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 | 3w0d0687vphfr3gvhgbcpfg89s | | | | |
| 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 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
853 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed
清除result cache中的缓存数据：
SQL> exec DBMS_RESULT_CACHE.FLUSH
PL/SQL procedure successfully completed.
查看result cache的使用情况：
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 = 1376K bytes (1376 blocks)
Maximum Result Size = 68K bytes (68 blocks)
[Memory]
Total Memory = 167864 bytes [0.121% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.008% of the Shared Pool]
... Dynamic Memory = 157168 bytes [0.114% of the Shared Pool]
....... verhead = 124400 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.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-748860/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
