#odddtky Oracle DBA & Developer Days 2014 for your Skill 使える実践的なノウハウがここにある 津島博士のパフォーマンス講座 SQL チューニングの基礎 日本オラクル株式会社データベース事業統括製品戦略統括本部データベースエンジニアリング本部 担

#odddtky Oracle DBA & Developer Days 2014 for your Skill 使える実践的なノウハウがここにある 津島博士のパフォーマンス講座 SQL チューニングの基礎 日本オラクル株式会社データベース事業統括製品戦略統括本部データベースエンジニアリング本部 担当ディレクター津島浩樹 Copyright 2014, Oracle and/or its affiliates. All rights reserved.

以下の事項は 弊社の一般的な製品の方向性に関する概要を説明するものです また 情報提供を唯一の目的とするものであり いかなる契約にも組み込むことはできません 以下の事項は マテリアルやコード 機能を提供することをコミットメント ( 確約 ) するものではないため 購買決定を行う際の判断材料になさらないで下さい オラクル製品に関して記載されている機能の開発 リリースおよび時期については 弊社の裁量により決定されます Oracle と Java は Oracle Corporation 及びその子会社 関連会社の米国及びその他の国における登録商標です 文中の社名 商品名等は各社の商標または登録商標である場合があります 3

はじめに 津島博士のパフォーマンス講座 の紹介 連載していますのでよろしくお願いいたします http://www.oracle.com/technetwork/jp/database/articles/tsushima/ 4

アジェンダ 1 2 3 4 5 SQLチューニングとはオプティマイザの概要実行計画実行計画のチューニング Oracle Database 12c の拡張 5

SQL チューニングとは 1. 問題がある ( 遅い )SQL の特定と分析 AWR などから分析する リソースの問題 ( キャッシュ ヒット率 I/O 性能 ) などはインスタンス チューニング 2. チューニング ( 最適な実行計画にする ) 手動 ( 実行計画を分析 ) 自動 (SQL チューニング アドバイザなど ) 3. テスト 効果を確認 (SQL*Plus で実行など ) 目標に対して評価 ( 未達であれば再度チューニング ) 6

SQL チューニングとは最適な実行計画 オプティマイザが以下の手順で決定する 1. アクセス方法 ( 索引スキャン フル スキャン ) カーディナリティ ( 述語を適用した行数 ) を算出して それの I/O 時間が短いアクセス 単一ブロック リードとマルチ ブロック リードの比較 リード回数とリード性能で決まる 2. 結合順序 ( 表数!( 階乗 ) の組合せ ) カーディナリティの小さいものから メモリを使用するとき ( ハッシュ結合など ) はサイズも影響 2 表目からは結合のカーディナリティ ( 結合後の行数 ) も考慮する 3. 結合方法 それぞれの結合順序で以下の結合方法のコストを算出 ネステッド ループ結合 ハッシュ結合 ソート マージ結合 1 行 2 行 3 行 10 行 1 行 2 行 3 行 1 行 2 行 3 行 1 行 2 行 3 行 10 行 7

SQL チューニングとは最適な実行計画 ( 結合方法 ) Nested Loop Join( ネステッド ループ結合 ) 基本は索引を使用する結合 ( 表の行数が少ないときは全表スキャンでも ) Hash Join( ハッシュ結合 ) 索引を使用しない等価結合では最も効果的 Sort Merge Join( ソート マージ結合 ) 索引を使用しない等価結合以外など ( ソート処理が重いのであまり使用しない ) 結合タイプによっては結合順序が決まる場合も 外部結合 セミ結合 アンチ結合 8

SQL チューニングとは最適な実行計画を決める要素 表 索引 テーブル構造 パーティション 索引の設計など これによる性能の違いが大きい すべてを速くすることはできない SQL 文 最適にするには多少の知識が必要 詳細は オプティマイザの概要 や 実行計画のチューニング で 初期化パラメータ メモリ領域サイズ I/O サイズなど オプティマイザ統計 自動メモリ管理など これでカーティナリティ ( 行数 ) などが決まる 自動オプティマイザ統計収集 9

アジェンダ 1 2 3 4 5 SQLチューニングとはオプティマイザの概要実行計画実行計画のチューニング Oracle Database 12cの拡張 10

オプティマイザの種類 ルールベースオプティマイザ (RBO) 使用可能なアクセスパスを順序づけるランキングに基づいて実行計画を作成 Oracle10g からは CBO のみに コストベースオプティマイザ (CBO) 統計情報に基づきコストを見積もり 最もコストの低い実行計画を作成 11

オプティマイザの種類ルールベース オプティマイザの問題 SQL 構文から実行計画を作成 OLTP タイプのような単純な SQL に向いている SQL の作成が難しい ルールを覚える必要がある ( 誰でも作成できる訳ではない ) データの変化に対応できない 同じ条件でも最適な実行計画は異なる ( データ分布の違い ) データの増加によって実行計画は変化する 限界がある 新機能には対応できない 12

オプティマイザの構成要素 問合せトランスフォーマ ( 問合せ変換 ) 適切と判断した問合せに変換 ( アクセス パスの候補を追加 ) エスティメータ アクセス パスのコストを計算 プラン ジェネレータ 複数のアクセス パスを計算させて 最もコストが低い実行計画を生成 アクセス パス数の制限 パーサー ( 構文解析 ) オプティマイザ 問合せトランスフォーマ エスティメータ プラン ジェネレータ CBO のみ ディクショナリ ( 統計 ) プランの実行 13

