SELECT文のSQLチューニング方法まとめ

本日はORACLEデータベースにおけるSQLチューニング【SELECT文】を紹介します。

【内容】
 1.インデックスの作成
 2.ヒントの追加
 3.パラレル実行
 4.SQLの書き換え
 5.バッファキャッシュの活用

1.インデックスの作成

SQLのチューニングを行うときに、最も簡単で、最も効果の出やすい、オーソドックスなやり方です。
インデックスはSQLの変更も必要ないので、比較的容易にチューニングが可能です。

一番簡単なのは、SQLのWHERE句の条件に書かれている抽出条件の項目をインデックスにすることです。

例えば、次のようなケースです。
select * from table1 a where a.col1 = :cond;

このSQLを実行した場合、実行計画が次のように、テーブル全体を読み込むフルスキャンになる場合があります。
これは非常に非効率なアクセスパスです。
–TABLE ACCESS FULL table1

そこで、データを効率良く絞り込むために、col1の項目にインデックスを作成します。

【インデックスの作成方法】
CREATE INDEX IX_table1_001
ON USER1.table1(col1) TABLESPACE index_tbs;

といった具合に、インデックスを作成します。

インデックスを作成することで、次のような実行計画に変わります。
col1の項目で、効率良くデータを絞り込むことができ、パフォーマンスの向上が狙えます。
–TABLE ACCESS BY INDEX ROWID table1
—-INDEX RANGE SCAN IX_table1_001

インデックスと作成する項目の適切な順番については、「インデックスで指定する項目の順番」で紹介していますので参考にしてください。

2.ヒントの追加

SQLは狙い通りに、効率良く実行されないケースがあります。
例えば、次のようなケースです。
 ・インデックスを作成したけど、インデックスが使われない
 ・テーブルの結合順序が狙い通りにならない
 ・たまに遅いときがある
 ・急に遅くなる時がある

原因は多岐に渡ります。例えば、次のようなことがあります。
 ・統計情報が最新でない
 ・SQLが複雑すぎるあまり、適切な実行計画が作成されない
 ・データの増減によって、実行の都度、実行計画が変わってしまう

こういったケースでは、ヒントが有効です。
ヒントを書き足すことで、実行計画をある程度、狙い通りに変更することが出来ます。
※ヒントは名前の通り、”ヒント”ですので、絶対に変更される訳ではありません。

いくつか、ヒントのサンプルケースを紹介します。

・作成したインデックスが使われない場合のヒント
select /*+INDEX(a IX_table1_001)*/ * from table1 a where a.col1 = :cond;

・FROM句に書いたテーブルの順番で結合したい場合のヒント
SELECT /*+ORDERED*/ a.* FROM TAB1 a,TAB2 b WHERE a.COL1 = b.COL1

・テーブル結合の順序を任意に決めたい場合のヒント
SELECT /*+LEADING(b,a)*/ a.* FROM TAB1 a,TAB2 b WHERE a.COL1 = b.COL1

・テーブル同士をネステッドループで結合したい場合のヒント
SELECT /*+USE_NL(a b)*/ a.* FROM TAB1 a,TAB2 b WHERE a.COL1 = b.COL1

・テーブル同士をハッシュジョインで結合したい場合のヒント
SELECT /*+USE_HASH(a b)*/ a.* FROM TAB1 a,TAB2 b WHERE a.COL1 = b.COL1

※もちろん、複数のヒントを同時に利用することもできます。
・ORDEREDヒントとUSE_NLを同時に利用した場合
SELECT /*+ORDERED USE_NL(a b)*/ a.* FROM TAB1 a,TAB2 b WHERE a.COL1 = b.COL1




3.SQLの書き換え

SQLの書き方は自由度が高く、結果として同じデータを抽出するSQLでも、書き方が異なるケースがあります。
そういった場合は、出来るだけ、効率よく、高速にデータを抽出できる書き方に変更しましょう。

ここでは、代表的なものを紹介します。

・IN句をEXISTS句に置き換え
(変更前)
select * from table1 a where a.div_cd in (select b.div_cd from table2 b where b.div_name = ‘IT’);

(変更後)
select * from table1 a
where exists (select ’x’ from table2 b where b.div_name = ‘IT’ and a.div_cd = b.div_cd);

・OR句の置き換え
(変更前)
select * from table1 a
where a.div_cd = ‘100’ OR a.div_cd = ‘200’;

(変更後)
select * from table1 a where a.div_cd = ‘100’
union all
select * from table1 a where a.div_cd = ‘200’;

・IN句の置き換え
(変更前)
select * from table1 a where a.div_cd in (‘100′,’200’);

(変更後)
select * from table1 a where a.div_cd = ‘100’
union all
select * from table1 a where a.div_cd = ‘200’;

4.パラレル実行

続いては、データベースサーバーのリソースを最大限に活用してチューニングする方法です。リソースに余裕があるのであれば、ORACLEのパラレル機能を活用してください。

これはセッション単位もしくはSQL単位に実行することができ、ORACLEデータベースの内部的プロセスを使って、SQLでのデータ抽出を並列実行することが出来ます。

※一歩間違えるとデータベースサーバー全体のリソースを消費しトラブルになりかねないので、お近くの上位SEやDBAに相談の上、利用してください。

・セッション単位
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4 ;

・ヒント
SELECT /*+ PRALLEL(4) */ a.* FROM TAB1 a,TAB2 b WHERE a.COL1 = COL2

5.バッファキャッシュの活用

例えば、データの変更はほとんど発生しないが、オンライン処理などで何度も呼び出されるようなマスタなどは、予め、キャッシュにインプットしておきます。そして、キャッシュからアウトプットされないように、設定しておくことができます。

具体的には、バッファキャッシュ領域のうち、いくらかをKEEPバッファキャッシュ領域として割り当てます。
そして、対象となるテーブルの属性をKEEPにします。後は事前にSELECT文を実行するなどして、キャッシュにインプットします。
※こちらもデータベースサーバのキャッシュサイズに関わる変更なので、導入検討する場合はDBAに相談してください。
 システムによっては、JAVA側でCACHEする仕組みもあると思います。必ずしもデータベース側での対応が必須ではありません。

・KEEPバッファキャッシュの指定
alter system db_keep_cache_size 5G

・テーブル属性の変更
ALTER TABLE tab1 BUFFER_POOL KEEP
※デフォルトに戻す場合は、ALTER TABLE tab1 BUFFER_POOL DEFAULT

まとめ

SELECT文のチューニング方法は多岐に渡ります。
今回は代表的なものを紹介しました。ぜひ参考にして、SQLチューニングを実践し経験値を増やしてください。

Oracle SQLチューニング パフォーマンス改善と事前対策に役立つ (DB selection) [ 加藤祥平 ]

価格:2,808円
(2018/11/19 19:23時点)

SELECT文のSQLチューニング方法まとめ” に対して1件のコメントがあります。

この投稿はコメントできません。