DBA & Developer Day 2016 ダウンロード資料

(1)

津島博士のパフォーマンス講座

チューニングの基礎から次期リリー

ス新機能まで

日本オラクル株式会社

クラウド・テクノロジー事業統括

Database & Exadataプロダクトマネジメント本部

担当ディレクター

(2)

• 以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するも

のです。また、情報提供を唯一の目的とするものであり、いかなる契約

にも組み込むことはできません。以下の事項は、マテリアルやコード、

機能を提供することをコミットメント（確約）するものではないため、

購買決定を行う際の判断材料になさらないで下さい。オラクル製品に関

して記載されている機能の開発、リリースおよび時期については、弊社

の裁量により決定されます。

OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。

文中の社名、商品名等は各社の商標または登録商標である場合があります。

(3)

本日の内容

AWRからの解析

SQLチューニング

オプティマイザ統計

12.2新機能

1

2

3

4

1

2

3

4 AWRからの解析

SQLチューニング

オプティマイザ統計

12.2新機能

(4)

• 解析できないもの

• DB Timeベースのチューニング

• AWRについて

• 待機イベント

• サンプルAWR

AWRからの解析

(5)

AWRからの解析

解析できないもの

• データベースに負荷が掛っていない

–

アプリケーション側の問題

–

データベース処理以外の時間が多い

• どんな処理か

–

大量のデータを持ってきてアプリケーションで処理する

• 必要な（処理をした）データだけを持ってきましょう

–

Row by Row処理（繰返し処理でデータベースに1行づつアクセスする）

• 結合などはデータベースで行いましょう

–

バッチ処理で多い

• ＯＳ統計でリソース（CPU、I/Oなど）の使用状況から判断

(6)

AWRからの解析

DB Timeベース・チューニング（DB Timeとアクティブ・セッション）

• DB Time（データベース時間）

–

すべてのデータベース内処理に要したセッション（フォアグランド）の合計時間

（

CPU

時間、

I/O

時間、

非アイドル待機

時間が含む）

• Active Session（アクティブ・セッション）

–

現在データベース内で処理を行っている（DB Time中の）セッション

• %Activity（平均アクティビティ）

–

実経過時間とアクティブな（データベースを使用した）時間の割合

• アクティブな時間の内訳（CPU、I/Oなど）を分析

SQL

本を閲覧する

カートに入れる

精算する

時間

= データベースを使用した時間

一冊の本のレビューを読む

ユーザ1

アクティブ・セッション

(7)

AWRからの解析

DB Timeベース・チューニング（複数セッションのとき）

• DB Time：すべてのセッションのデータベース時間の合計

• Average Active Sessions（平均アクティブ・セッション）

–

すべてのセッションの平均アクティビティ（%Activity）の合計

–

アクティブ・セッションが多い時間帯が負荷が多い

ユーザ2

ユーザ3

時間

t には2つのアクティブ・セッションが存在する

ユーザ1

ユーザn

(8)

AWRからの解析

DB Timeベース・チューニング（DB Timeの可視化）

• アクティブ・セッション（DB Time中のセッション）数から負荷を見る

–

多いところの内訳を分析する

平均アクティブ・セッション＝

全アクティブ・セッションの経過時間

全データベース時間

時間

1 2 3 4

アクティブ・セッションの経過

経過時間

t0

t1

ユーザ1

ユーザ2

ユーザ3

ユーザn

(9)

AWRからの解析

DB Timeベース・チューニング（EM パフォーマンス・ページ）

• 待機クラスごとのアクティブ・セッションの経過

（ASH:Active Session History）

(10)

AWRからの解析

DB Timeベース・チューニング（システムのパフォーマンスとDB Time）

• システム負荷の増加（セッション数の増加など）

–

競合待機時間などの増加（=> DB Timeの増加）

• DBマシンがＣＰＵネック

–

CPU実行時間の増加（=> DB Timeの増加）

• I/Oパフォーマンスの低下

–

I/O時間の増加（=> DB Timeの増加）

バックグランド（

BG）プロセスについて

• BGプロセスが原因でもDB Timeに現れる

（例、

log parallel writeが多いとlog file syncも増える）

• BGプロセスはH/Wリソースが問題なけれ

ば影響は少ない

db file sequential read

競合待機

CPU実行

db file sequential read

競合待機

CPU実行

ユーザ

1 I/O時間

I/O時間

(11)

AWRからの解析

AWRについて（AWR内での時間）

• DB Time（データベース時間）

–

フォアグランドがデータベース内で処理した合計時間 ≠ 応答時間

(Elapsed Time)

• SQL ordered by Elapsed Time（SQL経過時間の合計）

–

SQLで使用したDB Time ≠ SQLの応答時間（Elapsed Time）

• パラレル実行では全スレーブ・プロセスの合計時間が含む

• DB CPU（CPU時間）

–

DB Time内でのCPU時間（DB CPU / DB Time ≠ CPU使用率）

• バックグランド・プロセスは（Oracleデータベース以外も）含まれない

• CPUリソースの限界は判断できない（CPU%の分母は利用可能なCPU時間）

–

待機時間が存在してもCPU使用率が100%になるときもある

(12)

AWRからの解析

AWRについて（Report Summary）

• Load Profile

–

Oracleデータベースに対する負荷

• Oracleデータベースに対する負荷の変化などを監視／比較

• Instance Efficiency Percentages (Target 100%)

–

インスタンス内の効率良さ（100%に近いほど効率が良い）

• インスタンス・チューニングの判断

（そうでなければSQLチューニング=>SQL統計: by Elapsed Time ）

• バッファ・キャッシュ・ヒット率など

• Top 5 Timed Foreground Events

–

フォアグランド・イベント時間（DB Timeの内訳）の上位5（または上位10）

–

DB CPUが多いときは待機が少ない（または実行計画が非効率）

(13)

AWRからの解析

待機イベント（体表的なもの）

• I/O

–

非ダイレクトI/O, ダイレクトI/Oなど<=

第26回

• エンキュー