問合せ変換 CBO のとき SQL によっては最適な実行計画にならない 最適な SQL は多少の知識が必要 ( 特に 結合に対する副問合せの活用など ) オプティマイザが代わりに行う データ ( データ分布 ) によって最適な実行計画が異なる CBO ではコストから変換するか判断する 例えば 索引を使用する SQL 索引を使用しない SQL 副問合せを使用する SQL 副問合せを使用しない SQL オプティマイザ統計によって最適でないときもある 知っていると SQL を書き換えるときのヒントになる 最適な実行計画に変換 14

問合せ変換代表的な機能 ( 索引の使用 ) OR または IN リストで索引を使用するように OR Expansion(OR 拡張 ) UNION ALL に変換 (USE_CONCAT ヒント ) 変換しないとき (NO_EXPAND ヒント ) 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 SQL> SELECT * FROM tab1 WHERE c1 = 100 2 UNION ALL 3 SELECT * FROM tab1 WHERE c2 = 200 AND LNNVL(c1 = 100) 4 UNION ALL 5 SELECT * FROM tab1 WHERE c3 = 300 AND LNNVL(c2 = 200) AND LNNVL(c1 = 100) ; c2!= 200 OR c2 IS NULL 15

問合せ変換代表的な機能 ( 結合 / 副問合せを効果的に ) 副問合せ ビュー / インライン ビュー (FROM 句の副問合せ ) 通常の結合方法になる ( 使用する / 使用しない / 作成する ) 次頁参照 ネストした副問合せ (WHERE 句の副問合せ ) 通常の結合方法に変換する (FILTER などから ) Subquery Unnesting( 副問合せのネスト解除 ) スカラー副問合せ (SELECT リストの副問合せ ) 通常の結合方法に変換する (Oracle Database 12c から ) Scalar Subquery Unnesting( スカラー副問合せのネスト解除 ) 16

問合せ変換代表的な機能 ( 結合 / 副問合せを効果的に ) ビュー / インライン ビュー ビューを使用しない ( 結合順を変えたい 索引を使用したい ) View Merging( ビュー マージ ) ビューをマージできない / しない (Group by 後にネステッド ループ結合するなど ) Predicate Pushing( 述語のプッシュ ) インライン ビューを作成する ( 早い段階で重複値を削除するなど ) Group by Placement (Group by の配置の最適化 ) 補足説明参照 ビューとは アクセス制御などに使用 ビューから実行 ( 結合順が決まる ) 17

オプティマイザ統計情報主な項目 ( 正確にする運用が重要 ) 表統計 行数 データ ブロック数 行連鎖 行移行の数 平均行長 索引統計 索引内の個別値数 索引の深さ (BLEVEL) リーフ ブロック数 クラスタ化係数 列統計 表アクセス コスト 索引アクセス コスト カーディナリティ サイズ 個別値数 NULL 数 平均列データ長 データ分布 ( 最小値と最大値 ヒストグラム ) システム統計 (CPU コスト モデルで使用 ) CPU 性能 I/O 性能 ( 単一ブロック リード マルチ ブロック リード ) この情報には限界がるので 大きく異なるようなデータは注意が必要 (12c から改善 ) パーティション表には パーティション サブ パーティションごとにも同様の情報を収集する アクセスする行数やブロック数を求め CPU 性能や I/O 性能でコストを計算する 18

実行計画の変化 オプティマイザ統計を変更しなくても実行計画は変化するので注意 オプティマイザ統計が正しくない カーディナリティ フィードバック カーディナリティが異なる場合は 2 回目以降から補正して実行計画を作成 ( ただし メモリ上にカーソルが存在するときだけ ) バインド変数の値が異なる ( 戻される行数が異なる ) ハード パース時にハンド変数の値で実行計画を作成 ( バインド変数の先読み ) 等価条件はヒストグラムがないと動作しない 範囲条件 (<,>) はヒストグラムがなくても動作 大きな表に複雑な WHERE 句でパラレル実行する 動的サンプリングのレベルを自動的に決定 (Oracle Database 11gR2 から ) 19

問合せ変換補足説明 (View / Complex View Merging) ビューを主問合せにマージすることで以下を可能にする 結合順を最適にできない ビュー実行後には索引が使用できない 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' ; 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 ; 索引が使用されない Complex View(GROUP BY/DISTINCT が含むビュー ) 20

問合せ変換補足説明 (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 は構文エラー SQL> SELECT * FROM tab02 A, 2 (SELECT c02,sum(c03) FROM tab01 B 3 WHERE A.c2 = B.c02 GROUP BY c02) ; Oracle Database 12c からは LATERAL 句などで可能に 実行計画 ( 変換しない ) ---------------------------------------- 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 ---------------------------------------------------- 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): --------------------------------------------------- 4 - filter(count(*)>0) 7 - filter("c02"="a"."c2") 21

問合せ変換補足説明 (Group by Placement) 結合前にインライン ビューを実行 ( 作成 ) することで行数を削減する 重複値が多いときに Group by などをすることで行数を削減 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 ; 22

問合せ変換補足説明 (Subquery Unnesting) ネストした副問合せの結合方法を変換 FILTER から通常の結合方法 ( ハッシュ結合など ) に変換 IN 条件はインライン ビュー EXISTS 条件はセミ結合 NOT IN NOT EXISTS 条件はアンチ結合 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 23

