Result Cacheの動作原理と活用方法

(株)エクセムコンサルティング本部/DBコンサルティングチームキム・チョルファン

概要

ORACLE DBMSを使用するシステムでは、QUERYパフォーマンスは何よりも重要な要素の1つであり、そのパフォーマンスと直接関連するのがI / Oです。

多くの件数をACCESS しなければ、必要な結果値を得ることができないQUERY を実行すると、BLOCK I/Oが多く発生することになります。 このため、QUERYのパフォーマンスは悪いです。

 コードテーブルのように少ないデータを同時に多くのユーザーが照会する場合は、BUFFER CACHEで希望のBLOCKを見つけて結果を送信しますが、同時に多くのユーザーが使用するためHOT BLOCKになり、ORACLE待機イベントが発生し、速いパフォーマンスを期待することは難しいです。

I / Oに応じてQUERYのパフォーマンスが左右されるため、I / Oを最小限にし、QUERYを実行するとパフォーマンスが向上します。

ORACLE 11GでI/Oの改善のために実装した機能がRESULT CACHE機能です。

RESULT CACHE は SHARED POOL で RESULT CACHE MEMORY と呼ばれる領域に SQL と

PL/SQL FUNCTIONの結果を保存し、以降同じQUERY照会時にRESULT CACHEに格納されているQUERY結果値をそのまま活用する機能です。

繰り返し同じ結果値を照会するQUERYで使用され、多くのデータを処理して少ない結果件数を見たい場合にBLOCK I/Oを発生させずに結果を送信するため、照会パフォーマンスにおいて非常に速い結果が得られます。

これから、RESULT CACHE QUERYの動作原理と設定方法を知り、機能の活用について学びましょう。

RESULT CACHE QUERYの動作原理

通常、QUERYが実行され、BUFFER CACHEに目的のブロックがある場合は、要求されたセッションにブロックを送信します。

BUFFER CACHE領域に存在しない場合は、DISK I / Oが発生した後にBUFFER CACHEにBLOCKを置き、要求されたセッションに結果を送信します。

RESULT CACHE機能は一般的なQUERY動作方式と同じですが、結果の値をCACHINGするという点が異なります。

RESULT CACHE QUERY 最初の実行。

1. RESULT CACHE QUERYが実行されると、最初にSHARED POOL領域のRESULTCACHE メモリーで OBJECT の CACHING かどうかを確認する。
2. OBJECTがCACHINGされていない場合は、BUFFER CACHEでBLOCKを探します。
3. BUFFER CACHE に希望の BLOCK が存在しない場合は DISK から BLOCK を読み込みBUFFER CACHEに送信します。
4. その結果、値をQUERYしたセッションに送信する
5.  最後に、RESULT CACHE領域にQUERY結果値を格納します。

RESULT CACHE QUERY 繰り返し実行.

1.     RESULT CACHE QUERYが実行されると、最初にSHARED POOL領域のRESULTCACHE メモリーで OBJECT の CACHING かどうかを確認します。

2.     ＣＡＣＨＩＮＧされた情報が存在すれば、Ｉ／Ｏを発生させずにＣＡＣＨＩＮＧされた結果値を要求したセッションに送信します。

RESULT CACHE機能の設定と終了

RESULT CACHE機能の設定

PARAMETER設定でRESULT CACHE機能を使用できます。

 セットアップに必要な主要PARAMETERを見てみましょう。

RESULT_CACHE_MODE

RESULT_CACHE_MODE PARAMETERの値に応じて2つのMODEの設定が可能です。

MANUALの場合は、SQLごとに/*+ RESULT_CACHE */ HINT を与え、

RESULT CACHE 機能が使用可能で FORCE の場合、すべての SQL が RESULT CACHE の

対象となります。

 ただし、/*+ NO_ RESULT_CACHE */ HINT を除く。

 RESULT_CACHE_MODE PARAMETERのDEFAULT値はMANUALです。

RESULT_CACHE_MAX_SIZE               

RESULT CACHE機能を使用するには、RESULT_CACHE_MAX_SIZE値も明示的に指定されていますあるべきです。

このPARAMETERの最大値はSHARED POOLの最大75％まで設定できます。

RESULT_CACHE_MAX_RESULT

1つのSQLに結果セットのキャッシュ領域全体で最大割り当て可能なメモリサイズ。

DEFAULT値は5％です。

RESULT_CACHE_REMOTE_EXPIRATION

REMOTE DATABASE OBJECT の保管周期を時間（分）指定が可能で、DEFAULT値は0です。

The default is 0 which means that resultsets dependant on remote OBJECT

