11g result cache 结果高速缓存

 

=================

 

--说明
11g的sga中增加了一个新组件 result cache 结果高速缓存区
起功能在于从内存中直接获得已经缓存过的查询结果
通过这种形式减少物理io和其他开销
最终提升性能

 

对于结果缓存来说
经常发生变化的数据是不适合使用的
适合在返回结果数相对较少、数据不经常改变的情况下使用
通过合适的sql书写，还可以缓存内联视图中的结果
所以结果高速缓存很适用于数据仓库

--使用限制
1、使用绑定变量时，一个查询只能对相同的变量值重用高速缓存结果，不同变量值和变量名都会导致不命中
2、查询使用非当前数据版本，为保证一致性，结果高速缓存不会命中
3、如果查询成分的表上有挂起的事务，oracle不会高速缓存结果
4、不高速缓存闪回查询
5、包含以下成分的查询不缓存
  不确定的plsql函数
  currval和nextval
  sysdate和current_date等函数
  临时表
  字典表

--相关参数
result_cache_mode
result_cache_max_size
result_cache_max_result

-----------

 

result_cache_mode
可选值：manual、force、auot
默认值：manual
--默认情况manual只有在sql中明确用hint标识才使用结果高速缓存
--如果没有hint则不使用
--select /*+ result_cache */ * from t;就是使用了结果高速缓存
--select /*+ no_result_cache */ * from t;就是不使用结果高速缓存

可选值：auto
--程序自动根据执行频率、执行成本选择是否使用结果高速缓存
可选值：force
--只要高速缓存结果合法，系统就使用这个结果

修改方式：alter system\session\

-----------

 

result_cache_max_size

可选值：0-最大

--结果高速缓存区最大值
--值可以为0，即不使用结果高速缓存
--最大值为系统内存能够接受的值
--rac系统必须为两个节点分别配置这个参数

修改方式：alter system

----------

result_cache_max_result

 

可选值：0-100
默认值：5

--单个缓存结果占用结果高速缓存区大小的百分比

修改方式：alter system

----------

 

RESULT_CACHE_REMOTE_EXPIRATION

可选值：0-最大
默认值：0

--引用到远程对象的结果再高速缓存区中保留的最大分钟数
--超过设定值结果将被清除

修改方式：alter system\session

 

=============================

 

--测试

--修改参数，启用结果高速缓存
SQL> alter system set result_cache_mode=force;
System altered.

SQL> alter system set result_cache_max_size=2m;
System altered.

 

 

--当前参数配置为结果高速缓冲区启用，缓冲区大小2m、单个结果可以占用整个缓冲区
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     100
result_cache_max_size                big integer 2M
result_cache_mode                    string      FORCE
result_cache_remote_expiration       integer     0

 

 

--手动清除共享池、高速缓冲区、结果缓冲区中数据
--同时开启autotrace

alter system flush shared_pool;
alter system flush buffer_cache;
exec dbms_result_cache.flush;

set wrap off
set linesize 300
set timing on
set serveroutput on
set autotrace on

 

 

--第一次查询，执行计划中给出使用了result cache
--但因为是第一次查询，从开销中看到递归调用、物理读和罗季度都是有的
--第二次查询，所有上述开销都没有。返回时间也由0.44秒缩短到0.01秒
--第一次

SQL> select /*+ result_cache +*/ count(*) from t;

  COUNT(*)
----------
     10000

Elapsed: 00:00:00.44

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     9   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | cgf6d37j2b3hjffm9vnsxcxqwp |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| T                          | 10000 |     9   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(AN.T); attributes=(single-row); name="select /*+ result_cache +*/ count(*) from t"

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
        191  recursive calls
          0  db block gets
         87  consistent gets
         37  physical reads
          0  redo size
        422  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

--第二次

SQL> select /*+ result_cache +*/ count(*) from t;

  COUNT(*)
----------
     10000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |     9   (0)| 00:00:01 |
|   1 |  RESULT CACHE       | cgf6d37j2b3hjffm9vnsxcxqwp |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| T                          | 10000 |     9   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(AN.T); attributes=(single-row); name="select /*+ result_cache +*/ count(*) from t"

Note
-----
   - dynamic sampling used for this statement (level=2)

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

===============================

 

--管理工具

 

---------------

 

包dbms_result_cache

 

 

--清空结果高速缓冲区
exec dbms_result_cache.flush

 

--将特定对象清出结果高速缓冲区
exec dbms_result_cache.invalidate('owner','name')

 

--查看结果高速缓冲区状态：启用与否
select dbms_result_cache.status from dual;

 

