SQL実行計画の変更　その１(2/2)

2018年12月7日『心臓外科医の術式』

1 前回のおさらい
2 パフォーマンス改善策は出来てるのに・・・
3 Enterprise Edition だから・・・

前回のおさらい

前回、「SQL実行計画の変更(1/2)」は、改善策があるのにアプリケーションに手を入れることができない！というところで終わりました。

さて、たろーちゃんはこのピンチをどう切り抜けるのでしょうか？

パフォーマンス改善策は出来てるのに・・・

たろー「パフォーマンス改善策は出来てるのに、まさかアプリケーションを修正することが出来ないとは・・・。なんて、もどかしい。」

少しずつ上昇していくCPU使用率。たろーちゃんとAさんは為す術無く、茫然とモニタを見つめるしかありませんでした。

Enterprise Edition だから・・・

たろー「待てよ！このOracle Database はEnterprise Edition だから、SPM が使えるぞ！」

Aさん「SPM？」

たろー「Sql Plan Managementの略で、SQLの実行計画を固定する機能さ。」

Aさん「どうやって使うの？」

たろー「まず、オリジナルのSQL(遅いSQL)のSQL_IDをベースラインに登録するんだ。」

var cnt1 number
EXEC :cnt1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '5mvq10ractufy');

col SQL_HANDLE for a20
col PLAN_NAME  for a30

SELECT SQL_HANDLE, PLAN_NAME
  FROM dba_sql_plan_baselines
;

SQL_HANDLE           PLAN_NAME
-------------------- ------------------------------
SQL_cb1bc8b8c8c0bbdc SQL_PLAN_cq6y8r34c1fywc3e9dce0

たろー「次に、ヒント句を指定したSQL(早いSQL)のSQL_IDを、さっきのベースラインに対してロードする。」

EXEC :cnt1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '221ugwxqzmkv3', sql_handle => 'SQL_cb1bc8b8c8c0bbdc');

SELECT SQL_HANDLE, PLAN_NAME
  FROM dba_sql_plan_baselines
;

SQL_HANDLE           PLAN_NAME
-------------------- ------------------------------
SQL_cb1bc8b8c8c0bbdc SQL_PLAN_cq6y8r34c1fywc3e9dce0
SQL_cb1bc8b8c8c0bbdc SQL_PLAN_cq6y8r34c1fyw4a863123

たろー「そして、オリジナルのSQL(遅いSQL)のベースラインを削除する。」

EXEC :cnt1 := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_cb1bc8b8c8c0bbdc', 'SQL_PLAN_cq6y8r34c1fywc3e9dce0');

SELECT SQL_HANDLE, PLAN_NAME
  FROM dba_sql_plan_baselines
;

SQL_HANDLE           PLAN_NAME
-------------------- ------------------------------
SQL_cb1bc8b8c8c0bbdc SQL_PLAN_cq6y8r34c1fyw4a863123

Aさん「これで、どうなるんだ？」

たろー「オリジナルのSQLを実行してみると分かるさ。」

経過: 00:00:00.06

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

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |    95 |    60   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE                     |               |     1 |    95 |            |          |
|   2 |   VIEW                              | VW_DAG_1      |     1 |    95 |    60   (4)| 00:00:01 |
|   3 |    HASH GROUP BY                    |               |     1 |    21 |    60   (4)| 00:00:01 |
|   4 |     VIEW                            | VM_NWVW_0     |     1 |    21 |    60   (4)| 00:00:01 |
|   5 |      HASH GROUP BY                  |               |     1 |   257 |    60   (4)| 00:00:01 |
|   6 |       NESTED LOOPS                  |               |       |       |            |          |
|   7 |        NESTED LOOPS                 |               |     1 |   257 |    59   (2)| 00:00:01 |
|   8 |         NESTED LOOPS                |               |     1 |   220 |    57   (2)| 00:00:01 |
|*  9 |          HASH JOIN                  |               |     1 |   194 |    55   (2)| 00:00:01 |
|* 10 |           TABLE ACCESS FULL         | PARA5         |     1 |    56 |     3   (0)| 00:00:01 |
|* 11 |           TABLE ACCESS FULL         | KEKKA         |  1006 |   135K|    51   (0)| 00:00:01 |
|* 12 |          TABLE ACCESS BY INDEX ROWID| PARA8         |     1 |    26 |     2   (0)| 00:00:01 |
|* 13 |           INDEX RANGE SCAN          | PARA8_KEY     |     1 |       |     1   (0)| 00:00:01 |
|* 14 |         INDEX RANGE SCAN            | SKIHON_KEY    |     1 |       |     1   (0)| 00:00:01 |
|* 15 |        TABLE ACCESS BY INDEX ROWID  | KIHON         |     1 |    37 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Note
-----
   - SQL plan baseline SQL_PLAN_cq6y8r34c1fyw4a863123 used for this statement

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

Aさん「ああ！ヒント句を埋め込んだ実行計画になってる！
実行に掛かっている処理時間も 0.06 秒だ！」

たろー「Note部分も見てみてよ。SPMが機能しているのが分かるだろう？」

これを契機にCPU使用率は５０％台まで低下し、無事にアクセスピークの２２時台を乗り切ることが出来ました。

Aさん「よかったぁ。こんな便利な機能があるんなら、もっと早く言ってよー。
たろーちゃんも人が悪いなぁ、はっはっは。」

たろー「何を言ってるんだ。本来ならアプリケーション開発の段階でしっかり性能テストをやっておけば、事前に分かったことだろ？！」

Aさん「はっはっは……。いや、申し訳ない……。」

アプリケーションに手を入れることが出来ないケースって、よくあるんですよね。
（特にパッケージ製品を使っている時とか）
そんな時は SPMの出番です！
ほんと、Enterprise Edition はチューニングの幅があって助かります。

今回の「心臓外科医の術式」はいかがでしたか？
アプリケーションに手を入れることなくパフォーマンス改善を行う、見事な「術式」だったのではないでしょうか？
次回も頑張りますので、応援よろしくお願い致します。

投稿者プロフィール

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

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