Posted at

SQLチューニング~お手軽に使えるヒント句まで

More than 3 years have passed since last update.


SQLチューニング

ORACLEでの経験を元に書いていますがコストベースのリレーショナルデータべースなら全般的に共通の考え方で対応できると思います。

SQLのチューニングといえば比較的容易に済むインデックスをとりあえず作成する。といった対応を取られがちですが、数万レコード程度でのデータ量ではあまり効き目がなく、またインデックスを乱立すると複数の条件がある時などにオプティマイザが誤作動を起こす要因ともなります。よって本当にインデックスがないことが原因なのか?を熟考する必要があります。

基本的にルールベースと違いコストベースになるのでオプティマイザによって作成される実行計画が望み通りの計画になっているのかを見ながらチューニングしていく必要があります。

今まで見てきた遅いSQLはとんでもない結合順や使用されるインデックスがなんでここから?といった実行計画になっていた事が大半でした。

そういう時に正しい実行計画をオプティマイザに作ってもらうようにSQLを試行錯誤し変更してチューニングしていく必要があります。なので絶対的な回答はありませんが下記のように手段を増やしていくのは有効な手だと思います。


具体的なチューニング手段


bindpeek機能の停止

bindpeekとはバインド変数へ与える値によって実行計画が変動させることです。

※11gからは「adaptive cursor sharing」という機能があるので止めなくてもいいかもしれない。値によって実行計画がしょっちゅう狂って大変。という時には試してみてください。

※隠しパラメータなので使用には注意してください

特に千万以上の大量データを扱うようなシステムではよく使われる手法だと思います。

ORACLE BindPeek機能を停止する隠しパラメータ

_optim_peek_user_binds

通常はTRUEでBindPeek機能有効 FLASEで無効になります


設定方法

alter system set "_optim_peek_user_binds"=false; 



確認方法(sysユーザにて実行)

select a.ksppinm "Parameter", a.KSPPDESC "Description", b.ksppstvl "Value“

from x$ksppi a, x$ksppcv b
where a.indx = b.indx and a.ksppinm like '%パラメータ名%';


exists、not exists


inでのサブクエリ

not inやin句を使用したサブクエリは可能な限り

exists、not existsを使用すること

indexが使用されやすい、全走査の必要がない


遅い

SELECT name 

FROM Personnel WHERE birthday
IN (SELECT birthday FROM Celebrities);


速い

SELECT P.name 

FROM Personnel AS P WHERE
EXISTS (SELECT FROM Clelebrities AS C WHERE P.birthday = C.birthday);


不要な結合、不要なdistinct

あるテーブルにあるかどうか、ないかどうかを実現するためだけにjoinをして

結果をdistinctするSQLをみたことがありますが、大変無駄なのでexists、not existsを使用すること。


遅い

SELECT a.name 

FROM shain a,
(select distinct shain_id from sikaku where sikaku_name in (‘基本情報’,’java’) )b
Where a.shain_id = b.shain_id


速い

SELECT a.name

FROM shain a
Where exists
(select ‘X’ from sikaku b where sikaku_name in (‘基本情報’,’java’) and
a.shain_id = b.shain_id)


左辺の関数

条件では列側に関数はかまさない。

indexが使用されない。

Birthdayがdate型(値には日付までしか入れていない)


遅い

SELECT a.name 

FROM shain a
Where to_char(a.birthday,’YYYY/MM/DD’) = ‘2014/03/21’


速い

SELECT a.name

FROM shain a
Where a.birthday = to_date(‘2014/03/21’ ,’YYYY/MM/DD’)

そもそもプログラム側でdate型にしておくべき。
更に言えばdate型は秒まで管理しないのであれば、使いにくいので使わない方が良い。



暗黙の型変換


暗黙の型変換はNG

× SELECT * FROM SomeTable WHERE col_1 = 10;

○ SELECT * FROM SomeTable WHERE col_1 = '10';
○ SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

※そもそもプログラム側でcharにしておくべき。
最近はバインド変数なのでこういったミスはあまりないと思うが。



in句

IN 述語の引数リストには、最もありそうなキーを左寄せする

※これによって劇的に早くなり、他の手ではどうしようもない場合。

これによってソースの可視性や保守性が落ちるなら他の手を考えるべき


遅い(かもしれない)

SELECT * FROM Address WHERE prefecture IN ('鳥取', '徳島', '東京', '大阪');



速い(かもしれない)

SELECT * FROM Address WHERE prefecture IN ('東京', '大阪', '鳥取', '徳島'); 



indexについて

indexについてはとりあえずはっとけばいいだろうとか言う大きな勘違いしている方が

多々いますが大きな間違いです。indexを貼る時は、細心の注意を払って。

とりあえず外部キー全てにindex貼っておくといった事はしないように。

→オプティマイザが誤動作し、変な実行計画を作ってしまう。

よく誤解している人がいるが、

Shainテーブル(bumon_id,sisha_id)とあり、両方にindexが貼られていたとしても

bumon_idをインデックススキャン、絞った後のデータをsisha_idで

インデックススキャンといった事は出来ない。

あくまでもbumon_idのindex range scan後、

結果に対してsisha_idで抽出という事になる。

どちらの列もindexを効かせたければ連キーを貼るしか無い。

ただ、連キーを貼るとsisha_idだけで検索があった場合など、

index skip scanになってしまう。

上記のようにindexの設計は業務をよく知り、練っていく必要がある。


不可視インデックス

オプティマイザから見えないようにする事が出来る。

ある特定のSQLだけはこのindexを使いたいが他のSQLには影響を与えないようにという時に使える。

alter index in_res_guest invisible; 

select /* INDEX (res IN_RES_GUEST) */ res_id
from res where guest_id = 101;


ヒント句

どうしてもオプティマイザが言う事を聞いてくれない!!という時はヒント句を使うと解決する事が多いです。ヒント句を指定する事により実行計画をある程度操作する事ができます。

ヒント句一覧


特に有用なヒント句

実行計画が大幅に狂う時は結合順、結合方法が間違っているときが多いのでそれを下記ヒント句でただすことができます。


LEADINGヒント

SELECT /* LEADING(e j) */ * FROM employees e, departments d, job_history j WHERE e.department_id = d.department_id AND e.hire_date = j.start_date; 



USE_NLヒント

SELECT /* USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity FROM orders h, order_items l WHERE l.order_id = h.order_id; 



その他豆知識

●union より union all

重複行を排除するためのソートが発生するので、重複を許すならunion all

●withを使用すると早くなるとか言う人がいるが大きな間違い。

早くなりません。

●viewも同じく。

マテリアライズドビューなら早くなるが、MVは最終手段だと思っといた方が良い。

●is nullはなるべく使わない。

●not in、!=、<>などはなるべく使わないように。

not exists、orやinで代用できるならする。工夫しても無理なら使ってもよいと思う。

とは言ってもindexが貼られているようなキーでこういった事はしないだろう。

以上