– PX PARTITION RANGE ALL – PX PARTITION HASH ALL
3 WHERE 部門売上 < (SELECT avg(部門売上) FROM w_abc);
• 繰り返し副問合せは WITH 句で
SQL の変更
補足説明（ WITH 句を使用する）
SQL> SELECT * FROM
2 (SELECT 部門,sum(売上) 部門売上 FROM 売上表 GROUP BY 部門) 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 ;
SQL> RENAME tab001 TO 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のソートを回避する
• Oracle Database 12c から通常のインライン・ビューを左相関（ LATERAL 句など）に可能
SQL の変更
補足説明（ TEMP 領域を使用しない／索引を使用）
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;
実行計画
---| 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);
実行計画
---| 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 | ネステッド・ループ結合が可能になり、
最小限のソート領域（上位2件のみ）で行う C1に対する上位2件をすべて求める
アジェンダ
1
2
3
SQL チューニングとは オプティマイザの概要 実行計画
実行計画のチューニング Oracle Database 12c の拡張
4
5
• 索引アクセス
– 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 ）
Oracle Database 12c の拡張
主な機能
• 索引範囲スキャンの拡張
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を並べ替えて テーブルにアクセス
（出力が索引順にならない）
• セミ結合による最適化（ Oracle Database 11g までは DISTINCT Placement のみ ）
Oracle Database 12c の拡張
問合せ変換（ Partial Join Evaluation ）
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 |
• OR 条件の結合列 IS NULL でもセミ結合が可能に
Oracle Database 12c の拡張
問合せ変換（ Null Accepting Semi-Join ）
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("T---1"."ID"="T2"."ID")
• 結合列以外（ NULL 以外）の OR 条件はセミ結合に変換されない
– UNION ALL を使用する
Oracle Database 12c の拡張
問合せ変換（ Null Accepting Semi-Join ）
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 |
id = 10
でもセミ結合にならない
• 外部結合による最適化
– スカラー副問合せはセミ結合にできない（実行結果に NULL も存在する）
Oracle Database 12c の拡張
問合せ変換（ Scalar Subquery Unnesting ）
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 |
• 左側の複数表に対するビューをマージする
– 結合順などが最適に
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 |
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 | | | | | | 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|
Oracle Database 12c の拡張
データ再分散（ Hybrid HASH ）
重複データを分散したとき に'(SKEW)’が出力される 実行時に行数を求めて
BROADCAST / HASHを決定
Oracle Database 12c おすすめ研修コース
Oracle Database 12c: SQL チューニング ワックショップ
概要 このコースでは、OracleのSQL文のチューニングや、Oracle Databaseに合わせて適切にチューニングされたSQL文を記述する方法を説明します。SQLトレース機 能の使い方、実行計画の取得方法、オプティマイザ機能の活用方法などを、実機演習を通して習得することができます。
学習項目
Database Vaultの概要
Database Vaultの構成
権限の分析(12c 新機能)
レルムの構成
ルール・セットの定義
コマンド・ルールの構成
ルール・セットの拡張
セキュア・アプリケーション・ロールの構成
Database Vaultレポートによる監査
ベスト・プラクティスの実装
コース日数 3 日間 【トレーニングキャンパス赤坂】2014/12/3-5
Oracle Database 12c: パフォーマンス・チューニング
概要 このコースでは、OracleのSQL文のチューニングや、Oracle Databaseに合わせて適切にチューニングされたSQL文を記述する方法を説明します。SQLトレース機 能の使い方、実行計画の取得方法、オプティマイザ機能の活用方法などを、実機演習を通して習得することができます。
学習項目
基本チューニング診断
自動ワークロード・リポジトリの使用
パフォーマンス問題の範囲の定義
メトリックとアラートの使用
ベースラインの使用
AWRベースのツールの使用
リアルタイム・データベース操作監視
アプリケーションの監視
問題のあるSQL文の識別
オプティマイザへの影響
SQL操作のコストの削減
SQLパフォーマンス・アナライザの使用
SQLパフォーマンスの管理
データベース・リプレイの使用
共有プールのチューニング
バッファ・キャッシュのチューニング
PGAおよび一時領域のチューニング
自動メモリー管理の使用
パフォーマンス・チューニングのまとめ
コース日数 5 日間 【トレーニングキャンパス赤坂】2015/1/19-23