Query Result Cache

 

繰り返されるqueryの応答速度を改善するためにOracle 11gでは、メモリ領域にresult cacheを使用する機能を導入しました。Result Cacheは、shared poolのResult Cache Memoryと呼ばれる領域にSQLとPL/SQL funtionの結果を格納する機能です。

 

特定のqueryが繰り返し実行されるときに、この結果をキャッシュして、その次からはそのqueryを再実行するのではなく、キャッシュメモリに格納された結果の値をそのまま使用するようになります。この場合、db負荷だけでなく、応答速度の大幅な向上を期待することがことができます。

 

このresult cacheは、すべてのセッションのために共有されており、この保存された結果は、queryがアクセスするobjectが変更されると破棄されます。このresult cacheそれ自体はインスタンスごとに、それぞれ保存され、その使用局面においてはDatabase全体をカバーします。

 

この機能を使用しない場合はRESULT_CACHE_MAX_SIZEパラメータを0にセットしてinstanceを再起動する必要があります。 RACの場合も同様です。

目次

Result Cacheに関連するパラメータ

NAME VALUE 説明
result_cache_mode MANUAL result cacheのモードを選択(MANUAL、FALSE)
_result_cache_auto_size_threshold 100 result cache auto max sizeを設定
_result_cache_auto_time_threshold 1000 result cache auto timeしきい値
_result_cache_auto_execution_threshold 1 result cache auto executionしきい値
result_cache_max_size 1048576 キャッシュによって使用されるメモリの最大量 0の場合は未使用
result_cache_max_result 5 キャッシュサイズのパーセンテージとしての最大結果サイズ
result_cache_remote_expiration 0 リモートオブジェクトを使用するすべての結果の最大存続時間(分)
_result_cache_block_size 1024 Result Cacheブロックサイズ
_result_cache_timeout 60 セッションが結果を待つ最大時間(秒)
_result_cache_auto_time_distance 300 Result Cache自動時間間隔
client_result_cache_size 0 クライアントResult Cacheの最大サイズ(Byte)  0の場合は未使用
client_result_cache_lag 3000

クライアントのResult Cacheの最大遅延時間(ミリ秒単位)

もしOCIアプリケーションが頻繁に使用しない場合は、この値を十分に大きく保持しなければならない。そうでなければ、databaseのResult Cacheと同期するために頻繁にround tripが発生する。

_client_result_cache_bypass FALSE

クライアントのResult Cacheをバイパスするかどうか

_xsolapi_sql_result_set_cache_size 32 OLAP API Result Cacheサイズ

Result Cacheに関連するDictionary View<

・V$ RESULT_CACHE_DEPENDENCY:objectとcacheされたResult間の依存関係を表します。
 対応するSQLがアクセスしているobjectが変更される場合、Result cacheが無効になります。

・RESULT_CACHE_MEMORY:メモリ領域の使用状況を知らせるResult Cache Memory領域を
 チャンク別に分けていくつかのResultがどのメモリ領域にいるかどうかを示してくれるビューです。

・V$ RESULT_CACHE_OBJECTS:最も重要なビューとして現在Cacheた現状とこれに対する実行情報
 および結果セットに関する情報を表示します。
・V$ RESULT_CACHE_STATISTICS:Result Cacheの統計情報を表示します。

・CLIENT_RESULT_CACHE_STATS$:Clent Query Result Cacheの統計情報を表示します。

Result Cacheを制御するPackage

Oracleは、result cacheを制御するためにDBMS_RESULT_CACHEというパッケージを提供します。 このパッケージのサブプログラムは、次のとおりです。

・BYPASS Procedure:Result Cacheのbypassモードを設定します。
・FLUSH Function&Procedure:Result Cacheのオブジェクトは、引数は、統計情報、result setをすべて初期化します。
・INVALIDATE Function&Procedure:Result Cacheのすべてのオブジェクトとresult setを無効化します。
・INVALIDATE_OBJECT Function&Procedure:Result Cacheの特定のオブジェクトとresult setを無効化します。
・MEMORY_RESULT Procedure:Result Cacheの現在のメモリ使用に関するレポートを出力します。
・STATUS Function:Result Cacheの状態をチェックします。

Status Funtionの値は、以下のようになっており、これは奇妙に見えます。

STATUS_CLSD Cacheは、使用できません。
STATUS_OPEN Cacheは、使用できます。
STATUS_SYNC Cacheは、使用できますが、RACノードと同期しています。

 

しかし、実際は以下の通りです。

 SQL>select dbms_result_cache.status from dual

 STATUS                                 
 ----------------------------------------
 ENABLED                                

以下はDBMS_RESULT_CACHE.MEMORY_REPORTを実行した結果です。

SQL> exec DBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);
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  = 1M bytes (1K blocks)
Maximum Result Size = 51K bytes (51 blocks)
[Memory]
Total Memory = 103528 bytes [0.085% of the Shared Pool]
 
... Fixed Memory = 5132 bytes [0.004% of the Shared Pool]
....... Cache Mgr  = 108 bytes
....... Memory Mgr = 124 bytes
....... Bloom Fltr = 2K bytes
 
....... State Objs = 2852 bytes
... Dynamic Memory = 98396 bytes [0.081% of the Shared Pool]
....... Overhead = 65628 bytes
........... Hash Table    = 32K bytes (4K buckets)
........... Chunk Ptrs    = 12K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 8284 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)

SQL Query result Cacheの使用

