本文 PDF[226KB] - アシスト

はじめに
■コース概要と目的
Oracle をより効率的に使用するための SQL チューニング方法を説明します。また、索引の有無、 SQL の記述
方法がパフォーマンスにどのように影響するのかを実習を通して習得します。
■受講対象者
アプリケーション開発者/データベース管理者の方。
■前提条件
「SQL トレーニング」「データベース・アーキテクチャ」コースを受講された方、もしくは同等の知識をお
持ちの方。
■テキスト内の記述について
構文
[
]
省略可能
{ A | B }
A または B のどちらかを選択
n
数値の指定
_
デフォルト値
マーク
NOTE
参照
知っておいたほうが良いテクニック、もしくは注意事項
参照ページ
データ・ディクショナリ・ビュー
11.2
指定バージョンからの新機能
（左図の場合、 Oracle Database 11g R2 からの新機能）
Enterprise Edition でのみ使用できる機能
CONTENTS
第１章　SQL パフォーマンスチューニングの基礎知識
１. チューニング概要-----------------------------------
１－１
２. SQL の処理ステップ---------------------------------
１－７
３. 実行計画-------------------------------------------
１－９
４. アクセス・パス-------------------------------------
１－１１
５. オプティマイザ-------------------------------------
１－２１
第２章　SQL の診断ツール
１. チューニングすべき SQL の特定方法-------------------
２－１
２. V$SQL ビュー、V$SQLSTATS ビュー----------------------
２－３
３. SQL トレース、TKPROF--------------------------------
２－５
４. SQL*Plus の AUTOTRACE 機能--------------------------
２－２１
５. EXPLAIN PLAN---------------------------------------
２－２７
６. V$SQL_PLAN ビュー----------------------------------
２－３１
第３章　索引の作成と管理
１. 索引の特徴----------------------------------------
３－１
２. B ツリー索引の作成---------------------------------
３－３
３. 索引の効果が得られない場合の対処-------------------
３－１３
第４章　効率的な SQL の記述方法
１. 効率的な SQL の記述---------------------------------
４－１
２. 索引を使用する SQL の記述---------------------------
４－３
３. WHERE 句以外で索引を使用する SQL の記述-------------
４－１７
４. オプティマイザ・ヒント-----------------------------
４－２７
５. メモリーを効率的に使用する SQL の記述方法-----------
４－３５
６. Oracle が行う内部変換------------------------------
４－３９
CONTENTS
第５章　結合
１. 結合処理の概要---------------------------------------
５－１
２. 結合の種類-------------------------------------------
５－３
３. 結合順序---------------------------------------------
５－１７
４. 結合における考慮事項---------------------------------
５－２１
第６章　コストベース・オプティマイザと実行計画の決定
１. 実行計画決定の要素-----------------------------------
６－１
２. 初期化パラメータ-------------------------------------
６－３
３. オプティマイザ統計-----------------------------------
６－７
４. 手動での統計収集-------------------------------------
６－１１
５. Oracle Database 10g からの自動収集--------------------
６－２１
付録
１. 逆キー索引-------------------------------------------
付－１
２. ファンクション索引-----------------------------------
付－３
３. 索引構成表-------------------------------------------
付－７
４. ビットマップ索引-------------------------------------
付－１１
５. 不可視化索引-----------------------------------------
付－１７
６. DBMS_STATS パッケージ---------------------------------
付－２１
７. 索引の使用状況の監視---------------------------------
付－２７
８. 表の MONITORING 機能----------------------------------
付－２９
９. PLAN_TABLE 表-----------------------------------------
付－３３
１０. ヒントの種類-----------------------------------------
付－４１
１１. CBO に影響を与える初期化パラメータ--------------------
付－４５
１２. 動的サンプリング-------------------------------------
付－４９
１３. 統計収集機能の拡張-----------------------------------
付－５１
１４. 統計情報のリストア-----------------------------------
付－５７
CONTENTS
１５. その他の効果的な SQL の記述方法---------------------
付－６１
１６. 結果キャッシュ-------------------------------------
付－６３
１７. 索引構造の分析-------------------------------------
付－７１
１８. 整合性制約による索引作成---------------------------
付－７３
１９. ルールベース・オプティマイザ-----------------------
付－７５
２０. 自動 SQL チューニング-------------------------------
付－８３
２１. SQL 実行計画管理-----------------------------------
付－８７
２２. 11g からのトレース・ファイルの出力先---------------
付－８９
第５章
結合
結合のパフォーマンスに影響を与える結合の種類と、表の結合順序について
内部動作を交えて説明します。
１．結合処理の概要
２．結合の種類
３．結合順序
４．結合における考慮事項
第 5 章「結合」
１. 結合処理の概要
結合とは複数の表からデータを取り出す操作です。結合処理を実行するには、問合せ文の FROM 句に複数の表
を指定し、結合する各表の関係を WHERE 句内に結合条件として設定します。
(１) 結合処理の選択
コストベース・オプティマイザは、結合対象の各表のデータ量や、索引の有無などをもとに適切な結合
方法を選択してくれます。しかし、オプティマイザが選択した結合方法以外の方法が効率的だと思える
場合は、チューニングを試みる必要があります。
(２) 結合の種類と結合順序
結合のパフォーマンスを向上するには、最も適切な結合方法と結合順序を選択することが重要です。
１) 結合の種類
結合方法には、主に以下の 3 つの種類があります。
・ソート/マージ結合
結合条件に指定した各列をソートし、その結果をマージします。データ量の多い表同士を結合し、
表の大部分が結合対象である場合に向いています。
・ハッシュ結合
内部的にハッシュ関数を使用して結合を行います。小規模な表と大規模な表の結合で、表の大部分
のデータが結合対象である場合に向いています。多くの場合、ソート /マージ結合よりも効率的に
実行できます。
※ハッシュ結合は、ルールベース・オプティマイザでは使用できません。
・ネステッド・ループ結合
大規模な表と小規模な表（または WHERE 条件で行ソースを絞っている）を索引を使用して結合し、
表の一部のデータが結合対象である場合に向いています。
結合方法選択のガイドライン
ケース
ソート/マージ
ハッシュ
ネステッド・ループ
△
○
×
×
△
○
結合対象の行が多い結合（ DWH 、 DSS 系に多
い）
結合対象の行が少ない結合（OLTP 系に多い）
株式会社アシスト
Copyright（C） 2010 K.K. Ashisuto All Rights Reserved.
5-1
第 5 章「結合」
(３) 結合順序
Oracle では 2 つの表ずつ結合処理が行われます。そのため、3 つ以上の表の結合では、まず 2 つの表
を結合し、その結果作成された行ソースと 3 つ目の表を結合、さらにその結果と 4 つ目の表と結合、
というように処理が行われます。このような特徴があるため、3 つ以上の表の結合では何通りかの結
合順序が考えられます。
例）A 表、B 表、C 表を結合する場合の順序の候補
・A ⇒ B
→ C
・A ⇒ C
→ B
・B ⇒ A
→ C
・B ⇒ C
→ A
・C ⇒ A
→ B
・C ⇒ B
→ A
各結合順序によって実行負荷は異なります。そのため、複数の結合順序のうち、一番負荷が低いも
のを選択するようにします。
Copyright（C） 2010 K.K. Ashisuto All Rights Reserved.
株式会社アシスト
5-2
第 5 章「結合」
２. 結合の種類
各結合方法の内部的な動作と、どのような時に利用されるかを解説します。
(１) ソート/マージ結合
ソート/マージ結合は、結合条件に指定した各列をソートし、その結果をマージします。データ量の多い
表同士を結合し、表の大部分が結合対象である場合に向いています。
※一般的にソート/マージ結合よりもハッシュ結合の方がパフォーマンスが優れています。
１) 実行ステップ
ソート/マージ結合は、内部的に以下ステップで行われます。
１．ソート操作　：両方の表が、結合列をもとにソートされる。
２．マージ結合処理：ソートされた各表の行ソースがマージされる。
株式会社アシスト
Copyright（C） 2010 K.K. Ashisuto All Rights Reserved.
5-3
第 5 章「結合」
■ソート/マージ結合の動作
実行 SQL：SELECT ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno;
①それぞれの表の結合列（DEPTNO 列）を基準にソートする。
EMP表
DEPT表
ENAME
SAL
DEPTNO
DEPTNO
CLARK
2450
10
10
MILLER
1300
10
SMITH
800
20
JONES
2975
20
SCOTT
3000
20
ALLEN
1600
30
MARTIN
1250
30
BLAKE
2850
30
SORT
DNAME
LOC
ACCOUNTING NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON
②ソートされたそれぞれの行ソースを、結合条件をもとにマージする。
EMP表
DEPT表
ENAME
SAL
DEPTNO
DEPTNO
CLARK
2450
10
10
MILLER
1300
10
20
RESEARCH
DALLAS
SMITH
800
20
30
SALES
CHICAGO
JONES
2975
20
40
OPERATIONS
BOSTON
SCOTT
3000
20
ALLEN
1600
30
MARTIN
1250
30
BLAKE
2850
30
DNAME
LOC
ACCOUNTING NEW YORK
※上記例は、DEPT 表の DEPTNO 列 40 以外の行が全て結合対象となるため、結合対象の多いケースで
す。このような場合は、ソート/マージ結合かハッシュ結合が向いています。
Copyright（C） 2010 K.K. Ashisuto All Rights Reserved.
株式会社アシスト
5-4
第 5 章「結合」
２) ソート/マージ結合が選択されるケース
オプティマイザは、以下のようなケースでソート/マージ結合を選択する可能性があります。
・表の大部分のデータを結合する場合。
・2 つの表の間の結合条件が、<、>、<=、>=のような等価（=）ではない場合。
⇒ハッシュ結合は、等価条件（=）でないと使用されません。
・行ソースが既に他の操作などでソートされており、ソート処理を避けることができる場合。
⇒例えば、結合条件の列に索引が作成されており、それを利用するとハッシュ結合よりも低いコ
ストで実行できる場合があります。
※ソート用のメモリー領域サイズを大きく設定していればいるほど、ソート処理のコストが低下し
ソート/マージ結合が選択されやすくなります。Oracle Database 9i からの自動 PGA メモリー管理
（PGA_AGGREGATE_TARGET パラメータ）を使用している場合、ソート領域のサイズは自動調整され
ます。自動 PGA メモリー管理機能を使用していない場合は、 SORT_AREA_SIZE パラメータでソート
用のメモリー領域のサイズを調整できます。
■結合処理の実行計画の確認方法
索引アクセスやソート処理が含まれた結合処理では、索引アクセスと索引が定義されている表へのア
クセス、または表へのアクセスとソート処理がセットで実行された後、各表を結合します。
下記例の場合、IDX_DEPTNO 索引にアクセスした後（①）、その索引が定義されている EMP 表にアクセ
スしています（②）。①と同じインデントの深さである DEPT 表へのアクセス（③）は、EMP 表へのア
クセス後に実行されます。
列）EMP 表と DEPT 表をソート/マージ結合したときの実行計画を確認する。
SQL> SELECT ename,dname FROM emp,dept
2 WHERE emp.deptno = dept.deptno;
実行計画
--------------------------------------------------| Id | Operation
| Name
|
--------------------------------------------------|
0 | SELECT STATEMENT
|
|
|
1 | MERGE JOIN
|
|
|
|
2 |
TABLE ACCESS BY INDEX ROWID| EMP
|
3 |
INDEX FULL SCAN
| IDX_DEPTNO |
|* 4 |
SORT JOIN
|
|
|
5 |
TABLE ACCESS FULL
| DEPT
|
---------------------------------------------------
株式会社アシスト
⑤EMP 表と DEPT 表を結合
②EMP 表にアクセス
①IDX_DEPTNO 索引にアクセス
④DEPT 表をソート
③DEPT 表にアクセス
Copyright（C） 2010 K.K. Ashisuto All Rights Reserved.
5-5
第 5 章「結合」
例）EMP 表と DEPT 表を結合したときの実行計画を確認する。
/* 表の大部分のデータを結合する場合、通常はハッシュ結合が選択されやすい */
SQL> SELECT ename,dname FROM emp,dept
2 WHERE emp.deptno = dept.deptno;
実行計画
----------------------------------| Id | Operation
| Name |
----------------------------------|
0 | SELECT STATEMENT
|
|
|* 1 | HASH JOIN
|
|
|
2 |
TABLE ACCESS FULL| DEPT |
|
3 |
TABLE ACCESS FULL| EMP |
-----------------------------------
列）上記例題と同じ SQL を結合列に索引を作成して実行する。
/* 結合列である EMP 表の DEPTNO 列に索引を作成する */
SQL> CREATE INDEX idx_deptno ON emp(deptno);
/* ソート/マージ結合に変更された */
SQL> SELECT ename,dname FROM emp,dept
2 WHERE emp.deptno = dept.deptno;
実行計画
--------------------------------------------------| Id | Operation
| Name
|
--------------------------------------------------|
0 | SELECT STATEMENT
|
|
|
1 | MERGE JOIN
|
|
|
2 |
TABLE ACCESS BY INDEX ROWID| EMP
|
|
3 |
INDEX FULL SCAN
| IDX_DEPTNO |
|* 4 |
SORT JOIN
|
|
|
5 |
TABLE ACCESS FULL
| DEPT
|
--------------------------------------------------※結合列である EMP 表の DEPTNO 列に索引を作成したため、索引を使用すれば EMP 表のソート処理をしな
くて済むようになった。そのため、ソート/マージ結合のコストが低くなり、ハッシュ結合ではなく
ソート/マージ結合が使用されるようになった。
<MERGE JOIN SORT JOIN>
ソート/マージ結合が使用されたことを示します。
※この後のページで他の結合の種類や、
結合におけるチューニングポイントの
詳細を解説しています。
Copyright（C） 2010 K.K. Ashisuto All Rights Reserved.
株式会社アシスト
5-6
索引
索引
A
ADR、付-89
ALL_ROWS ヒント、6-4
ALTER INDEX MONITORING USAGE、付-27
ALTER INDEX 索引名 COALESCE 文、3-17
ALTER INDEX 索引名 REBUILD 文、3-17
ALTER TABLE 表名 MONITORING、付-30
ALTER_STATS_HISTORY_RETENTION、付-57
ANALYZE INDEX VALIDATE STRUCTURE、付-71
AND-EQUAL、付-78
Automatic Diagnostic Repository、付-89
AUTOTRACE、2-21、2-23
B
BIND、1-7
bind peek 機能、4-37
BITMAP CONVERSION TO ROWIDS / FROM ROWIDS、4-11
B ツリー索引、3-1
DBMS_STATS.GATHER_DICTIONARY_STATS プロシージャ、
6-16
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS プロシー
ジャ、6-18
DBMS_STATS.GATHER_INDEX_STATS プロシージャ、6-13
DBMS_STATS.GATHER_SCHEMA_STATS プロシージャ、6-14
DBMS_STATS.GATHER_SYSTEM_STATS プロシージャ、6-17
DBMS_STATS.IMPORT_PENDING_STATS、付-55
DBMS_STATS.PUBLISH_PENDING_STATS、付-55
DBMS_STATS.RESTORE_DATABASE_STATS、付-59
DBMS_STATS.SET_TABLE_PREFS、付-55
DBMS_STATS パッケージ、付-21
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION プロシージャ、
2-11
DBMS_XPLAN.DISPLAY、2-29
DETERMINISTIC、付-4
DIAGNOSTIC_DEST、2-8、付-90
DROP INDEX 索引名文、3-19
E
C
EXECUTE、1-7
EXPLAIN PLAN、2-27
CASE 文、付-61
CBO、1-21
CLUSTERING_FACTOR 列、1-19
COMPRESS、3-11
CONCATENATION、4-7
CREATE BITMAP INDEX、付-16
CREATE INDEX 文、3-7
CREATE TABLE … ORGANIZATION INDEX、付-9
CREATE TABLE 表名 MONITORING、付-30
CURSOR_SHARING、付-45
F
FETCH、1-7
FIRST_ROWS(n)ヒント、6-4
FULL ヒント、4-30
G
GATHER_TABLE_STATS プロシージャ、6-11
D
H
DB_FILE_MULTIBLOCK_READ_COUNT、6-5、付-45
DBA_INDEXES、1-19
DBA_OPTSTAT_OPERATIONS、付-57
DBA_TAB_MODIFICATIONS、付-31
DBA_TAB_STATS_HISTORY、付-57
DBMS_MONITOR.SESSION_TRACE_ENABLE プロシージャ、
2-13
DBMS_RESULT_CACHE.FLUSH、付-69
DBMS_RESULT_CACHE.MEMORY_REPORT、付-67
DBMS_SESSION.SET_SQL_TRACE プロシージャ、2-11
DBMS_STATS.ALTER_DATABASE_TAB_MONITORING、付-30
DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING、付-30
DBMS_STATS.CREATE_EXTENDED_STATS、付-53
DBMS_STATS.EXPORT_PENDING_STATS、付-55
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO、付-31
DBMS_STATS.GATHER_DATABASE_STATS プロシージャ、615
HASH JOIN、5-10
HASH JOIN INDEX、4-10
HASH_AREA_SIZE、6-5、付-47
HWM、1-11
I
INDEX FAST FULL SCAN、4-26
INDEX FULL SCAN、4-14
INDEX FULL SCAN (MIN/MAX)、4-24
INDEX RANGE SCAN、4-4
INDEX SKIP SCAN、4-16
INDEX UNIQUE SCAN、4-6
INDEX_FFS ヒント、4-34
INDEX_JOIN ヒント、4-33
INDEX_SS ヒント、4-34
INDEX_STATS、付-71
Copyright（C） 2010 K.K. Ashisuto All Rights Reserved.
株式会社アシスト
索引-1
索引
INDEX ヒント、4-31
INLIST ITERATOR、4-13
INVISIBLE、付-17
IOT、付-7
L
LEADING ヒント、5-23
S
SORT (GROUP BY) / SORT (GROUP BY NOSORT)、4-22
SORT (UNIQUE) / SORT (UNIQUE NOSORT)、4-20
SORT AGGREGATE、4-24
SORT_AREA_SIZE、6-5、付-47
SQL トレース、2-5
SQL プロファイル、付-84
SQL 実行計画管理、付-87
M
MAX_DUMP_FILE_SIZE パラメータ、2-7
MERGE JOIN CARTESIAN、5-16
MERGE JOIN SORT JOIN、5-6
T
TIMED_STATISTICS パラメータ、2-7
TKPROF、2-5、2-15
TRACEFILE_IDENTIFIER パラメータ、2-9
N
NESTED LOOPS、5-14
NO_INDEX ヒント、4-33
NOLOGGING、3-9
NOSORT、3-9
U
OPTIMIZER_DYNAMIC_SAMPLING、付-49
OPTIMIZER_FEATURES_ENABLE、付-45
OPTIMIZER_INDEX_CACHING、付-46
OPTIMIZER_INDEX_COST_ADJ、6-6、付-46
OPTIMIZER_MAX_PERMUTATIONS、付-46
OPTIMIZER_MODE、6-3、付-46
OPTIMIZER_USE_INVISIBLE_INDEXES、付-19
OPTIMIZER_USE_PENDING_STATISTICS、付-55
ORDERED ヒント、5-22
UNION ALL、付-62
UNIQUE 制約、付-73
USE_HASH ヒント、5-25
USE_INVISIBLE_INDEXES ヒント、付-19
USE_MERGE ヒント、5-24
USE_NL_WITH_INDEX ヒント、5-27
USE_NL ヒント、5-26
USER_CONSTRAINTS、付-73
USER_DUMP_DEST パラメータ、2-8
USER_IND_COLUMNS ビュー、3-12
USER_INDEXES ビュー、3-10
utlxplan.sql、2-21、2-27
utlxplp.sql、2-27
utlxpls.sql、2-27
P
V
O
PARSE、1-7
PGA_AGGREGATE_TARGET、6-5、付-47
PLAN_TABLE、2-21、2-27、付-33
PLUSTRACE ロール、2-21
plustrce.sql、2-21
PRIMARY KEY 制約、付-73
Q
QUERY REWRITE 権限、付-3
QUERY_REWRITE_ENABLE、付-4
QUERY_REWRITE_INTEGRITY、付-4
R
RBO、1-21、付-75
RESULT_CACHE_MAX_RESULT、付-69
RESULT_CACHE_MAX_SIZE、付-69
RESULT_CACHE_MODE、付-65
RESULT_CACHE ヒント、付-65
ROWID スキャン、1-13
V$DIAG_INFO、付-91
V$OBJECT_USAGE、付-27
V$RESULT_CACHE_MEMORY、付-67
V$RESULT_CACHE_OBJECTS、付-67
V$RESULT_CACHE_STATISTICS、付-67
V$SQL_PLAN ビュー、2-31
V$SQLSTATS ビュー、2-4
V$SQL ビュー、2-3
お
オプティマイザ・ヒント、4-27
オプティマイザ統計、1-23、6-1、6-7
か
カーディナリティ、3-3
外部結合、5-15
株式会社アシスト
Copyright（C） 2010 K.K. Ashisuto All Rights Reserved.
索引-2
索引
き
ね
逆キー索引、付-1
ネステッド・ループ結合、5-11
け
は
結果キャッシュ、付-63
ハッシュ結合、5-7
こ
ひ
高速全索引スキャン、4-25
コストベース・オプティマイザ、1-21
コンポジット索引、3-4
ヒストグラム、6-9
ビットマップ索引、3-1、付-11
ヒント、4-1、4-27
さ
ふ
最高水位標、1-11
索引一意スキャン、4-6
索引クラスタ化係数、1-19
索引構成表、付-7
索引スキップ・スキャン、4-34
索引スキャン、1-15
索引レンジ・スキャン、4-4
ファンクション索引、4-3、付-3
不可視化索引、付-17
複合索引、3-4
ブランチ・ブロック、1-17
フル・テーブル・スキャン、1-11
り
し
リーフ・ブロック、1-17
リーフ分割、3-15
実行計画、1-9
自動 SQL チューニング、付-83
初期化パラメータ、6-1、6-3
る
す
ルート・ブロック、1-17
ルールベース・オプティマイザ、1-21、付-75
スループット、1-1
れ
せ
レスポンスタイム、1-1
連結索引、3-4
整合性制約、付-73
全表スキャン、1-11
そ
ソート/マージ結合、5-3
ち
直積結合、5-15
て
デカルト結合、5-15
と
問合せオプティマイザ、1-21
統計公開の保留、6-20
統計のエクスポート/インポート、6-19
統計のリストア、6-19
統計のロック、6-20
統計の削除、6-20
動的サンプリング、付-49
Copyright（C） 2010 K.K. Ashisuto All Rights Reserved.
株式会社アシスト
索引-3
SQL パフォーマンスチューニング（ 11gR2 対応版）第 1版 2010 年 9月 1日
本テキストで使用されるメーカ名、商品名は各社の商標または登録商標です。当社の文書による
許可無しに、本誌の一部または全部を複製することはお断りします。
また、本誌に記載された内容については、予告なしに変更することがありますので、
予めご了承下さい。
株式会社アシストは、本書および本書に付属する資料についてその記載内容に誤りがないこと、
および特定目的に対する適合性に関するいっさいの保証を行うものではありません。
また、本資料を使用したことによって被った直接的・間接的な損害などについて、いかなる場合に
おいても責任を負いかねます。
発売元　株式会社
株式会社アシストは、日本オラクル
株式会社の代理店です。

Download Report