SQLチューニング - ForeFrontier

SQLチューニング詳細
作成日：２０１３年１１月10日
作成者：石川
易
難
本課題の目的
データベースシステムの仕組みと動きを理解し、正しいチューニ
ング方法を理解する事を目的とする。
本課題では、Oracleを中心に説明しているが、理論や考え方は
どのRDMBSでも通用する。
本課題の範囲
本来は、SQLチューニング詳細が目的でしたが、RDBMSの動き
を理解せずサイトに書かれているSQLテクニックをただコピーし
ているだけの人が多い為、今回はSQLを処理する仕組み（内部
の動き）について説明し、今後説明するSQLチューニングへの
土台作りをしようと思います。
問題
以下の中でチューニング効果が高い順は？
（選択）
１．テーブル構造のチューニング
２．SQLのチューニング
３．メモリのチューニング
４．ディスクI/Oのチューニング
５．OSのチューニング
答え
チューニング効果と費用効果は、一般的に以下のように言われる。
効果・リスク・コスト
大
テーブル構造のチューニング
SQLのチューニング
メモリのチューニング
ディスクI/Oのチューニング
OSのチューニング
効果・リスク・コスト
小
テーブル構造のチューニング①
最も効果があるのは、テーブル構造の変更である。
• テーブルのシンプル化
• データの分割
• データの事前集計
テーブル構造のチューニング②
■テーブルのシンプル化
複数のテーブルを結合する複雑なSQLや副問い合わせを駆使してデータ
を検索するSQLは、複雑さの分だけデータベースも解析とデータ取得に時
間が掛かる可能性がある。
■データの分割
データ件数が増加したり、列（カラム）数が極端に多くなる場合は、テーブ
ルを分割する事で効率良くデータを取得できる可能性がある。
■データの事前集計
データ件数の増加により集計処理が時間が掛かる場合は、バッチ処理な
どで事前に集計し、集計結果を検索する事でパフォーマンスが向上する。
テーブル構造のチューニング③
テーブル構造のチューニングは、本番システム稼動後や
データ蓄積後に行うとプログラムの改修範囲が多く、テスト工数
増加によるコスト増加や時間が掛かる。
また、完成するまで全体的な効果が実感できないという点も大
きなリスクとなり得る。
⇒効果が最も大きいが、リスク・コストも大きい。
SQLのチューニング
次に効果があるのは、SQLのチューニングで比較的に短い期
間でパフォーマンスが改善できる。
SQLは同じ結果を求めるSQLであっても書き方により何倍も
違った効果が得られる為、開発現場では、間違った使われ方が
多くチューニングとして最も多く行われる。
⇒比較的リスクが小さく、大きな効果が得られる。
メモリ・ディスクI/O、OSのチューニング
これらのチューニングはアプリケーションの改修を一切行わず
アプリケーション全体のパフォーマンスの向上が見込めるが、
実際に実施するまで効果が表れない。
また、これらのチューニングだけで目標を満たす事は稀（何倍
の効果が得られる事はない）ので、前述のチューニングが前提
となるケースが多い。
⇒リスクは小さいが、効果も小さい。
SQLのチューニングのポイント
RDBMSのアーキテクチャと仕組みを説明したいが、それこそ書
籍レベルのボリュームとなる為、ここではポイントだけを記述
します。
•
•
•
•
SQLの内部処理
オプティマイザと実行計画
アクセス方法
テーブル結合
SQLの内部処理①
SQLを実行するとRDBMSは、以下の流れで処理する。
アプリケーション
RDBMS
プログラム
①解析
SELECT * FROM TBL_XXX
②実行
③フェッチ
SQLの内部処理②
① 解析
構文チェックや対象テーブルなどの権限チェックを実施。
解析後はライブラリ・キャッシュに格納される。同一のSQLが
キャッシュ存在すれば、これらのチェックはキャンセルされる。
つまり、SQLをキャッシュされるように心掛ければ、この処理
は行われない。
・バインド変数を使ったSQLを使用。
・SQL記述の統一化。似たようなSQLを多く作らない。
SQLの内部処理②
② 実行
Oracleは、オプティマイザを使って最も高速に処理できる方法
を導き出し、実行計画を作成する。
その後、実行計画をSQLを実行し、データ取得処理を行う。
前述の①解析でキャッシュされていれば、キャッシュされてい
る実行計画が使用される。
旨くキャッシュさせる事で、データディクショナリへの再帰SQL
実行がなくなる為、CPU使用率の軽減、メモリ使用量削減が
実現できる。
SQLの内部処理③
③ フェッチ
SQL結果セットからデータを取得する。
Oracleはデータを取り出す際に最初にデータベース・バッファ・
キャッシュ内を検索し、キャッシュに存在しなければハードディ
スクからデータを取り出す。
データベース・バッファ・キャッシュのヒット率を高めるようにす
る事で、低速なハードディスクアクセスを減らす事ができる。
SQLの内部処理④
（内部のイメージ図）
RDBMS
①解析
SGA
共有プール
②実行
③フェッチ
ライブラリ・キャッシュ
データディクショナリ
キャッシュ
データベース・バッファ・キャッシュ
ハードディスク
オプティマイザとは？
SQL実行時にどのようにSQLを実行するかを考える頭脳。
オプティマイザには「RBO」「CBO」の２種類ある。
種類
RBO
（ルールベースオプ
ティマイザ）
CBO
（コストベースオプ
ティマイザ）
特徴
メリット
デメリット
予め決められたルー
ルを元に索引、テー
ブル結合処理を実施
して実行計画を立て
る
データの増減に左右
されずに安定した実
行計画を実施できる
データ件数やデータ
の偏りにより最適な
実行計画が実施でき
ない
Oracleの新機能を利
用できない
テーブル行数やブ
ロック数、値の分布
などのデータ情報
（統計情報）を元に実
行計画を立てる
データ件数やデータ
の偏りにより最適な
実行計画が実施でき
る
統計情報が古いと最
適な実行計画が立て
られない
ある日、突然パ
フォーマンスが悪くな
る事もある
質問
CBOを使っていて「ある日、突然パフォーマンスが悪くなる」時っ
て、どういうケースで発生するの？
答え
統計情報は、１日１回スケジューラーによって自動収集される。
この統計情報の収集の実施後、極端にデータの分布が変わる
ようなSQL実施を行うと、前日まで最適だったSQLが最適でなく
なるため、パフォーマンスが悪くなることがある。
⇒TRUNCATE後やIMPORTによる大量データ投入など
解決方法としては、手動統計収集や常に大量データを集計す
るような夜間バッチの場合、ヒント句を使ったRBOを利用するこ
とで統計情報に左右されないSQLにする。
実行計画とは？
オプティマイザによって導きだされたSQLを実行する為の指示書。
SQL> select seq, text1 from tbl_heavy;
1000000行が選択されました。
実行計画
---------------------------------------------------------Plan hash value: 3031089632
------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------| 0 | SELECT STATEMENT |
| 1063K|
65M| 20950
(1)| 00:04:12 |
| 1 | TABLE ACCESS FULL| TBL_HEAVY | 1063K|
65M| 20950 (1)| 00:04:12 |
-------------------------------------------------------------------------------
SQL> select seq, text1 from tbl_heavy where seq = 10000;
大部分を取得
するので
FULL SCAN
create table tbl_heavy (
seq number,
text1 varchar(100),
text2 varchar(100),
text3 varchar(100),
text4 varchar(100),
text5 varchar(100),
text6 varchar(100),
text7 varchar(100),
text8 varchar(100),
text9 varchar(100),
text10 varchar(100),
primary key( seq )
);
TBL_HEAVY
（１００万件）
実行計画
---------------------------------------------------------Plan hash value: 901822958
------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT
|
|
1 |
65 |
1
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBL_HEAVY |
1 |
65 |
1
(0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN
| SYS_C004051 |
1 |
|
1
(0)| 00:00:01 |
-------------------------------------------------------------------------------------------
索引が使える
から
INDEX SCAN
アクセス方法①
アクセス方法とはオプティマイザが指示するデータの取得方法。
• フルテーブルスキャン
• ROWIDアクセス
• 索引（インデックス）スキャン
– B*Tree索引
– ビットマップ索引
– ファンクション索引
• 全索引スキャン（高速全索引スキャン）
アクセス方法②
【フルテーブルスキャン】
テーブルにある全ての行をブロック単位でHWM（最高水位標）まで読み続
ける。
ブロックとはＯｒａｃｌｅが読み込む最小単位で１ブロック内に複数行レコードが
格納されている。フルスキャンではマルチブロックアクセスが行われるため、
大量のデータアクセスでは最も速い。
但し、大量データを読み込む必要がある時には有効だが、例えデータが１
件しかなくてもHWMの位置までデータブロックを読み続けるので注意が必
要！
HWM（High Water Mark）とは
（ＨＷＭのイメージ）
1行目
テーブル
2行目
データが入っている部分
･･･
･･･
･･･
･･･
･･･
･･･
以前はデータが入っていたが、
既にデータが削除された部分
･･･
･･･
ＨＷＭ
必ずここまで
読み続ける
アクセス方法③
【ROWIDアクセス】
ROWIDには、データ・ブロック番号とオフセット番号が格納され、アクセス方
法の中で最も速い。
全く同じデータであっても、Export/ImportやデータのDELTE/INSERTで
ROWIDが変わってしまうため、検索条件として指定するのはNG。
使用する場合は、必ず行ロックが保証されている条件下でのみ使用するこ
と。
ROWIDアクセス
（ROWIDのイメージ）
・・・データブロック（通常は１ブロック内に複数レコード存在）
AAADatAAEAAAADsAAA
1行目
AAADatAAEAAAADsAAB
ROWID･･･
ブロック番号とオフセット番号
＝データのアドレス値
2行目
AAADatAAEAAAADsAAC
3行目
AAADatAAEAAAADsAAD
...
アクセス方法④
【索引スキャン】
索引には、ROWIDとキー値のセットが格納されている。
Oracleは該当するキーのROWIDを取得後、ROWIDスキャンでデータを取
得している。
索引スキャンでは該当するキーを取得するまで、シングルブロックアクセス
で索引テーブルをスキャンしていくため、ある程度のデータ数を取得する時
は、索引スキャンよりフルスキャンの方が効率が良い場合もある。
アクセス方法⑤
【全索引スキャン】
索引の値を取得するだけで、実際のデータを取得する事ができる。（索引へ
のアクセスだけで完結できる）
ただし、問い合わせ時に以下の条件を満たすときのみ実施。
・必要な列が索引に存在していること。（SELECT句やWHERE句で指定し
た列が索引内に存在）
・索引内部の行の10%以上が問い合わせから戻されること。
テーブル結合①
データを取得する際、FROM句に2つ以上のテーブルが指定さ
れていたとしても、必ず任意の２つのテーブル同士の結合を繰
り返し行っている。
効率よくアクセスし、データを取得する方法として、以下の結合
方法がある。
• ネステッド・ループ結合
• ソート／マージ結合
• ハッシュ結合
テーブル結合②
（結合イメージ）
select a.id, b.id, c.id from A a, B b, C c where a.id = b.id and a.id = c.id
テーブルA
テーブルB
テーブルC
抽出結果
第一段階
テーブルA
テーブルX
テーブルB
抽出結果
第二段階
テーブルX
テーブルC
テーブルY
テーブル結合③
【ネステッド・ループ結合】
結合処理の駆動元テーブル（外部テーブル）から結合先テーブル（内部テー
ブル）を順次参照する結合。
★ポイント
・外部テーブルの選択レコード数が少ないとパフォーマンスが向上。
・内部テーブルの結合列に索引があるとパフォーマンスが向上。
・後述する他の結合より、一時表領域の利用が少ない。
テーブル結合④
（ネステッド・ループ結合イメージ）
dep_codeが’20’(＝OPERATIONS)のemp_codeとdep_nameを抽出。
外部テーブル
内部テーブル
tbl_employ
emp_code
tbl_department
dep_code
0001
10
0002
20
0003
30
0004
20
0005
10
dep_code
①
②
emp_code
dep_code
dep_name
10
SALES
20
OPERATIONS
30
ACCOUNTING
40
RESEARCH
dep_name
0002
20
OPERATIONS
0004
20
OPERATIONS
テーブル結合⑤
実行計画はこんな感じ
SQL> select emp.emp_code, dep.dep_code, dep.name from tbl_employ emp, tbl_department dep where emp.dep_code = dep.dep_code ;
実行計画
---------------------------------------------------------Plan hash value: 1900569049
------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT
|
|
4 |
84 |
2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBL_DEPARTMENT
|
1 |
13 |
1 (0)| 00:00:01 |
| 2 | NESTED LOOPS
|
|
4 |
84 |
2 (0)| 00:00:01 |
| 3 |
INDEX FULL SCAN
| IDX_EMPLOY_02
|
4 |
32 |
1 (0)| 00:00:01 |
|* 4 |
INDEX RANGE SCAN
| IDX_DEPARTMENT_01 |
1 |
|
0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------4 - access("EMP"."DEP_CODE"="DEP"."DEP_CODE")
統計
---------------------------------------------------------0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
636 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
テーブル結合⑥
【ソート／マージ結合】
双方のテーブルを結合条件列でソートし、ソートした行をマージしていく結合。
★ポイント
・条件列に索引があり、事前にその索引で絞り込まれているとソートが不要
となるためにパフォーマンスが向上。
・ソート／マージ結合のために、ソート処理が発生する場合は効率が悪くな
る。
・ソート済みの表、索引列だけの操作などソート処理が不要な場合で有用。
テーブル結合⑥
（ソート／マージ結合イメージ）
tbl_employ
emp_code
tbl_department
dep_code
0001
10
0005
10
0002
20
0004
20
0003
30
dep_code
10
SALES
20
OPERATIONS
30
ACCOUNTING
40
RESEARCH
dep_codeでソート済み
マージ
dep_codeでソート済み
dep_code
emp_code
dep_name
dep_name
0001
10
SALES
0005
10
SALES
0002
20
OPERATIONS
0004
20
OPERATIONS
0003
30
ACCOUNTING
テーブル結合⑧
実行計画はこんな感じ
SQL> select /*+ USE_MERGE(emp dep) */ emp.emp_code, emp.dep_code, dep.name from tbl_employ emp, tbl_department dep where emp.dep_code = dep.dep_code;
実行計画
---------------------------------------------------------Plan hash value: 1652441396
-------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT
|
|
4 |
84 |
4 (25)| 00:00:01 |
| 1 | MERGE JOIN
|
|
4 |
84 |
4 (25)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TBL_DEPARTMENT
|
4 |
52 |
2 (0)| 00:00:01 |
| 3 |
INDEX FULL SCAN
| IDX_DEPARTMENT_01 |
4 |
|
1 (0)| 00:00:01 |
|* 4 | SORT JOIN
|
|
4 |
32 |
2 (50)| 00:00:01 |
| 5 |
INDEX FULL SCAN
| IDX_EMPLOY_02
|
4 |
32 |
1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------4 - access("EMP"."DEP_CODE"="DEP"."DEP_CODE")
filter("EMP"."DEP_CODE"="DEP"."DEP_CODE")
統計
---------------------------------------------------------0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
636 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
テーブル結合⑨
【ハッシュ結合】
メモリ内部にあるテーブルを結合する特殊な結合。
※CBOを利用しないとオプティマイザに選択されない。
★ポイント
・結合条件に等価条件（＝）が指定され、大量のレコード、表の大部分を結
合する場合に有効。
・ハッシュテーブルを作る為に有効な索引がないとフルスキャンが発生する。
テーブル結合⑩
（ハッシュ結合イメージ）
tbl_employ
emp_code
tbl_department
hash化
dep_code
ハッシュ化
0001
0005
0002
0004
0003
10
10
20
20
30
dep_code
dep_name
ハッシュ関数を実施
10
10
20
20
10
SALES
20
OPERATIONS
30
ACCOUNTING
40
RESEARCH
30
ハッシュ値が一致する
列を抽出
dep_code
emp_code
dep_name
0001
10
SALES
0005
10
SALES
0002
20
OPERATIONS
0004
20
OPERATIONS
0003
30
ACCOUNTING
テーブル結合⑪
実行計画はこんな感じ
SQL> select /*+ USE_HASH(emp, dep) */ emp.emp_code, dep.dep_code, dep.name from tbl_employ emp, tbl_department dep
where emp.dep_code = dep.dep_code ;
実行計画
---------------------------------------------------------Plan hash value: 2532871061
------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------------| 0 | SELECT STATEMENT |
|
4 |
84 |
5 (20)| 00:00:01 |
|* 1 | HASH JOIN
|
|
4 |
84 |
5 (20)| 00:00:01 |
| 2 | INDEX FULL SCAN | IDX_EMPLOY_02 |
4 |
32 |
1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TBL_DEPARTMENT |
4 |
52 |
3 (0)| 00:00:01 |
------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------1 - access("EMP"."DEP_CODE"="DEP"."DEP_CODE")
統計
---------------------------------------------------------1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
636 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
まとめ
テーブルのアクセス方法、テーブルの結合方法以外にRDBMS独自の技術・
仕様がありますが、結局のところ、以下の２点をどう解決するかに集約され
ます。
・データブロックへアクセス量を減らす
・キャッシュを有効利用し、ハードディスクへの物理アクセスを減らす
SQLによるチューニングのテクニックは、上記２点の理解が前提となるため、
これらの仕組みを正しく理解するようにして下さい。

Download Report