複雑すぎるSQL(2/2)

2019年2月6日『心臓外科医の術式』

1 前回のおさらい
2 たろーちゃんの提案
3 指定したヒント句
- 3.1 結果キャッシュ

前回のおさらい

前回、「複雑すぎるSQL(1/2)」では、3,000行を超える複雑なSQLの性能問題を解決すべく、たろーちゃんはどのような提案をするのか！？というところで終わりました。

果たしてたろーちゃんは、この問題を解決できるのでしょうか？

たろーちゃんの提案

たろー「SQLにこのヒント句を入れて実行してみれくれ。」
Aさん「なんだこのヒント句は？」
Aさん（カタカタカタ）
Aさん「……実行したけど、速度は変わらないぞ。」
たろー「もう一回、実行してみてくれ。」
Aさん「もう一回？」
Aさん（カタカタカタ）
Aさん「……！なんだ、メチャメチャ早いぞ？！」
たろー「何度か試してみれくれ。」
Aさん「ああ、わかった。」
Aさん（カタカタカタ）
Aさん「何度やっても早いぞ！どうなってんだ？！あんなに遅かったのに？！」

たろーちゃんは、どんなヒント句を指定したのでしょうか？

指定したヒント句

たろーちゃんが指定したのは RESULT_CACHE というヒント句でした。

SQL> SELECT /*+ RESULT_CACHE */　・・・

このヒント句を埋め込むだけで、２回目以降の処理は早くなりました。

たろー「これが俺からの『提案』だ。最初の１回目は遅いけど、その結果をキャッシュするんだ。だからそれ以降は瞬時に結果が返ってくる。『１００点満点な提案じゃない』と言ったのはそのためだ。」
Aさん「なるほど。SELECTの結果をキャッシュしてるのか。」
たろー「そうだ。このキャッシュは元テーブルが更新されると無効になるが、元テーブルは更新頻度が少ないと聞いたから、これでもOKなんじゃないかと思ってさ。」
Aさん「うむ、最初にアクセスした人には申し訳ないけど１６秒間待ってもらって、あとの人はスイスイ動作する訳か。」
たろー「SELECTの結果も１行だけなんだよな？ RESULT_CACHEのキャッシュサイズは大きくないけど、１行くらいならキャッシュに乗せれる。」
Aさん「なるほど…。これは使えるかもしれない！」

Aさんはこの件を持ち帰り、御客様と相談しました。
その結果、予算との兼ね合いもあるので、この方法でよいとの許可が下りました。

Aさん「ありがとう、たろーちゃん！」
たろー「御客様の許可が下りてよかったな。」
Aさん「性能テストも問題なかったよ！」

本当は、「あの複雑なSQLを根本からチューニングさせられるんじゃないかと、内心ビクビクしてた」なんて言えないけどな……（汗）

結果キャッシュ

結果キャッシュは11gR1からサポートされた機能で、前述のとおりSELECTの結果をキャッシュしますが、そのキャッシュはどこに格納されているのでしょうか？
答えは SGA の共有プール内です。
結果キャッシュのサイズは初期化パラメータのRESULT_CACHE_MAX_SIZEとRESULT_CACHE_MAX_RESULTで調整することが出来ます。

※注意：結果キャッシュを使用するには、Enterprise Editionである必要があります。

結果キャッシュを使用すると、実行計画には以下のように出力されます。

◆１回目

SQL> select /*+ RESULT_CACHE */ count(*) from ttt ;


COUNT(*)
----------
  10000000

経過: 00:00:00.11


実行計画
----------------------------------------------------------
Plan hash value: 641185257


------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |  4213   (1)| 00:00:01 |
|   1 |  RESULT CACHE       | 64g8h0guv98v76tu2hyq8a6zwt |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| TTT                        |    10M|  4213   (1)| 00:00:01 |
------------------------------------------------------------------------------------------


Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=1; dependencies=(PDBADMIN.TTT); attributes=(single-row); name="select /*+ RESULT_CACHE */ count(*) from ttt "


統計
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      15245  consistent gets
          0  physical reads
          0  redo size
        565  bytes sent via SQL*Net to client
        552  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 /*+ RESULT_CACHE */ count(*) from ttt ;

  COUNT(*)
----------
  10000000

経過: 00:00:00.02


実行計画
----------------------------------------------------------
Plan hash value: 641185257

------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |     1 |  4213   (1)| 00:00:01 |
|   1 |  RESULT CACHE       | 64g8h0guv98v76tu2hyq8a6zwt |       |            |          |
|   2 |   SORT AGGREGATE    |                            |     1 |            |          |
|   3 |    TABLE ACCESS FULL| TTT                        |    10M|  4213   (1)| 00:00:01 |
------------------------------------------------------------------------------------------

