津島博士のパフォーマンス講座
SQLチューニングの基礎
日本オラクル株式会社
データベース事業統括 製品戦略統括本部
データベースエンジニアリング本部
担当ディレクター
津島 浩樹
Oracle DBA & Developer Days 2014
for your
Skill
使える実践的なノウハウがここにある
•
以下の事項は、弊社の一般的な製品の方向性に関する概要を説明する
ものです。また、情報提供を唯一の目的とするものであり、いかなる契約
にも組み込むことはできません。以下の事項は、マテリアルやコード、機
能を提供することをコミットメント（確約）するものではないため、購買決定
を行う際の判断材料になさらないで下さい。オラクル製品に関して記載さ
れている機能の開発、リリースおよび時期については、弊社の裁量により
決定されます。
OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。 文中の社名、商品名等は各社の商標または登録商標である場合があります。はじめに
「津島博士のパフォーマンス講座」の紹介
•
連載していますのでよろしくお願いいたします。
アジェンダ
1 2 3SQLチューニングとは
オプティマイザの概要
実行計画
実行計画のチューニング
Oracle Database 12cの拡張
4 5 2 3オプティマイザの概要
実行計画
実行計画のチューニング
Oracle Database 12cの拡張
4 51.
問題がある（遅い）SQLの特定と分析
•
AWRなどから分析する
–
リソースの問題（キャッシュ・ヒット率、I/O性能）などはインスタンス・チューニング
2.
チューニング（最適な実行計画にする）
•
手動（実行計画を分析）
•
自動（SQLチューニング・アドバイザなど）
3.
テスト
•
効果を確認（SQL*Plusで実行など）
•
目標に対して評価（未達であれば再度チューニング）
SQLチューニングとは
•
オプティマイザが以下の手順で決定する
1.
アクセス方法（索引スキャン、フル・スキャン）
–
カーディナリティ（述語を適用した行数）を算出して、
それのI/O時間が短いアクセス
–
単一ブロック・リードとマルチ・ブロック・リードの比較
•
リード回数とリード性能で決まる
2.
結合順序（表数!(階乗)の組合せ）
–
カーディナリティの小さいものから
•
メモリを使用するとき（ハッシュ結合など）はサイズも影響
–
2表目からは結合のカーディナリティ（結合後の行数）も考慮する
3.
結合方法
–
それぞれの結合順序で以下の結合方法のコストを算出
SQLチューニングとは
最適な実行計画
1行 2行 3行 1行 2行 3行 ・ ・ ・ 10行 1行 2行 3行 1行 2行 3行 10行 ・ ・ ・•
Nested Loop Join（ネステッド・ループ結合）
–
基本は索引を使用する結合（表の行数が少ないときは全表スキャンでも）
•
Hash Join（ハッシュ結合）
–
索引を使用しない等価結合では最も効果的
•
Sort Merge Join（ソート・マージ結合）
–
索引を使用しない等価結合以外など（ソート処理が重いのであまり使用しない）
•
結合タイプによっては結合順序が決まる場合も
–
外部結合、セミ結合、アンチ結合
SQLチューニングとは
•
表、索引
–
テーブル構造、パーティション、索引の設計など
•
これによる性能の違いが大きい
→ すべてを速くすることはできない
•
SQL文
–
最適にするには多少の知識が必要
•
詳細は「オプティマイザの概要」や「実行計画のチューニング」で
•
初期化パラメータ
–
メモリ領域サイズ、I/Oサイズなど
•
オプティマイザ統計
–
これでカーティナリティ（行数）などが決まる
SQLチューニングとは
最適な実行計画を決める要素
自動メモリ管理など
自動オプティマイザ統計収集
アジェンダ
1 2 3SQLチューニングとは
オプティマイザの概要
実行計画
実行計画のチューニング
Oracle Database 12cの拡張
4 5オプティマイザの種類
使用可能なアクセスパスを
順序づけるランキングに基づいて
実行計画を作成
統計情報に基づきコストを
見積もり、最もコストの低い
実行計画を作成
コストベース
オプティマイザ
（ＣＢＯ）
ルールベース
オプティマイザ
（ＲＢＯ）
※Oracle10gからはCBOのみに
•
SQL構文から実行計画を作成
–
OLTPタイプのような単純なSQLに向いている
•
SQLの作成が難しい
–
ルールを覚える必要がある（誰でも作成できる訳ではない）
•
データの変化に対応できない
–
同じ条件でも最適な実行計画は異なる（データ分布の違い）
–
データの増加によって実行計画は変化する
•
限界がある
–
新機能には対応できない
オプティマイザの種類
ルールベース・オプティマイザの問題
•
問合せトランスフォーマ（問合せ変換）
–
適切と判断した問合せに変換
（アクセス・パスの候補を追加）
•
エスティメータ
–
アクセス・パスのコストを計算
•
プラン・ジェネレータ
–
複数のアクセス・パスを計算させて、
最もコストが低い実行計画を生成
–
アクセス・パス数の制限
オプティマイザの構成要素
パーサー（構文解析）
プランの実行
ディクショナリ
（統計）
CBOのみ
オプティマイザ
プラン・ジェネレータ
エスティメータ
問合せトランスフォーマ
•
SQLによっては最適な実行計画にならない
–
最適なSQLは多少の知識が必要（特に、結合に対する副問合せの活用など）
•
オプティマイザが代わりに行う
•
データ（データ分布）によって最適な実行計画が異なる
–
CBOではコストから変換するか判断する
–
例えば、
•
索引を使用するSQL
⇔
索引を使用しないSQL
•
副問合せを使用するSQL
⇔
副問合せを使用しないSQL
•
オプティマイザ統計によって最適でないときもある
–
知っているとSQLを書き換えるときのヒントになる
問合せ変換
CBOのとき
最適な実行計画に変換
•
ORまたはINリストで索引を使用するように
–
OR Expansion（OR拡張）
•
UNION ALLに変換（USE_CONCATヒント）
–
変換しないとき（NO_EXPANDヒント）
問合せ変換
代表的な機能（索引の使用）
SQL> SELECT * FROM tab1 WHERE c1 = 100
2 UNION ALL
3 SELECT * FROM tab1 WHERE c2 = 200 AND LNNVL(c1 = 100)
4 UNION ALL
SQL> SELECT * FROM tab1
2 WHERE c1 = 100 OR c2 = 200 OR c3 = 300;
実行計画 ---| Id ---| Operation ---| Name ---| ---| 0 ---| SELECT STATEMENT ---| ---| | 1 | CONCATENATION | | | 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | |* 3 | INDEX UNIQUE SCAN | PK_TAB1 | |* 4 | TABLE ACCESS BY INDEX ROWID| TAB1 | |* 5 | INDEX RANGE SCAN | IX_TAB1 | | … |•
副問合せ
–
ビュー／インライン・ビュー（FROM句の副問合せ）
•
通常の結合方法になる（使用する／使用しない／作成する）
–
次頁参照
–
ネストした副問合せ（WHERE句の副問合せ）
•
通常の結合方法に変換する（FILTERなどから）
–
Subquery Unnesting（副問合せのネスト解除）
–
スカラー副問合せ（SELECTリストの副問合せ）
•
通常の結合方法に変換する（Oracle Database 12cから）
–
Scalar Subquery Unnesting（スカラー副問合せのネスト解除）
問合せ変換
•
ビュー／インライン・ビュー
–
ビューを使用しない（結合順を変えたい、索引を使用したい）
•
View Merging（ビュー・マージ）
–
ビューをマージできない／しない（Group by 後にネステッド・ループ結合するなど）
•
Predicate Pushing（述語のプッシュ）
–
インライン・ビューを作成する（早い段階で重複値を削除するなど）
•
Group by Placement （Group by の配置の最適化）
•
補足説明参照
問合せ変換
代表的な機能（結合／副問合せを効果的に）
ビューとは
•
アクセス制御などに使用
•
ビューから実行（結合順が決まる）
•
表統計
–
行数、データ・ブロック数、行連鎖・行移行の数、平均行長
•
索引統計
–
索引内の個別値数、索引の深さ（BLEVEL）、リーフ・ブロック数、クラスタ化係数
•
列統計
–
個別値数、NULL数、平均列データ長、データ分布（最小値と最大値、ヒストグラム）
•
システム統計（CPUコスト・モデルで使用）
–
CPU性能、I/O性能（単一ブロック・リード、マルチ・ブロック・リード）
パーティション表には、パーティション、サブ・パーティションごとにも同様の情報を収集する
オプティマイザ統計情報
主な項目（正確にする運用が重要）
表アクセス・コスト
索引アクセス・コスト
カーディナリティ、サイズ
アクセスする行数やブロック数を求め、CPU性能やI/O性能でコストを計算する
この情報には限界がるので、大きく異なる ようなデータは注意が必要（12cから改善）•
オプティマイザ統計を変更しなくても実行計画は変化するので注意
–
オプティマイザ統計が正しくない
•
カーディナリティ・フィードバック
–
カーディナリティが異なる場合は、2回目以降から補正して実行計画を作成
（ただし、メモリ上にカーソルが存在するときだけ）
–
バインド変数の値が異なる（戻される行数が異なる）
•
ハード・パース時にハンド変数の値で実行計画を作成（バインド変数の先読み）
–
等価条件はヒストグラムがないと動作しない
–
範囲条件（<,>）はヒストグラムがなくても動作
–
大きな表に複雑なWHERE句でパラレル実行する
•
動的サンプリングのレベルを自動的に決定（Oracle Database 11gR2から）
実行計画の変化
•
ビューを主問合せにマージすることで以下を可能にする
–
結合順を最適にできない
–
ビュー実行後には索引が使用できない
問合せ変換
補足説明（View / Complex View Merging）
SQL> SELECT D.loc, avg(sal) FROM dept D, emp E
2 WHERE D.deptno = E.deptno AND D.loc = 'London'
3 GROUP BY E.deptno, D.loc ;
SQL> CREATE VIEW v_avg_salary AS
2 SELECT deptno, avg(sal) avg_sal FROM emp GROUP BY deptno ;
SQL> SELECT D.loc, avg_sal FROM dept D, v_avg_salary V
2 WHERE D.deptno = V.deptno AND D.loc = 'London' ;
•
ビューは結合前に行数を削減できるメリットがある
–
ビューをマージしない場合は述語をビュー内へ
•
結合述語で索引を使用（ネステッド・ループ結合）
問合せ変換
補足説明（Predicate Pushing）
SQL> CREATE VIEW v_tab01 AS
2 SELECT c02,SUM(c03) FROM tab01 GROUP BY c02 ; SQL> SELECT * FROM tab02 A, v_tab01 B
2 WHERE A.c2 = B.c02 ;
SQL> SELECT * FROM tab02 A,
2 (SELECT c02,SUM(c03) FROM tab01 B 3 WHERE A.c2 = B.c02 GROUP BY c02) ;
実行計画 ---| Id ---| Operation ---| Name ---| ---| 0 ---| SELECT STATEMENT ---| ---| | 1 | NESTED LOOPS | | | 2 | TABLE ACCESS FULL | TAB02 | | 3 | VIEW PUSHED PREDICATE | V_TAB01 | |* 4 | FILTER | | | 5 | SORT AGGREGATE | | | 6 | TABLE ACCESS BY INDEX ROWID| TAB01 | |* 7 | INDEX RANGE SCAN | IX_TAB01 | ---Predicate Information (identified by operation id): ---実行計画（変換しない） ---| Id ---| Operation ---| Name | ---| 0 ---| SELECT STATEMENT ---| | |* 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL | TAB02 | | 3 | VIEW | V_TAB01 | | 4 | HASH GROUP BY | | | 5 | TABLE ACCESS FULL| TAB01 |
•
結合前にインライン・ビューを実行（作成）することで行数を削減する
–
重複値が多いときにGroup byなどをすることで行数を削減
問合せ変換
補足説明（Group by Placement）
SQL> SELECT A,c2, SUM(B.c2), COUNT(*) cnt
2 FROM tab1 A, tab2 B WHERE A.c1 = B.c1 GROUP BY A.c2 ;
SQL> SELECT A,c2, SUM(B.c2), SUM(ct) cnt FROM tab1 A,
2 (SELECT c1, SUM(c2) c2, COUNT(*) ct FROM tab2 GROUP BY) B
3 WHERE A.c1 = B.c1 GROUP BY A.c2 ;
•
ネストした副問合せの結合方法を変換
–
FILTERから通常の結合方法（ハッシュ結合など）に変換
•
IN条件はインライン・ビュー
•
EXISTS条件はセミ結合
•
NOT IN、NOT EXISTS条件はアンチ結合
問合せ変換
補足説明（Subquery Unnesting）
SQL> SELECT * FROM tab1 A WHERE EXISTS (SELECT 0 FROM tab2 B WHERE A.c1 = B.c1); 実行計画 ---| Id ---| Operation ---| Name ---| ---| 0 ---| SELECT STATEMENT ---| ---| |* 1 | FILTER | | | 2 | TABLE ACCESS FULL| TAB1 | |* 3 | TABLE ACCESS FULL| TAB2 |
実行計画
---| Id ---| Operation ---| Name ---| ---| 0 ---| SELECT STATEMENT ---| ---| |* 1 | HASH JOIN SEMI | | | 2 | TABLE ACCESS FULL| TAB1 | | 3 | TABLE ACCESS FULL| TAB2 |
•
EXISTSとIN
–
どのように使い分けるか
•
EXISTS（相関副問合せ）
–
ネステッド・ループ結合の駆動表はtab1（tab2.c1の索引を使用する）
•
IN（非相関副問合せ）
–
ネステッド・ループ結合の駆動表はtab2（tab1.c1の索引を使用する）
•
インライン・ビュー
問合せ変換
補足説明（Subquery Unnesting）
SQL> SELECT c1 FROM tab1 A, (SELECT DISTINCT c1 FROM tab2) B WHERE A.c1 = B.c1 ; SQL> SELECT c1 FROM tab01 WHERE c1 IN (SELECT c1 FROM tab2) ;
索引や重複値な
どによって
SQL> SELECT c1 FROM tab1 A WHERE EXISTS (SELECT 0 FROM tab2 B WHERE A.c1 = B.c1) ;
セミ結合
同じ意味（重複値を排除して結合）なので、 インライン・ビュー（通常の表結合）に変換
（現在は、どちらを使用しても変換してくれる）
アジェンダ
1 2 3SQLチューニングとは
オプティマイザの概要
実行計画
実行計画のチューニング
Oracle Database 12cの拡張
4 5•
Explain plan for <SQL>
–
実際にはSQLは実行されない
•
SQL*PlusのAUTOTRACEコマンド
–
set autotrace traceonly explain以外は実際にSQLを実行
•
SQLトレース
–
SQLのトレースを取得
–
Tkprofコマンドによりトレースファイルからプランを取得
実行計画の確認方法
• 再実行が必要
• plan_tableが必要
• 負荷が高い
•
V$SQL及びV$SQL_PLAN（Oracle9iから）
–
共有プールのSQL
の実行計画をV$SQL_PLANビューを使用して検索
•
DBMS_XPLAN.DISPLAY_CURSOR関数（Oracle Database 10gから）
–
実行時の統計も出力
•
リアルタイムSQL監視（Oracle Database 11gから）
–
実行中の実行計画も出力
•
AWR（Oracle Database 10gから） / STATSPACK（Oracle9iから）からの出力
–
レベルによってスナップショットにSQLの実行計画が含まれる
•
AWR（STATISTICS_LEVEL=TYPICAL） ⇒ $ORACLE_HOME/rdbms/admin/awrsqrpt.sql
•
STATSPACK（スナップショットLEVELが6以上） ⇒ $ORACLE_HOME/rdbms/admin/sprepsql.sql
実行計画の確認方法
実行計画の確認方法
DBMS_XPLAN.DISPLAY_CURSOR関数
•
見積もり以外に実行時の統計も出力できる（STATISTICS_LEVEL=ALLまたはヒント）
SQL> SELECET /*+ GATHER_PLAN_STATISTICS */ … ;
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id=>'<sql_id>',format=>'typical allstats last')); 実行計画
---| Id ---| Operation ---| Name ---| Starts| E-Rows|E-Bytes| Cost (%CPU)| E-Time | A-Rows| A-Time | ---
---Buffers| OMem| 1Mem| Used-Mem| Used-Tmp| ---＜実行統計＞ Starts：実行された回数 Buffers：バッファのアクセス数 A-Rows：処理行数 A-Time：処理時間 Used-Mem：使用されたメモリサイズと処理方法（0:Optimal,1:1-pass,N;Multi-pass） Used-Tmp：使用されたTEMPサイズ 省略すると最後のSQL
実行計画の確認方法
リアルタイムSQL監視
•
実行中の実行計画を表示
（デフォルトで5秒以上のSQL、MONITORヒントで強制的に）
時間のかかっているSQLが自動的に監視
されリストされる (経過時間等でソート可能)
このSQL実行
全体の統計
実行計画のステッ
プごとの統計など
ステップごとの待機イベント
•
カーディナリティ
–
述語を適用した行数（Rows、E-Rows、A-Rows）
•
アクセス方法
–
索引スキャン、フル・スキャン、ビュー・アクセス
•
結合方法
–
ネステッド・ループ結合、ハッシュ結合、ソート・マージ結合、直積
•
結合タイプ
–
外部結合、セミ結合、アンチ結合
•
結合順序
–
SQLによって決まる場合も
実行計画の解説
注目する項目
•
パーティション
–
パーティション・プルーニング
•
パラレル実行
–
データ分散のステップが増える
実行計画の解説
注目する項目
実行計画の解説
•
リーフ・ステップ(インデントの一番深いステップ)から実行して、結合（同一インデ
ント）は上位に表示されたものが最初になる
SQL> SELECT … FROM tab1,tab2,tab3 WHERE tab1.c2=tab2.c2 AND tab1.c3=tab3.c3 AND tab1.c1<100
2 GROUP BY … ORDER BY … ;
実行計画
---| Id ---| Operation ---| Name ---| Rows ---| … ---| Cost (%CPU)---|
---| 0 ---| SELECT STATEMENT ---| ---| ---| ---| 271 (1)---|
| 1 | SORT GROUP BY | | | | |
|* 2 | HASH JOIN | | | | |
|* 3 | HASH JOIN | | | | |
|* 4 | TABLE ACCESS FULL | TAB1 |
10 | | |
| 5 | TABLE ACCESS FULL | TAB2 | 50 | | |
| 6 | TABLE ACCESS FULL | TAB3 | 100 | | |
(1)
(2)
①
(3)
②
③
| TABLE ACCESS BY INDEX ROWID| TAB1 |
| INDEX RANGE SCAN | IX_TAB1 |
索引アクセス
| VIEW | |
| HASH GROUP BY | |
| TABLE ACCESS FULL | TAB1 |
ビュー・アクセス
カーディナリティ
結合
CPUコスト・モデル
（CPUコストの割合）
実行計画の解説
•
後半には、述語の情報とNote部が出力される
実行計画 ---| Id ---| Operation ---| Name ---| ---| 0 ---| SELECT STATEMENT ---| ---| | 1 | SORT GROUP BY | | |* 2 | HASH JOIN | | |* 3 | HASH JOIN | | |* 4 | TABLE ACCESS FULL | TAB1 | | 5 | TABLE ACCESS FULL | TAB2 | …Predicate Information (identified by operation id): ---2 - access("TAB1"."C---2"="TAB---2"."C---2") 3 – access("TAB1"."C3"="TAB3"."C3") 4 – filter("TAB1"."C1"<100) Note
---述語の情報（どのステップで述語が使用されているか）
Note部（内部的に動作しなものなど）
•
索引スキャン（シングル・ブロック・リード）
–
Index Range Scan（索引範囲スキャン）
–
Index Unique Scan（索引一意スキャン）
–
Index Skip Scan（索引スキップ・スキャン）
•
後方一致（中間一致）条件
•
フル・スキャン
–
Table Full Scan（全表スキャン）
•
テーブルをマルチ・ブロック・リード
–
Index Full Scan（全索引スキャン）
•
シングル・ブロック・リード
–
Index Fast Full Scan（高速全索引スキャン）
•
リーフ・ノードをマルチ・ブロック・リード
実行計画の解説
アクセス方法
< 50 < 20 30 < 60 70 1 0 11 12 3 0 31 50 51…
範囲スキャン/全
索引スキャン
…
•
Index Unique Scan
–
一意索引の等価条件
•
Index Range Scan
–
非一意索引の等価条件
–
一意索引／非一意索引の範囲条件
実行計画の解説
アクセス方法（Index Range Scan / Index Unique Scan）
SQL> SELECT * FROM tab1 WHERE c1 < 10; 実行計画
---| Id ---| Operation ---| Name ---| ---| 0 ---| SELECT STATEMENT ---| ---| | 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | SQL> SELECT * FROM tab1 WHERE c1 = 10;
実行計画
---| Id ---| Operation ---| Name ---| ---| 0 ---| SELECT STATEMENT ---| ---| | 1 | TABLE ACCESS BY INDEX ROWID| TAB1 |
•
後方一致（中間一致）条件での索引スキャン（Oracle9iから）
–
全表スキャンより効果的なとき（c1の個別値が少ないときなど）
実行計画の解説
アクセス方法（Index Skip Scan）
SQL> CREATE INDEX ix2_tab1 ON tab1 (c1,c2);
…
SQL> SELECT * FROM tab1 WHERE c2 = 10;
実行計画
---| Id ---| Operation ---| Name ---|
---| 0 ---| SELECT STATEMENT ---| ---|
| 1 | TABLE ACCESS BY INDEX ROWID| TAB1
|
|* 2 |
INDEX SKIP SCAN
| IX2_TAB1 |
c1 c2 1 1 ･･･ 1 1 2 ･･･ 10 2 2 ･･･ 2 1 2 ･･･ 10 3 3 ･･･ 1 2 ･･･ 10 3 ･･･ ･･･
テーブル
索引
c1の値ごとにc2=10を行う
c1=1 AND c2=10
c1=2 AND c2=10
c1=3 AND c2=10
・・・
•
索引範囲スキャンはできないが（索引に対する条件はないが）
ソート処理が必要なときなど
実行計画の解説
アクセス方法（Index Full Scan）
SQL> CREATE INDEX ix2_tab1 ON tab1 (c1,c2);
…
SQL> SELECT * FROM tab1 WHERE c2 > 10 ORDER BY c1;
実行計画
---| Id ---| Operation ---| Name ---|
---| 0 ---| SELECT STATEMENT ---| ---|
| 1 | TABLE ACCESS BY INDEX ROWID| TAB1
|
|* 2 | INDEX FULL SCAN | IX2_TAB1 |
•
テーブルをアクセスする必要がないとき
–
テーブルより索引の方がサイズが小さい
実行計画の解説
アクセス方法（Index Fast Full Scan / Table Full Scan）
SQL> CREATE INDEX ix2_tab1 ON tab1 (c1,c2);
…
SQL> SELECT c1,c2 FROM tab1 WHERE c1 > 10;
実行計画
---| Id ---| Operation ---| Name ---|
---| 0 ---| SELECT STATEMENT ---| ---|
|* 1 | INDEX FAST FULL SCAN| IX2_TAB1 |
SQL> SELECT * FROM tab1 WHERE c1 > 10;
実行計画（全表スキャン）
---| Id ---| Operation ---| Name ---|
---| 0 ---| SELECT STATEMENT---| ---|
|* 1 | TABLE FULL SCAN| TAB1 |
•
ビュー／インライン・ビューをマージしないとき（”VIEW”が出力される）
–
内部的に作成されたときでも出力される
実行計画の解説
アクセス方法（ビュー・アクセス）
SQL> SELECT … FROM tab2,
(SELECT c1,sum(c2) FROM tab1 GROUP BY c1)
A WHERE A.c1 = tab2.c1 ;
実行計画
---| Id ---| Operation ---| Name ---| Rows ---|
---| 0 ---| SELECT STATEMENT ---| ---| ---|
| 1 | HASH JOIN | | xxx |
| 2 |
VIEW
| | 50 |
| 3 | HASH GROUP BY | | 50 |
| 4 | TABLE ACCESS FULL| TAB1 | 100K|
| 5 | TABLE ACCESS FULL | TAB2 | 100 |
SQL> SELECT … FROM tab1,tab2
2 WHERE tab1.c1 = tab2.c1 GROUP BY c1 ;
実行計画（ビュー・マージすると）
---| Id ---| Operation ---| Name ---| Rows ---|
---| 0 ---| SELECT STATEMENT ---| ---| ---|
| 1 | HASH GROUP BY | | |
| 2 | HASH JOIN | | xxx |
| 3 | TABLE ACCESS FULL| TAB2 | 100 |
•
Nested Loop Join（ネステッド・ループ結合）
–
索引を使用する結合のためアクセス行数が多いと性能が悪くなる
•
索引アクセスのI/Oを改善
–
Multi Join Key Pre-fetching（Oracle9iから）
–
Nested Loops Join Batching（Oracle Database 11gから）
•
Hash Join（ハッシュ結合）
–
索引を使用しない等価結合
•
Sort Merge Join（ソート・マージ結合）
–
索引を使用しない等価結合以外など
•
Cross Join（直積）
–
結合条件がない（負荷が高いのでできるだけ使用しないように）
実行計画の解説
•
基本は内部表の索引を使用して結合する
–
駆動表は絞り込み条件があるときに索引スキャン
実行計画の解説
結合方法（ネステッド・ループ結合）
SQL> SELECT … FROM tab1,tab2 WHERE tab1.c1 = tab2.c1 GROUP BY … ;
実行計画
---| Id ---| Operation ---| Name ---| Rows ---|
---| 0 ---| SELECT STATEMENT ---| ---| ---|
| 1 | HASH GROUP BY | | |
| 2 |
NESTED LOOPS
| | xxx |
| 3 | TABLE ACCESS FULL | TAB2 | 100 | <- 駆動表
| 4 | TABLE ACCESS BY INDEX ROWID| TAB1 | xxx | <- 内部表
|* 5 | INDEX RANGE SCAN | IX_TAB1 | xxx |
•
Multi Join Key Pre-fetching（Oracle 9iから）
–
索引レンジ・スキャンのデータ・ブロックの先読み
実行計画の解説
結合方法（ネステッド・ループ結合）
SQL> SELECT … FROM tab1,tab2 WHERE tab1.c1 = tab2.c1 GROUP BY … ;
実行計画
---| Id ---| Operation ---| Name ---| Rows ---|
---| 0 ---| SELECT STATEMENT ---| ---| ---|
| 1 | HASH GROUP BY | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | xxx | 内部表をPer-fetchする（Id=5のROWIDから）
| 3 |
NESTED LOOPS
| | xxx |
| 4 | TABLE ACCESS FULL | TAB2 | 100 | 駆動表
•
Nested Loops Join Batching（Oracle Database 11gから）
–
索引で結合後にROWIDを並べ替えてテーブルにアクセス（索引一意スキャンも可）
実行計画の解説
結合方法（ネステッド・ループ結合）
SQL> SELECT … FROM tab1,tab2 WHERE tab1.c1 = tab2.c1 GROUP BY … ;
実行計画
---| Id ---| Operation ---| Name ---| Rows ---|
---| 0 ---| SELECT STATEMENT ---| ---| ---|
| 1 | HASH GROUP BY | | |
| 2 | NESTED LOOPS | | xxx | Nested Loops Join(2)
| 3 |
NESTED LOOPS
| | xxx | Nested Loops Join(1) => 結果を駆動表(2)
| 4 | TABLE ACCESS FULL | TAB2 | 100 | 駆動表(1)
|* 5 | INDEX RANGE SCAN | IX_TAB1 | xxx | 内部表(1)（索引のみにアクセス）
| 6 | TABLE ACCESS BY INDEX ROWID| TAB1 | xxx | 内部表(2)（
ここのI/Oを最適化
）
ROWIDを
並べ替えて
•
索引の代わりにメモリ上にハッシュ・テーブルを作成
（最初にアクセスするテーブル）
–
片方のテーブル（アクセスするデータ）が小さいと効果的
–
等価結合のみ
–
結合列以外で絞り込み条件があるときに索引スキャン
実行計画の解説
結合方法（ハッシュ結合）
SQL> SELECT … FROM tab1,tab2 WHERE tab1.c1 = tab2.c1 GROUP BY … ;
実行計画
---| Id ---| Operation ---| Name ---| Rows ---| ---| 0 ---| SELECT STATEMENT ---| ---| ---| | 1 | HASH GROUP BY | | | | 2 | HASH JOIN | | xxx |
| 3 | TABLE ACCESS FULL| TAB2 | 100 |←ハッシュ・テーブルを作成
•
索引の代わりにソートして結合
–
等価結合以外など
–
絞り込み条件があるときに索引スキャン
実行計画の解説
結合方法（ソート・マージ結合）
SQL> SELECT … FROM tab1,tab2 WHERE tab1.c1 > tab2.c1 GROUP BY … ;
実行計画
---| Id ---| Operation ---| Name ---| Rows ---| ---| 0 ---| SELECT STATEMENT ---| ---| ---| | 1 | HASH GROUP BY | | | | 2 | MERGE JOIN | | xxx | | 3 | SORT JOIN | | 100 | | 4 | TABLE ACCESS FULL| TAB2 | 100 | |* 5 | SORT JOIN | | 100K|
•
できるだけ行わない
–
結合条件がないので効率が悪い
実行計画の解説
結合方法（直積）
SQL> SELECT … FROM tab1,tab2 GROUP BY … ;
実行計画
---| Id ---| Operation ---| Name ---| Rows ---|
---| 0 ---| SELECT STATEMENT ---| ---| ---|
| 1 | HASH GROUP BY | | |
| 2 |
MERGE JOIN CARTESIAN
| | |
| 3 | TABLE ACCESS FULL | TAB2 | 100 |
| 4 | BUFFER SORT | | 100K|
←ソートする（メモリにバッファリングするため）
•
Outer Join（外部結合）
–
OUTER JOIN
•
Semi Join（セミ結合）
–
EXISTS, IN
•
Anti Join（アンチ結合）
–
NOT EXISTS, NOT IN
•
このような特殊な結合タイプは結合順が決まっている
–
Oracle Database 10gでハッシュ結合の結合順を改善
実行計画の解説
結合タイプ
アンチ結合
主問合せ 副問合せセミ結合
主問合せ 副問合せ•
左外部結合、右外部結合
–
Oracle Database 10gからハッシュ結合で核でないテーブルを先にアクセス可能に
実行計画の解説
結合タイプ（外部結合）
SQL> SELECT … FROM tab1 LEFT OUTER JOIN tab2 USING (c1) GROUP BY … ;
実行計画
---| Id ---| Operation ---| Name ---| Rows ---|
---| 0 ---| SELECT STATEMENT ---| ---| ---|
| 1 | HASH GROUP BY | | |
| 2 |
HASH JOIN OUTER
| | |
| 3 | TABLE ACCESS FULL| TAB1 | 100K|
| 4 | TABLE ACCESS FULL| TAB2 | 100 |
実行計画（Oracle Database 10gから）
---| Id ---| Operation ---| Name ---| Rows ---|
---| 0 ---| SELECT STATEMENT ---| ---| ---|
| 1 | HASH GROUP BY | | |
| 2 |
HASH JOIN RIGHT OUTER
| | |
| 3 | TABLE ACCESS FULL | TAB2 | 100 |
| 4 | TABLE ACCESS FULL | TAB1 | 100K|
•
完全外部結合
–
Native Full Outer Join（Oracle Database 11gから）
•
以前はFull Outer Joinを二つのブランチのUNION ALLで（Left Outer JoinとNOT EXISTS）
実行計画の解説
結合タイプ（外部結合）
SQL> SELECT * FROM tab1 A FULL OUTER JOIN tab2 B USING (c1) ;
実行計画
---| Id ---| Operation ---| Name ---|
---| 0 ---| SELECT STATEMENT ---| ---|
| 1 | VIEW | VW_FOJ_0 |
|* 2 |
HASH JOIN FULL OUTER
| |
| 3 | TABLE ACCESS FULL | TAB1 |
| 4 | TABLE ACCESS FULL | TAB2 |
＜以前は以下のSQLを実行＞
SQL> SELECT *
2 FROM tab1 LEFT OUTER JOIN tab2 USING (c1)
3 UINON ALL
4 SELECT * FROM tab2 B WHERE NOT EXISTS
•
EXISTS、IN条件の副問合せ
•
Oracle Database 10gからセミ・ハッシュ結合で副問合せのテーブルを先にアクセス可能
に
実行計画の解説
結合タイプ（セミ結合）
SQL> SELECT … FROM tab1 WHERE
EXISTS
(SELECT 0 FROM tab2 WHERE tab1.c1 = tab2.c1) ;
実行計画
---| Id ---| Operation ---| Name ---| Rows ---|
---| 0 ---| SELECT STATEMENT ---| ---| ---|
| 1 |
HASH JOIN SEMI
| | |
| 2 | TABLE ACCESS FULL| TAB1 | 100K|
| 3 | TABLE ACCESS FULL| TAB2 | 100 |
実行計画（Oracle Database 10gから）
---| Id ---| Operation ---| Name ---| Rows ---|
---| 0 ---| SELECT STATEMENT ---| ---| ---|
| 1 |
HASH JOIN RIGHT SEMI
| | |
| 2 | TABLE ACCESS FULL | TAB2 | 100 |
| 3 | TABLE ACCESS FULL | TAB1 | 100K|
•
NOT EXISTS、NOT IN条件の副問合せ
–
Oracle Database 10gからアンチ・ハッシュ結合で副問合せのテーブルを先にアクセス可能
に
実行計画の解説
結合タイプ（アンチ結合）
SQL> SELECT … FROM tab1 WHERE
NOT EXISTS
(SELECT 0 FROM tab2 WHERE tab1.c1 = tab2.c1) ;
実行計画
---| Id ---| Operation ---| Name ---| Rows ---|
---| 0 ---| SELECT STATEMENT ---| ---| ---|
| 1 |
HASH JOIN ANTI
| | |
| 2 | TABLE ACCESS FULL| TAB1 | 100K|
| 3 | TABLE ACCESS FULL| TAB2 | 100 |
実行計画（Oracle Database 10gから）
---| Id ---| Operation ---| Name ---| Rows ---|
---| 0 ---| SELECT STATEMENT ---| ---| ---|
| 1 |
HASH JOIN RIGHT ANTI
| | |
| 2 | TABLE ACCESS FULL | TAB2 | 100 |
| 3 | TABLE ACCESS FULL | TAB1 | 100K|
•
パーティション・プルーニング
–
静的プルーニングと動的プルーニング（バインド変数、データ型の暗黙変換など）
–
パーティションの開始と終了の項目が増える
•
Pstart：アクセス開始のパーティション（静的：数字、動的：KEY）
•
Pstop：アクセス終了のパーティション（静的：数字、動的：KEY）
•
ジェイン・フィルター（ブルーム・フィルタリング）
–
結合列のパーティション・プルーニング
•
パーティション化された明細表をマスタ表の条件で検索するなど
–
このSQLのときなどに’明細表.c1’でパーティション・プルーニングを行う
実行計画の解説
パーティション
実行計画の解説
パーティション（パーティション・プルーニング）
SQL> SELECT * FROM tab01 WHERE sdate = TO_DATE('2012/08/01','YYYY/MM/DD'); 実行計画（静的パーティション・プルーニング）
---<途中省略>---| Id ---<途中省略>---| Operation ---<途中省略>---| Name ---<途中省略>---| ---<途中省略>---| Pstart---<途中省略>---| Pstop ---<途中省略>---| ---<途中省略>---| 0 ---<途中省略>---| SELECT STATEMENT ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| | 1 | PARTITION RANGE SINGLE | | | 8 | 8 | |* 2 | TABLE ACCESS FULL | TAB01 | | 8 | 8 |
実行計画（動的パーティション・プルーニング）
---<途中省略>---| Id ---<途中省略>---| Operation ---<途中省略>---| Name ---<途中省略>---| ---<途中省略>---| Pstart---<途中省略>---| Pstop ---<途中省略>---| ---<途中省略>---| 0 ---<途中省略>---| SELECT STATEMENT ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| | 1 | PARTITION RANGE SINGLE | | | KEY | KEY | |* 2 | TABLE ACCESS FULL | TAB01 | | KEY | KEY |
• バインド変数
• データ型の暗黙変換 • など
•
結合時に効果的にフィルタリングする（結合列でフィルタリングする）
–
ymでフィルターされたtime_cdのビットマップを作成する
実行計画の解説
パーティション（ジョイン・フィルター／ブルーム・フィルタリング）
SQL>
SELECT * FROM sales_h S, time T
2 WHERE S.time_cd = T.time_cd AND T.ym = TO_DATE('201201','YYYYMM') ;
実行計画
---<途中省略>---| Id ---<途中省略>---| Operation ---<途中省略>---| Name ---<途中省略>---| ---<途中省略>---| Pstart---<途中省略>---| Pstop ---<途中省略>---|
---<途中省略>---| 0 ---<途中省略>---| SELECT STATEMENT ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| ---<途中省略>---|
|* 1 | HASH JOIN | | | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | | | |
| 3 | PARTITION RANGE SINGLE | | | 1 | 1 |
|* 4 | TABLE ACCESS FULL | time | | 1 | 1 |
| 5 |
PARTITION RANGE JOIN-FILTER
| | |
:BF0000
|
:BF0000
|
| 6 | TABLE ACCESS FULL | sales_h | |
:BF0000
|
:BF0000
|
•
スキャンのデータ分散
–
ブロック単位（PX BLOCK ITERATOR）
–
パーティション単位（PX PARTITION RANGE ALLなど）
–
データ分散しない（片方の読込みデータが少ないとき）
•
それぞれのPQプロセスで全てのデータ
•
スキャン以外でのデータ再分散
–
PX SEND HASH / RANGE / BROADCAST / PARTITION
•
基本はHASH（重複データが多いときに注意）
–
PX RECEIVE
実行計画の解説
パラレル実行（データ分散）
PQ スキャン 表 PQ PQ PQ 結合 PQ PQ PQ PQ QC PQ PQ PQ PQ OrderBy スキャン以外でのデータ再分散 スキャンのデータ分散•
Block-based granules
–
ブロック単位で分割してアクセス
–
PX BLOCK ITERATOR
•
Partition-based granules
–
パーティション単位に分割してアクセス
•
パーティション・ワイズ結合など
–
PX PARTITION RANGE ALL
–
PX PARTITION HASH ALL
–
など
実行計画の解説
パラレル実行（スキャンのデータ分散）
PQ スキャン PQ PQ PQ QCP1
表P2
P3
P4
PQ スキャン PQ PQ PQ QC 表•
操作内パラレル化のデータ分散（偏りが発生すると効果が低下する）
–
HASH（基本はこれを使用する）
•
重複データが多いときに注意
–
RANGE（ソートなど）
–
BROADCAST（結合の片方が小さい）
•
同じデータをすべてのプロセスに
–
PARTITION（パーティション・ワイズ結合）
•
パーティション分割（片方がパーティション表のとき）
–
NONE（データ分散しない／データ通信しない）
•
PX SEND xxxxとPX RECEIVEがない
•
PARTITIONやBROADCASTなどと使用
実行計画の解説
パラレル実行（スキャン以外でのデータ再分散）
PQ スキャン 表 操 作 間 パ ラ レ ル 化 操作内パラレル化 PQ PQ PQ 結合 PQ PQ PQ PQ QC PQ PQ PQ PQ OrderBy ① ② ③ PX SEND xxxx PX RECEIVE•
結合の片方をデータ分散しない（スキャンと結合が同じプロセス）
実行計画の解説
パラレル実行（BROADCAST分散とPARTITION分散）
スキャン ＋結合P2
P3
P4
PQ スキャン PQ PQ PQ 結合 PQ PQ PQ PQ tab1 tab2P1
PARTITION分割 （パーシャル・パーティション・ワイズ結合） 結合 tab1 BROADCAST分割 スキャン PQ PQ PQ PQ tab2 PQ PQ PQ PQ スキャンと結合が同じプロセス スキャン ＋結合 同じデータを 全プロセスに tab2の パーティション に合わせる フル・パーティション・ワイズ結合は、どちらでもデータ分散しない実行計画の解説
パラレル実行
SQL> SELECT * FROM tab01,tab02 WHERE tab01.c1 = tab02.c1 ORDER BY c0;
実行計画
---<途中省略>---| Id ---<途中省略>---| Operation ---<途中省略>---| Name ---<途中省略>---| ---<途中省略>---| TQ ---<途中省略>---|IN-OUT---<途中省略>---| PQ Distrib ---<途中省略>---| ---<途中省略>---| 0 ---<途中省略>---| SELECT STATEMENT ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10003 | | Q1,03 | P->S | QC (ORDER) | | 3 | SORT ORDER BY | | | Q1,03 | PCWP | | | 4 | PX RECEIVE | | | Q1,03 | PCWP | | | 5 | PX SEND RANGE | :TQ10002 | | Q1,02 | P->P | RANGE | | 6 | HASH JOIN BUFFERED | | | Q1,02 | PCWP | | | 7 | PX RECEIVE | | | Q1,02 | PCWP | | | 8 | PX SEND HASH | :TQ10000 | | Q1,00 | P->P | HASH | | 9 | PX BLOCK ITERATOR | | | Q1,00 | PCWC | | | 10 | TABLE ACCESS FULL| TAB01 | | Q1,00 | PCWP | | | 11 | PX RECEIVE | | | Q1,02 | PCWP | | | 12 | PX SEND HASH | :TQ10001 | | Q1,01 | P->P | HASH | | 13 | PX BLOCK ITERATOR | | | Q1,01 | PCWC | |
結合プロセス（データをチェッ クするので同じプロセス）
PX PARTITION RANGE ALL
スキャンの データ分散
PX SEND PARTITION PX SEND BROADCAST
•
片方の読込みデータが少ないときはBROADCASTも行わない
–
それぞれのプロセスで全てのデータをスキャンする（スキャンのデータ分散もしない）
実行計画の解説
パラレル実行（データ分散なし）
SQL> SELECT COUNT(*) FROM (SELECT * FROM t3,t1 WHERE t3.col1 = t1.col1);
実行計画
---| Id ---| Operation ---| Name ---| Starts ---| E-Rows ---| ---| TQ ---|IN-OUT---| PQ Distrib ---| A-Rows ---| ---| 0 ---| SELECT STATEMENT ---| ---| 1 ---| ---| ---| ---| ---| ---| 1 ---| | 1 | SORT AGGREGATE | | 1 | 1 | | | | | 1 | | 2 | PX COORDINATOR | | 1 | | | | | | 4 | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | | Q1,00 | P->S | QC (RAND) | 0 | | 4 | SORT AGGREGATE | | 4 | 1 | | Q1,00 | PCWP | | 4 | |* 5 | HASH JOIN | | 4 | 50000 | | Q1,00 | PCWP | | 100K| | 6 | TABLE ACCESS FULL | T3 | 4 | 5 | | Q1,00 | PCWP | | 20 | | 7 | PX BLOCK ITERATOR | | 3 | 100K| | Q1,00 | PCWC | | 100K| |* 8 | TABLE ACCESS FULL| T1 | 52 | 100K| | Q1,00 | PCWP | | 100K|
パラレル度が4なので、4つのPQプロセスで5行 リードしている（A-Rowsは20行になっている） すべて同一プロセス
アジェンダ
1 2 3SQLチューニングとは
オプティマイザの概要
実行計画
実行計画のチューニング
Oracle Database 12cの拡張
4 5•
索引（最適な索引）を使用していない
•
結合行数が多い
–
重複値が多い場合は要注意
•
結合方法や結合順が悪い
•
副問合せが効果的に使用されていない
•
TEMP領域を使用している
実行計画のチューニング
SQLが遅くなる主な原因
•
AWR / EM（Enterprise Manager）から特定する
–
Top 5 Timed Events
•
どこに時間が掛っているか
–
SQL統計
•
問題のSQLを探す
–
SQL ordered by Elapsed Time
• パラレル実行ではすべてのプロセスの合計時間になる
–
SQL ordered by CPU Time
–
SQL ordered by User I/O Wait Tim
–
など
•
PL/SQLはPL/SQLとその中のSQLの両方が出力される
•
すべての実行の合計になる（実行回数に注意）
実行計画のチューニング
•
CPUが多いのかI/Oが多いのか（またはその他の待機が多いか）
• db file sequential read（索引スキャンの待機）
• db file scattered read（フル・スキャンの待機） / direct path read（バッファ・キャッシュを経由しないフル・スキャンの待機）
• db file parallel read（リカバリ、プリフェッチの読込みの待機）
• direct path read temp（Tempからの読込みの待機）
実行計画のチューニング
実行計画のチューニング
実行計画のチューニング
実行計画のチューニング
•
チューニングの手順（この順番で改善できるか検討する）
1.
オプティマイザ統計の再収集
2.
索引の作成（索引のチューニング）
3.
SQLの変更（ヒントの追加）
•
SQL計画ベースラインの設定（SQL Plan Management）
–
SQLの変更ができない
•
SQLチューニング・アドバイザ
–
問題を特定できない
実行計画のチューニング
•
行数の見積もりが正しくない（実行時と大きく異なるとき）
–
実行計画の見積もり行数（E-Rows）と実行行数（A-Rows）を比較
実行計画のチューニング
オプティマイザ統計の再収集
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ c1,c2,c3 FROM tab01 WHERE c1 = 11 ;
レコードが選択されませんでした。
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format=>'typical allstats last'));
実行計画
---| Id ---| Operation ---| Name ---| Starts ---| E-Rows ---|E-Bytes---| … ---| A-Rows ---| … ---|
---| 0 ---| SELECT STATEMENT ---| ---| 1 ---| ---| ---| … ---| 0 ---| … ---|
| 1 | TABLE ACCESS BY INDEX ROWID| TAB01 | 1 | 21 | 189| … | 0 | … |
|* 2 | INDEX RANGE SCAN | IX_TAB01 | 1 | 21 | | … | 0 | … |
•
全表スキャンを行っているテーブルは索引スキャンした方が良くないか
–
実行計画のRowsなどを確認
•
索引スキャンの効率が悪くないか
–
索引の列の組合せ、索引の列の順番などを確認
•
例えば、以下のようなSQL
•
優先順位を明確にして作成する
–
これが難しい（そのため、フル・スキャンでも高速なExadataが効果的）
•
作り過ぎないように使用しない索引は削除する（次頁参照）
実行計画のチューニング
索引の作成（索引のチューニング）
SQL> SELECT … WHERE c1=xx AND c2=xx AND c3=xx; ①
SQL> SELECT … WHERE c1=xx AND c3=xx;
②
SQL> SELECT … WHERE c1=xx AND c2=xx AND c4=xx; ③
列(c3,c1)の索引が作成されているとすると
①と②はIndex Range Scanになる（②の方が
より効果的な索引スキャンになる）
③はIndex Skip Scanになる（c3の条件がない）
『使われていない索引を削除したい』
索引のMONITORING属性を設定することにより、V$OBJECT_USAGEビューを検索
することにより監視できます（Oracle9i ～）
SQL> ALTER INDEX PK_EMP
MONITORING USAGE;
…
SQL> SELECT * FROM v$object_usage WHERE INDEX_NAME='PK_EMP' ;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
--- --- --- ---- ---
---PK_EMP EMP YES NO 01/16/2014 08:29:03
実行計画のチューニング
•
オプティマイザの判断を調整する
–
問合せ変換が最適でない
–
結合方法や結合順が最適でない
•
効率の悪いアクセスを改善する
–
索引が使用できない
–
同じテーブルに複数回アクセスしている
–
Redoログを出力しないように
•
TEMP領域の使用を改善する
–
プロセスで使用するデータ・サイズを小さくする
•
パラレル度を上げる、プログラムを分割／並列化する
–
索引を使用する（結合とソートなど）
実行計画のチューニング
SQLの変更／ヒントの追加（主なチューニング）
TEMP領域を使用しないときでも
性能を向上させるためには重要
•
索引を使用しない条件を変更する（可能であれば）
–
演算している、NULL比較、NOT(!=)、OR、後方一致（中間一致）条件
•
インライン・ビューを作成する（問合せ変換しないとき）
–
結合前に行数を削減する（Group by Placementなどを明示的に）
•
結合するUPDATE文をMERGE文にする（UPDATE のみでも可）
–
UPDATE文で結合するには副問合せが必要（効率が悪い）
•
SQLを分割する（WITH句を使用する）
–
同じ副問合せを複数回使用するなど
実行計画のチューニング
SQLの変更（主なSQLの変更）
•
ダイレクト・インサートにする（UPDATE、DELETEなども）
–
NOLOGGING属性でRedoログが出力されない
•
同じテーブルにアクセスするSQLを一つにする
–
CASE式（条件の異なるSELECTから）
–
マルチ・テーブル・インサート（同じテーブルにも使用できる）
–
MERGE文（UPDATE & INSERTから）
•
補足説明参照
実行計画のチューニング
•
結合順を変える
–
ORDERED（FROM句の順番に結合する）、LEADING（指定した順番に結合する）
•
ビュー・マージを行わない
–
NO_MERGE（指定ビューをマージしない）
•
索引を使用する
–
INDEX（索引スキャン）、INDEX_FFS（高速全索引スキャン）
•
索引を使用しない
–
FULL（全表スキャン）、NO_INDEX（指定索引を使用しない）
•
結合方法を変える
実行計画のチューニング
SQLの変更（主に使用するヒント）
•
ヒントを入れた実行計画をベースラインとして登録する
–
索引スキャンを全表スキャンに変更
実行計画のチューニング
SQL計画ベースラインの設定
SELECT * FROM tab01 WHERE c1 = 1;
SELECT /*+ FULL(tab01) */ * FROM tab01 WHERE c1 = 1; SQL計画ベースライン プランA プランB 常にSQL計画ベースラインに ある実行計画が使用される プランB 変更（A → B） 実行
•
問題のSQLを特定できないときはOracleデータベースに任せる
実行計画のチューニング
SQLチューニング・アドバイザ
・統計の分析 ・アクセス・パスの分析 ・SQLプロファイリング ・SQL構造分析 ・代替計画分析（11gR2から） 分析 ・統計の再収集 ・索引の作成／SQLアクセス・アドバイザの実行 ・SQLプロファイルの作成 ・SQLのリライト ・SQL計画ベースラインの作成 推奨事項 高負荷の SQL文 自動チューニング・オプティマイザ SQLチューニング ・アドバイザ•
Group byなどで重複値をなくす
–
慣れないと難しいですが
•
結合列’tab2.c1’でGROUP BYを行う
•
最終的にSUM関数を行う
SQLの変更
補足説明（インライン・ビューを作成する）
SQL> SELECT A.c2, count(*) FROM tab1 A, tab2 B
2 WHERE A.c1 = B.c1 AND B.c2 = 10 GROUP BY A.c2;
SQL> SELECT A.c2,
sum(ct)
FROM tab1 A,
2 (SELECT c1,count(*) ct FROM tab2 WHERE c2 = 10
GROUP BY c1
) B
3 WHERE A.c1 =
B.c1
GROUP BY A.c2;
•
あるテーブルのデータでUPDATEするなど
（
テーブルのアクセスが2回になる
）
SQLの変更
補足説明（結合するUPDATE文をMERGE文にする）
SQL> UPDATE t01 A SET A.c3 = A.c3 + (SELECT c2 FROM t02 B WHERE A.c1 = B.c1) 2 WHERE EXISTS (SELECT 0 FROM t02 B WHERE A.c1 = B.c1);
実行計画 ---| Id ---| Operation ---| Name ---| ---| 0 ---| UPDATE STATEMENT ---| ---| | 1 | UPDATE | T01 | |* 2 | HASH JOIN SEMI | | | 3 | TABLE ACCESS FULL| T01 | | 4 | TABLE ACCESS FULL| T02 | |* 5 | TABLE ACCESS FULL | T02 |
---Predicate Information (identified by operation id):
---2 - access("A"."C1"="B"."C1")
SQL> MERGE INTO t01 A
2 USING (SELECT c1,c2 FROM t02) B 3 ON (A.c1 = B.c1)
4 WHEN MATCHED THEN UPDATE SET A.c3 = A.c3 + B.c2; 実行計画 ---| Id ---| Operation ---| Name ---| ---| 0 ---| MERGE STATEMENT ---| ---| | 1 | MERGE | T01 | | 2 | VIEW | | |* 3 | HASH JOIN | | | 4 | TABLE ACCESS FULL| T02 |
•
繰り返し副問合せはWITH句で
SQLの変更
補足説明（WITH句を使用する）
SQL> SELECT * FROM
2 (
SELECT 部門,sum(売上) 部門売上 FROM 売上表 GROUP BY 部門
) w_abc
3 WHERE 部門売上 < (SELECT avg(部門売上) FROM
4 (
SELECT 部門,sum(売上) 部門売上 FROM 売上表 GROUP BY 部門
));
SQL> WITH w_abc AS (SELECT 部門, sum(売上) FROM 売上表 GROUP BY 部門)
2 SELECT * FROM w_abc
•
UPDATEやDELETEはダイレクト・インサートする
–
特にパラレル実行には効果的（Redoログのネックになりやすい）
SQLの変更
補足説明（ダイレクト・インサートにする）
SQL> DELETE FROM tab000
2 WHERE 日付 < TO_DATE(‘20101001’,’YYYYMMDD’) ;
SQL> CREATE TABLE tab001 NOLOGGING PARALLEL AS
2 SELECT * FROM tab000
3 WHERE 日付
>=
TO_DATE(‘20101001’,’YYYYMMDD’) ;
SQL> DROP TABLE tab000 ;
•
大量行のGroup byなどではメモリ不足になりやすい
–
指定日付の売上に対して過去の売上最新日時を求めるなど
SQLの変更
補足説明（TEMP領域を使用しない／索引を使用）
SQL> SELECT * FROM (SELECT * FROM sales WHERE dtime = TO_DATE('140401','YYMMDD')) A LEFT JOIN 2 (SELECT tenpo, prod, cust, MAX(dtime) dtime1 FROM sales
3 WHERE dtime < TO_DATE('140401','YYMMDD') GROUP BY tenpo, prod, cust) B 4 USING(tenpo, prod, cust) ;
実行計画
---<途中省略>---| Id ---<途中省略>---| Operation ---<途中省略>---| Name ---<途中省略>---| ---<途中省略>---| Used-Mem ---<途中省略>---| ---<途中省略>---| 0 ---<途中省略>---| SELECT STATEMENT ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| ---<途中省略>---| |* 1 | HASH JOIN OUTER | | | xxxxx (n)| |* 2 | TABLE ACCESS FULL | SALES | | | | 3 | VIEW | | | | | 4 | HASH GROUP BY | | | xxxxx (n)| |* 5 | TABLE ACCESS FULL | SALES | | |
•
索引を使用することでGroup byのメモリ使用を回避する
–
TEMP領域へのマルチ・パスI/Oより中間テーブル作成の方が効果的など
SQLの変更
補足説明（TEMP領域を使用しない／索引を使用）
SQL> CREATE INDEX ix_sale01 ON sales (tenpo, prod, cust, dtime); SQL> CREATE TABLE w_sal01 AS
2 SELECT tenpo, prod, cust, MAX(dtime) FROM sales
3 WHERE dtime < TO_DATE('140401','YYMMDD‘) GROUP BY tenpo, prod, cust; 実行計画
---| 0 ---| CREATE TABLE STATEMENT ---| ---| | 1 | LOAD AS SELECT | | | 2 | SORT GROUP BY NOSORT | | |* 3 | INDEX FULL SCAN | IX_SALE01 | …<省略>…
SQL> SELECT * FROM (SELECT * FROM sales WHERE dtime = TO_DATE('140401','YYMMDD')) A この列の順番で索引を作成することで Group byのソートを回避する
