oracle11g RESULT_CACHE測試 （一）

環境介紹：

ORACLE11GR2 64BIT

SOLARIS 10 64BIT

SUBJECT:RESULT_CACHE (SQL RESULT CACHE)

[@more@]

在oracle11g中，為了極大的緩解在select count(*) XXXX時能夠小的消耗系統資源以及更快的返回統計記錄於是增加了RESULT_CACHE的功能，使用方法就是透過hints固定：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show parameter result_cache;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 1984K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0

以上的parameter分別指定了分配給result cache的記憶體大小、RC的控制方式以及遠端RC的過期設定，還可以透過執行DBMS_RESULT_CACHE來獲得更詳細的資訊：

SQL> set serveroutput on
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 = 1984K bytes (1984 blocks)
Maximum Result Size = 99K bytes (99 blocks)
[Memory]
Total Memory = 153264 bytes [0.094% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.007% of the Shared Pool]
... Dynamic Memory = 142568 bytes [0.087% of the Shared Pool]
....... Overhead = 109800 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 26 blocks
........... Used Memory = 6 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 5 blocks
................... SQL = 1 blocks (1 count)
................... Invalid = 4 blocks (4 count)

PL/SQL ????????
-----------------------------------------------------------------

效能測試：

• 正常訪問：

SQL> select count(*) from bigtable;

COUNT(*)
----------
1835014

????: 00: 00: 10.09

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

COUNT(*)
----------
1835014

????: 00: 00: 00.01

• DML下的測試

SQL> insert into bigtable select * from user_objects;

???7??

????: 00: 00: 00.36SQL> select count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 09.69SQL> select count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 10.18SQL> select count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 09.94SQL> select /*+result_cache*/count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 09.28SQL> select /*+result_cache*/count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 10.16SQL> select /*+result_cache*/count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 08.73

SQL> commit;

?????

????: 00: 00: 00.01
SQL> select count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 13.02SQL> select count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 23.26SQL> select count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 15.83SQL> select /*+result_cache*/count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 08.10SQL> select /*+result_cache*/count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 00.01SQL> select /*+result_cache*/count(*) from bigtable;

COUNT(*)
----------
1835028

????: 00: 00: 00.01

不難發現，如果透過RESULT_CACHE訪問的目標表被某些session做DML操作，並且沒有commit時，RESULT_CACHE和NO_RESULT_CACHE佔用的時間沒大的差異。

針對以上的測試結果，RESULT_CACHE主要應用在OLAP系統裡或者是一些DML操作極少的表內比較合理。