--查看结果高速缓冲区内存状况
set serveroutput on
exec dbms_result_cache.memory_report

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 = 2M bytes (2K blocks)
[Memory]
Total Memory = 107836 bytes [0.055% of the Shared Pool]
... Fixed Memory = 9440 bytes [0.005% of the Shared Pool]
... Dynamic Memory = 98396 bytes [0.050% of the Shared Pool]
....... verhead = 65628 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 25 blocks
........... Used Memory = 7 blocks
............... Dependencies = 3 blocks (3 count)
............... Results = 4 blocks
................... SQL     = 4 blocks (4 count)

PL/SQL procedure successfully completed.

 

---------------

 

动态性能视图

v$result_cache_statistics
--结果高速缓冲区设置和内存使用情况
--其中create count success就是成功从结果高速缓冲区中读取数据的次数

SQL> select name,value from v$result_cache_statistics;

NAME                                                         VALUE
------------------------------------------------------------ --------
Block Size (Bytes)                                           1024
Block Count Maximum                                          2048
Block Count Current                                          32
Result Size Maximum (Blocks)                                 2048
Create Count Success                                         4
Create Count Failure                                         1
Find Count                                                   1
Invalidation Count                                           0
Delete Count Invalid                                         0
Delete Count Valid                                           0
Hash Chain Length                                            1

11 rows selected.

 

v$result_cache_objects
--查看缓冲区中的缓冲对象
--可以通过制定cache_id来查看特定的缓冲对象
--cache_id就是在执行计划中result cache后面对应的那么中的串
--status值含义:
--new：高速缓存结果正在构建
--published:可供查询
--bypass:其他查询绕开该结果
--expired：过期
--invalid：不能被其他查询使用

 

SQL> select type,status,name,namespace
  2  from v$result_cache_objects
  3  ;

TYPE       STATUS    NAME                                                         NAMES
---------- --------- ------------------------------------------------------------ -----
Dependency Published APEX_030200.WWV_FLOW_MAIL_QUEUE
Dependency Published APEX_030200.WWV_FLOW_PLATFORM_PREFS
Dependency Published AN.T
Result     Published SELECT ID FROM WWV_FLOW_MAIL_QUEUE                           SQL
Result     Published SELECT VALUE V FROM WWV_FLOW_PLATFORM_PREFS WHERE NAME = :B1 SQL
Result     Published SELECT VALUE V FROM WWV_FLOW_PLATFORM_PREFS WHERE NAME = :B1 SQL
Result     Published select /*+ result_cache +*/ count(*) from t                  SQL

7 rows selected.

SQL> select status,name,namespace
  2  from v$result_cache_objects
  3  where cache_id='cgf6d37j2b3hjffm9vnsxcxqwp';

STATUS    NAME                                                         NAMES
--------- ------------------------------------------------------------ -----
Published select /*+ result_cache +*/ count(*) from t                  SQL

 

v$result_cache_dependency
--高速缓存结果和依赖之间的关系
SQL> select * from v$result_cache_dependency;

 RESULT_ID  DEPEND_ID  OBJECT_NO
---------- ---------- ----------
         6          5      70719
         4          2      70368
         3          2      70368
         1          0      73574

 

v$result_cache_memory
--结果高速缓冲区中所有内存块及其统计信息

SQL> select * from v$result_cache_memory;

        ID      CHUNK     OFFSET FRE  OBJECT_ID   POSITION
---------- ---------- ---------- --- ---------- ----------
         0          0          0 NO           0          0
         1          0          1 NO           1          0
         2          0          2 NO           2          0
         3          0          3 NO           3          0
         4          0          4 NO           4          0
         5          0          5 NO           5          0
         6          0          6 NO           6          0
         7          0          7 YES
         8          0          8 YES
         9          0          9 YES
        10          0         10 YES
        11          0         11 YES
        12          0         12 YES
        13          0         13 YES
        14          0         14 YES
        15          0         15 YES
        16          0         16 YES
        17          0         17 YES
        18          0         18 YES
        19          0         19 YES
        20          0         20 YES
        21          0         21 YES
        22          0         22 YES
        23          0         23 YES
        24          0         24 YES
        25          0         25 YES
        26          0         26 YES
        27          0         27 YES
        28          0         28 YES
        29          0         29 YES
        30          0         30 YES
        31          0         31 YES

32 rows selected.

=============================

 

plsql函数结果高速缓存

 

可以在创建函数时指定result_cache relies_on(table_name)来对plsql函数结果使用结果高速缓存
一旦依赖的表发生dml，之前缓存的结果将会失效

 