–

HW(HWM), SQ(シーケンス), TX(行ロック)など<=

第18回

• ライブラリ・キャッシュ

–

共有カーソル関係（cursor: xxx, library cache: xxxなど）<=

第32回

–

ライブラリ・キャッシュ・オブジェクト（同じSQLやPL/SQLパッケージの実

行）<=

第50回

• マニュアルにない待機イベント

(14)

AWRからの解析

待機イベント（I/O）

• 代表的なI/O統計

–

db file sequential read（索引スキャン）/ db file parallel read（索引のprefech）

–

db file scattered read / direct path read（フル・スキャン）

–

direct path write

（ダイレクト・ライト、その他のライトはバッファ・キャッシュ関係の待機）

–

direct path read temp / direct path write temp（一時表へのI/O）

• I/O性能の確認（問題なければSQLチューニングへ）

–

AWRのI/O統計のAv Rd(ms)など

–

OS統計（sar, iostat）の%Busyなど

• SQLの特定

（SQL統計のSQL ordered by User I/O Wait Time）

• 実行計画の出力（AWRのスナップショットから）

(15)

AWRからの解析

待機イベント（子カーソル）

• 子カーソルとは（

第7回

）

–

同じSQLで別の実行計画を作成すること（ハード・パースが増える）

• 子カーソルの待機イベント

–

11.1以上はcursor: mutex関係、10.2以前はlibrary cache pin

–

SQL統計のSQL ordered by Version Count（子カーソルの多いSQL）

• 子カーソルが作成される原因はV$SQL_SHARED_CURSORを参照

–

代表的なもの

• カーディナリティ・フィードバック、

バイド・ピーク/Adaptive Cursor Sharing（優れたカーソル共有）、

バインド変数の属性が異なる、スキーマ・オブジェクトが異なるなど

(16)

AWRからの解析

サンプルAWR

• サンプルのAWRを見てみる

Execute to Parse % パースなし（カーソルキャッシュ）で実行された割合

Parse CPU to Parse

Elapsd %

パース時間に対する

_{CPU時間の割合}

CPU時間以外（ラッチなど）が多いか

(17)

本日の内容

AWRからの解析

SQLチューニング

オプティマイザ統計

12.2新機能

1

2

3

4

(18)

• SQLの基本知識

–

索引を使用しない条件

–

結合条件(1=1)、WHERE句条件(1=2)の注意点

–

問合せ変換（Query Transformation）

• 実行計画の見方

–

必要最低限のもの

• 実行計画のチューング

–

チューニング手順、SQL、ヒントなど

SQLチューニング

(19)

SQLチューニング

SQLの基礎知識（索引を使用しない条件）

• 演算している（BIツールなどは注意）

• NULL比較

• NOT(!=)

• OR条件（INリスト）

–

OR拡張（UNION ALLに変換）で索引を使用

• 後方一致（中間一致）条件

–

索引スキップ・スキャン（9iから）

• 全表スキャンより効果的なとき（先頭の個別値が少ないときなど）に使用

意識して使用する（

第9回

）

(20)

SQLチューニング

SQLの基礎知識（結合条件(1=1)、WHERE句条件(1=2)の注意点）

• 異なる結合を同じ構文で行うようなとき（動的SQLで多い）

–

結合（内部結合以外）の効率が悪いので行わない（

第44回

）

/* すべてを FULL OUTER JOIN で行う */

…

IF abc = '01' THEN –- すべての行にMAX(c1)を入れる

v_from := '(SELECT MAX(c1) c1 FROM tab2 WHERE c2 = 1) B ON

(1=1)

';

ELSIF abc = '02' THEN -- 結合しない

v_from := '(SELECT c1 FROM tab2 WHERE

(1=2)

) B ON ON A.c1 = B.c1';

ELSE -– 通常のFULL OUTER JOIN

v_from := 'tab2 B ON A.c1 = B.c1';

END IF

v_stmt_str := 'SELECT … FROM tab1 A

FULL OUTER JOIN

' || v_from;

OPEN v_tab3_cursor FOR v_stmt_str;

(21)

• 無駄な処理の排除

–

結合（外部キー）、ORDER BY（インライン・ビュー内）、DISTINCT（主キー）<=

第34回

–

GROUP BY（結合をしないインライン・ビュー内）<=12cから

• ビュー（インライン・ビュー）の最適化（次頁）

• 副問合せのネスト解除（セミ結合、アンチ結合）

–

INの方が制約が多いのでEXISTSを使用する（

第29回

）

–

OR条件で変換されないときがある（

第44回

、

第52回

）

• UNION ALLに変換

（OR拡張、表拡張

_{<=索引が使用可と使用不可のパーティションに}

）

–

索引を使用するように（

第9回

、

第34回

）

–

パラレル実行時のOR拡張は一部シリアル処理に（

第52回

）

SQLチューニング

(22)

SQLチューニング

SQLの基礎知識（ビュー、インライン・ビュー）

• ビューとは

–

アクセス制御などで使用（条件は外で指定）

–

最初に実行される

• 特性（メリット／デメリット）

–

結合順が調整できない <=

• 例のtab1は最後の結合に

–

ビュー実行後は索引が使用できない <=

• 例のB.c3<100の索引を使用しない

–

結合前に行数を削減できる（DISTINCT、

Group Byなど） <=

• 例のインライン・ビューにGroup byがなく

ても結果は同じ（主問合せで同じSUM集計を

行っているから）

• ビューの問合せ変換

–

ビューを使用しない

• 結合順を変えたい、索引を使用したい

• View Merging（ビュー・マージ）<=

第29回

–

ビューが効果的なとき（ビューを作成）

• Group by Placement（Group byの配置）<=

第42回

–

ビューをマージできない／しない

• Group by後にネステッド・ループ結合するなど

• Predicate Pushing（述語のプッシュ）<=

第29回

SQL> SELECT A.c2, SUM(b2), SUM(b3) FROM tab1 A ,