RESULT CACHE機能を終了する

RESULT CACHE機能を取り消す方法には、特定のINSTANCEがRESULT CACHE機能を使用できないように設定する方法とRESULT CACHE機能は使用可能ですが、CACHEでOBJECTを解除する2つの方法があります。

INSTANCEがRESULT CACHE機能を使用したくない場合は、RESULT_CACHE_MODE値を0の値に設定してINSTANCEを再起動するだけです。

RESULT CACHE機能は使用可能ですが、CACHINGされているOBJECTを取り消す方法は、RESULT CACHEパッケージを利用して取り消すことができます。

CACHEで終了する方法については、例を見てみましょう。

CACHINGされたOBJECTを取り消す方法

RESULT CACHE 機能を使用して CACHING された複数の OBJECT のうち、特定の OBJECT のみCACHEで取り消すには、DBMS_RESULT_CACHE.INVALIDATEパッケージを使用して取り消すことができます。

[QUERY 実行] SELECT ID,

TYPE, STATUS, BUCKET_NO, HASH,NAME

FROM   V$RESULT_CACHE_OBJECT;

[結果値]

ID    TYPE STATUS BUCKET_NO HASH     NAME

—– —- ——- ———- ——— —————————-

0 Dependency Published   660 319061 DBAADM.TB_RC_TEST_YYYYMMDD

1 RESULT     Published 2011 159411      SELECT       /*+ RESULT_CACHE */

SUBSTR(SDATE,1,6) SDATE, PROD,

SUM(AMT1) AMT1, SUM(amt2) AMT2, SUM(AMT3) AMT3

FROM         TB_RC_TEST_YYYYMMDD GROUP BY SUBSTR(SDATE,1,6),

PROD

[パッケージの実行]

EXEC DBMS_RESULT_CACHE.INVALIDATE(‘DBAADM’,’ TB_RC_TEST_YYYYMMDD’)

[QUERY 実行] SELECT

ID, TYPE, STATUS,

BUCKET_NO, HASH,NAME

FROM   V$RESULT_CACHE_OBJECT;

[結果値]

no rows selected.

[パッケージの実行]

EXEC DBMS_RESULT_CACHE.FLUSH

[QUERY 実行] SELECT

ID, TYPE, STATUS,

BUCKET_NO, HASH,NAME

FROM  V$RESULT_CACHE_OBJECT;

[結果値]

no rows selected.

RESULT CACHEに登録されているすべてのOBJECTをDBMS_RESULT_CACHE.FLUSHパッケージを介して一括解約できます。

[QUERY 実行]

SELECT /*+ RESULT_CACHE */ 1 FROM DUAL; SELECT /*+ RESULT_CACHE */ 2 FROM DUAL; SELECT /*+ RESULT_CACHE */ 3 FROM DUAL;

[QUERY 実行] SELECT

ID, TYPE, STATUS,

BUCKET_NO, HASH,NAME

FROM   V$RESULT_CACHE_OBJECT;

[結果値]

ID TYPE          STATUS  BUCKET_NO HASH            NAME

— ———-    ——— ——– ——– ————————————-

2 RESULT        Published 3870      83370270 SELECT /*+ RESULT_CACHE */ 3 FROM DUAL

1 RESULT        Published 2222      93590190 SELECT /*+ RESULT_CACHE */ 2 FROM DUAL

0 RESULT        Published 3414      7988310   SELECT /*+ RESULT_CACHE */ 1 FROM DUAL

[パッケージの実行]

EXEC DBMS_RESULT_CACHE.FLUSH

[QUERY 実行] SELECT

ID, TYPE, STATUS,

BUCKET_NO, HASH,NAME

FROM   V$RESULT_CACHE_OBJECT;

[結果値]

no rows selected

RESULT CACHE機能を活用する

過去の顧客会社の例を見ると、オンライン時に商品について実績を見る業務があった。

業績表は1年間保管され、NON PARTITIONされた表が12個存在しています。

特定の月に該当するパフォーマンスを表示するには、そのテーブルにすべてのデータを読み込む必要があります。

 そうなるとI/Oが多く発生し、速い性能が期待できません。

 そこで毎日夜間バッチが実行され、前日基準で実績集計テーブルを更新していました。

多くの改善の効果がありますが、まだ読み取るべきデータが多いため、多くのブロックI/Oが発生しています。

 ここで、各月に対応する実績照会をRESULT CACHE機能を使用する場合、多くのパフォーマンス改善があります。

 特定の月に対応するパフォーマンステストデータを生成して、RESULT CACHE機能を活用してみます。

 テーブル生成スクリプト

