かなり前にOracle9iR2環境で、IN句を含んだSQLの性能問題に遭遇した時のことをふと思い出したので、そのときのSQLの実行計画を再現させて、色々調べてみた。
1.前提
テーブル定義やSQL文の詳細までは覚えていないことから、問題となったSQLの実行計画部分の再現重視の前提(ストーリー)となっていることで、そもそもそれはxxxなんじゃないかという様なツッコミたくなる点があるかもしれないが、その点についてはご容赦頂きたい。
問題となったSQLがアクセスしていたテーブルは、2桁のchar型のcode列に数字を格納している、下記の様なパーティションテーブルで、code列にはローカル索引も構成されていた。
このテーブルに対して、code列に'10'～'19'を格納しているレコードを取得する、下記の様なSQLを実行したところ、実行時間が長いことが問題となった。
SELECT * FROM tab11
WHERE code IN ('10', '11', '12', '13', '14', '15', '16', '17', '18', '19') AND id = 123456
2.検証方法
当該パーティションテーブルの'10'～'19'を格納するパーティションに100万件のレコードを格納し、SQLの実行計画を確認する。
Oracle Database9iR2の環境を用意できなかったため、Oracle Database 19c on Linux x86-64環境で、初期化パラメータoptimizer_features_enableを'9.2.0.8'を設定した状態で、SQLの実行計画を確認する。
SQLの実行計画は、アクセスブロック数など見たかったので、SQLトレースで取得する。SQLトレースを用いた実行計画の取得手順は以下。
-- オプティマイザの動きを9iR2にする SQL> ALTER SESSION SET optimizer_features_enable = '9.2.0.8'; -- SQLトレースの取得開始 SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; <<実行計画を取得したいSQLの実行>> -- SQLトレースの取得終了 SQL> ALTER SESSION SET EVENTS '10046 trace name context off'; -- SQL*Plusの終了 SQL> exit -- トレースファイルの確認 ※占有環境なので最新のトレースファイルにSQLトレースが出力されている想定 $ ls -altr <初期化パラメータdiagnostic_dest>/diag/rdbms/<DB名>/<SID名>/trace/*_ora_*.trc |tail -n 20 -- tkprofによるSQLトレースの成型 $ tkprof <上記で確認したトレースファイル名> <出力ファイル名>
3.検証結果1
SQLトレースの取得結果は、以下の通り。
SELECT *
FROM
tab11 WHERE code IN ('10', '11', '12', '13', '14', '15', '16', '17', '18',
'19') AND id = 123456
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.38 0.39 0 90466 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.38 0.39 0 90466 0 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 77
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 INLIST ITERATOR (cr=90466 pr=0 pw=0 time=219427 us starts=1)
1 1 1 PARTITION RANGE ITERATOR PARTITION: KEY(INLIST) KEY(INLIST) (cr=90466 pr=0 pw=0 time=375201 us starts=10 cost=4 size=59 card=1)
1 1 1 TABLE ACCESS BY LOCAL INDEX ROWID TAB11 PARTITION: KEY(INLIST) KEY(INLIST) (cr=90466 pr=0 pw=0 time=375073 us starts=10 cost=4 size=59 card=1)
1000000 1000000 1000000 INDEX RANGE SCAN IDX02TAB11 PARTITION: KEY(INLIST) KEY(INLIST) (cr=1977 pr=0 pw=0 time=2152245 us starts=10 cost=3 size=0 card=5)(object id 31149)
INLIST ITERATORとなっていることから、IN句に書かれた条件の数(10個)だけ、パーティションに対するインデックススキャンが繰り返された結果、アクセスブロック数が90466ブロックになったと考えられる。
試しに条件の数を1個にした場合のアクセスブロック数が、以下の通り9049ブロックで、およそ1/10のアクセスブロック数であることからも、条件の数だけインデックススキャンが繰り返されていたと言えるだろう。
SELECT *
FROM
tab11 WHERE code IN ('10') AND id = 123456
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.10 0.10 0 9049 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.11 0.11 0 9051 0 0
このSQLの実行計画だが、INLIST ITERATORで条件の個数だけスキャンを繰り返すより、該当パーティションをフルスキャンさせてしまった方が効率的に思えた。
char型で10～19に相当する数字を表す文字列を拾いたかったので、WHERE句で「code IN ('10', '11', …, '19')」という書き方をしていて、この考え方に問題はないと思っているが、業務(AP)特性からcode列には数字が入ってくることはないことと、その前提でパーティション自体もリストパーティションではなく、レンジパーティションで定義されていることもあったので、下記の様なIN句を使用しないSQLにしてもよいのではないかと考えた。※FULLヒントを付けるという手もあるとは思いますが…
SELECT * FROM tab11 WHERE code >= '10' AND code < '20' AND id = 123456
4.検証結果2
IN句を使用しないSQLのトレース結果は以下の通り。予想通りINLIST ITERATORから対象パーティションのFULL SCANに実行計画が変わっており、アクセスブロック数も約1/10に削減された。
SELECT * FROM tab11 WHERE code >= '10' AND code < '20' AND id = 123456 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.07 0.07 0 8853 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.08 0.08 0 8853 0 1 Misses in library cache during parse: 1 Optimizer mode: CHOOSE Parsing user id: 77 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 PARTITION RANGE SINGLE PARTITION: 2 2 (cr=8853 pr=0 pw=0 time=13795 us starts=1 cost=1363 size=59 card=1) 1 1 1 TABLE ACCESS FULL TAB11 PARTITION: 2 2 (cr=8853 pr=0 pw=0 time=13781 us starts=1 cost=1363 size=59 card=1)
char型だからと律義な条件指定に捕らわれないことで、良好な結果が得られることもあるんだという事を経験できた、SQLの性能問題であった。
5.検証結果(おまけ)
今回再現検証した際に思い付いたのだが、Oracleのバージョンが上がるに連れてオプティマイザは改良されているので、今のバージョンでも当時の事象が再現するか検証してみた。先程までの検証では設定していたoptimizer_features_enableを設定しない(= 19.1.0)でSQLトレースを取得してみた。
SELECT *
FROM
tab11 WHERE code IN ('10', '11', '12', '13', '14', '15', '16', '17', '18',
'19') AND id = 123456
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.02 0 8852 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.02 0 8852 1 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 77
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 PARTITION RANGE INLIST PARTITION: KEY(INLIST) KEY(INLIST) (cr=8852 pr=0 pw=0 time=3587 us starts=1 cost=2437 size=59 card=1)
1 1 1 TABLE ACCESS FULL TAB11 PARTITION: KEY(INLIST) KEY(INLIST) (cr=8852 pr=0 pw=0 time=3578 us starts=1 cost=2437 size=59 card=1)
なんと、INLIST ITERATORにならずに、対象パーティションを1回フルスキャンして終わるSQLの実行計画になることが確認できた。Oracleのバージョンがあがったことで、オプティマイザが賢くなっていたようだ。
ちなみに、optimizer_features_enable = '10.1.0でも、この実行計画になったので、かなり早い段階で、オプティマイザは賢く実行計画を選択してくれるように成長していたようだ。
そう考えると、今回の様な事象を今後見かけることは殆どないのかもしれない。もし不幸にも遭遇してしまった場合は、参考にしてもらえればと思う。
次の記事：OCIのOracle Base Database Serviceを構築してみた ～その1～