2 (SELECT C.c1,C.c3,SUM(C.c2) b2,SUM(D.c2) b3

3 FROM tab2 C,tab3 D

4 WHERE C.c1 = D.c1 GROUP BY C.c1,C.c3) B

5 WHERE A.c1 = B.c1 AND B.c3 < 100

6 GROUP BY A.c2 ;

(23)

SQLチューニング

実行計画の見方（注目する項目）

• カーディナリティ

–

述語を適用した行数（Rows）

• アクセス方法

–

索引スキャン、全表スキャン、ビューアクセス

• 結合方法

–

ネステッド・ループ結合、ハッシュ結合、

ソート・マージ結合、直積結合

• 結合タイプ

–

内部結合、外部結合、セミ結合、アンチ結合

• 結合順序

–

SQLによって決まる場合も

• パーティション

–

パーティション・プルーニング（

第22回

、

第46回

）

• 静的（アクセスする番号）

• 動的（KEY、KEY(OR)など）

• 11gR2からKEY(AP)が追加

–

AND Pruning（静的＋動的）

• パラレル実行

–

スキャンのデータ分散（

第20回

、

第39回

）

• ブロック単位で分割（PX BLOCK ITERATOR）

• パーティション単位（PX PARTITION RANGE

ALL など）

–

パーティション・ワイズ結合など

(24)

SQLチューニング

実行計画の見方（注目する項目）

• パラレル実行（続き）

–

スキャン以外のデータ再分散（

第20回

、

第39回

）

• HASH

（基本はこれを使用する）

• RANGE

（ソートなど）<=

偏りやすい

• BROADCAST

（結合時の片方が小さい）

–

同じデータをすべてのプロセスに

• PARTITION

（パーティション・ワイズ結合）

–

パーティション分割（片方がパーティション表）

• NONE

（データ分散しない／データ通信しない）

–

PX SEND xxxx

とPX RECEIVEがない

–

PARTITION

やBROADCASTなどと使用

• 偏りが発生すると効果が低下する

–

PQ_DISTRIBUTE

ヒントで調整

PQ

スキャン

表

操作間パ

ラ

レ

ル

化

操作内パラレル化

PQ

結合

_PQ

QC

PQ

_PQ

PQ

OrderBy

①

②

③

PX SEND xxxx

PX RECEIVE

(25)

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 ---<…>---| ---<…>---| Pstart---<…>---| Pstop ---<…>---|

---<…>---| 0 ---<…>---| SELECT STATEMENT ---<…>---| ---<…>---| ---<…>---| ---<…>---| ---<…>---| ---<…>---|

| 1 | SORT GROUP BY | | | | | |

|* 2 | HASH JOIN | | | | | |

|* 3 | HASH JOIN | | | | | |

|* 4 | TABLE ACCESS FULL | TAB1 |

10 | | 1 | 1 |

| 5 | TABLE ACCESS FULL | TAB2 | 50 | | | |

(1)

(2)

①

②

③

|

TABLE ACCESS BY INDEX ROWID| TAB1 |

| INDEX RANGE SCAN | IX_TAB1 |

索引アクセス

|

VIEW | |

| HASH GROUP BY | |

| TABLE ACCESS FULL | TAB1 |

ビュー・アクセス

カーディナリティ

結合

パーティション・プルー

ニング（動的：

KEYなど）

(26)

SQLチューニング

実行計画の見方（

パラレル実行）

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 PARTITION RANGE ALL

スキャンの分散

PX SEND PARTITION

PX SEND BROADCAST

(27)

SQLチューニング

実行計画の見方

• 後半には、述語の情報と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部

(28)

SQLチューニング

実行計画の見方（

ネステッド・ループ結合

）

• 通常のネステッド・ループ結合

–

基本は内部表の索引を使用して結合

• 駆動表の1行に対して索引アクセス

• Multi Join Key Pre-fetching（9iから）

第34回

–

索引範囲スキャンのデータ・ブロックの

先読み（Index Range Scanのみ）

• Nested Loops Join Batching（

11gから

）

第34回

–

索引で結合後にROWIDを並べ替えて

テーブルにアクセス

–

Index Unique Scanでも可

実行計画

---| Id ---| Operation ---| Name ---|

---| 0 ---| SELECT STATEMENT ---| ---|

| 1 | HASH GROUP BY | |

| 2 |

NESTED LOOPS

| |

Nested Loops Join(2)

| 3 |

NESTED LOOPS

| |

Nested Loops Join(1)

| 4 | TABLE ACCESS FULL | TAB2 | 駆動表(1)

|* 5 | INDEX RANGE SCAN | IX_TAB1 | 内部表(1)

| 6 | TABLE ACCESS BY INDEX ROWID| TAB1 | 内部表(2)(

ここを改善

)

実行計画

---| Id ---| Operation ---| Name ---|

---| 0 ---| SELECT STATEMENT ---| ---|

| 1 | HASH GROUP BY | |

| 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | 内部表をPer-fetchする

| 3

| NESTED LOOPS

| |

| 4 | TABLE ACCESS FULL | TAB2 | 駆動表

|* 5 | INDEX RANGE SCAN | IX_TAB1 | 内部表

SQL> SELECT … FROM tab1,tab2 WHERE

tab1.c1 = tab2.c1

GROUP BY … ;

実行計画

---| Id ---| Operation ---| Name ---|

---| 0 ---| SELECT STATEMENT ---| ---|

| 1 | HASH GROUP BY | |

| 2

| NESTED LOOPS

| |

| 3 | TABLE ACCESS FULL | TAB2 | <- 駆動表

| 4 |

TABLE ACCESS BY INDEX ROWID| TAB1 | <- 内部表

|* 5 | INDEX RANGE SCAN | IX_TAB1 |

(29)

SQLチューニング

実行計画の見方（ソート・マージ結合、直積結合）

• ソート・マージ結合

–

索引の代わりにソートして結合

–

等価結合以外など

• 直積結合

–

結合条件がないので効率が悪い

–

できるだけ行わない（ソートも行われる）