[DDLの実行]

CREATE TABLE TB_RC_TEST_YYYYMMDD AS

SELECT

TO_CHAR(ADD_MONTHS(SYSDATE,-1) ,’YYYYMM’)|| TRIM(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,31)),’09’))||

TRIM(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(0,23)),’09’))|| TRIM(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(00,59)),’09’)) SDATE, DBMS_RANDOM.STRING(‘U’,2) PROD, ROUND(DBMS_RANDOM.VALUE(100,100000)) AMT1, ROUND(DBMS_RANDOM.VALUE(10,10000)) AMT2, ROUND(DBMS_RANDOM.VALUE(1000,100)) AMT3

FROM   DUAL

CONNECT BY LEVEL <= 10000000 ;

RESULT CACHE機能を使用していない場合（X）

[QUERY 実行] SELECT

SUBSTR(SDATE,1,6) SDATE, PROD,

SUM(AMT1) AMT1, SUM(AMT2) AMT2, SUM(AMT3) AMT3

FROM   TB_RC_TEST_YYYYMMDD GROUP BY SUBSTR(SDATE,1,6),

PROD

[実行計画]

call    count      cpu   elapsed      disk     QUERY   current       rows

——- —— ——– ———- ———- ———- ———-  ———-

Parse	1	0.00	0.00	0	0	0	0
Execute	1	0.00	0.00	0	0	0	0
Fetch	47	15.62	15.80	46944	46948	0	676

——- —— ——– ———- ———- ———- ———-  ———- total 49           15.62 15.80     46944     46948     0            676

Rows (1st) Row Source OPERATION

———- ————————————————— 676 SORT GROUP BY (cr=46948 pr=46944 pw=0 time=15803245)

10000000 TABLE ACCESS FULL TB_RC_TEST_YYYYMMDD (cr=46948 pr=46944 pw=0 time=3144528)

実施結果を見ると、38,670個のBLOCK I/Oが発生しています。

RESULT CACHE機能を使用してテストをやり直してください。

RESULT CACHE機能を使用した場合（O）（最初の実行）

[QUERY 実行]

SELECT /*+ RESULT_CACHE */ SUBSTR(SDATE,1,6) SDATE, PROD,

SUM(AMT1) AMT1, SUM(AMT2) AMT2, SUM(AMT3) AMT3

FROM   TB_RC_TEST_YYYYMMDD GROUP BY SUBSTR(SDATE,1,6),

PROD

[実行計画]

call    count      cpu   elapsed      disk     QUERY   current       rows

——- —— ——– ———- ———- ———- ———-  ———-

Parse	1	0.00	0.00	0	0	0	0
Execute	1	0.00	0.00	0	0	0	0
Fetch	47	15.62	15.80	46944	46948	0	676

——- —— ——– ———- ———- ———- ———-  ———- total 49           15.62 15.80     46944     46948     0            676

Rows (1st) Row Source OPERATION

———- ————————————————— 676 SORT GROUP BY (cr=46948 pr=46944 pw=0 time=15803245)

10000000 TABLE ACCESS FULL TB_RC_TEST_YYYYMMDD (cr=46948 pr=46944 pw=0 time=3144528)

行われた結果、依然として38,670個のBLOCK I / Oが発生しています。

最初のQUERY実行時にCACHINGされた領域にOBJECTが存在しなかったからです。 VIEWを見ると、CACHEエリアに登録されていることがわかります。

CACHINGされたOBJECTビュー

[QUERY 実行]

SELECT           ID,

TYPE, STATUS, BUCKET_NO, HASH,

NAME

FROM   V$RESULT_CACHE_OBJECT;

[結果値]

ID       TYPE            STATUS    BUCKET_NO HASH        NAME

—-    —————    ——– ——— ———- ——— ——————-

0     Dependency        Published 660      319061    DBAADM.TB_RC_TEST_YYYYMMDD

1     RESULT            Published 2011      159411    SELECT    /*+ RESULT_CACHE */

これでCACHE登録になっていることを確認したので、もう一度やってみよう。

RESULT CACHE機能を使用した場合（O）（繰り返し実行）

[QUERY 実行]

SELECT /*+ RESULT_CACHE */ SUBSTR(SDATE,1,6) SDATE, PROD,

SUM(AMT1) AMT1, SUM(AMT2) AMT2, SUM(AMT3) AMT3

FROM   TB_RC_TEST_YYYYMMDD GROUP BY SUBSTR(SDATE,1,6),

PROD

[実行計画]

call    count      cpu   elapsed      disk     QUERY   current       rows