SQL query result Cacheの使用は、RESULT_CACHE_MODEパラメータによって制御が可能です。このパラメータは、MANUALとFORCEという値を持つことができます。

MANUALの場合、Result Cacheを使用するには、特定のSQLブロックにresult_cacheヒントを使用しなければなりません。FORCEの場合は、すべての結果を保存するためno_result_cacheというヒントを使用して、これを回避することができます。最も最近使用されたアルゴリズムは、cache resultをage outされるようにするため、 もしresultが使用可能なメモリ容量よりも大きい場合は、キャッシュがありません。

MANUALモードでResult Cacheを使用するには、次のように使用すれば良いのです。

select /*+ result_cache */ deptno, avg(sal)
from emp
group by deptno;

この場合、以下のようなプランが表示されます。

SELECT STATEMENT ALL_ROWS-Cost : 4
 RESULT CACHE 
  HASH GROUP BY
   TABLE ACCESS FULL SCOTT.EMP(1)

RESULT CACHEというオペレーションが表示されます。RESULT CACHEオペレーションは、一度result cache memoryを見つけ、もしこの結果がcacheにあればすぐに結果の値を取得し、それ以外の場合、これを実行して、結果の値をresult cache memoryに保存します。

いくつかの実験をしてみましょう。

Javaのアプリケーションquery_result_cache.classを通じてCacheを使用する場合と使用しない場合の性能を比較してみます。

このアプリケーションで使用したSQLとPlanは、以下の通りです。

*CACHE
SELECT emp.deptno , sal , dname , loc
FROM   dept , (SELECT /*+ result_cache */
                      deptno ,AVG( sal ) sal
               FROM   emp
               GROUP  BY deptno ) emp
WHERE  dept.deptno = emp.deptno
-----------------------------------------------------------
 SELECT STATEMENT ALL_ROWS-Cost : 7
  MERGE JOIN 
   TABLE ACCESS BY INDEX ROWID SCOTT.DEPT(1)
    INDEX FULL SCAN SCOTT.PK_DEPT (DEPTNO)
   SORT JOIN  ("DEPT"."DEPTNO"="EMP"."DEPTNO")  ("DEPT"."DEPTNO"="EMP"."DEPTNO")
    VIEW  SCOTT.(2)
     RESULT CACHE 
      HASH GROUP BY
       TABLE ACCESS FULL SCOTT.EMP(3)

* NO CACHE
SELECT emp.deptno , sal , dname , loc
FROM   dept , (SELECT deptno ,AVG( sal ) sal
               FROM   emp
               GROUP  BY deptno ) emp
WHERE  dept.deptno = emp.deptno
-----------------------------------------------------------
SELECT STATEMENT ALL_ROWS-Cost : 7
  MERGE JOIN 
   TABLE ACCESS BY INDEX ROWID SCOTT.DEPT(1)
    INDEX FULL SCAN SCOTT.PK_DEPT (DEPTNO)
   SORT JOIN  ("DEPT"."DEPTNO"="EMP"."DEPTNO")  ("DEPT"."DEPTNO"="EMP"."DEPTNO")
    VIEW  SCOTT.(2)
     HASH GROUP BY
      TABLE ACCESS FULL SCOTT.EMP(3)

これを実行すると以下のような違いが現れました。

Stat No Cache Cache
table scan rows gotten 140072 72
session logical reads 90052 20052
table scan blocks gotten 50003 3
table fetch by rowid 40016 40016
execute count 10009 10009
user calls 10007 10007
CPU used by this session 483 85
Elapsed_Time 10.157 5.313

指標から見ると、Full Scanの仕事量に格段に差が出て、最終的にLogical Readや実行時間に大きな違いを示すことがわかります。Planから見れば、Full Scanが使用されている部分は、in line viewであるため、Result Cacheを使用すると、かなりの部分の性能を向上させることができるということを証明しています。

Client Query Result Cacheの使用 今まではShared Poolを利用したQuery Result Cacheを調べて来ました。ここではClientのメモリ領域、特にOCIのメモリ空間を使用したQuery Result Cacheも使用が可能です。これは、使用する方法は同じですが、どこのCacheがされているかの違いが発生します。

ところが、Clientにcacheとして、そのセッションやプロセスにのみ適用されるメカニズムではないのです。この機能を使用すると、優先的にShared memoryにcacheされてOCIのこれに対するコピーを再Cacheすることになります。したがって、database領域にわたってResult Cacheが行われるようになるのです。この機能を使用すると、latchの獲得の試みが減少するなどのShared memoryの負荷を軽減という利点を享受することができるようになります。

OCIのResult Cacheは、継続して使用をすると維持しますが、そうでなければclient_result_cache_lagに設定された時間ごとにShared Memoryと同期を取ることになります。

この機能を使用するには、前述したように、CLIENT_RESULT_CACHE_SIZEの値を付与すればよいのです。

SQL Query Result Cacheの制約

次の場合には、SQL Query Result Cacheの使用に制約を受けることになります。

・DictionaryやTemporaryテーブルの場合
・CurrvalやNextvalのようにSequence列が入っている場合、
・SQL functionのsysdate、current_date、userenv/ sys_context(変数に定数ではない場合)、
 local_timestamp、current_timestamp、sys_guid、sys_timestampなど可変的な結果値を使用する場合
・バインド変数を使用した場合は、変数だけの場合はcacheが可能
・サブクエリ

上記のように結果の値が変更される余地がある場合は、使用が不可能か、または制約を受けるということを思い出してください。