SQL> SELECT … FROM tab1,tab2 WHERE

tab1.c1 > tab2.c1

2 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|

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|

(30)

SQLチューニング

実行計画の見方（ハッシュ結合、外部結合）

• ハッシュ結合（等価結合のみ）

–

索引の代わりにメモリ上にハッシュ・テーブ

ルを作成（最初にアクセスした表に）

–

スター・スキーマはRight-deep Joinが効果的

（SWAP_JOIN_INPUTSヒント）<=

第46回

• 外部結合

–

LEFT OUTER JOIN, RIGHT OUTER JOIN

–

ハッシュ結合で核でないテーブルを先にア

クセス可能に（10gから）

SQL> SELECT … FROM tab1,tab2,tab3

2 WHERE tab1.c1=tab2.c1 AND tab1.c2=tab3.c2

3 AND tab2.c3=xxx AND tab3.c2=xxx GROUP BY … ;

実行計画（Left-deep Join）

---| Id ---| Operation ---| Name ---|

---| 0 ---| SELECT STATEMENT ---| ---|

| 1 | HASH GROUP BY | |

|* 2 |

HASH JOIN

| |

|* 3 | HASH JOIN | |

|* 4 | TABLE ACCESS FULL| TAB2 |

| 5 | TABLE ACCESS FULL|

TAB1

|

|* 6 |

TABLE ACCESS FULL

|

TAB3

|

実行計画（Right-deep Join）

---| Id ---| Operation ---| Name ---|

---| 0 ---| SELECT STATEMENT ---| ---|

| 1 | HASH GROUP BY | |

|* 2 |

HASH JOIN

| |

|* 3 |

TABLE ACCESS FULL

|

TAB3

|

|* 4 | HASH JOIN | |

|* 5 | TABLE ACCESS FULL| TAB2 |

| 6 | TABLE ACCESS FULL|

TAB1

|

SQL> SELECT … FROM tab1 LEFT OUTER JOIN tab2 USING (c1) GROUP BY … ;

実行計画

---| Id ---| Operation ---| Name ---|

---| 0 ---| SELECT STATEMENT ---| ---|

| 1 | HASH GROUP BY | |

| 2 |

HASH JOIN OUTER

| |

| 3 | TABLE ACCESS FULL|

TAB1

|

| 4 | TABLE ACCESS FULL| TAB2

|

実行計画（Oracle Database 10gから）

---| Id ---| Operation ---| Name ---|

---| 0 ---| SELECT STATEMENT ---| ---|

| 1 | HASH GROUP BY | |

| 2 |

HASH JOIN RIGHT OUTER

| |

| 3 | TABLE ACCESS FULL | TAB2 |

| 4 | TABLE ACCESS FULL |

TAB1

|

(31)

SQLチューニング

実行計画の見方（セミ結合、アンチ結合）

• セミ結合

–

EXISTS, IN

条件の副問合せ

–

セミ・ハッシュ結合で副問合せのテーブル

を先にアクセス可能に（10gから）

• アンチ結合

–

NOT EXISTS, NOT IN条件の副問合せ

–

アンチ・ハッシュ結合で副問合せのテーブ

ルを先にアクセス可能に（10gから）

SQL> SELECT … FROM tab1 WHERE

EXISTS

2 (SELECT 0 FROM tab2 WHERE tab1.c1 = tab2.c1) ;

実行計画

---| Id ---| Operation ---| Name ---|

---| 0 ---| SELECT STATEMENT ---| ---|

| 1 |

HASH JOIN SEMI

| |

| 2 | TABLE ACCESS FULL|

TAB1

|

| 3 | TABLE ACCESS FULL| TAB2

|

実行計画（Oracle Database 10gから）

---| Id ---| Operation ---| Name ---|

---| 0 ---| SELECT STATEMENT ---| ---|

| 1 |

HASH JOIN RIGHT SEMI

| |

| 2 | TABLE ACCESS FULL | TAB2 |

| 3 | TABLE ACCESS FULL |

TAB1

|

SQL> SELECT … FROM tab1 WHERE

NOT EXISTS

2 (SELECT 0 FROM tab2 WHERE tab1.c1 = tab2.c1) ;

実行計画

---| Id ---| Operation ---| Name ---|

---| 0 ---| SELECT STATEMENT ---| ---|

| 1 |

HASH JOIN ANTI

| |

| 2 | TABLE ACCESS FULL|

TAB1

|

| 3 | TABLE ACCESS FULL| TAB2

|

実行計画（Oracle Database 10gから）

---| Id ---| Operation ---| Name ---|

---| 0 ---| SELECT STATEMENT ---| ---|

| 1 |

HASH JOIN RIGHT ANTI

| |

| 2 | TABLE ACCESS FULL | TAB2 |

| 3 | TABLE ACCESS FULL |

TAB1

|

(32)

• チューニングの手順

1. オプティマイザ統計の再収集

2. 索引の作成（索引のチューニング）

3. SQLの変更（ヒントの追加）

• SQLの変更ができない

–

SPM （SQL Plan Management）のSQL計画手動ロード（

第38回

）

• ヒントを入れた実行計画をベースラインとして登録する

–

SQL翻訳フレームワーク（12cから）

第51回

• SQLを変更したいとき（SQLの置き換え方法を登録）

• 問題を特定できない

–

SQLチューニング・アドバイザ（

第38回

）

SQLチューニング

実行計画のチューニング

(33)

SQLチューニング

問題となる実行計画（主な

SQL）と対処

問題点

統計

ヒント

_{SQLなどの変更}

結合方法や結合順が最適でない

○

○ 副問合せの追加

/削除など

索引が使用できていない

○

○ 索引を使用できるように変更、索引を作成

問合せ変換が最適でない

○

○ 明示的に

SQLを変更

同じような副問合せがある

×

SQLを分割する（WITH句を使用する）

第

11回

同じ表に異なる条件で

SELECTしている

×

SQLをCASE式で1つに

(

第

24回

)

同じ表に異なる条件で