Result Cache Information (identified by operation id):
------------------------------------------------------
   1 - column-count=1; dependencies=(PDBADMIN.TTT); attributes=(single-row); name="select /*+ RESULT_CACHE */ count(*) from ttt "


統計
----------------------------------------------------------
          0  recursive calls　※
          0  db block gets
          0  consistent gets　※
          0  physical reads
          0  redo size
        565  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

※　キャッシュにヒットしているので、recursive calls も consistent gets もゼロになっているのが分かります。

また、結果キャッシュの状態は V$RESULT_CACHE_STATISTICS や V$RESULT_CACHE_OBJECTS 、更にDBMS_RESULT_CACHE.MEMORY_REPORTプロシージャから確認することが出来ます。

col ID for 999
col NAME for a30
col VALUE for a10
SELECT ID, NAME, VALUE FROM V$RESULT_CACHE_STATISTICS order by ID ;

  ID NAME                           VALUE
---- ------------------------------ ----------
   1 Block Size (Bytes)             1024
   2 Block Count Maximum            23616
   3 Block Count Current            1216
   4 Result Size Maximum (Blocks)   1180
   5 Create Count Success           81
   6 Create Count Failure           0
   7 Find Count                     73
   8 Invalidation Count             12
   9 Delete Count Invalid           35
  10 Delete Count Valid             0
  11 Hash Chain Length              1
  12 Find Copy Count                66
  13 Latch (Share)                  0

Find Count は結果キャッシュが検索された回数です。
※その他の項目についてはマニュアルを参照して下さい。

col ID for 999
col NAME for a55
col CACHE_ID for a30
col NAME for a55
col CACHE_ID for a30
SELECT NAME, CACHE_ID, SCAN_COUNT
  FROM V$RESULT_CACHE_OBJECTS
 WHERE CACHE_ID = 'ajdf56153m89h0zxy4jxc3jnkk'
;

  ID NAME                                                    CACHE_ID                       SCAN_COUNT
---- ------------------------------------------------------- ------------------------------ ----------
 232 select /*+ RESULT_CACHE */ count(*) from TAB27 where CO ajdf56153m89h0zxy4jxc3jnkk              2
     L5 = :var_hoge

 197 select /*+ RESULT_CACHE */ count(*) from TAB27 where CO ajdf56153m89h0zxy4jxc3jnkk              1
     L5 = :var_hoge

 196 select /*+ RESULT_CACHE */ count(*) from TAB27 where CO ajdf56153m89h0zxy4jxc3jnkk              2
     L5 = :var_hoge

上記のように、結果キャッシュはバインド変数の内容毎にキャッシュを持つことが出来ます。

 
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  = 23616K bytes (23616 blocks)
Maximum Result Size = 1180K bytes (1180 blocks)
[Memory]
Total Memory = 1391000 bytes [0.101% of the Shared Pool]
... Fixed Memory = 12264 bytes [0.001% of the Shared Pool]
....... Memory Mgr = 208 bytes
....... Cache Mgr  = 256 bytes
....... Bloom Fltr = 2K bytes
....... State Objs = 9752 bytes
... Dynamic Memory = 1378736 bytes [0.100% of the Shared Pool]
....... Overhead = 133552 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 133552 bytes
....... Cache Memory = 1216K bytes (1216 blocks)
........... Unused Memory = 1164 blocks
........... Used Memory = 52 blocks
............... Dependencies = 22 blocks (22 count)
............... Results = 30 blocks
................... SQL     = 1 blocks (1 count)
................... CDB     = 29 blocks (27 count)

DBMS_RESULT_CACHE.MEMORY_REPORTプロシージャでは、結果キャッシュの総サイズや、現在のキャッシュ状況を確認することが出来ます。

今回の「心臓外科医の術式」いかがだったでしょうか？
アプリケーションの仕様も分からないのに、「Oracle Databaseに詳しいから」という理由だけでパフォーマンスチューニングを求められるDBAの方も多いのではないでしょうか？

100点満点の結果は出せないかもしれませんが、時間と予算の兼ね合いもありますので、時には落とし所を見つけることも重要です。
落とし所を見つける為の、見事な「術式」だったのではないでしょうか？

次回も頑張りますので、応援よろしくお願い致します。

投稿者プロフィール

DBひとりでできるもん運営チーム

「DBひとりでできるもん」運営チームです。
「親しみやすさと技術力」をテーマに、技術情報・サービス・インフラ系資格取得に役立つ情報、社員等の情報をお届けします。
70名弱の事業部員で鋭意、執筆中です。
少しでも当社を知って頂けるよう、愛情込めて頑張ります！
※facebook、X(旧twitter)、インスタグラムでは「DBひとりでできるもん」の更新情報を発信しています。