2021.03.08
SQLチューニング (第1回）
SQLチューニングのブログを、始めます。
今後このブログでは、EXEM韓国本社の技術者によるSQLチューニングの本の内容を、お伝えします。
今回は、ご紹介する内容の第一章になります。お楽しみください。次回は、全体像を章立て（目次）としてご紹介します。
それでは、SQLチューニングの世界をご堪能ください。
SQLチューニングの初めは？
SQLチューニングの開始は、SQLの意味(作成意図)を正しく把握するということだと考える。SQLの意味を正確に把握できないならば、原本SQLで抽出しようした結果集合体でない他の集合を抽出することもあるためである。 したがってこのような改善案は改善案ということはできない。 すなわち、原本SQLの作成意図を正しく把握しないで、単純にI/O発生量を減らしたり実行時間を短縮するためにヒントを乱発するのは非常に危険なことである。 したがってSQLの意味を把握することが性能問題を誘発するSQLに対する改善の開始だと話すことができる。
理解を助けるため、チューニング事例を用いて考察する。
以下に、特定の使用者がウェブ画面でメールの内容を確認する時使うSQLを示す。
SELECT *
FROM (
SELECT /*+ INDEX_DESC(A IDX_MBOX_SENDDATE) */
a.*,
rownum as rnum
FROM tbs_mbox a
WHERE userid = :b1
AND status = :b2
AND ROWNUM <= :b3
)
where rnum >= :b4 ;
select statement – choose- cost estimate:3
view
count stopkey
table access by index rowid :imsi.tbs_mbox
index range scan descending :imsi.idx_mbox_senddate
該当SQLはメール プログラムで使用され、同時間帯(特に、業務開始時間、09:00)に多数の使用者が使う特性がある。 それだけでなく、データを抽出する過程で発生する競合によりDBサーバー全般的な性能低下を誘発する悪性SQLである。
該当SQLは、INDEX_DESCヒントとROWNUM条件によりCOUNT STOPKEYの実行計画が樹立されている。 したがって全体データを全部処理をせずに、ROWNUM条件に該当する一部のデータだけ処理すると予想される。 そのため、実行計画上ではSQLの問題点を探すのが容易でない。 したがってインデックス構成とヒント使用が適切なのかに対する検討が必要となる。
インデックス構成は次のとおりである。
INDEX_NAME COLUMN LIST
—————– —————
idx_mbox_status userid, status
idx_mbox_senddate userid, senddate
インデックス構成だけで見れば、該当SQLは、IDX_MBOX_SENDDATEインデックスよりはUSER_ID,STATUSコラムで構成されたIDX_MBOX_STATUSインデックスを使うことが有利に見える。 したがってヒントやインデックスに対する知識がある読者ならば“なぜUSERID,STATUSコラムで構成されたIDX_MBOX_STATUSインデックスを使わなかっただろう？”という疑問がおこるだろう。
ところで、条件節に適合したインデックス構成だけ確認して、該当SQLの意味を把握しないまま性能改善をしようとするならば、次のような改善案を提案する可能性が高い。
“そうだインデックスの使い方を間違って、条件節にはUSERID,STATUSで照会になるが、USERID,SENDDATEで構成されたIDX_MBOX_SENDDATEインデックスを使っているじゃない。 ヒントに使われたインデックス名を変更すれば性能が良くなるんだね!”
ここでINDEX_DESCヒントのインデックス名だけを変更することで結論を導き出すならば、とても致命的な失敗を犯すことになる。 なぜなら、SQLの作成意図とは全く違うデータが抽出されることもあるためである。 もちろん、場合によってはUSERID,STATUSで構成されたインデックスを利用することが正しい方法でもある。 しかしこのような改善案は“USER_ID,SENDDATEコラムで整列が必要ない”という前提条件を満足しなければならない。 このような業務的な内容は開発担当者または、業務運営者に問い合わせする必要があって、そうでない場合ならば、SQL自体で作成意図を探してみなければならない。
では、該当SQLの作成意図を把握してみよう。
- ROWNUMを使ったのでPAGINATION QUERYである可能性が高い
- INDEX_DESCヒントはORDER BY DESCを代えようとすると見られる。
- インデックス コラム情報とヒントを考慮してみた時、意味上では“ORDER BY USER_ID DESC,SEND_DATE DESC”が内包されたと見られる。
前で把握したSQLの意味を通じて、抽出しようと思うデータは条件に満足する“最近発送された” ROWNUM <=:B3のメールのうちでRNUM >=:B4のメールというものが分かる。
したがって、INDEX_DESCヒントに内包された意味を解いてみれば、以下のようになる。
SELECT x.*
FROM (
SELECT a.* ,
rownum as rnum
FROM (
SELECT a.*
FROM tbs_mbox a
WHERE userid = :b1
AND status = :b2
ORDER BY a.userid desc,
a.senddate desc
) a
WHERE ROWNUM <= :b3
) x
where x.rnum >= :b4
select statement choose-cost : 3
view bizmax.(1) (“x”.”rnum”>=to_number(:z))
count stopkey (rownum<=to_number(:z))
view bizmax.(2)
table access by index rowid imsi.tbs_mbox(3)
index range scan descending imsi.idx_mbox_senddate(nu) (userid,senddate)
再作成したSQLを見れば、原本SQLでINDEX_DESCヒントのインデックス名だけを変更するのは正しいチューニング方法ではないということが分かる。 なぜなら、INDEX_DESCヒントに使われたIDX_MBOX_SENDDATEインデックスは条件に対する処理だけでなくORDER BY DESC役割も実行するためだ。 したがってSQLの意味を把握した後に導き出されることができる改善案中の一つはIDX_MBOX_SENDDATEインデックスにSTATUSコラムを追加して再作成することだ。 このように、SQLチューニングのために最も必要なことはSQLを正確に理解することである。 SQLの意味を把握してこそ正しいSQL改善案が導出されるだけでなく、時にはSQLの意味把握自体がSQL改善につながる場合もあるためである。
開発者やDBAの一部の方々は、SQLチューニングの世界に飛び込むのをかなり難しいと考えている。 なぜなら、“SQLチューニング”という（のは）用語自体が身近でないだけでなくSQLチューニングの開始点（チューニングをどこから開始したら良いのか？）を定めにくいためである。 しかし、 SQLの意味を解釈することから、すでにSQLチューニングが始まっているのである。
今後の内容で、SQLの正しい解釈を通した最適な性能を出すことができるSQL作成方法に対して一つずつ調べてみることにする。
（注）テスト中Oracleバージョンに対する言及がない場合、10g R2で実行されている。
（第一回 完）
いかがでしたか。今後このような、SQLのチューニング関するTIPSをどんどん掲載していきます。ご期待ください。次回は、今後のブログで取り上げる内容の全貌を、目次形式でお知らせします。
See You Soon Bye Bye ^-^
