11g result cache 結果快取記憶體

 

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.