--测试
--创建函数
SQL> create or replace function f_count (v_in_name varchar2)
return number
result_cache relies_on(t)
is
v_count number;
begin
        select count(*) into v_count from t
        where name=v_in_name;
        return v_count;
end;
/
  2    3    4    5    6    7    8    9   10   11 
Function created.

 

 

--第一次查询，用时0.10秒，存在递归调用和逻辑读
SQL> set timing on                         
SQL> set autotrace on
SQL> select f_count('anbaisheng') from dual;

F_COUNT('ANBAISHENG')
---------------------
                10000

Elapsed: 00:00:00.10

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

--------------------------------------------------------------------------------
| Id  | Operation        | Name                       | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |                            |     1 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE    | 75xwbnb6b4gxcdcfhr05gksasd |       |            |      |
|   2 |   FAST DUAL      |                            |     1 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(AN.F_COUNT); attributes=(single-row, dynami
c); name="select f_count('anbaisheng') from dual"

 

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

SQL> set wrap off

 

--第二次调用用时0.01秒，没有递归调用和逻辑读
SQL> l
  1* select f_count('anbaisheng') from dual
SQL> /

F_COUNT('ANBAISHENG')
---------------------
                10000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

---------------------------------------------------------------------------------------
| Id  | Operation        | Name                       | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                            |     1 |     2   (0)| 00:00:01 |
|   1 |  RESULT CACHE    | 75xwbnb6b4gxcdcfhr05gksasd |       |            |          |
|   2 |   FAST DUAL      |                            |     1 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - column-count=1; dependencies=(AN.F_COUNT); attributes=(single-row, dynamic); name="select f_count('anbaisheng') from dual"

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

 

 

--查询缓存对象信息
SQL> select type,status,name,namespace
from v$result_cache_objects     
;

TYPE       STATUS    NAME                                                         NAMES
---------- --------- ------------------------------------------------------------ -----
Dependency Published AN.F_COUNT
Dependency Published APEX_030200.WWV_FLOW_MAIL_QUEUE
Dependency Published APEX_030200.WWV_FLOW_PLATFORM_PREFS
Dependency Published AN.T
Result     Published SELECT DECODE('A','A','1','2') FROM DUAL                     SQL
Result     Published select f_count('anbaisheng') from dual                       SQL
Result     Published "AN"."F_COUNT"::8."F_COUNT"#8440831613f0f5d3 #1              PLSQL
Result     Published SELECT ID FROM WWV_FLOW_MAIL_QUEUE                           SQL
Result     Published SELECT VALUE V FROM WWV_FLOW_PLATFORM_PREFS WHERE NAME = :B1 SQL
Result     Published SELECT VALUE V FROM WWV_FLOW_PLATFORM_PREFS WHERE NAME = :B1 SQL
Result     Published SELECT USERENV('SESSIONID') FROM DUAL                        SQL
Result     Published SELECT USER FROM DUAL                                        SQL
Result     Published select * from t                                              SQL
Result     Published select /*+ result_cache +*/ count(*) from t                  SQL

14 rows selected.

--对表进行dml操作
SQL> SQL> insert into t select 10001,'xiangxiang' from dual;
1 row created.

SQL> commit;
Commit complete.

--再次查询缓存对象信息
--因为函数依赖表发生了dml操作
--之前缓存的结果状态变为invalid
SQL> select type,status,name,namespace
from v$result_cache_objects     
;

  2    3 
TYPE       STATUS    NAME                                                         NAMES
---------- --------- ------------------------------------------------------------ -----
Dependency Published AN.F_COUNT
Dependency Published APEX_030200.WWV_FLOW_MAIL_QUEUE
Dependency Published APEX_030200.WWV_FLOW_PLATFORM_PREFS
Dependency Published AN.T
Result     Published SELECT DECODE('A','A','1','2') FROM DUAL                     SQL
Result     Published SELECT ID FROM WWV_FLOW_MAIL_QUEUE                           SQL
Result     Published SELECT VALUE V FROM WWV_FLOW_PLATFORM_PREFS WHERE NAME = :B1 SQL
Result     Published SELECT VALUE V FROM WWV_FLOW_PLATFORM_PREFS WHERE NAME = :B1 SQL
Result     Published SELECT USERENV('SESSIONID') FROM DUAL                        SQL
Result     Published SELECT USER FROM DUAL                                        SQL
Result     Invalid   select /*+ result_cache +*/ count(*) from t                  SQL
Result     Invalid   select * from t                                              SQL
Result     Invalid   "AN"."F_COUNT"::8."F_COUNT"#8440831613f0f5d3 #1              PLSQL
Result     Invalid   select f_count('anbaisheng') from dual                       SQL

14 rows selected.