前回のおさらい

前回、「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使用率は50%台まで低下し、無事にアクセスピークの22時台を乗り切ることが出来ました。

 

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

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

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

 

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

 

 

 

 

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

投稿者プロフィール

たろーちゃん
たろーちゃん
株式会社システムサポート インフラソリューション事業部に在籍するPlatinumホルダー。
Oracle Databaseのパフォーマンスチューニングを得意とする。
データベースは Oracle 以外興味がないという変わり者。
一番嫌いなエラーメッセージは CRS-02625。
連載「心臓外科医の術式」を執筆。