_{INSERTしている}

×

_{SQLをマルチ・テーブル・インサートに（同じ表でも可）}

第

_24回

同じ表を

UPDATE文とINSERT文でアクセス

×

SQLをMERGE文で1つに(

第

30回

)

結合した

UPDATE文

×

SQL（複雑な副問合せを使用する）をMERGE文に

(

第

30回

)

Redoログ出力がネック(特にパラレルDML)

×

ダイレクト・パス・インサートに（

UPDATE、DELETEも）

第

15回

TEMP領域を使用

×

• パラレル度を上げる、プログラムを分割／並列化

• 索引、パーティション（パーティション・ワイズ）を使用

(

第

45回

)

PL/SQLプロシージャが遅い

×

• バルク処理、パラレル化（

DBMS_PARALLEL_EXECUTE）

第

11回

(34)

• 行数の見積もりが正しくない（実行時と大きく異なるとき）

–

実行計画の見積もり行数（E-Rows）と実行行数（A-Rows）を比較

SQLチューニング

オプティマイザ統計の再収集

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 | … |

(35)

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）

省略すると最後のSQL

(36)

SQLチューニング

実行計画の確認（

リアルタイムSQL監視）

• 実行中の実行計画を表示（デフォルトで5秒以上のSQL、MONITORヒントで強制的に）

時間のかかっているSQLが自動的に監視

されリストされる(経過時間等でソート可能)

このSQL実行

全体の統計

実行計画のステッ

プごとの統計など

ステップごとの待機イベント

(37)

SQLチューニング

オプティマイザ統計の再収集

• サンプル・サイズを大きくしても精度が上がらないとき

–

ヒストグラムと拡張統計を確認

• DBA_TAB_COL_STATISTICS、DBA_STAT_EXTENSIONS

• 拡張統計は自動的に作成されない<=12cで改善

–

列グループ（ないときは列値の組合せは均等となる）

• WHERE c1 = xxx AND c2 = xxx（フィルター条件、結合）

• GROUP BY c1,c2

–

式（BIツールを使用しているときなど）

• WHERE UPPER(c1) = xxx

• 一意値が多い列のヒストグラムには限界がある

–

バケット数の最大が254（12cから2048に拡張）

(38)

• 全表スキャンを行っている表は索引スキャンした方が良くないか

–

実行計画のRowsを確認

• 索引スキャンの効率が悪くないか

–

索引の列の組合せ、索引の列の順番などを確認

• 例えば、以下のようなSQL

• 優先順位を明確にして作成する

–

これが難しい（そのため、フル・スキャンでも高速なExadataが効果的）

• 作り過ぎないように使用しない索引は削除する

SQLチューニング

索引の作成（索引のチューニング）

SQL> SELECT … WHERE c1=xx AND c2=xx AND c3=xx;

SQL> SELECT … WHERE c1=xx AND c3=xx;

(39)

SQLチューニング

SQLの変更（主なSQLの変更）

• 結合のUPDATE文をMERGE文に

• 繰り返し副問合せは

WITH句で

• UPDATE、DELETEもダイレクト・イン

サートに

–

Nologgingにすると効果的だが、Data Guard

などで災害対策サイトを作成するときは表圧

縮が効果的

• ブロック・イメージの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 ;

SQL> UPDATE t01 A

2 SET A.c3 = A.c3 + (SELECT c2 FROM

t02

B WHERE A.c1 = B.c1)

