複雑すぎるSQL(1/2)

2019年1月30日『心臓外科医の術式』

1 前回のおさらい
2 アプリケーション開発者からの相談
3 問題のSQL

前回のおさらい

前回『SQL実行計画の変更その2』では、プランスタビリティでヒント句を埋め込むことができないディクショナリビューの実行計画を変更する方法についてお話しました。

どんな内容だったかなぁ？というかたは、上記リンクから改めて確認してくださいね。

アプリケーション開発者からの相談

システムの「心臓」こと、Oracle Databaseに日々携わるたろーちゃん。
たろーちゃんが休憩室の自販機に缶コーヒーを買いに行くと、アプリケーション開発者のAさんが頭を抱えてソファーに座っていました。
何かあったのでしょうか？

たろー「お疲れ。どうかしたの？」
Aさん「ああ、たろーちゃん。実はいつものWEBシステムで性能テストをしてるんだが、SQLの結果が返ってくるまで１６秒くらい掛かって、困ってるんだ。」

※Aさんのシステムについては以前の『正体不明のパフォーマンス悪化の謎を解き明かせ！』の回を参照して下さい。

たろー「WEBシステムで１６秒？致命的な遅さだな。」
Aさん「だろう？」
たろー「大変だなぁ。じゃあ頑張って。」
Aさん「…いやいや、待って待って。
同僚がこんなに困ってるのに、君は見捨てていくのかね？この人で無しめ。」
たろー「だって、Aさんのシステム。いつも大変なんだもん…。」
Aさん「そこをどうか！この通り！」

腐れ縁という奴でしょうか。
たろーちゃんは、Aさんに詳しく話を聞くことにしました。

問題のSQL

Aさん「見て驚くなよ？これが問題のSQLだ……。」
たろー「………………………なんじゃ、こりゃあ。」

問題のSQLはなんと３,０００行を超える長さでした。
数多くのテーブルが結合されており、複雑な処理をしています。
アプリケーションの仕様を知らないたろーちゃんには、なんの処理を行っているのか？どこをどう直せばいいのか？サッパリ分かりませんでした。

たろー「Aさん、このSQL、複雑すぎるよ。もっと簡単にならないの？？」
Aさん「簡単になったら、苦労しないよ……（泣）」
たろー「……とりあえず、実行計画を見てみるか。」
　

----------------------------------------------------------------------------
| Id  | Operation                                             | Name       
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                      |            
|*  1 |  VIEW                                                 |            
|*  2 |   WINDOW SORT PUSHED RANK                             |            
|   3 |    VIEW                                               |            
|   4 |     HASH UNIQUE                                       |            
|*  5 |      HASH JOIN                                        |            
|*  6 |       HASH JOIN                                       |            
|   7 |        NESTED LOOPS                                   |            
|   8 |         NESTED LOOPS                                  |            
|   9 |          NESTED LOOPS                                 |            
|* 10 |           INDEX RANGE SCAN                            | C_COUNT     
|* 11 |           TABLE ACCESS BY INDEX ROWID                 | T_TUSER    
|* 12 |            INDEX RANGE SCAN                           | PK_TUSER   
|* 13 |          TABLE ACCESS BY INDEX ROWID                  | T_TUSERINF 
|* 14 |           INDEX RANGE SCAN                            | PK_TUSERINF
|* 15 |         INDEX RANGE SCAN                              | PK_TMAIL   
|  16 |        VIEW                                           |            
|* 17 |         CONNECT BY NO FILTERING WITH START-WITH       |            
|  18 |          INDEX FAST FULL SCAN                         | I_TOR      

＜以下、延々と続く・・・＞

　

たろー「うげぇ……。どこがボトルネックなのか、サッパリ分からない。」
Aさん「たろーちゃんでも無理か？」
たろー「せめて、アプリケーションの仕様が頭に入っていれば、どうにかなるかもしれないけど、いきなりSQLだけ見せられても、こうも長くちゃなぁ……。」
Aさん「そりゃそうだよなぁ…。」
たろー「……。SQLトレースを取ってみるか。」
　

＜中略＞

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.09       0.12          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     15.46      15.98          0    9698940          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     15.55      16.10          0    9698940          0           1

＜中略＞

　

たろー「フェッチに時間が掛かってるなぁ……。あれだけ複雑なSQLなのに、返すのは、たった１行なのか。」
Aさん「そうだ。そういう処理になってる。」
たろー「…試しにマテリアライズド・ビューを作ってみるか。」
Aさん「マテリアライズド・ビュー？」
たろー「ああ。簡単に言うと、普通のビューと違って実体を持っているビューだ。元テーブルの情報をキャッシュしてるようなイメージさ。でも…」
Aさん「でも？」
たろー「色々と制約が厳しいんだ。………ああ、やっぱりだめだ。こうも複雑なSQLだと何かしらの制約に引っかかってマテリアライズド・ビューが作成できないことが多いんだ。」
Aさん「えー、いい方法が見つかったと思ったのに。残念。」
たろー「うーむ、どうしたらいいかなぁ…。」
Aさん「 AWR でもダメなのか？」
たろー「いや、もうSQLトレースまで取得してあるだろ？」
Aさん「 SPM でもダメなのか？」
たろー「…。」
Aさん「プランスタビリティでもダメなのか？」
たろー「お前なぁ…。さっきから知ってる単語並べてるだけだろ？」
Aさん「だって、今までそれらで解決してきたじゃんかよ。」
たろー「無理な物は無理だよ。」
Aさん「あー、もう！どうして殆ど更新が掛からないテーブルばかりなのに、毎回SELECTしなきゃならないんだよ！」
たろー「………ん？殆ど更新が掛からない？」
Aさん「ああ。更新タイミングは不定期なんだけど、どのテーブルも殆ど更新しないんだ。」
たろー「じゃあジョブで定期的に、このSQLの結果を格納するテーブルを別に用意しておけばいいんじゃないか？」
Aさん「それも考えたんだけど、いつ元テーブルに更新が掛かるか分からないんだよなぁ。御客様が管理者画面から操作したタイミングでマスタに更新が掛かるんだけど、それをエンドユーザ様側の画面に瞬時に反映してくれないと困るんだ。」
たろー「じゃあ、管理者画面のアプリケーションを修正して、御客様がマスタを更新するたびに、このSQLを実行して別テーブルに格納すればいいんじゃないか？」
Aさん「いや、管理者画面のアプリケーションを修正するとなると、修正箇所が多すぎるんだ。何せこの数の結合テーブルだろ？画面数が多すぎて、とても予算内に収まらないんだよ。」

まーた予算かよ…。

たろー「…アプリの詳しい事情は分からないけど、即時反映が必須っていう仕様なんだな？」
Aさん「そうだ。」
たろー「更新タイミングは不定期だけど、更新頻度は低いんだな？」
Aさん「うん。更新頻度は、月に１回くらいだと思う。」
たろー「複雑なSQLだけど、返すのは毎回１行だけなんだな？」
Aさん「そういう仕様だ。」
たろー「うーむ…。それならば、『提案』が無い訳じゃない。」
Aさん「なに？本当か？！」
たろー「その代わり、『１００点満点な提案』じゃないぞ。」
Aさん「何でもいい。言ってみれくれ！」

たろーちゃんは、どのような提案をするのでしょうか？

投稿者プロフィール

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

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