問合せ変換補足説明 (Subquery Unnesting) EXISTS と IN どのように使い分けるか EXISTS( 相関副問合せ ) ネステッド ループ結合の駆動表は tab1(tab2.c1 の索引を使用する ) ( 現在は どちらを使用しても変換してくれる ) SQL> SELECT c1 FROM tab1 A WHERE EXISTS (SELECT 0 FROM tab2 B WHERE A.c1 = B.c1) ; IN( 非相関副問合せ ) ネステッド ループ結合の駆動表は tab2(tab1.c1 の索引を使用する ) セミ結合 索引や重複値などによって SQL> SELECT c1 FROM tab01 WHERE c1 IN (SELECT c1 FROM tab2) ; インライン ビュー 同じ意味 ( 重複値を排除して結合 ) なので インライン ビュー ( 通常の表結合 ) に変換 SQL> SELECT c1 FROM tab1 A, (SELECT DISTINCT c1 FROM tab2) B WHERE A.c1 = B.c1 ; 24

アジェンダ 1 2 3 4 5 SQLチューニングとはオプティマイザの概要実行計画実行計画のチューニング Oracle Database 12cの拡張 25

実行計画の確認方法 Explain plan for <SQL> 実際には SQL は実行されない SQL*Plus の AUTOTRACE コマンド set autotrace traceonly explain 以外は実際に SQL を実行 SQL トレース SQL のトレースを取得 Tkprof コマンドによりトレースファイルからプランを取得 再実行が必要 plan_table が必要 負荷が高い 26

実行計画の確認方法実行計画のキャッシュ機能 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 27

実行計画の確認方法 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')); 実行計画省略すると最後のSQL ------------------------------------------------------------------------------------------------- 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サイズ 28

実行計画の確認方法リアルタイム SQL 監視 実行中の実行計画を表示 ( デフォルトで 5 秒以上の SQL MONITOR ヒントで強制的に ) 時間のかかっている SQL が自動的に監視されリストされる ( 経過時間等でソート可能 ) この SQL 実行全体の統計 実行計画のステップごとの統計など ステップごとの待機イベント 29

実行計画の解説注目する項目 カーディナリティ 述語を適用した行数 (Rows E-Rows A-Rows) アクセス方法 索引スキャン フル スキャン ビュー アクセス 結合方法 ネステッド ループ結合 ハッシュ結合 ソート マージ結合 直積 結合タイプ 外部結合 セミ結合 アンチ結合 結合順序 SQL によって決まる場合も 30

実行計画の解説注目する項目 パーティション パーティション プルーニング パラレル実行 データ分散のステップが増える 31

実行計画の解説 リーフ ステップ ( インデントの一番深いステップ ) から実行して 結合 ( 同一インデント ) は上位に表示されたものが最初になる 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 ; TABLE ACCESS BY INDEX ROWID TAB1 INDEX RANGE SCAN IX_TAB1 結合実行計画 ---------------------------------------------------------------- Id Operation Name Rows Cost (%CPU) ---------------------------------------------------------------- 0 SELECT STATEMENT 271 (1) 1 SORT GROUP BY 3 * 2 HASH JOIN 2 * 3 HASH JOIN 1 * 4 (1) TABLE ACCESS FULL TAB1 10 5 (2) TABLE ACCESS FULL TAB2 50 6 (3) TABLE ACCESS FULL TAB3 100 索引アクセス CPU コスト モデル (CPU コストの割合 ) VIEW HASH GROUP BY TABLE ACCESS FULL TAB1 カーディナリティ ビュー アクセス 32

実行計画の解説 後半には 述語の情報と 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"."c2"="tab2"."c2") 3 access("tab1"."c3"="tab3"."c3") 4 filter("tab1"."c1"<100) Note 部 ( 内部的に動作しなものなど ) Note ----- - dynamic sampling used for this statement (level=2) 33

実行計画の解説 アクセス方法 索引スキャン ( シングル ブロック リード ) Index Range Scan( 索引範囲スキャン ) Index Unique Scan( 索引一意スキャン ) Index Skip Scan( 索引スキップ スキャン ) 後方一致 ( 中間一致 ) 条件 範囲スキャン / 全索引スキャン < 5 0 フル スキャン Table Full Scan( 全表スキャン ) テーブルをマルチ ブロック リード Index Full Scan( 全索引スキャン ) シングル ブロック リード Index Fast Full Scan( 高速全索引スキャン ) リーフ ノードをマルチ ブロック リード 1 0 11 1 2 < 2 0 3 0 3 0 3 1 5 0 5 1 < 6 0 7 0 34

実行計画の解説アクセス方法 (Index Range Scan / Index Unique Scan) Index Unique Scan 一意索引の等価条件 Index Range Scan 非一意索引の等価条件 一意索引 / 非一意索引の範囲条件 SQL> SELECT * FROM tab1 WHERE c1 = 10; 実行計画 ----------------------------------------------- Id Operation Name ----------------------------------------------- 0 SELECT STATEMENT 1 TABLE ACCESS BY INDEX ROWID TAB1 * 2 INDEX UNIQUE SCAN IX_TAB1 SQL> SELECT * FROM tab1 WHERE c1 < 10; 実行計画 ----------------------------------------------- Id Operation Name ----------------------------------------------- 0 SELECT STATEMENT 1 TABLE ACCESS BY INDEX ROWID TAB1 * 2 INDEX RANGE SCAN IX_TAB1 35

