本文 PDF[226KB]

はじめに
■コース概要と目的
Oracle をより効率的に使用するための SQL チューニング方法を説明します。また、索引の有無、SQL の記述方
法がパフォーマンスにどのように影響するのかを実習を通して習得します。
■受講対象者
アプリケーション開発者/データベース管理者の方。
■前提条件
「SQL トレーニング」「データベース・アーキテクチャ」コースを受講された方、もしくは同等の知識をお持
ちの方。
■テキスト内の記述について
▼構文
[
]
省略可能
{ A | B }
A または B のどちらかを選択
n
数値の指定
_
デフォルト値
▼マーク
指定バージョンからの新機能
（左記の場合、Oracle 12cR1 からの新機能）
Enterprise Edition で使用できる機能
知っておいたほうが良いテクニック、もしくは注意事項
参照ページ
データ・ディクショナリ・ビュー
CONTENTS
第１章　SQL パフォーマンスチューニングの基礎知識
１．チューニング概要-----------------------------------
１－１
２．SQL の処理ステップ---------------------------------
１－５
３．実行計画-------------------------------------------
１－７
４．代表的なアクセス・パス-----------------------------
１－９
５．コストベース・オプティマイザ-----------------------
１－１９
第２章　SQL の診断ツール
１．SQL チューニング概要-------------------------------
２－１
２．V$SQLSTATS ビュー ---------------------------------
２－３
３．SQL トレース、TKPROF--------------------------------
２－５
４．SQL*Plus の AUTOTRACE 機能--------------------------
２－１９
５．EXPLAIN PLAN---------------------------------------
２－２５
６．DBMS_XPLAN.DISPLAY_CURSOR ファンクション-----------
２－２７
第３章　索引の作成と管理
１．索引の特徴-----------------------------------------
３－１
２．B ツリー索引の作成---------------------------------
３－３
３．索引の効果が得られない場合の対処-------------------
３－１３
第４章　効率的な SQL の記述方法
１．効率的な SQL 記述の主なポイント---------------------
４－１
２．索引を使用する SQL の記述---------------------------
４－３
３．WHERE 句以外で索引を使用する SQL の記述-------------
４－１９
４．オプティマイザ・ヒント-----------------------------
４－２９
５．メモリーを効率的に使用する SQL の記述---------------
４－３７
CONTENTS
第５章　結合
１．結合処理のチューニング概要-------------------------
５－１
２．結合の種類-----------------------------------------
５－３
３．結合順序-------------------------------------------
５－１９
４．結合処理のチューニングポイント---------------------
５－２１
５．結合関連のヒント-----------------------------------
５－２３
第６章　コストベース・オプティマイザと実行計画の決定
１．実行計画決定の要素---------------------------------
６－１
２．初期化パラメータ-----------------------------------
６－３
３．オプティマイザ統計概要-----------------------------
６－９
４．オプティマイザ統計の自動収集-----------------------
６－１１
５．オプティマイザ統計の手動収集-----------------------
６－１３
６．オプティマイザ統計収集の方針-----------------------
６－１５
７．オプティマイザ統計の管理---------------------------
６－１７
付録
１．逆キー索引-----------------------------------------
付－１
２．ファンクション索引---------------------------------
付－３
３．索引構成表-----------------------------------------
付－７
４．ビットマップ索引-----------------------------------
付－１１
５．不可視化索引---------------------------------------
付－１７
６．整合性制約による索引作成---------------------------
付－２１
７．索引の使用状況の監視-------------------------------
付－２３
８．索引構造の分析-------------------------------------
付－２５
９．表の MONITORING 機能--------------------------------
付－２７
１０．PLAN_TABLE 表---------------------------------------
付－２９
１１．ヒントの種類---------------------------------------
付－３７
CONTENTS
１２．CBO に影響を与える主な初期化パラメータ-------------
付－４３
１３．DBMS_STATS パッケージ一覧--------------------------
付－４７
１４．DBMS_STATS パッケージの使用方法--------------------
付－５３
１５．統計収集の拡張機能---------------------------------
付－６１
１６．12c 新機能：実行計画の最適化機能-------------------
付－６５
１７．オプティマイザ統計のリストア-----------------------
付－６７
１８．オプティマイザ統計公開の保留-----------------------
付－７３
１９．Enterprise Edition で使用できる SQL チューニングの
機能-----------------------------------------------
付－７５
２０．Oracle 11g からのトレース・ファイルの出力先--------
付－７９
第5章
結合
結合のパフォーマンスに影響を与える結合の種類と、表の結合順序について内部動作を交えて
説明します。
１. 結合処理のチューニング概要
２. 結合の種類
３. 結合順序
４. 結合処理のチューニングポイント
５. 結合関連のヒント
第 5 章「結合」
１. 結合処理のチューニング概要
結合とは複数の表からデータを取り出す操作です。結合するには、問合せ文の FROM 句に複数の表を指定し、結合
する各表の関係を WHERE 句内に結合条件として設定します。
※ANSI の場合、JOIN 句に複数の表を指定し、ON 句（または USING 句）に結合条件を指定します。
結合のパフォーマンスを向上するには、最も適切な結合方法と結合順序を選択することが重要です。
(１) 結合の種類
結合方法には、主に以下の 3 つの種類があります。
・ハッシュ結合
内部的にハッシュ関数を使用して結合します。
・ソート/マージ結合
結合条件に指定した各列をソートし、その結果をマージします。
・ネステッド・ループ結合
大規模な表と小規模な表（または WHERE 句で行ソースを絞っている）を、索引を使用して結合します。
結合の種類
有効なシーン
結合する表の特徴
使用する条件
ハッシュ結合
結合結果が大量
大規模な表と小規模な表
結合条件が等価のみ使用可能
ソート/マージ結合
結合結果が大量
結合する表が同規模
結合条件が非等価でも使用可能
ネステッド・ループ結合
結合結果が少量
大規模な表と小規模な表
大規模な表側に索引が必要
株式会社アシスト
Copyright（C） K.K. Ashisuto All Rights Reserved.
5-1
第 5 章「結合」
(２) 結合順序
結合では 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） K.K. Ashisuto All Rights Reserved.
株式会社アシスト
5-2
第 5 章「結合」
２. 結合の種類
各結合の内部的な動作と、どのようなときに利用されるかを解説します。
(１) ハッシュ結合
ハッシュ結合は、小規模な表と大規模な表の結合で、表の大部分の行が結合対象である場合に向いていま
す。多くの場合、ソート/マージ結合よりも効率的に実行できます。
１) 内部動作
ハッシュ結合は、結合する一方の表にハッシュ関数を適用してメモリー上（ハッシュ領域）に展開した
後、もう一方の表にもハッシュ関数を適用してハッシュ値が等しいデータを結合する方法です。
ハッシュ結合は、以下のステップによって行われます。
１．結合する 2 つの表を比較し、小さな表を選択する。
２．選択した表の結合条件列にハッシュ関数を適用し、ハッシュ領域に展開する（ハッシュ表の作成）。
※ハッシュ領域は PGA 内に獲得されます。また、表をハッシュ領域に格納できなかった場合は、一
時セグメントとしてディスクに書出されます。
３．もう一方の大きな表の結合条件列にハッシュ関数を適用しハッシュ表と比較する。同じハッシュ値
の行があった場合は結合する。この作業を行ごとに繰り返す。
株式会社アシスト
Copyright（C） K.K. Ashisuto All Rights Reserved.
5-3
第 5 章「結合」
<実行 SQL>
SELECT ename,dname
FROM
emp,dept
WHERE
emp.deptno = dept.deptno;
①小さな表として DEPT 表を選択する。
② DEPT 表の DEPTNO 列にハッシュ関数を適用し、ハッシュ領域に格納する（ハッシュ表の作成）。
③表がハッシュ領域に収まり切らない場合は、一時セグメントとしてディスクに書出す。
EMP表
DEPT表
PGA
ハッシュ領域
ENAME
SAL
DEPTNO
DEPTNO
SMITH
800
20
1
30 … SALES
ALLEN
1600
30
2
10 … ACCOUNTING
JONES
2975
20
3
40 … OPERATIONS
MARTIN
1250
30
4
20 … RESEARCH
BLAKE
2850
30
CLARK
2450
10
SCOTT
3000
20
MILLER
7934
10
10
②
20
ハッシュ関数
DNAME
①
LOC
ACCOUNTING NEW YORK
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON
ハッシュ表
< ハッシュ値 >
10→2
20→4
30→1
40→3
③
一時表領域
④もう一方の EMP 表の DEPTNO 列にハッシュ関数を適用。その結果とハッシュ表を比較し、同じハッシュ値の
行があれば結合する。
EMP表
ENAME
SAL
DEPTNO
SMITH
800
20
ALLEN
1600
30
JONES
2975
MARTIN
DEPT表
PGA
ハッシュ領域
DEPTNO
DNAME
LOC
1
30 … SALES
10
2
10 … ACCOUNTING
20
RESEARCH
DALLAS
20
3
40 … OPERATIONS
30
SALES
CHICAGO
1250
30
4
20 … RESEARCH
40
OPERATIONS
BOSTON
BLAKE
2850
30
CLARK
2450
10
SCOTT
3000
20
MILLER
7934
10
④
ACCOUNTING NEW YORK
ハッシュ関数
ハッシュ表
< ハッシュ値 >
10→2
20→4
30→1
一時表領域
Copyright（C） K.K. Ashisuto All Rights Reserved.
株式会社アシスト
5-4
第 5 章「結合」
２) ハッシュ結合が選択されるケース
オプティマイザは以下のようなケースでハッシュ結合を選択する可能性があります。
・表の大部分の行を結合する場合。
⇒通常、ソート処理よりもハッシュ処理のコストの方が低いため、ハッシュ結合が選択されやすい
といえます。
・結合条件が等価（=）である場合。
⇒非等価演算子（
株式会社アシスト
<、>、<=、>=
）では、ハッシュ結合は行われません。
Copyright（C） 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 |
-----------------------------------
<HASH JOIN>
ハッシュ結合が行われたことを示します。
先に全表スキャンされている表がハッシュ表になります。上記例では DEPT 表がハッシュ表です。
Copyright（C） K.K. Ashisuto All Rights Reserved.
株式会社アシスト
5-6
第 5 章「結合」
(２) ソート/マージ結合
ソート/マージ結合は、結合条件の各列をソートし、その結果をマージします。
データ量の多い表同士を結合し、表の大部分の行が結合対象である場合に向いています。
※一般的にソート/マージ結合よりもハッシュ結合の方がパフォーマンスが優れています。
１) 内部動作
ソート/マージ結合は、内部的に以下ステップで行われます。
１．ソート操作　：両方の表が、結合条件列をもとにソートされる。
２．マージ結合処理：ソートされた各表の行ソースがマージされる。
株式会社アシスト
Copyright（C） K.K. Ashisuto All Rights Reserved.
5-7
第 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
ソート
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
Copyright（C） K.K. Ashisuto All Rights Reserved.
DNAME
LOC
ACCOUNTING NEW YORK
株式会社アシスト
5-8
第 5 章「結合」
２) ソート/マージ結合が選択されるケース
オプティマイザは、以下のようなケースでソート/マージ結合を選択する可能性があります。
・表の大部分の行を結合する場合。
・結合条件が非等価演算子（ <、>、<=、>= など）である場合。
⇒ハッシュ結合は、等価条件（=）でないと使用されません。
・行ソースが既に他の操作などでソートされており、ソート処理を避けることができる場合。
⇒例えば、結合条件の列に索引が作成されている場合、索引を利用することでソート処理を省略で
き、ハッシュ結合よりも低いコストで結合できる場合があります。
株式会社アシスト
Copyright（C） K.K. Ashisuto All Rights Reserved.
5-9
第 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 |
----------------------------------/* 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 表へのアクセスでは、索引をフルスキャン
し、索引からソート済みデータを取得
DEPT 表の DEPTNO 列には索引がないため、
全表スキャン後にソートが発生
※結合条件列に索引が作成されたため、ソート処理をスキップできるようになった。これにより、ソー
ト/マージ結合のコストがハッシュ結合を下回り、ソート/マージ結合が使用されるようになった。
<MERGE JOIN>
ソート/マージ結合が使用されたことを示します。
Copyright（C） K.K. Ashisuto All Rights Reserved.
株式会社アシスト
5-10
索引
索引
DBMS_STATS.GATHER_TABLE_STATS プロシージャ、付-53
DBMS_STATS.RESTORE_DATABASE_STATS、付-69
DBMS_STATS パッケージ、付-47、付-53
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION プロシージャ、
2-12
DBMS_XPLAN.DISPLAY_CURSOR ファンクション、2-27
DIAGNOSTIC_DEST パラメータ、2-8、付-80
DROP INDEX 文、3-17
A
ADDM、付-75
ADR、付-79
ALTER INDEX COALESCE 文、3-18
ALTER INDEX MONITORING USAGE 文、付-23
ALTER INDEX NOMONITORING USAGE 文、付-23
ALTER INDEX REBUILD 文、3-17
ANALYZE INDEX VALIDATE STRUCTURE 文、付-25
Automatic Database Diagnostic Monitor、付-75
Automatic Diagnostic Repository、付-79
AUTOTRACE、2-19
E
EXECUTE、1-5
EXPLAIN PLAN、2-25
B
F
B ツリー索引、3-1
FETCH、1-5
FULL ヒント、4-32
C
CARTESIAN、5-18
COMPRESS、3-11
CONCATENATION、4-8
CREATE INDEX 文、3-7
CURSOR_SHARING パラメータ、付-44
H
D
DB_FILE_MULTIBLOCK_READ_COUNT パラメータ、6-5、
付-43
DBA_OBJECT_USAGE ビュー、付-23
DBA_OPTSTAT_OPERATIONS ビュー、付-67
DBA_TAB_MODIFICATIONS ビュー、付-28
DBA_TAB_STATS_HISTORY ビュー、付-67
DBMS_MONITOR.SESSION_TRACE_DISABLE プロシージャ、
2-13
DBMS_MONITOR.SESSION_TRACE_ENABLE プロシージャ、
2-13
DBMS_SESSION.SET_SQL_TRACE プロシージャ、2-11
DBMS_STATS.GATHER_DATABASE_STATS プロシージャ、
付-57
DBMS_STATS.GATHER_DICTIONARY_STATS プロシージャ、
付-58
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS プロシー
ジャ、付ｰ 60
DBMS_STATS.GATHER_INDEX_STATS プロシージャ、付-55
DBMS_STATS.GATHER_SCHEMA_STATS プロシージャ、
付-56
DBMS_STATS.GATHER_SYSTEM_STATS プロシージャ、
付-59
HASH GROUP BY、4-24
HASH JOIN、5-6
HASH JOIN INDEX、4-9
HASH UNIQUE、4-22
HASH_AREA_SIZE パラメータ、付-44
HWM、1-9
I
INDEX FAST FULL SCAN、4-28
INDEX FULL SCAN、4-12
INDEX FULL SCAN (MIN/MAX)、4-26
Index Organization Table、付ｰ 7
INDEX RANGE SCAN、4-4
INDEX SKIP SCAN、4-18
INDEX UNIQUE SCAN、4-6
INDEX_FFS ヒント、4-36
INDEX_JOIN ヒント、4-35
INDEX_SS ヒント、4-36
INDEX ヒント、4-33
IOT、付ｰ 7
L
LEADING ヒント、5-24
Copyright（C） K.K. Ashisuto All Rights Reserved.
株式会社アシスト
索引-1
索引
SQL 実行計画管理、付-77
STAR_TRANSFORMATION_ENABLED パラメータ、付-46
STATISTICS_LEVEL パラメータ、付-27
M
MAX_DUMP_FILE_SIZE パラメータ、2-7
MERGE JOIN、5-10
T
N
TIMED_STATISTICS パラメータ、2-7
TKPROF、2-15、2-5
TRACEFILE_IDENTIFIER パラメータ、2-9
NDEX_STATS ビュー、付-25
NESTED LOOPS、5-16
NO_INDEX ヒント、4-35
NOLOGGING、3-9
NOSORT、3-9
U
O
ONLINE、3-11
OPTIMIZER_ADAPTIVE_REPORTING_ONLY パラメータ、
付-45
OPTIMIZER_DYNAMIC_SAMPLING パラメータ、付-64
OPTIMIZER_FEATURES_ENABLE パラメータ、付-46
OPTIMIZER_INDEX_CACHING パラメータ、6-8、付-44
OPTIMIZER_INDEX_COST_ADJ パラメータ、6-6、付-43
OPTIMIZER_MODE パラメータ、6-3、付-43
OPTIMIZER_USE_INVISIBLE_INDEXES パラメータ、付-19
ORDERED ヒント、5-23
UNIQUE 制約、付-21
USE_HASH ヒント、5-27
USE_INVISIBLE_INDEXES ヒント、付-19
USE_MERGE ヒント、5-28
USE_NL_WITH_INDEX ヒント、5-26
USE_NL ヒント、5-25
USER_CONSTRAINTS ビュー、付-21
USER_DUMP_DEST パラメータ、2-8
USER_IND_COLUMNS ビュー、3-12
USER_INDEXES ビュー、3-10
utlxplp.sql、2-25
V
V$DIAG_INFO ビュー、付-81
V$OBJECT_USAGE ビュー、付-23
V$SQLSTATS ビュー、2-3
P
PARSE、1-5
PGA_AGGREGATE_TARGET パラメータ、6-7、付-43
PLAN_TABLE 表、2-25、付-29
PLUSTRACE ロール、2-19
plustrce.sql、2-19
PRIMARY KEY 制約、付-21
あ
アクセス・パス、1-7
い
一意索引、3-7
R
RESULT_CACHE_MAX_RESULT パラメータ、付-45
RESULT_CACHE_MAX_SIZE パラメータ、付-45
RESULT_CACHE_REMOTE_EXPIRATION パラメータ、付-45
REVERSE、付-1
ROWID スキャン、1-11
S
SORT AGGREGATE、4-26
SORT GROUP BY NOSORT、4-24
SORT UNIQUE NOSORT、4-22
SORT_AREA_SIZE パラメータ、付-44
SQL アクセスアドバイザ、付-76
SQL チューニングアドバイザ、付-75
SQL トレース、2-5
SQL 計画ディレクティブ、付-66
株式会社アシスト
お
オプティマイザ・ヒント、4-29
オプティマイザ統計、1-19、6-1、6-9
か
カーディナリティ、3-3
き
逆キー索引、付-1
こ
コストベース・オプティマイザ、1-19
コンポジット索引、3-5、4-17
Copyright（C） K.K. Ashisuto All Rights Reserved.
索引-2
索引
さ
ひ
最高水位標、1-9
索引スキャン、1-13
索引レンジ・スキャン、4-4
索引一意スキャン、4-6
索引構成表、付ｰ 7
ビットマップ索引、3-1、付-11
ヒント、4-29、付-37
し
自動 SQL チューニング、2-1
自動メンテナンス、3-1
自動メンテナンス、3-15
自動再最適化、付-65
実行計画、1-7
手動 SQL チューニング、2-1
初期化パラメータ、6-1
初期化パラメータ、6-3
初期化パラメータ、付-43
す
スループット、1-2
せ
全索引スキャン、4-12
全表スキャン、1-9
そ
ふ
ファンクション索引、付ｰ 7
不可視化索引、付-17
複合索引、3-5
ブランチ・ブロック、1-15
フル・テーブル・スキャン、1-9
ま
マルチ・ブロック読込み、1-9
り
リアルタイム SQL 監視、付-76
リーフ・ブロック、1-15
リーフ分割、3-16
る
ルート・ブロック、1-15
れ
レスポンスタイム、1-2
連結索引、3-5
ソート/マージ結合、5-7
ち
直積結合、5-17
て
デカルト結合、5-17
適応計画、付-65
と
動的統計、付-63
トレース・ファイル、付-79
ね
ネステッド・ループ結合、5-11
は
バインド変数、4-39
ハッシュ結合、5-3
Copyright（C） K.K. Ashisuto All Rights Reserved.
株式会社アシスト
索引-3
SQLパフォーマンス・チューニング（12cR1対応版）　第1版　2015年1月1日
本テキストで使用されるメーカ名、商品名は各社の商標または登録商標です。当社の文書による許可なしに、
本誌の一部または全部を複製することはお断りします。
また、本誌に記載された内容については、予告なしに変更することがありますので、予めご了承ください。
株式会社アシストは、本書および本書に付属する資料についてその記載内容に誤りがないこと、および特定目的
に対する適合性に関するいっさいの保証を行うものではありません。
また、本資料を使用したことによって被った直接的・間接的な損害などについて、いかなる場合においても責任
を負いかねます。
発売元　株式会社
株式会社アシストは、日本オラクル
株式会社の代理店です。

Download Report