3 WHERE EXISTS (SELECT 0 FROM

t02

B WHERE 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;

SQL> SELECT * FROM

2 (

SELECT 部門,sum(売上) 部門売上 FROM 売上表 GROUP BY 部門

) w_A

3 WHERE 部門売上 < (SELECT avg(部門売上) FROM

4 (

SELECT 部門,sum(売上) 部門売上 FROM 売上表 GROUP BY 部門

));

SQL> WITH w_A AS (

SELECT 部門, sum(売上) FROM 売上表 GROUP BY 部門

)

(40)

SQLチューニング

SQLの変更（主なSQLの変更）

• 同じ表に異なる条件でのSELECTは

CASE式に

–

索引アクセスが効果的なときはUNION ALL

• 異なる条件でのINSERTはマルチ・

テーブル・インサートに

• テーブル・ファンクション

SQL> INSERT INTO tab02

2 SELECT c2,c4, ... FROM tab01 WHERE c1 = '01' ... ;

SQL> INSERT into tab02

2 SELECT c3,c5, ... FROM tab01 WHERE c1 != '01' ... ;

SQL> INSERT FIRST

2 WHEN c1 = '01' THEN INTO tab02 VALUES (c2,c4, ...);

3 WHEN c1 != '01' THEN INTO tab02 VALUES (c3,c5, ...);

4 SELECT c2,c3,c4,c5, ... FROM tab1 WHERE ... ;

SQL> SELECT c2,c4, ... FROM tab01 WHERE c1 = '01' ...

2 UNION ALL

3 SELECT c3,c5, ... FROM tab01 WHERE c1 != '01' ... ;

SQL> SELECT CASE WHEN c1 = '01' THEN c2 ELSE c3 END a1,

2 CASE WHEN c1 = '01' THEN c4 ELSE c5 END a2, ...

3 FROM tab01 WHERE ... ;

SQL> SELECT *

2 FROM TABLE(

test_pkg.func02

(CURSOR(SELECT * FROM tab02)))

3 JOIN tab01 USING (c1);

(41)

SQLチューニング

SQLの変更（ヒント）

• 表の別名があるときは別名を

• ビュー（副問合せ）内の表には

• ビュー・マージされても使用されるが

LEADINGヒントではオブジェクト別名を

• 主に使用するヒント

–

結合順を変える（ORDERED, LEADING）

–

ビュー・マージを止める（NO_MERGE）

–

索引を使用する（INDEX, INDEX_FFS）

–

索引を使用しない（FULL, NO_INDEX）

–

結合方法を変える（USE_HASH, USE_NL,

USE_MERGE）

–

パラレル実行関係（PARALLEL,

PQ_DISTRIBUTE）

–

問合せ変換関連のヒント

<=

第55回

SQL> SELECT /*+ INDEX(

A

ix_tab1) / FROM tab1

A

WHERE … ;

-- グローバル・ヒント（

ANSI準拠の結合文では使用できない

）

SQL> SELECT /*+ INDEX(

A

.tab1 ix_tab1) /

2 FROM (SELECT * FROM tab1 WHERE … )

A

;

-- 問合せブロック付きヒント

SQL> SELECT /*+ INDEX(

@SEL$2

tab1 ix_tab1) /

2 FROM (SELECT * FROM tab1 WHERE … ) A ;

SQL> SELECT /+ LEADING(tab1 v2.tab2 v2.tab3) MERGE(v2) / *

2 FROM tab1,v2 WHERE tab1.c2=v2.c2;

(42)

本日の内容

AWRからの解析

SQLチューニング

オプティマイザ統計

12.2新機能

1

2

3

4

(43)

• 統計の種類

–

どのように使用されるか

• 収集方法

–

収集方法とデフォルトで収集されるもの

• 統計の補正

–

どこまで行えるか

オプティマイザ統計

(44)

• 表統計

–

行数、データ・ブロック数、行連鎖・行移行の数、平均行長

• 索引統計

–

索引内の個別値数、索引の深さ（BLEVEL）、リーフ・ブロック数、クラスタ化係数

• 列統計

–

個別値数、NULL数、平均列データ長、データ分布（最小値と最大値、ヒストグラム）

• システム統計（I/O+CPUコスト・モデル）

–

CPU性能、I/O性能（単一ブロック・リード、マルチ・ブロック・リード）

パーティション表には、パーティション、サブ・パーティションごとにも同様の情報を収集する

表アクセス・コスト

索引アクセス・コスト

カーディナリティ、サイズ

アクセスする行数やブロック数を求め、

CPU性能やI/O性能でコストを計算する

オプティマイザ統計

統計の種類

(45)

オプティマイザ統計

統計の種類

• 列統計（ヒストグラム）

–

ヒストグラムは完全には設定できないものも（12cで拡張）<=

第35回

• 異なる値が255以上の高さ調整済ヒストグラム

–

バケット数が最大254なので、各値を別バケットに入れられない

–

拡張統計（自動作成されない）

• システム統計（CPU性能、I/O性能）

–

Exadata

は固有のシステム統計を取得する（DB稼働後一度だけで良い）

• DBMS_STATS.GATHER_SYSTEM_STATS(‘EXADATA’);

–

Exadata

以外

• 初回起動時のデフォルト値（単一ブロック・リード、マルチ・ブロック・リード時間などが

ない）で良いが、索引スキャンとフル・スキャンを使用する場合は一度収集した方が良い

(46)

• 列グループの統計（Column Groups）

–

同一表内の複数列に跨る統計を保持することで、列データ間の相関関係を

考慮したカーディナリティの計算を可能とする

–

例：顧客表の住所列と年代列など

–

12c

からのSQL計画ディレクティブ（

第52回

）と自動列グループ検出（

第49回

）

• 式の統計（Expression Statistics）

–

関数や演算式を含めた統計を保持することで、WHERE句におけるカーディナリ

ティの計算を可能する

–

例：Where UPPER（氏名) = :B1

オプティマイザ統計

統計の種類（拡張統計）

(47)

• 自動オプティマイザ統計収集

–

22:00から26:00（土日は6:00から26:00）に自動実行する

–

独自の方式で収集しているシステム以外はこれを使用する

–

ディクショナリだけの自動収集も可能

• DBMS_STATS.SET_GLOBAL_PREFS(’AUTOSTATS_TARGET’,’ORACLE’)

• オプティマイザ統計の手動収集

–

必要な（大量に変更があった）ときを判断して実行する

–

ディクショナリの手動収集

• DDL

を多く発行された後などに実行

–

DBMS_STATS.GATHER_DICTIONARY_STATS

オプティマイザ統計

統計収集

(48)

• 自動的に収集するものを決める

–

デフォルトは10%以上変更されたオブジェクト

• デフォルト値

–

収集する適切なサイズをOracleが決定（AUTO_SAMPLE_SIAZE）

• Oracle11gで拡張されたハッシュ・アルゴリズム

（サンプリングより高速、Computeモード統計

と同等精度）

–

索引統計の収集を自動判断（AUTO_CASCADE）

–

ヒストグラムの収集を自動判断（FOR ALL COLUMNS SIZE AUTO）

–

カーソルの無効化の時期をOracleが決定（AUTO_INVALIDATE）

• 収集後に共有プール上の実行計画が正しくなくなる

• ディクショナリもデフォルトで収集される

オプティマイザ統計

(49)

• デフォルトで収集しないものがあるので注意

–

拡張統計

• 列グループ統計、式の統計

–

固定オブジェクト

• 動的パフォーマンス・ビュー（v$ビュー）の実表（x$表）

• アプリケーションの変更やデータベース構成の変更の時に手動で再収集する

–

DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

• AWRやStatspackでも使用されているので、取得に時間が掛るような時も

–

一時表（Global Temporary Table）

• 正しく収集できないので動的サンプリングを使用する

–

12cからセッション固有統計が提供されたので手動でも収集しやすくなった<=

第35回

オプティマイザ統計

(50)

• 動的サンプリング（11gまで）

–

11gR2からパラレル実行時にレベルを自動決定（大きな表、複雑なWHERE句）

• カーディナリティ・フィードバック（11gまで）

–

見積りと実行時の統計が大きく異なると記録して2回目から使用する

（単一表カーディナリティのみ）

• 適応問合せ最適化（

Adaptive Query Optimization

）

<=12cから（

第33回

）

–

適応計画（Adaptive Plans）

・・・実行時の最適化

• 結合方法（Join Methods）

• パラレル分散方法（Parallel Distribution Methods）

–

適応統計（Adaptive Statistics）

・・・次回実行時以降の最適化

• 動的統計（Dynamic Statistics）… SQL計画ディレクティブからも起動

–

サンプル・サイズの自動調整、統計の再利用や他の問合せでも利用可

• 自動再最適化（Automatic Reoptimization）… 統計フィードバック（結合カーディナリティも）

• SQL計画ディレクティブ（SQL Plan Directives） <=

第52回

オプティマイザ統計

統計の補正

(51)

• SQL文実行時に統計が不十分な場合の動作

オプティマイザ統計

統計の補正（適応問合せ最適化）

SQL文の実行(初回)

ディレクティブから動的統計

の収集（統計の再利用も可）

カーソルがエージ

アウトなどで存在

しない場合

自動再最適化

(統計

フィードバック

)を実施

・適用計画

・実行時統計のメモリ上への保存

・

SQL 計画ディレクティブの作成

カーソルが残って

いる場合

同じ

SQL文の実行

SQL 計画ディレクティブから列グループ

統計を追加（

SQL計画ディレクティブは

・・・

使用可能な

SQL計画

ディレクティブがある

(52)

本日の内容

AWRからの解析

SQLチューニング

オプティマイザ統計

12.2新機能

1

2

3

4

(53)

• オプティマイザ

–

適応問合せ最適化（

Adaptive Query Optimization）の変更

–

拡張統計（式の統計）

–

オプティマイザ統計アドバイザー（Optimizer Statistics Advisor）

–

インクリメンタル・グローバル統計の強化

• SQL処理

–

OR Expansion（OR拡張）の強化

–

Recursive WITH（再帰問合せ）の強化

–

Cursor-Duration Temporary Tables（一時表変換の強化）

–

Partition-wise DISTINCTの強化

–

Band Join（範囲条件結合）の強化

(54)

新しいデフォルトとより細かい制御

パフォーマンスの安

定性重視（

Minimal

Adaptive）

やや保守的な

Adaptive アプローチ

（

Default Adaptive）

新規の複雑な問合せ

にもベストパフォーマ

ンス（

Fully Adaptive）

optimizer_adaptive_plans

FALSE

TRUE

optimizer_adaptive_statistics

FALSE

TRUE

optimizer_adaptive_features

Adaptive

Plans

Adaptive

Statistics

optimizer_adaptive_statistics

Default:

FALSE

optimizer_adaptive_plans

Default:

TRUE

Oracle 12.1

Oracle 12.2

12.2新機能（オプティマイザ）

(55)

• 列グループ統計を自動的に作成するかを指定可能に

–

AUTO_STAT_EXTENSIONSの追加

• DBMS_STATSのプリファレンス・パラメータ

• デフォルトはOFF

–

SQL計画ディレクティブから列グループ統計を作成しない

• 12.1 でもパッチを当てれば同様

国

姓

相関関係

12.2新機能（オプティマイザ）

(56)

12c Optimizer

列使用情報統計

SQL計画

ディレクティブ

動的統計

(Adaptive)

Adaptive

Plans

Fully

Adaptive

12.2新機能（オプティマイザ）

Adaptive Query Optimization（Fully Adaptive）

ヒストグラム

列グループ統計

DBMS_STATSプリファレンス:

AUTO_STAT_EXTENSIONS=ON

（デフォルト

OFF

）

G AT H E R _ x x x _ S TAT S

( F O R A L L C O L U M N S

S I Z E A U T O )

統計フィードバック

（単一表カーディナリティ

と結合カーディナリティ

）

(57)

12c Optimizer

列使用情報統計

SQL計画

ディレクティブ

統計フィードバック

（

単一表カーディナリティのみ

）

G AT H E R _ x x x _ S TAT S

( F O R A L L C O L U M N S

S I Z E A U T O )

ヒストグラム

動的統計

Adaptive

Plans

DBMS_STATSプリファレンス:

AUTO_STAT_EXTENSIONS=ON

（デフォルト

OFF

）

Default

Adaptive

12.2新機能（オプティマイザ）

Adaptive Query Optimization（Default Adaptive）

(58)

12c Optimizer

列使用情報統計

SQL計画

ディレクティブ

Minimal

Adaptive

12.2新機能（オプティマイザ）

Adaptive Query Optimization（Minimal Adaptive）

ヒストグラム

統計フィードバック

（

単一表カーディナリティのみ

）

動的統計

G AT H E R _ x x x _ S TAT S

( F O R A L L C O L U M N S

S I Z E A U T O )

(59)

• 有効なとき…

–

長時間実行の問合せ

–

複雑な問合せ

–

複雑なスキーマ

–

複雑なデータ

• Ad-hoc問合せの強化

–

SQL計画ディレクティブにより、オプティマイザがSQLから学び、その情報

を他と共有することが可能

• より多くのSQLを最適な実行計画にする

–

FALSE では単一表の統計フィードバックと列グループ統計の作成/収集のみ

Ad-hoc問合せでは統計収集するまで初回実行が遅くなる（結合が多いと速くできない場合も）

12.2新機能（オプティマイザ）

OPTIMIZER_ADAPTIVE_STATISTICS=TRUE

(60)

• SELECTリスト、WHERE句、GROUP BY句などの演算式を格納

–

DBA_EXPRESSION_STATISTICSで参照可能

• オプティマイザ統計には反映されない

• DBIMのIn-Memory Expressionsで使用され仮想列を作成する

12.2新機能（オプティマイザ）

式の統計（Expression Statistics Store :ESS）

SQL> SELECT c3 FROM abc1 WHERE c1+c2 < 105;

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SQL> SELECT table_name,expression_id,snapshot,evaluation_count,fixed_cost,dynamic_cost,expression_text,last_modified

2 FROM dba_expression_statistics WHERE table_name = 'ABC1';

TABLE_NAME EXPRESSION_ID SNAPSHOT EVALUATION_COUNT FIXED_COST DYNAMIC_COST EXPRESSION_TEXT LAST_MOD

-- --- -- -- ---- ---

---ABC1 1.6850E+19 LATEST 1 3.1710E-08 0 "C3" 16-09-09

ABC1 1.1808E+19 LATEST 1 1.5855E-06 0 "C1"+"C2" 16-09-09

(61)

• ESSは、常にワークロード

を監視

–

問合せパース中に、頻度

とコストをベースに

“hot”

な演算式を記録

• 新しいプロシージャを使用

してTop Nの式をキャプチャ

–

DBMS_INMEMORY_ADMIN.

IME_CAPTURE_EXPRESSIONS

• 新しいプロシージャを使用

してIM列ストアに隠し仮想列

を作成してポピュレートする

–

DBMS_INMEMORY_ADMIN.

IME_POPULATE_EXPRESSIONS

Expression Statistic Store(ESS)

Capture Top N Expressions

In-Memory Expressions automatically

added to IM Column Store

IM Column store

EXPRESSION

TEXT COUNT COST

A+5 UPPER(x)

C*D

12.2新機能（オプティマイザ）

In-Memory Expressions（自動キャプチャ）

(62)

• オプティマイザ統計の収集が現在の

ベストプラクティス

に従っているかを

ルール

セットを使用して確認する

–

過去から引き継いでいる資産（スクリプト）が使われていることがよくあり、最

適ではない設定でオプティマイザ統計が収集されている

• 調査結果

と

推奨事項

を提示するレポートを生成

• アクション

を使用して推奨事項を実施するためのSQLスクリプトを生成

• メンテナンス・ウィンドウ中のジョブでの実行がデフォルトで設定される

Rules

Findings

Recommendations

Actions

12.2新機能（オプティマイザ）

Optimizer Statistics Advisor

(63)

ID

NAME

RULE_TYPE

DESCRIPTION

1UseAutoJob

SYSTEM

自動ジョブで統計収集する

2CompleteAutoJob

SYSTEM

メンテナンス時間枠

(window)でジョブ実行が失敗している

3MaintainStatsHistory

SYSTEM

統計履歴表が肥大化している

4UseConcurrent

SYSTEM

統計収集にConcurrentプリファレンスを使用している

5UseDefaultPreference

SYSTEM

統計収集にデフォルト設定を使用している

6TurnOnSQLPlanDirective

SYSTEM

SQL Plan Directive(SPD) を有効化すべき

7AvoidSetProcedures

OPERATION

Set Statisticsプロシージャを避ける

8UseDefaultParams

OPERATION

統計収集はデフォルト・パラメータを使用する

9UseGatherSchemaStats

OPERATION

gather_schema_statsプロシージャを使用する

10AvoidInefficientStatsOprSeq

OPERATION

非効率的な統計の動作シーケンスを避ける

11AvoidUnnecessaryStatsCollection

OBJECT

不要な統計収集を避ける

12.2新機能（オプティマイザ）

Optimizer Statistics Advisor（アドバイザーのルール）

(64)

• アドバイザーのスコープは3種類のフィルターによって狭めることが可能

–

オブジェクト（スキーマ名、オブジェクト名）

–

オペレーション（DBMS_STATSのプロシージャ）

–

ルール（アドバイザー・ルール）

• 特定のオブジェクト（SHスキーマのCOUNTRIES表）を除外する例

-- Turn off validation/reporting… for table SH.COUNTRIES

DECLARE

tname

VARCHAR2(32767) := 'demo';

-- タスク名

filter_report clob;

-- report of operation

BEGIN

filter_report :=

dbms_stats.

CONFIGURE_ADVISOR_OBJ_FILTER

(tname, NULL, NULL,'SH','COUNTRIES','DISABLE‘);

END;

/

12.2新機能（オプティマイザ）

Optimizer Statistics Advisor（カスタマイズ）

(65)

12.2新機能（オプティマイザ）

Optimizer Statistics Advisor（統計プリファレンスの制御）

• 強制的なデフォルト値での収集

–

プリファレンスPREFERENCE_OVERRIDES_PARAMETERの追加

–

統計プリファレンス・パラメータを強制的にデフォルト値で収集可能に

• DBMS_STATS.GATHER_XXX_STATSの入力パラメータを無視する

–

TRUEで入力パラメータを無視する（デフォルトはFALSE）

• 様々なところで行っているオプティマイザ統計収集に対して、統計プリファレ

ンス・パラメータの値を強制的に設定したいときなどに有効

• アドバイザーで「デフォルト・パラエメータを使用する」とアドバイスされても簡単に変えら

れない

(66)

12.2新機能（オプティマイザ）

インクリメンタル・グローバル統計の強化（第33回）

Sales Table

2012/10/22

2012/10/23

2012/10/18

2012/10/19

2012/10/20

2012/10/21

SYSAUX表領域

3. 新しいパーティションをテーブルに

追加してデータロード

2012/10/24

_{4. 新しいパーティションの}

パーティション統計を収集

5. SYSAUXから他のパーティ

ションのシノプシスを取得

6.既存と新しい一つのパー

ティションのシノプシスを集

計することでグローバル統

計を作成する

1. パーティション・レベル統計が収集され

シノプシス

を

作成

2. パーティションレベルの統計とシノプシスを

集計することでグローバル統計を作成する

(67)

• シノプシスの収集を新しいアルゴリズムに

–

DBMS_STATS プレファレンスにNDV収集アルゴリズム指定を追加

• AUTO_SAMPLE_SIZEと同等のアルゴリズム

• プレファレンスAPPROXIMATE_NDV_ALGORITHMの追加

–

‘REPEAT OR HYPERLOGLOG’

（デフォルト）

• 古いフォーマットのシノプシスが存在する場合古いアルゴリズムを使い続ける

• シノプシスが存在しない、または、新しいフォーマットのものがある場合、新アルゴリズム

を使用

–

‘ADAPTIVE SAMPLING’

• 強制的に古いアルゴリズムを使用

–

'HYPERLOGLOG’

12.2新機能（オプティマイザ）

インクリメンタル・グローバル統計の強化（新しい収集アルゴリズム）