2017.10.04
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の場合も同様です。
目次
- 1.Result Cacheに関連するパラメータ
- 2.Result Cacheに関連するDictionary View
- 3.Result Cacheを制御するPackage
- 4.SQL Query result Cacheの使用
- 5.SQL Query Result Cacheの制約
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が可能 ・サブクエリ
上記のように結果の値が変更される余地がある場合は、使用が不可能か、または制約を受けるということを思い出してください。