Oracle Result Cache

result cache
1.结果集缓存，oracle 11g新功能，可以在服务器以及客户端缓存结果集，
服务器端可以通过参数
sql query result cache:存储sql查询结果集
PL/SQL Function Result Cache:用于存储PL/SQL函数结果集

2.result cache 相关参数设置
如果result_cache_mode 设置为MANUAL 时，需要通过手工指定来实现，hints 是result_cache
注意：参数设置为auto时，只有在结果集已经存在的情况下才会直接查询结果集，
如果结果集不存在，则不会自动创建

 

相关参数设置

 

SQL> select  a.VALUE,a.NAME from v$parameter a where a.NAME like '%result%';

 

cache 的对象

SQL> select  type,name  from v$result_cache_objects;

TYPE       NAME
---------- ------------------------------------------------------------------------------------------------------------
--------------
Dependency SCOTT.DEPT
Dependency SCOTT.EMP
Result     select /*+ result_cache */  empno,ename,dname from scott.emp e,scott.dept d where d.deptno = e.deptno
Result     select /*+ result_cache */  empno,ename from scott.emp
Result     select /*+ result_cache */ * from scott.emp
Result     select /*+ result_cache */ count(1) from scott.emp

 

3.测试结果集

SQL>  select /*+ result_cache */  empno,ename from scott.emp;

已选择14行。

执行计划
----------------------------------------------------------
Plan hash value: 3956160932

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |    14 |   140 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE      | dhf4gu6spq6tjbu3f8pu4y3652 |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP                        |    14 |   140 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(SCOTT.EMP); name="select /*+ result_cache */  empno,ename from scott.emp"

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        612  bytes sent via SQL*Net to client
        408  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

再次查询

SQL>  select /*+ result_cache */  empno,ename from scott.emp;

已选择14行。

执行计划
----------------------------------------------------------
Plan hash value: 3956160932

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |    14 |   140 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE      | dhf4gu6spq6tjbu3f8pu4y3652 |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP                        |    14 |   140 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   1 - column-count=2; dependencies=(SCOTT.EMP); name="select /*+ result_cache */  empno,ename from scott.emp"

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        612  bytes sent via SQL*Net to client
        408  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed
其逻辑读降为0，即直接从缓冲结果集获取数据