実行計画の解説アクセス方法 (Index Skip Scan) 後方一致 ( 中間一致 ) 条件での索引スキャン (Oracle9i から ) 全表スキャンより効果的なとき (c1 の個別値が少ないときなど ) 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 c1 の値ごとに c2=10 を行う c1=1 AND c2=10 c1=2 AND c2=10 c1=3 AND c2=10 1 1 1 1 2 10 2 2 2 1 2 10 3 3 1 2 10 3 テーブル 36

実行計画の解説アクセス方法 (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 37

実行計画の解説アクセス方法 (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 38

実行計画の解説アクセス方法 ( ビュー アクセス ) ビュー / インライン ビューをマージしないとき ( 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 4 TABLE ACCESS FULL TAB1 100K 39

実行計画の解説結合方法 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( 直積 ) 結合条件がない ( 負荷が高いのでできるだけ使用しないように ) 40

実行計画の解説結合方法 ( ネステッド ループ結合 ) 基本は内部表の索引を使用して結合する 駆動表は絞り込み条件があるときに索引スキャン 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 41

実行計画の解説結合方法 ( ネステッド ループ結合 ) 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 駆動表 * 5 INDEX RANGE SCAN IX_TAB1 xxx 内部表 ( 駆動表の1 行に対し索引アクセス ) 42

実行計画の解説結合方法 ( ネステッド ループ結合 ) 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) ROWID を並べ替えて 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 を最適化 ) 43

実行計画の解説結合方法 ( ハッシュ結合 ) 索引の代わりにメモリ上にハッシュ テーブルを作成 ( 最初にアクセスするテーブル ) 片方のテーブル ( アクセスするデータ ) が小さいと効果的 等価結合のみ 結合列以外で絞り込み条件があるときに索引スキャン 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 ハッシュ テーブルを作成 4 TABLE ACCESS FULL TAB1 100K 44

実行計画の解説結合方法 ( ソート マージ結合 ) 索引の代わりにソートして結合 等価結合以外など 絞り込み条件があるときに索引スキャン 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 6 TABLE ACCESS FULL TAB1 100K 45

実行計画の解説結合方法 ( 直積 ) できるだけ行わない 結合条件がないので効率が悪い 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 ソートする ( メモリにバッファリングするため ) 5 TABLE ACCESS FULL TAB1 100K 46

実行計画の解説結合タイプ Outer Join( 外部結合 ) OUTER JOIN Semi Join( セミ結合 ) EXISTS, IN Anti Join( アンチ結合 ) NOT EXISTS, NOT IN 主問合せ セミ結合 副問合せ 主問合せ アンチ結合 副問合せ このような特殊な結合タイプは結合順が決まっている Oracle Database 10g でハッシュ結合の結合順を改善 HASH JOIN RIGHT xxxx 47

実行計画の解説結合タイプ ( 外部結合 ) 左外部結合 右外部結合 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 48

実行計画の解説結合タイプ ( 外部結合 ) 完全外部結合 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 5 (SELECT 0 FROM tab1 A WHERE A.c1 = B.c1) ; 49

実行計画の解説結合タイプ ( セミ結合 ) 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 50

実行計画の解説結合タイプ ( アンチ結合 ) 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 51

実行計画の解説パーティション パーティション プルーニング 静的プルーニングと動的プルーニング ( バインド変数 データ型の暗黙変換など ) パーティションの開始と終了の項目が増える Pstart: アクセス開始のパーティション ( 静的 : 数字 動的 :KEY) Pstop: アクセス終了のパーティション ( 静的 : 数字 動的 :KEY) ジェイン フィルター ( ブルーム フィルタリング ) 結合列のパーティション プルーニング パーティション化された明細表をマスタ表の条件で検索するなど SQL> SELECT FROM 明細表 A, マスタ 1 B WHERE A.c1 = B.c1 AND B.c2 = 'xxx'; この SQL のときなどに 明細表.c1 でパーティション プルーニングを行う 52

実行計画の解説パーティション ( パーティション プルーニング ) 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 バインド変数 データ型の暗黙変換 など 53

実行計画の解説パーティション ( ジョイン フィルター / ブルーム フィルタリング ) 結合時に効果的にフィルタリングする ( 結合列でフィルタリングする ) 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 Time_cdでフィルターを作成 * 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 54

実行計画の解説パラレル実行 ( データ分散 ) スキャンのデータ分散 ブロック単位 (PX BLOCK ITERATOR) パーティション単位 (PX PARTITION RANGE ALL など ) データ分散しない ( 片方の読込みデータが少ないとき ) それぞれの PQ プロセスで全てのデータ スキャン以外でのデータ再分散 PX SEND HASH / RANGE / BROADCAST / PARTITION 基本は HASH( 重複データが多いときに注意 ) PX RECEIVE スキャン以外でのデータ再分散 OrderBy スキャン PQ QC PQ PQ PQ PQ 結合 PQ PQ PQ PQ PQ PQ PQ 表 スキャンのデータ分散 55

実行計画の解説パラレル実行 ( スキャンのデータ分散 ) Block-based granules ブロック単位で分割してアクセス PX BLOCK ITERATOR QC スキャン PQ PQ PQ PQ 表 Partition-based granules パーティション単位に分割してアクセス パーティション ワイズ結合など PX PARTITION RANGE ALL PX PARTITION HASH ALL など QC スキャン PQ PQ PQ PQ P1 表 P2 P3 P4 56

実行計画の解説パラレル実行 ( スキャン以外でのデータ再分散 ) 操作内パラレル化のデータ分散 ( 偏りが発生すると効果が低下する ) HASH( 基本はこれを使用する ) 重複データが多いときに注意 RANGE( ソートなど ) BROADCAST( 結合の片方が小さい ) 同じデータをすべてのプロセスに PARTITION( パーティション ワイズ結合 ) パーティション分割 ( 片方がパーティション表のとき ) NONE( データ分散しない / データ通信しない ) PX SEND xxxx と PX RECEIVE がない PARTITION や BROADCAST などと使用 PQ_DISTRIBUTE ヒントで調整 操作間パラレル化 OrderBy スキャン PX SEND xxxx PX RECEIVE PQ QC PQ PQ PQ PQ 結合 PQ PQ PQ PQ PQ PQ PQ 表 操作内パラレル化 3 1 2 57

実行計画の解説パラレル実行 (BROADCAST 分散と PARTITION 分散 ) 結合の片方をデータ分散しない ( スキャンと結合が同じプロセス ) BROADCAST 分割 tab1 同じデータを全プロセスに PARTITION 分割 ( パーシャル パーティション ワイズ結合 ) tab1 tab2 のパーティションに合わせる スキャン PQ PQ PQ PQ スキャン PQ PQ PQ PQ 結合 PQ PQ PQ PQ スキャン + 結合 結合 PQ PQ PQ PQ スキャン + 結合 tab2 tab2 スキャンと結合が同じプロセス P1 P2 P3 P4 フル パーティション ワイズ結合は どちらでもデータ分散しない ( 結合列が同じパーティションのとき ) 58

実行計画の解説パラレル実行 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 14 TABLE ACCESS FULL TAB02 Q1,01 PCWP 結合プロセス ( データをチェックするので同じプロセス ) PX SEND PARTITION PX SEND BROADCAST データ再分散 PX PARTITION RANGE ALL スキャンのデータ分散 59

実行計画の解説パラレル実行 ( データ分散なし ) 片方の読込みデータが少ないときは BROADCAST も行わない それぞれのプロセスで全てのデータをスキャンする ( スキャンのデータ分散もしない ) SQL> SELECT COUNT(*) FROM (SELECT * FROM t3,t1 WHERE t3.col1 = t1.col1); パラレル度が4なので 4つのPQプロセスで5 行実行計画すべて同一プロセスリードしている (A-Rowsは20 行になっている ) ------------------------------------------------------------------------------------------------------ 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 60

アジェンダ 1 2 3 4 5 SQLチューニングとはオプティマイザの概要実行計画実行計画のチューニング Oracle Database 12cの拡張 61

実行計画のチューニング SQL が遅くなる主な原因 索引 ( 最適な索引 ) を使用していない 結合行数が多い 重複値が多い場合は要注意 結合方法や結合順が悪い 副問合せが効果的に使用されていない TEMP 領域を使用している 62

実行計画のチューニング問題となる 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 の両方が出力される すべての実行の合計になる ( 実行回数に注意 ) 63

実行計画のチューニング問題となる SQL の特定 (Top 5 Timed Events) CPU が多いのか I/O が多いのか ( またはその他の待機が多いか ) db file sequential read( 索引スキャンの待機 ) db file scattered read( フル スキャンの待機 ) / direct path read( バッファ キャッシュを経由しないフル スキャンの待機 ) db file parallel read( リカバリ プリフェッチの読込みの待機 ) direct path read temp(tempからの読込みの待機 ) gc current block 2-way( キャッシュ フュージョンの待機 ) 64

実行計画のチューニング問題となる SQL の特定 (SQL 統計 ) 65

実行計画のチューニング問題となる SQL の特定 (SQL 統計 ) 66

実行計画のチューニング問題となる SQL の特定 (SQL 統計 ) 67

実行計画のチューニング チューニングの手順 ( この順番で改善できるか検討する ) 1. オプティマイザ統計の再収集 2. 索引の作成 ( 索引のチューニング ) 3. SQLの変更 ( ヒントの追加 ) SQL 計画ベースラインの設定 (SQL Plan Management) SQL の変更ができない SQL チューニング アドバイザ 問題を特定できない 68

実行計画のチューニングオプティマイザ統計の再収集 行数の見積もりが正しくない ( 実行時と大きく異なるとき ) 実行計画の見積もり行数 (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 69

実行計画のチューニング索引の作成 ( 索引のチューニング ) 全表スキャンを行っているテーブルは索引スキャンした方が良くないか 実行計画の Rows などを確認 索引スキャンの効率が悪くないか 索引の列の組合せ 索引の列の順番などを確認 例えば 以下のような SQL SQL> SELECT WHERE c1=xx AND c2=xx AND c3=xx; 1 SQL> SELECT WHERE c1=xx AND c3=xx; 2 SQL> SELECT WHERE c1=xx AND c2=xx AND c4=xx; 3 優先順位を明確にして作成する これが難しい ( そのため フル スキャンでも高速な Exadata が効果的 ) 作り過ぎないように使用しない索引は削除する ( 次頁参照 ) 列 (c3,c1) の索引が作成されているとすると 1 と 2 は Index Range Scan になる (2 の方がより効果的な索引スキャンになる ) 3 は Index Skip Scan になる (c3 の条件がない ) つまり 2 を最速にするのであれば問題ない 70

実行計画のチューニング索引の作成 ( 索引の使用状況の情報収集 ) 使われていない索引を削除したい 索引の 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 索引がまだ使用されていない 71

実行計画のチューニング SQL の変更 / ヒントの追加 ( 主なチューニング ) オプティマイザの判断を調整する 問合せ変換が最適でない 結合方法や結合順が最適でない 効率の悪いアクセスを改善する 索引が使用できない 同じテーブルに複数回アクセスしている Redo ログを出力しないように TEMP 領域の使用を改善する プロセスで使用するデータ サイズを小さくする パラレル度を上げる プログラムを分割 / 並列化する 索引を使用する ( 結合とソートなど ) TEMP 領域を使用しないときでも性能を向上させるためには重要 72

実行計画のチューニング SQL の変更 ( 主な SQL の変更 ) 索引を使用しない条件を変更する ( 可能であれば ) 演算している NULL 比較 NOT(!=) OR 後方一致 ( 中間一致 ) 条件 インライン ビューを作成する ( 問合せ変換しないとき ) 結合前に行数を削減する (Group by Placement などを明示的に ) 結合する UPDATE 文を MERGE 文にする (UPDATE のみでも可 ) UPDATE 文で結合するには副問合せが必要 ( 効率が悪い ) SQL を分割する (WITH 句を使用する ) 同じ副問合せを複数回使用するなど 73

実行計画のチューニング SQL の変更 ( 主な SQL の変更 ) ダイレクト インサートにする (UPDATE DELETE なども ) NOLOGGING 属性で Redo ログが出力されない 同じテーブルにアクセスする SQL を一つにする CASE 式 ( 条件の異なる SELECT から ) マルチ テーブル インサート ( 同じテーブルにも使用できる ) MERGE 文 (UPDATE & INSERT から ) 補足説明参照 74

実行計画のチューニング SQL の変更 ( 主に使用するヒント ) 結合順を変える ORDERED(FROM 句の順番に結合する ) LEADING( 指定した順番に結合する ) ビュー マージを行わない NO_MERGE( 指定ビューをマージしない ) 索引を使用する INDEX( 索引スキャン ) INDEX_FFS( 高速全索引スキャン ) 索引を使用しない FULL( 全表スキャン ) NO_INDEX( 指定索引を使用しない ) 結合方法を変える USE_HASH( ハッシュ結合 ) USE_NL( ネステッド ループ結合 ) USE_MERGE( ソート マージ結合 ) 75

実行計画のチューニング SQL 計画ベースラインの設定 ヒントを入れた実行計画をベースラインとして登録する 索引スキャンを全表スキャンに変更 実行 プラン B SELECT * FROM tab01 WHERE c1 = 1; SQL 計画ベースライン プラン A 変更 (A B) 常に SQL 計画ベースラインにある実行計画が使用される プラン B SELECT /*+ FULL(tab01) */ * FROM tab01 WHERE c1 = 1; 76

実行計画のチューニング SQL チューニング アドバイザ 問題の SQL を特定できないときは Oracle データベースに任せる 自動チューニング オプティマイザ 高負荷の SQL 文 SQL チューニング アドバイザ 分析 統計の分析 アクセス パスの分析 SQL プロファイリング SQL 構造分析 代替計画分析 (11gR2 から ) 推奨事項 統計の再収集 索引の作成 /SQL アクセス アドバイザの実行 SQL プロファイルの作成 SQL のリライト SQL 計画ベースラインの作成 77

SQL の変更補足説明 ( インライン ビューを作成する ) Group by などで重複値をなくす 慣れないと難しいですが 結合列 tab2.c1 で GROUP BY を行う 最終的に SUM 関数を行う 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; 78

SQL の変更補足説明 ( 結合する UPDATE 文を MERGE 文にする ) あるテーブルのデータで UPDATE するなど ( テーブルのアクセスが 2 回になる ) 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 WHERE 句 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") 5 - filter("b"."c1"=:b1) 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 5 TABLE ACCESS FULL T01 79

SQL の変更補足説明 (WITH 句を使用する ) 繰り返し副問合せは 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 3 WHERE 部門売上 < (SELECT avg( 部門売上 ) FROM w_abc); 80

SQL の変更補足説明 ( ダイレクト インサートにする ) UPDATE や DELETE はダイレクト インサートする 特にパラレル実行には効果的 (Redo ログのネックになりやすい ) 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 ; SQL> RENAME tab001 TO tab000 ; 81

SQL の変更補足説明 (TEMP 領域を使用しない / 索引を使用 ) 大量行の Group by などではメモリ不足になりやすい 指定日付の売上に対して過去の売上最新日時を求めるなど 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 82

SQL の変更補足説明 (TEMP 領域を使用しない / 索引を使用 ) 索引を使用することで Group by のメモリ使用を回避する TEMP 領域へのマルチ パス I/O より中間テーブル作成の方が効果的など 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 < 省略 > この列の順番で索引を作成することで Group by のソートを回避する SQL> SELECT * FROM (SELECT * FROM sales WHERE dtime = TO_DATE('140401','YYMMDD')) A 2 LEFT JOIN w_sal01 USING(tenpo, prod, cust); 83

SQL の変更補足説明 (TEMP 領域を使用しない / 索引を使用 ) Oracle Database 12c から通常のインライン ビューを左相関 (LATERAL 句など ) に可能 SQL> SELECT * FROM tab1 A, ( 2 SELECT * FROM (SELECT tab2.*,row_number() OVER (PARTITION BY c1 ORDER BY c2) rn FROM tab2) 6 WHERE rn <= 2) B 7 WHERE B.c1 = A.c1; 実行計画 C1に対する上位 2 件をすべて求める ------------------------------------------ Id Operation Name ------------------------------------------ 0 SELECT STATEMENT * 1 HASH JOIN 2 TABLE ACCESS FULL TAB1 * 3 VIEW * 4 WINDOW SORT PUSHED RANK 5 TABLE ACCESS FULL TAB2 SQL> SELECT * FROM tab1 A, LATERAL( 2 SELECT * FROM ( 3 SELECT * FROM tab2 B WHERE B.c1 = A.c1 ORDER BY B.c2) 4 WHERE ROWNUM <= 2); ネステッド ループ結合が可能になり 実行計画 最小限のソート領域 ( 上位 2 件のみ ) で行う -------------------------------------------------------------------- 0 SELECT STATEMENT 1 NESTED LOOPS 2 TABLE ACCESS FULL TAB1 3 VIEW VW_LAT_A18161FF * 4 COUNT STOPKEY 5 VIEW * 6 SORT ORDER BY STOPKEY 7 TABLE ACCESS BY INDEX ROWID BATCHED TAB2 * 8 INDEX RANGE SCAN IX_TAB2 84

アジェンダ 1 2 3 4 5 SQLチューニングとはオプティマイザの概要実行計画実行計画のチューニング Oracle Database 12cの拡張 85

Oracle Database 12c の拡張主な機能 索引アクセス Batch table access by rowid 問合せ変換 Partial Join Evaluation( パーシャル結合評価 ) Null Accepting Semi-Join(Null 許可のセミ結合 ) Scalar Subquery Unnesting( スカラー副問合せのネスト解除 ) Multi-Table Left Outer Join( 複数表の左外部結合 ) データ再分散 Hybrid HASH BROADCAST と HASH を実行時に決定する ( 行数がパラレル度の 2 倍以上のとき HASH) HASH のとき重複データも分散する ( ヒストグラムが必要 ) PQ_SKEW ヒントで強制的に 86

Oracle Database 12c の拡張索引アクセス (Batch table access by rowid) 索引範囲スキャンの拡張 SQL> SELECT * FROM tab1 WHERE c1 < 10; 実行計画 ------------------------------------------------------- Id Operation Name ------------------------------------------------------- 0 SELECT STATEMENT 1 TABLE ACCESS BY INDEX ROWID BATCHED TAB1 * 2 INDEX RANGE SCAN IX_TAB1 ROWID を並べ替えてテーブルにアクセス ( 出力が索引順にならない ) 87

Oracle Database 12c の拡張問合せ変換 (Partial Join Evaluation) セミ結合による最適化 (Oracle Database 11g までは DISTINCT Placement のみ ) SQL> SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.col1 = t2.col1; 実行計画 (DISTINCT Placement) ------------------------------------------------- Id Operation Name ------------------------------------------------- 0 SELECT STATEMENT 1 HASH UNIQUE * 2 HASH JOIN 3 VIEW VW_DTP_AE9E49E8 4 HASH UNIQUE 5 TABLE ACCESS FULL T2 6 TABLE ACCESS FULL T1 SQL> SELECT DISTINCT t1.* FROM t1 WHERE EXISTS 2 (SELECT 0 FROM t2 WHERE t1.col1 = t2.col1); 実行計画 ------------------------------------ Id Operation Name ------------------------------------ 0 SELECT STATEMENT 1 HASH UNIQUE * 2 HASH JOIN SEMI 5 TABLE ACCESS FULL T1 6 TABLE ACCESS FULL T2 88

Oracle Database 12c の拡張問合せ変換 (Null Accepting Semi-Join) OR 条件の結合列 IS NULL でもセミ結合が可能に SQL> SELECT * FROM t1 WHERE id IS NULL OR EXISTS (SELECT 0 FROM t2 WHERE t1.id = t2.id) ; 実行計画 ----------------------------------- Id Operation Name ----------------------------------- 0 SELECT STATEMENT * 1 FILTER 2 TABLE ACCESS FULL T1 * 3 TABLE ACCESS FULL T2 ----------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("id" IS NULL OR EXISTS (SELECT 0 FROM "T2" WHERE "T2"."ID"=:B1)) 3 - filter("t2"."id"=:b1) 実行計画 ---------------------------------------- Id Operation Name ---------------------------------------- 0 SELECT STATEMENT * 1 HASH JOIN RIGHT SEMI NA 2 TABLE ACCESS FULL T2 3 TABLE ACCESS FULL T1 ---------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("t1"."id"="t2"."id") 89

Oracle Database 12c の拡張問合せ変換 (Null Accepting Semi-Join) 結合列以外 (NULL 以外 ) の OR 条件はセミ結合に変換されない UNION ALL を使用する id = 10 でもセミ結合にならない SQL> SELECT * FROM t1 WHERE col1 = 10 OR EXISTS (SELECT 0 FROM t2 WHERE t1.id = t2.id) ; 実行計画 ----------------------------------- Id Operation Name ----------------------------------- 0 SELECT STATEMENT * 1 FILTER 2 TABLE ACCESS FULL T1 * 3 TABLE ACCESS FULL T2 SQL> SELECT * FROM t1 WHERE col1 = 10 2 UNION ALL 3 SELECT * FROM t1 4 WHERE EXISTS (SELECT 0 FROM t2 WHERE t1.id = t2.id) 5 AND LNNVL(col1 = 10) ; 実行計画 -------------------------------------- 0 SELECT STATEMENT 1 UNION-ALL * 2 TABLE ACCESS FULL T1 * 3 HASH JOIN RIGHT SEMI 4 TABLE ACCESS FULL T2 5 TABLE ACCESS FULL T1 90

Oracle Database 12c の拡張問合せ変換 (Scalar Subquery Unnesting) 外部結合による最適化 スカラー副問合せはセミ結合にできない ( 実行結果に NULL も存在する ) SQL> SELECT col1, (SELECT MAX(B.id) FROM t2 B WHERE A.col1 = B.col1) max_id 2 FROM t1 A WHERE A.id < 10000 ; 実行計画 ----------------------------------- Id Operation Name ----------------------------------- 0 SELECT STATEMENT 1 SORT AGGREGATE * 2 TABLE ACCESS FULL T2 * 3 TABLE ACCESS FULL T1 SQL> SELECT A.col1, MAX(B.id) FROM t1 A, t2 B 2 WHERE A.col1 = B.col1(+) AND A.id < 10000 3 GROUP BY A.col1 ; 実行計画 ------------------------------------ Id Operation Name ------------------------------------ 0 SELECT STATEMENT 1 HASH GROUP BY * 2 HASH JOIN OUTER * 3 TABLE ACCESS FULL T1 4 TABLE ACCESS FULL T2 91

Oracle Database 12c の拡張問合せ変換 (Multi-Table Left Outer Join) 左側の複数表に対するビューをマージする 結合順などが最適に SQL> SELECT * FROM tab1 t1 JOIN tab2 t2 USING(c1) LEFT JOIN 2 tab3 t3 ON (t1.c2 = t3.c2 AND t2.c3 = t3.c3); 実行計画 ------------------------------------- Id Operation Name ------------------------------------- 0 SELECT STATEMENT * 1 HASH JOIN OUTER 2 VIEW * 3 HASH JOIN 4 TABLE ACCESS FULL TAB1 5 TABLE ACCESS FULL TAB2 6 TABLE ACCESS FULL TAB3 実行計画 (Oracle Database 12c) ------------------------------------ Id Operation Name ------------------------------------ 0 SELECT STATEMENT * 1 HASH JOIN OUTER * 2 HASH JOIN 3 TABLE ACCESS FULL TAB1 4 TABLE ACCESS FULL TAB2 5 TABLE ACCESS FULL TAB3 92

Oracle Database 12c の拡張データ再分散 (Hybrid HASH) SQL> SELECT COUNT(*) FROM (SELECT * FROM t3,t1 WHERE t3.col1 = t1.col1); 実行計画 実行時に行数を求めて重複データを分散したとき BROADCAST / HASHを決定に '(SKEW) が出力される --------------------------------------------------------------------------------------------------------------- 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 2 3 PX SEND QC (RANDOM) :TQ10002 0 1 Q1,02 P->S QC (RAND) 0 4 SORT AGGREGATE 2 1 Q1,02 PCWP 2 * 5 HASH JOIN 2 50000 Q1,02 PCWP 100K 6 PX RECEIVE 2 5 Q1,02 PCWP 6 7 PX SEND HYBRID HASH :TQ10000 0 5 Q1,00 P->P HYBRID HASH 0 8 STATISTICS COLLECTOR 2 Q1,00 PCWC 5 9 PX BLOCK ITERATOR 2 5 Q1,00 PCWC 5 * 10 TABLE ACCESS FULL T3 1 5 Q1,00 PCWP 5 11 PX RECEIVE 2 100K Q1,02 PCWP 100K 12 PX SEND HYBRID HASH (SKEW) :TQ10001 0 100K Q1,01 P->P HYBRID HASH 0 13 PX BLOCK ITERATOR 2 100K Q1,01 PCWC 100K * 14 TABLE ACCESS FULL T1 26 100K Q1,01 PCWP 100K 93

Oracle Database 12c おすすめ研修コース Oracle Database 12c: SQL チューニングワックショップ 概要 このコースでは Oracle の SQL 文のチューニングや Oracle Database に合わせて適切にチューニングされた SQL 文を記述する方法を説明します SQL トレース機能の使い方 実行計画の取得方法 オプティマイザ機能の活用方法などを 実機演習を通して習得することができます 学習項目 Database Vaultの概要 Database Vaultの構成 権限の分析 (12c 新機能 ) レルムの構成 ルール セットの定義 コース日数 3 日間 トレーニングキャンパス赤坂 2014/12/3-5 コマンド ルールの構成 ルール セットの拡張 セキュア アプリケーション ロールの構成 Database Vaultレポートによる監査 ベスト プラクティスの実装 Oracle Database 12c: パフォーマンス チューニング 概要 このコースでは Oracle の SQL 文のチューニングや Oracle Database に合わせて適切にチューニングされた SQL 文を記述する方法を説明します SQL トレース機能の使い方 実行計画の取得方法 オプティマイザ機能の活用方法などを 実機演習を通して習得することができます 学習項目 基本チューニング診断 自動ワークロード リポジトリの使用 パフォーマンス問題の範囲の定義 メトリックとアラートの使用 ベースラインの使用 AWR ベースのツールの使用 リアルタイム データベース操作監視 アプリケーションの監視 問題のある SQL 文の識別 オプティマイザへの影響 SQL 操作のコストの削減 SQL パフォーマンス アナライザの使用 SQL パフォーマンスの管理 データベース リプレイの使用 共有プールのチューニング バッファ キャッシュのチューニング PGA および一時領域のチューニング 自動メモリー管理の使用 パフォーマンス チューニングのまとめ コース日数 5 日間 トレーニングキャンパス赤坂 2015/1/19-23 詳細は Oracle University Web サイトにてご確認ください 94

95