– PX PARTITION RANGE ALL – PX PARTITION HASH ALL
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 から）
• 補足説明参照
実行計画のチューニング
SQL の変更（主な SQL の変更）
• 結合順を変える
– 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 | WHERE句