背景
PLSQL経由で実行される特定の検索SQLのパフォーマンスが遅く、改善する必要があった。
そのSQLの特徴は以下
- Oracle11g以降
- PLSQLで実装されている
- SELECT文は数百行、10以上のテーブル結合がある
- バインド変数を利用している
結果
色々と調査した結果、PLSQL内の実行SQLにHint句を追加することで
パフォーマンスを改善することができた。
調査遷移
実際に実行されているSQLの確認
対象SQLはWebアプリケーションからPLSQLを経由して実行されており、
実際のSELECT文を取得することに少し手間がかかった。
最終的に以下の手順を用いて取得した。
--以下のSQLでSQL_IDと実行SQLの最初の数百バイトが取得できる
SELECT V$SQL.SQL_ID,V$SQL.SQL_TEXT,
V$SQL.LAST_ACTIVE_TIME
,USER_OBJECTS.OBJECT_NAME
,USER_OBJECTS.OBJECT_ID
,USER_OBJECTS.OBJECT_TYPE
FROM USER_OBJECTS
JOIN V$SQL
ON USER_OBJECTS.OBJECT_ID = V$SQL.PROGRAM_ID
ORDER BY V$SQL.LAST_ACTIVE_TIME DESC
/
--上記SQL結果の中から、SQL全文を取得したいSQLのsql_idで以下を実行
SELECT sql_text
FROM v$sqltext
WHERE sql_id = '上記SQLで取得したID'
ORDER BY piece
/
--取得した全レコードを結合すればSQL全文のできあがり。
確認した結果、対象のSQLは実行時にバインド変数を使用していることが分かった。
FUNCTION INDEXの追加
対象のSQLの検索条件には、日付を文字列化したものが含まれていた。
これでは日付のINDEXが使用されない。SQLを編集して日付型の検索条件にすることも可能と思われたが、日付を文字列化して条件にしている背景が不明だったので触らないことにした。
その代わり、日付を文字列化したものに対してFUNCTION INDEXを追加した。
FUNCTION INDEXとは、特定のカラムに何らかの処理を行った結果、また複数のカラムを結合した結果などに対してINDEXを作成する機能。
ファンクション索引とか関数インデックスと呼ばれたりもする。
CREATE INDEX IDX_TABLENAME_FC
ON TABLENAME (TO_CHAR("TARGET_DATE",'YYYYMMDD'))
/
バインドピーク機能の存在
前述のINDEXを作成し、対象のSQLを実行してみたが改善せず。
確認のため実行計画を確認したところ、作成したINDEXが使用されていなかった。
何が悪いのだろう、と色々試していると、バインド変数部分を実値に置換したSQLの場合はINDEXが使用されていることが分かった。
バインド変数を使用している場合の挙動を調査したところ、バインドピークという機能を知った。
http://www.oracle.com/technetwork/jp/articles/chapter4-2-100886-ja.html#p01e
バインドピーク機能とは、簡単に言うとSQLの実行前にバインド変数の中身を確認した上で実行計画を作成してくれる機能。
本来ならこの機能によって、適切な実行計画になるはずが、なっていない。
そうなっている原因は結局不明のままだった。
(SQLがかなり複雑なためか、PLSQL内での書き方が原因かなと予想している)
Hint句の使用
最終的な解決策としてHint句を使用した。
Hint句については以下を参照のこと
http://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements006.htm#sthref482
バインドピーク機能で適したINDEXが使用されていない事が分かったので、
どうにかして使用して欲しいINDEXが使用されるようにできないかと調査した結果、Hint句を使用するに至った。
以下のようにSELECTの直後に/*+から始まるコメントを挿入することで、
それがHint句として解析される。
SELECT /*+ INDEX(TABLENAME IDX_TABLENAME_FC) */
...
FROM TABLENAME
WHERE TO_CHAR("TARGET_DATE",'YYYYMMDD') >= :bind_variable
--以下略
上記のHint句を追加した結果、対象のSQLのパフォーマンスが改善した。
懸念事項
後々問題になるかもしれないなと感じたことと感想
バインドピークによる挙動
対象のSQLが実行される際のバインド変数の値で実行計画が変わりうる。
バインドピーク機能について詳しく調べたわけではないので、
これがどう影響するかがよく分かっていない。
Hint句による特定INDEX使用の強制
現時点では問題はないが、今後DBのデータが増えた結果、
指定INDEX以外のINDEXを使用した方がパフォーマンスが高くなる、という事が起こりえる。オプティマイザさんが仕事してくれるのが一番なのは間違いない。
複雑怪奇なSQL
数百行にも及ぶSQLだと、もはや何をやっているのかの把握すら難しい。
結合が多いと、どこがボトルネックになるかも判断がつかなくなってしまう。
仕様が許す限りシンプルなものがいいなと実感。
感想
Oracleにおけるバインド変数を使用したSQLの挙動と、バインドピーク機能について学んだ。
なにげにHint句を使用するのも初だった。
Hint句はなるべく使用を避けるべきだな、と考えている。