——- —— ——– ———- ———- ———- ———-  ———-

Parse	1	0.00	0.00	0	0	0	0
Execute	1	0.00	0.00	0	0	0	0
Fetch	47	0.00	0.00	0	0	0	676

——- —— ——– ———- ———- ———- ———-  ———- total 49           0.00    0.00      0           0         0         676

Rows (1st) Row Source OPERATION

———- —————————————————

676 RESULT CACHE gnxqgpxppdavj80b6rddg4qsqj (cr=0 pr=0 pw=0 time=20 us)

0 SORT GROUP BY (cr=0 pr=0 pw=0 time=0 )

0   TABLE ACCESS FULL TB_RC_TEST_YYYYMMDD (cr=0 pr=0 pw=0 time=0 )

実行計画を見ると、I / Oはまったく発生しませんせした。

 I / Oがまったく発生しないため、SQLを繰り返し実行する場合でも性能を向上させてくれます。

ここでRESULT CACHEというOPERATIONを見ることができます。

結果の値がCACHINGされたのです。

 INLINE VIEWやWITHステートメントなどのクエリでもRESULT CACHE機能を使用できます。

独立してQUERYブロックにCACHINGが可能だからです。

テストを通し学習しましょう。

 テーブル生成スクリプト

[DDLの実行]

CREATE TABLE TB_RC_TEST_SYSDATE AS

SELECT

TO_CHAR(ADD_MONTHS(SYSDATE,-1) ,’YYYYMM’)|| TRIM(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,31)),’09’))|| TRIM(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(0,23)),’09’))|| TRIM(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(00,59)),’09’)) SDATE, DBMS_RANDOM.STRING(‘U’,2) PROD, ROUND(DBMS_RANDOM.VALUE(100,100000)) AMT1, ROUND(DBMS_RANDOM.VALUE(10,10000)) AMT2, ROUND(DBMS_RANDOM.VALUE(1000,100)) AMT3

FROM   DUAL

CONNECT BY LEVEL <= 300000 ;

INLINE VIEW と WITH 文の使用例

[INLINE VIEW 사용 QUERY] SELECT SDATE, SUM(SUM_AMT)

FROM ( SELECT    SDATE,

NVL(AMT1,0) + NVL(AMT2,0) + NVL(AMT3,0) SUM_AMT FROM          ( SELECT /*+ RESULT_CACHE */

SUBSTR(SDATE,1,6) SDATE, PROD,

SUM(AMT1) AMT1, SUM(AMT2) AMT2, SUM(AMT3) AMT3

UNION ALL SELECT

FROM    TB_RC_TEST_YYYYMMDD GROUP BY SUBSTR(SDATE,1,6),

PROD )

SUBSTR(SDATE,1,6) SDATE,

SUM(AMT1) + SUM(AMT2) + SUM(AMT3) SUM_AMT

FROM     TB_RC_TEST_SYSDATE WHERE         SDATE < :SDATE GROUP BY SUBSTR(SDATE,1,6),

PROD)

GROUP BY SDATE;

[WITH 文の使用 QUERY]

WITH W_TB_RC_TEST_YYYYMMDD AS

(SELECT SDATE,

NVL(AMT1,0) + NVL(AMT2,0) + NVL(AMT3,0) SUM_AMT FROM ( SELECT /*+ RESULT_CACHE */

SUBSTR(SDATE,1,6) SDATE, PROD,

SUM(AMT1) AMT1, SUM(AMT2) AMT2, SUM(AMT3) AMT3

FROM      TB_RC_TEST_YYYYMMDD GROUP BY SUBSTR(SDATE,1,6),

SELECT

SDATE, SUM(SUM_AMT)

PROD ))

FROM   (SELECT  SDATE,

SUM_AMT

FROM          W_TB_RC_TEST_YYYYMMDD UNION ALL

SELECT   SUBSTR(SDATE,1,6) SDATE,

SUM(AMT1) + SUM(AMT2) + SUM(AMT3) SUM_AMT FROM         TB_RC_TEST_SYSDATE

WHERE    SDATE < :SDATE GROUP BY SUBSTR(SDATE,1,6),

PROD)

GROUP BY SDATE;

[実行計画]

call    count      cpu   elapsed       disk     QUERY   current       rows

——- —— ——– ———- ———- ———- ———-  ———-

Parse	1	0.00	0.00	0	0	0	0
Execute	1	0.00	0.00	0	0	0	0
Fetch	2	0.18	0.18	0	1413	0	2

——- —— ——– ———- ———- ———- ———-  ———- total 4            0.18   0.19      0           1413    0            2

Rows (1st) Row Source OPERATION

———- ————————————————— 2 SORT GROUP BY (cr=1413 pr=0 pw=0 time=188846 )

1352 VIEW (cr=1413 pr=0 pw=0 time=2355 )

1352  UNION-ALL (cr=1413 pr=0 pw=0 time=2230 us)

676   VIEW (cr=0 pr=0 pw=0 time=578 )

676    RESULT CACHE gnxqgpxppdavj80b6rddg4qsqj (cr=0 pr=0 pw=0 time=126 us)

0     SORT GROUP BY (cr=0 pr=0 pw=0 time=0 )

0      TABLE ACCESS FULL TB_RC_TEST_YYYYMMDD (cr=0 pr=0 pw=0 time=0 ) 676      SORT GROUP BY (cr=1413 pr=0 pw=0 time=187432 )

104850    TABLE ACCESS FULL TB_RC_TEST_SYSDATE (cr=1413 pr=0 pw=0 time=57449 )

RESULT CACHEを使用する際の考慮事項

DMLを使用する際の考慮事項

CACHINGされたOBJECTにDML（変更）が発生した場合、変更された時点でRESULT CACHE機能は無効になります。

その理由は、CACHINGされたOBJECTが変更された場合、QUERYの結果値に対する整合性を保証できないためです。

したがって、DMLが多く発生するOBJECTはRESULT CACHE機能を使用しないことをお勧めします。

BINDを使用する際の考慮事項

BIND変数の変更が多いQUERYに対しても非効率が発生します。

以下のテスト結果を見るとBIND変数の値に応じて独立してCACHINGされることがわかります。

そのため、BIND変数が

増えると、特定のQUERYがCACHEの領域をすべて使用し、CACHEしたいそれぞれが

他のクエリによってCACHEの登録と失効が頻繁に発生し、CACHEの効率が低下します。

 同じQUERYでBIND変数の値が異なる場合

[QUERY実行] VAR NUM NUMBER; EXEC :NUM := 1;

SELECT /*+ RESULT_CACHE */ ‘CACHING Count’ FROM DUAL WHERE 1 = :NUM;

EXEC :NUM := 2;

SELECT /*+ RESULT_CACHE */ ‘CACHING Count’ FROM DUAL WHERE 1 = :NUM;

EXEC :NUM := 3;

SELECT /*+ RESULT_CACHE */ ‘CACHING Count’ FROM DUAL WHERE 1 = :NUM;

[QUERY 実行] SELECT

ID, TYPE, STATUS,

BUCKET_NO, HASH,NAME

FROM   V$RESULT_CACHE_OBJECT;

[結果値]

ID TYPE   STATUS      BUCKET_NO   HASH        NAME

– —-    ——— ——— ———– ——————————————–

3 RESULT Published	1882	2742746	SELECT /*+ RESULT_CACHE */ ‘CACHING Count’ FROM
DUAL WHERE 1 = :A
2 RESULT Published	1714	7906098	SELECT /*+ RESULT_CACHE */ ‘CACHING Count’ FROM
DUAL WHERE 1 = :A
1 RESULT Published	3880	95977768	SELECT /*+ RESULT_CACHE */ ‘CACHING Count’ FROM
DUAL WHERE 1 = :A
0 RESULT Published	3157	98148181	SELECT /*+ RESULT_CACHE */ ‘CACHING Count’ FROM
DUAL WHERE 1 = :A

 特定のOBJECTを使用する際の考慮事項

以下は、クエリ結果セットをCACHINGできない場合です。

l     一時表または DICTIONARY OBJECT (DBA_*, V$_*, GV$_* など) 参照時

• シーケンスCURRVALとNEXTVAL COLUMNを呼び出す場合

l     QUERY で SQL 関数を使用する場合- CURRENT_TIMESTAMP、LOCAL_TIMESTAMP、SYS_GUID、SYSDATE、SYSTIMESTAMPなど

結論

既存のQUERYは、I / Oが発生しなければ結果セットを知ることができません。 多くのBLOCKでも、多くのセッションが同時に同じBLOCKを使用する場合、WAIT EVENTが発生します。 これは良い性能を期待するのが難しいが、RESULT CACHE機能を使用すると、I/Oをまったく発生しないという部分において、大幅な性能改善の効果があります。 しかし、その機能の長所と短所をよく知って使用しなければ、システムを安定して使用できるはずです。 また、運用しているシステムを見ると、RESULT CACHE機能を使用できる部分があるでしょう。 この機能を活用してI / O性能をさらに向上させることは、より安定化されて最適化されたシステムに発展することになるでしょう。

---