Oracle Direct SeminarプログラムやSQLを特定するのに必要十分な区分で管理用コメントの付加します。• 汎用的なSQLがコメントによって、“同一のSQL”でなくなってしまっては、本末転倒です。汎用

<Insert Picture Here>

Oracle Direct Seminar

オラクルコンサルが語るSQLチューニングの真髄解決編Part1,2

日本オラクル株式会社

1

Copyright© 2010, Oracle. All rights reserved. 2

• Introduction

• 目的とゴール

• SQLパフォーマンス問題の理由と原因• SQLパフォーマンス問題の定義

• SQLパフォーマンス問題へのアプローチ

• SQLパフォーマンス問題の解決へのアプローチ• 定型的なSQLチューニング

• SQLコーディングルール

• 非定型的なSQLチューニング

• コストベース・オプティマイザへのインプット

• 実行計画の妥当性

• データアクセス方法

• 表結合方法・順序

• まとめ

• Appendix

アジェンダ



Introduction

Copyright© 2010, Oracle. All rights reserved.

本セミナーの目的とゴール

4

目的とゴール

• SQLパフォーマンス問題が発生する理由を理解する• SQLパフォーマンス問題とは何かを理解する

• SQLパフォーマンス問題へのアプローチを理解する

• SQLパフォーマンス問題の対処方法を理解する• 定型的なSQLチューニングを理解する

• 非定型的なSQLチューニングを理解する



SQLパフォーマンス問題の理由と原因


SQLパフォーマンス問題の定義

SQLパフォーマンス問題 = 「SQLのパフォーマンス要件を満たせない」状況

要件とは？

• レスポンス要件ある処理に対してどの程度の時間内で結果が返る必要があるか

• スループット要件単位時間毎にどれだけの処理を並行して行う必要があるか

要件が満たせない要因は？

• 無駄な処理本来想定していない処理(不要なソート処理、不要な関数の実行、不要な表結合etc)のこと

• 無駄な待機処理をしたいのに処理できずに不要な時間を要してしまう状態

いわゆるロック待ちとして他の処理が終了するまで待機せざるを得ない場合

6


「無駄な処理」「無駄な待機」

SQLレスポンス時間は次の式で表すことができる

SQLレスポンス時間 CPU時間待機時間＝＋

＝必要なCPU時間＋無駄なCPU時間

＋必要な待機時間＋無駄な待機時間

無駄な待機時間＝待機回数 × 1待機あたりの平均待機時間

7


なぜSQLでパフォーマンス問題は起きるのか?

正しい結果が返ることには気にするだけでなく、

処理の効率化やデータベース内での処理を意識する必要がある

同一の処理内容に対して、複数の記述方法が可能

処理ロジックを意識しないコーディングが可能

データ取得方法などの処理は、データベースに任されている

手続き型言語

Java、C、Perl

問い合わせ言語

SQL

処理の方法を記述

処理の内容を記述

8


チューニング・アプローチ

定型的なチューニング

非定型的なチューニング

アーキテクチャを意識したチューニング

SQLパフォーマンス問題へのアプローチSQL文の記述方法が柔軟でも、パフォーマンス問題を発生させないようにするためには、記述方法にある程度守るべきルールが存在する。

SQLコーディングルールによるSQL文の確認

定型的なチューニング、非定型的なチューニングを実施しても改善しない場合は、Oracleアーキテクチャやアプリケーション・アーキテクチャを意識して考察する必要がある。

SQL文の実行状況、処理状況をアーキテクチャも含めて確認

定型的な解があるわけではない、頭で考える必要のあるSQLチューニング。

SQLがどのように処理しているのかを意識しながら、実行計画の確認



SQLパフォーマンス問題の解決へのアプローチ

定型的なSQLチューニング


定型的なSQLチューニング

問題がおきたら最低限

これだけはチェックする

最低限これだけは守って

コーディングする

「定型的なSQLチューニング」 = 「最低限のSQLコーディング・ルール」

予防チューニング

• 柔軟な言語だからこそ、守るべきルールが存在する

• 大きく4つのカテゴリに分かれる

1. アーキテクチャに伴う性能問題を避けるためのルール

2. 使用方法やノウハウを元に性能問題を避けるためのルール

3. 可読性や管理性を高めるためのルール

4. 運用ポリシーを考慮したルール

11


SQLコーディング・ルール1.アーキテクチャに伴う性能問題を避けるためのルール

指針• WHERE句に条件を指定する場合は、バインド変数を使用する

理由• 一般にOLTP系ではバインド変数を使用しない場合、本来必要でない再解析が多発し、CPU負

荷の上昇、共有プール内の他SQL用の解析結果を追い出すことによるＤＢ全体のスループット

低下を招く可能性が高くなるためです。

注意点• アプリケーションでSQLを生成するような場合には特に注意してください。SQLの内部に値を直

接付け加えるのではなく、(JDBCであれば) setInt, setStgring等を使用し必ずバインド変数を使

用してください。

• なお、このルールは一般にOLTP向けのガイドとなります。DWH系のシステムではバインド変数

を使用せずに値データの異なるSQLを共有させずに、SQLごとに最適な実行計画で処理を行っ

た方が性能がよい場合もあります。

バインド変数の使用


SQLコーディング・ルール2.使用方法やノウハウを元に性能問題を回避するためのルールの例

指針• ビューとビューの結合やビューと表の結合は避ける。このような結合によりデータを取得したい

場合はビュー定義を参照し、ビューの元表を使用してSQL文を記述する。

理由

• ビューを結合に用いると、データを取得するために必要な本来の表以外にも余計な表にアクセ

スする可能性があります。このような状況が生じた場合、直積結合の発生等で、必要以上のデ

ータアクセスが発生し性能問題が発生する可能性があります。

ビューに対する結合の回避


SQLコーディング・ルール3.可読性や管理性を高めるためのルール

指針• SQLに管理用コメント追加する。管理コメントの命名規則を定義し、それに従ってコメントを記載す

る。どのモジュールからどのような目的で発行されたものであるかがわかるような命名規則にしておく必要がある。

理由• 管理用コメントを記載しておくことで、チューニングやデバックの際に原因SQLを発行しているプロ

グラムの特定が容易になります。また、SQLの発行状況などのトレンド把握も容易になります。

注意点• 管理用コメントをあまりにも細かく分類すると、解析情報を共有すべき“同一のSQL”が同一でなく

なってしまう懸念があります。プログラムやSQLを特定するのに必要十分な区分で管理用コメントの付加します。

• 汎用的なSQLがコメントによって、“同一のSQL”でなくなってしまっては、本末転倒です。汎用SQLに対するコメントも命名規則も定義しておく必要があります。

管理用コメントの付与


SQLコーディング・ルール④運用ポリシーをコーディングに反映させるルールの例

指針• SQL内にヒント句の使用を検討する。

理由• アプリケーションロジックからSQLを外部ファイルに記載しておいたものを呼び出す運用を行って

いるので、SQLチューニングの判断によりヒント句を使用して改善を行うことを検討します。

• SQLのパフォーマンス管理は、業務チームが最終的に判断して行ってください。しかし、業務チームの判断のみで、ヒント句を追加することは禁止します。

ヒント句の使用を検討する




非定型的なSQLチューニング

- コストベース・オプティマイザのインプット -



次の2点を理解することが重要

• CBOが何をベースに実行計画を決定するか• 実行計画の妥当性をどのように判断するか

ポイント

• SQL文の実行処理は「実行計画」で定義されるため、CBOが決定する実行計画の良し悪しがSQL文のパフォーマンスに影響します

• SQL文を「適切な実行計画」で実行させることが非定型的なSQLチューニングのポイント


オプティマイザへのインプット情報は妥当か？

自身で実行計画を検討

オプティマイザが生成した実行計画は適切か？

パフォーマンスを確認し妥当であればチューニング完了

インプット情報を修正できるか？

NOYES

NOYES

NOYESNO

STEP１まずはCBOがどのような情報を基に実行計画を決定するかを理解します

STEP2

次にCBOが決定した実行計画が妥当であるかどうかを判断するための指針を理解します

チューニングの進め方



コストベース・オプティマイザ

CBO(コストベースオプティマイザ)

SQLテキスト

パラメータ

オブジェクト構造

データの実態

環境

統計情報

実行計画

レスポンス

• コストベース・オプティマイザ(CBO)はどのような情報をもとに実行計画を決定するか

19


オプティマイザへのインプット情報

ルールに従っていない箇所を修正

コーディング・ルールを遵守しているか確認

SQLテキスト

• SQL文そのもの



同じ表を使用する他のSQLに影響するので慎重に！

索引が不足している場合は索引構成を変更

注意

オブジェクト構造

• アクセスするオブジェクトの情報

ディクショナリ表から対象SQLに関連するオブジェクト情報(表、索引、列定義を使用するビューのテキスト)を収集し、WHERE句条件に適した索引が作成されているかを確認



必要に応じてパラメータ値を変更

オプティマイザに影響を与えるパラメータについて、

V$SYS_OPTIMIZER_ENVビューより環境に適した値に設定されてい

るか確認

インスタンス・レベルで設定値を変更する場合には全てのSQL文に影響を与えるため、十分な注意が必要です。

セッション・レベルで設定値を変更可能なパラメータもあるため状況に応じて使い分けましょう。

注意

初期化パラメータ

• オプティマイザがコストを判断する際の基礎情報



実行計画に大きな影響を与えるため、再収集によりSQLパフォーマンスが悪化する可能性もあります。必ず実行計画や性能の確認を行いましょう。

統計情報を再収集または固定化

必要な統計情報が欠落していないか、取得した統計情報と実データの傾向が乖離していないかどうかを確認

注意

統計情報

• オブジェクトやデータの状態を表す

• コストベース・オプティマイザのコスト計算に直接的に影響するので、デフォルト値を使用せず実データを基に値を収集すること





- 実行計画の妥当性 -


実行計画の読み方

SQL> SELECT d.dname , e.empno , e.ename , e.job

FROM emp e , dept d

WHERE e.deptno = d.deptno;

順序 Execution Plan

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

4 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=392)

3 1 HASH JOIN (Cost=5 Card=14 Bytes=392)

1 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=44)

2 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=238)

• インデントで整形されたツリー構造となっている

• ツリー構造の深いオペレーションから実行される

• 同一のレベルであれば、上に表示されているものから

• 結合方法の次のレベルに結合対象の表が表示される(結合操作ありの場合)

25


索引を利用して参照

表を直接参照

データアクセス方法

全表スキャン（TABLE ACCESS FULL）

索引のレンジスキャン（INDEX RANCE SCAN）

索引の一意スキャン（INDEX UNIQUE SCAN）

索引のフルスキャン（INDEX FULL SCAN）

索引の高速フルスキャン（INDEX FAST FULL SCAN）

索引のスキップスキャン（INDEX SKIP SCAN）

データアクセス方法データアクセス方法の種類

• データアクセス方法は大きく2つあります

26


データアクセス方法表を直接参照

AP サーバープロセス

PGA

DBバッファキャッシュ

HWM


データアクセス

方法

全表スキャン

索引のレンジスキャン

索引の一意スキャン

索引のフルスキャン

索引の高速フルスキャン

索引のスキップスキャン

表を直接参照

ポイント3：必ずHWMまでの読みが発生

ポイント2：複数ブロック読み込みでのI/O回数

ポイント1：キャッシュに残されない場合が多い

• 全表スキャン(TABLE ACCESS FULL)

• HWM(High Water Mark)までの全ブロックを読み込みます

• 表がパーティション化されている場合、一部のツールではTABLE ACCESS

FULLとなっていても表全体にアクセスするとは限らず、特定のパーティションのみにアクセスしている可能性があります

27


データアクセス方法索引を利用して参照

root

B1 B2

L11 L12 L13 L21 L22

INDEX RANGE SCAN



方法

全表スキャン






表を直接参照

• 索引のレンジスキャン

• キー値の範囲でリーフ・ブロックをスキャンして条件に該当する複数エントリを返します

28


root

B1 B2

L11 L12 L13 L21 L22

INDEX UNIQUE SCAN



方法

全表スキャン






表を直接参照


• 索引の一意スキャン

• 条件に該当する1エントリを返します

• UNIQUE索引の列に対して、等価条件を使用している場合のみ出力されるオペレーションです

29


root

B1 B2

L11 L12 L13 L21 L22

INDEX FULL SCAN



方法

全表スキャン






表を直接参照


• 索引のフルスキャン

• リーフブロックをフルスキャンして、条件に該当するエントリを返します

• リンク順にスキャンするため、キー値でソートされた順にエントリを返します

• 返された値がソートされているため、その後のソート処理を省略できる場合があります

30


セグメント・ヘッダ

root

L11

B1

L21

B2

L22

INDEX FAST

FULL SCAN



方法

全表スキャン






表を直接参照


• 索引の高速フルスキャン

• ツリー構造を意識せずに、セグメント・ヘッダから順にブロックをフルスキャンするため、返ってくる値はソートされていません。

• マルチ・ブロック・リードやパラレル実行を行うことができます

31


root

B1 B2

L11 L12 L13 L21 L22

INDEX SKIP SCAN



方法

全表スキャン






表を直接参照


• 索引のスキップスキャン

• 複合索引の第1列目に対する条件指定がなく、2列目以降の列に対して条件指定があった場合に採用され得る可能性があります

• INDEX RANGE SCANと比較すると、効率が悪いオペレーションです

32


セレクティビティとカーディナリティ

• セレクティビティとは

セレクティビティ（選択率）＝条件を適用した結果の行数 / 全体の行数

計算方法は、ヒストグラム（※）の有無により異なります

条件句算出方法

等価条件 COL = ‘X’ 1 / NDV

Number of Distinct Value （列に含まれる値の種類）

データが一様に分布していると仮定するので等価条件のセレクティビティは1/NDVとして計算されます。

非等価条件 COL > ‘X’ （HIVAL-X+1)/(HIVAL-LOWVAL+1)

算出方法の例（ヒストグラムが存在しない場合）

SQLの条件（条件の組み合わせ）にヒットする行ソースの割合

33



• セレクティビティの計算例

顧客ID 顧客名地域性別

000001 田中隆二関東男性

000002 井上みき東北女性

000003 工藤朊子関東女性

000004 本井美由紀九州女性

000005 日高千尋関東女性

000006 齋藤美佳関東女性

000007 若木泉水関東女性

000008 佐々木千枝東北女性

000009 立花真由関西女性

000010 宮原希美関東女性

例） SELECT * FROM customer WHERE 性別=‘男性’

性別列は、「男性」「女性」の2種類＝NDVは2

SQLのセレクティビティは1/2として計算されます

実際のデータでは男性の比率が10%だったとしても

50%の割合で条件にヒットすると予測を立てて実行計画を算出します

34



• カーディナリティとは

例）SELECT * FROM customer WHERE 性別＝‘男性’

1万行の表セレクティビティは1/2

1万×1/2＝5000行

行ソースから戻される行の予測数

• カーディナリティ＝表の行数 × セレクティビティ





000004 本井美由紀九州女性

000005 日高千尋関東女性

・

・

・

・

・

・

・

・

010000 木村あかね関東女性

35


【セレクティビティ】

• カーディナリティの生成に必要

• セレクティビティが低い場合

• 条件列に索引が定義されていると索引を使用してデータを絞り込む効果が高いため索引が使用されやすくなる

例）SELECT * FROM customer WHERE 性別=‘男性’ AND 顧客ID=‘000001’;

前提：顧客ID=‘000001’ のセレクティビティ： 0.0001性別=‘男性’のセレクティビティ： 0.5

顧客ID=1 の条件を使用して先にデータを絞り込むと効率的

顧客ID列に索引が定義されている場合には索引スキャンが選ばれる可能性が高い





・・・・

010000 木村あかね関東女性

【カーディナリティ】

• 結合やソートオペレーションの効率性を検討する際に使用

データアクセス方法判断例

36


表結合方法表結合方法の種類

表結合方法

ネステッドループ結合

ハッシュ結合

ソートマージ結合

直積結合

• 表結合方法は4つ

37


表結合順序結合順のパターン

• 同時に3つ以上の表を結合することはできない

• 結合順序のパターンは表の数に依存する

表結合順序 EMP表から？DEPT表から？それとも・・

38


表結合方法ネステッドループ結合

• 動作概要：• 最初に外部表にアクセスする

• 外部表から戻された行数分、内部表にアクセスして条件に合致するデータを戻す

• ポイント：

1. カーディナリティが小さい表を外部表とする• 外部表のカーディナリティが内部表の参照回

数となる

• 実レコード件数ではなくカーディナリティが小さい表を外部表とする

2. 内部表のアクセス効率を上げる• 内部表の結合列に索引があると効率的

• 内部表の結合列に索引がないと、内部表の全件検索を繰り返すため効率が悪くなる場合がある

while {外部表から絞込条件に合致する1行を取得する1行もなければループから抜けるwhile {

内部表から絞込条件、結合条件に合致する1行を取得する取得した行を結果として返す1行もなければループから抜ける

}}

外部ループ

内部ループ

DEPTNO DNAME

10 OracleDirect

20 RESEARCH

30 Support

DEPTNO DNAME

20 RESEARCH

EMP

NO

DEPT

NO

1 10

2 20

3 20

4 30

5 30

外部表内部表

表結合方法


ハッシュ結合


直積結合

39


表結合方法ネステッドループ結合

• 動作のステップ：1. DEPT表を外部表、 EMP表が内部表となる

2. DEPT表をフルスキャンし、条件（DNAME=‘RESEARCH’ ）に合致する行をフェッチする

3. 2で取得する行数分4～5を繰り返す（ポイント1）

4. EMP表の索引EMP_IX2を索引レンジスキャンし（ポイント2）、フェッチした行と結合する行を特定する

5. EMP表にアクセスして特定した行を取得する

• 実行計画例：

表結合方法


ハッシュ結合


直積結合

40


表結合方法ハッシュ結合

• 動作概要：• 表1から抽出条件に合致する結果セットを返す

• 結果セットの結合キーをもとにハッシュ表をPGA内に作成する

• 表2から絞込条件に合致する結果セットを返す

• 結果セットの結合キーを順にハッシュし、ハッシュ表と照らし合わせて結合条件に該当する行を特定する

• ポイント：1. カーディナリティが小さい方を先に処理する

• PGA上に作成されるハッシュ表が小さくなるため、結合処理が効率的になる

2. 一時表領域へのI/Oが発生する可能性がある• ハッシュ表がメモリ内に収まらない場合、一時表領域を使

用するため、ディスクI/Oの発生により性能が劣化しやすい

• PGA_AGGREGATE_TARGET もしくはHASH_AREA_SIZEを考慮する

3. 等価条件でのみ使われる• 結合条件が等価結合でない（範囲指定）場合、ハッシュ結

合は使われない

4. アクセス方法は基本的にフルスキャン• ハッシュ結合の場合は、基本的に表のフルスキャンもしく

は索引のフルスキャンとなります

HASH表

結合キー結合キー

PGA

HASH関数 HASH関数

表結合方法


ハッシュ結合


直積結合

TEMP

ファイル

表1 表2

41


表結合方法ハッシュ結合


• 動作のステップ：1. DEPT表をフルスキャンし、絞込条件（DNAME=‘RESEARCH’ ）に合致するデータを取り出

す（ポイント4）

2. 結合キーの値をハッシュし、ハッシュ表の対応するパーティションに値を格納する

3. EMP表をフルスキャンする（ポイント4）

4. 結合キーの値をハッシュし、該当するパーティションに行があるか確認し、行がある場合には値を適切なパーティションに格納する

5. 結合した結果を返す

表結合方法


ハッシュ結合


直積結合

42


表結合方法ソートマージ結合

• 動作：• 表1の結果セットを結合列でPGA内でソートする

• 表2の結果セットを結合列でPGA内でソートする

• ソート処理はシリアルに実行されます

• ソート結果をPGA内でマージして結果を返します

• ポイント：1. 一時表領域へのI/Oが発生する可能性が

ある• ソート処理がメモリ内に収まらない場合、一時表領

域を使用するため、ディスクI/Oの発生により性能が劣化しやすい

• PGA_AGGREGATE_TARGET もしくはSORT_AREA_SIZEを考慮する

2. 表1は一定条件でソート処理が回避できる（表2のソートは回避できない）• 表1は、結合列に索引が定義されNOT NULL制約

が存在する場合、索引フルスキャンを実行することでソート処理を回避できる

• 表2は、ソートを行いながら結合を進めるため、ソート処理は回避できない

PGA

ソート

表1 表2

ソート

表結合方法


ハッシュ結合


直積結合

TEMP

ファイル

43


表結合方法ソートマージ結合

• 動作のステップ：1. DEPT表をフルスキャンする

2. DEPT表の結果セットを結合列（DEPTNO）でソートする

3. EMP表をフルスキャンする

4. EMP表の結果セットを結合列（DEPTNO）でソートしながら2の結果と結合する


表結合方法


ハッシュ結合


直積結合

44


表結合方法直積結合

• 動作概要：

• 結果セット1の全行と結果セット2の全行を直積（掛け算）する

• ポイント：

• 2つの行ソースに対する結合条件がない場合に直積が選択される

• 結合条件にもれがないか確認する

結果セット1

直積演算

n件 m件

結果セット2

アクセス件数 ≒ n件 × m件

表結合方法


ハッシュ結合


直積結合

※スター・スキーマ構造となっているDWHシステムでは有効な場合がある

45


表結合方法直積結合

• 動作のステップ：

1. DEPT表をフルスキャンする

2. EMP表をフルスキャンする

3. 2の結果セットをソートする

4. 2と4の結果をマージ


表結合方法


ハッシュ結合


直積結合

46


実行計画を判断するポイント

表結合方法

表結合順序


表を直接参照

データアクセス方法

全表スキャン（TABLE ACCESS FULL）

索引のレンジスキャン（INDEX RANCE SCAN）

索引の一意スキャン（INDEX UNIQUE SCAN）

索引のフルスキャン（INDEX FULL SCAN）

索引の高速フルスキャン（INDEX FAST FULL SCAN）


索引のスキップスキャン（INDEX SKIP SCAN）

ハッシュ結合


直積結合

判断のポイント分岐の種類

EMP表から？DEPT表から？それとも・・

47



まとめ


• それぞれの特性と考慮点から「検討」「検証」「判断」すること• 適切な索引が作成されているか？

• 最適な索引が使用されているか？

• WHERE句条件が適切か（コーディング、条件指定）？

• 表のフルスキャンの方が効率的か（索引を使用しても10%以上の表データアクセスなど）？

• ネステッドループ結合で内部表のフルスキャンが発生していないか？

• カーディナリティの小さい表から結合されているか？

最適化のアプローチ

SQLによるデータの取得を最小のブロックアクセスで行うことが原則です。しかしながら、SQL単体を最適化（レスポンス改善）するアプローチだけでは不十分です。

SQL単体の最適化


• 実行計画の違いによる必要リソース（CPU,メモリなど）のバランスで判断• サーバ上でOracle以外のアプリケーションが動作し物理メモリが圧迫されている

→ハッシュ結合やソートマージ結合が同時に実行されて物理メモリが圧迫されないか

• CPU使用率が高い

→無駄なソート処理がないか、ネステッドループ結合によりCPU負荷が高くなっていないか

最適化のアプローチ

SQLパフォーマンス問題を解決するにはSQL単体の最適化だけではなく、システム全体の最適化をふまえて判断することが重要である

システム全体の最適化



Appendix


表結合順序現状を分析する手順

① 表の結合関係をクリアにする• SQL文、索引定義情報、統計情報

• WHERE句に含まれている列

• 結合されている列同士の結合関係

• 推移律

• 選択率とカーディナリティ

② 結合順序のスタートポイントを見つける• どれくらい絞れるか

• 絞り込める表（スタートポイント）はどれか

52


[SQL文]SELECT count(*)

FROM tab1 t1 , tab2 t2 , tab3 t3 , tab4 t4 , tab5 t5

WHERE t1.id = t2.idAND t1.id = t3.idAND t2.class = t5.classAND t3.class = t4.classAND t4.flag = 'Y'AND t5.num = TO_NUMBER(:b1)AND t4.code = TO_NUMBER(:b2)AND t1.start_date <= (TO_DATE(:b3, 'yyyymmdd') + 1)AND t1.end_date > TO_DATE(:b3, 'yyyymmdd')

[索引の定義]TABLE_NAME INDEX_NAME COLUMN_NAME KIND

----------- ----------- ------------------ ---------------TAB1 TAB1_P1 ID Primary Key

TAB1_U1 ID, END_DATE Unique Key

TAB2 TAB2_PK ID, CLASS, ZONE Primary KeyTAB2_I1 CLASS, IDTAB2_I2 ID

TAB3 TAB3_P1 ID, CLASS, DEPTH Primary KeyTAB3_I1 CLASS, ID

TAB4 TAB4_PK CODE Primary KeyTAB4_I1 CLASS, CODE

TAB5 TAB5_PK CLASS Primary KeyTAB5_U1 DATA, CLASS, NUM Unique KeyTAB5_I1 NUM, DATA

• SQL文、索引定義情報

表結合順序①表の結合関係をクリアにする

53


• 統計情報

[SQL文]SELECT count(*)

FROM tab1 t1 , tab2 t2 , tab3 t3 , tab4 t4 , tab5 t5

WHERE t1.id = t2.idAND t1.id = t3.idAND t2.class = t5.classAND t3.class = t4.classAND t4.flag = 'Y'AND t5.num = TO_NUMBER(:b1)AND t4.code = TO_NUMBER(:b2)AND t1.start_date <= (TO_DATE(:b3, 'yyyymmdd') + 1)AND t1.end_date > TO_DATE(:b3, 'yyyymmdd')

[表の統計情報]OWNER TABLE_NAME COLUMN_NAME NUM_ROWS NUM_DISTINCT

------ ----------- ------------ --------- ------------SCOTT TAB1 ID 275 275SCOTT TAB1 START_DATE 275 5SCOTT TAB1 END_DATE 275 1

SCOTT TAB2 ID 282 273SCOTT TAB2 CLASS 282 17

SCOTT TAB3 ID 17442 274SCOTT TAB3 CLASS 17442 8210

SCOTT TAB4 CODE 834030 834030SCOTT TAB4 FLAG 834030 1SCOTT TAB4 CLASS 834030 834030

SCOTT TAB5 NUM 133 132SCOTT TAB5 CLASS 133 133


54


TAB5

NUM = :b1

CLASSTAB2

ID

CLASS

TAB1

ID

START_DATE <= :b3+1

END_DATE > :b3

TAB3

ID

CLASSTAB4

CODE = :b2

FLAG = ‘Y’

CLASS

列名索引の使用できる列

• 登場するオブジェクトを抽出し、WHERE句の列を書き出す


55


TAB5

NUM = :b1

CLASSTAB2

ID

CLASS

TAB1

ID

START_DATE <= :b3+1

END_DATE > :b3

TAB3

ID

CLASSTAB4

CODE = :b2

FLAG = ‘Y’

CLASS



結合されている列

• 結合列を結ぶ


56


TAB5

NUM = :b1

CLASSTAB2

ID

CLASS

TAB1

ID

START_DATE <= :b3+1

END_DATE > :b3

TAB3

ID

CLASSTAB4

CODE = :b2

FLAG = ‘Y’

CLASS




• 結合列を結んでみる• A=BかつB=Cであれば、A=Cである論理条件を見つける

（推移律）


57


TAB5

NUM = :b1

CLASSTAB2

ID

CLASS

TAB1

ID

START_DATE <= :b3+1

END_DATE > :b3

TAB3

ID

CLASSTAB4

CODE = :b2

FLAG = ‘Y’

CLASS





（推移律）

C:133/132=1

S:1/132

C:133/133=1

S:1/133

C:282/17=16.5

S:1/17

C:282/273=1

S:1/273

C:275/275=1

S:1/275

C:275/1=275

S:1/1

C:275/5=55

S:1/5C:17442/274=63.5

S:1/274

C:17442/8210=2

S:1/8210

C:83万/83万=1

S:1/83万

C:83万/83万=1

S:1/83万C:83万/1=83万S:1/1

C:カーディナリS:選択率

• カーディナリティとセレクティビティを整理する


58


表結合順序現状を分析する手順

① 表の結合関係をクリアにする• SQL文、索引定義情報、統計情報

• WHERE句に含まれている列

• 結合されている列同士の結合関係

• 推移律

• 選択率とカーディナリティ

② 結合順序のスタートポイントを見つける• どれくらい絞れるか

• 絞り込める表（スタートポイント）はどれか

59


TAB5

NUM = :b1

CLASSTAB2

ID

CLASS

TAB1

ID

START_DATE <= :b3+1

END_DATE > :b3

TAB3

ID

CLASSTAB4

CODE = :b2

FLAG = ‘Y’

CLASS





（推移律）

C:133/132=1

S:1/132

C:133/133=1

S:1/133

C:282/17=16.5

S:1/17

C:282/273=1

S:1/273

C:275/275=1

S:1/275

C:275/1=275

S:1/1

C:275/5=55

S:1/5C:17442/274=63.5

S:1/274

C:17442/8210=2

S:1/8210

C:83万/83万=1

S:1/83万

C:83万/83万=1

S:1/83万C:83万/1=83万S:1/1


表結合順序②結合順序のスタートポイントを見つける

• どれくらい行が絞れるか考える

絞込みで0～275件

絞込みで1件

絞込みで1件

絞込めないので17442件


60


TAB5

NUM = :b1

CLASSTAB2

ID

CLASS

TAB1

ID

START_DATE <= :b3+1

END_DATE > :b3

TAB3

ID

CLASSTAB4

CODE = :b2

FLAG = ‘Y’

CLASS





（推移律）

C:133/132=1

S:1/132

C:133/133=1

S:1/133

C:282/17=16.5

S:1/17

C:282/273=1

S:1/273

C:275/275=1

S:1/275

C:275/1=275

S:1/1

C:275/5=55

S:1/5C:17442/274=63.5

S:1/274

C:17442/8210=2

S:1/8210

C:83万/83万=1

S:1/83万

C:83万/83万=1

S:1/83万C:83万/1=83万S:1/1


表結合順序②結合順序のスタートポイントを見つける

• どれくらい絞れるか

絞込みで0～275件

絞込みで1件

絞込みで1件



• 絞り込める表（スタートポイント）の候補を出す

Start

Point

Start

Point

Start

Point

61


現在の実行計画を把握する1つ目の結合

Execution Plan

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

SELECT STATEMENT GOAL: CHOOSE

SORT (AGGREGATE)

NESTED LOOPS

┣NESTED LOOPS

┃┣NESTED LOOPS

┃┃ ┣ NESTED LOOPS

┃┃ ┃┣TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TAB1'

┃┃ ┃┗INDEX GOAL: ANALYZED (RANGE SCAN) OF 'TAB2_PK' (UNIQUE)

┃┃ ┗INDEX GOAL: ANALYZED (FULL SCAN) OF 'TAB3_I1' (NON-UNIQUE)

┃┗ TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAB4'

┃ INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'TAB4_PK' (UNIQUE)

┗TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TAB5'

INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'TAB5_PK' (UNIQUE)

外部表になる行ソース内部表になる行ソースフォーカスする処理

62


TAB2

ID

CLASS

TAB1

ID

START_DATE <= :b3+1

END_DATE > :b3

アクセス1回⇒絞込でX件ヒット

NLアクセス X回⇒X*1件ヒット

①Start

Point


63


Execution Plan

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


SORT (AGGREGATE)

NESTED LOOPS

┣NESTED LOOPS

┃┣NESTED LOOPS











64


TAB2

ID

CLASS

TAB1

ID

START_DATE <= :b3+1

END_DATE > :b3



①

TAB3

ID

CLASS

NLアクセス X回⇒X*63.5件ヒット

②

Start

Point


65




Execution Plan

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


SORT (AGGREGATE)

NESTED LOOPS

┣NESTED LOOPS

┃┣NESTED LOOPS









66


TAB2

ID

CLASS

TAB1

ID

START_DATE <= :b3+1

END_DATE > :b3



①

TAB3

ID

CLASS


②

TAB4

CODE = :b2

FLAG = ‘Y’

CLASS

③

NL アクセスX*63.5回⇒絞込で2件ヒット

Start

Point


67


Execution Plan

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


SORT (AGGREGATE)

NESTED LOOPS

┣NESTED LOOPS

┃┣NESTED LOOPS











68


TAB2

ID

CLASS

TAB1

ID

START_DATE <= :b3+1

END_DATE > :b3



①

TAB3

ID

CLASS


②

TAB4

CODE = :b2

FLAG = ‘Y’

CLASS

③


TAB5

NUM = :b1

CLASS

④

NL アクセス2回⇒絞込で1件ヒット

Start

Point


69


TAB2

ID

CLASS

TAB1

ID

START_DATE <= :b3+1

END_DATE > :b3

現在の実行計画を分析する



①

TAB3

ID

CLASS


②

TAB4

CODE = :b2

FLAG = ‘Y’

CLASS

③


TAB5

NUM = :b1

CLASS

④


Start

Point

表アクセス回数カーディナリティ

TAB1 1回 X行

TAB2 X回 X×1行

TAB3 X回 X×63.5行

TAB4 X×63.5回 2行

TAB5 2回 1行

70


現在の実行計画を分析する

TAB1

フルスキャンTAB2

索引レンジスキャン

ネステッド TAB3

索引フルスキャン


索引ユニーク


索引ユニーク

ネステッド


TAB1 1回 X行

TAB2 X回 X×1行

TAB3 X回 X×63.5行

TAB4 X×63.5回 2行

TAB5 2回 1行

①

②

③

④

71


TAB5

NUM = :b1

CLASS TAB2

ID

CLASS

TAB1

ID

START_DATE <= :b3+1

END_DATE > :b3

TAB3

ID

CLASSTAB4

CODE = :b2

FLAG = ‘Y’

CLASS

NL アクセス2回⇒2*1件ヒット

NLアクセス2回⇒2*1件ヒット

NLアクセス 1回⇒1*2件ヒット

アクセス1回⇒絞込で1件ヒット


①

②

③

④

Start

Point

実行計画を検討する別のチューニング案


TAB4 1回 1行

TAB3 1回 2行

TAB1 2回 2行

TAB2 2回 2行

TAB5 2回 1行

72


TAB4

索引ユニークTAB3

索引フルスキャン


フルスキャン


索引レンジスキャン


索引ユニーク

ネステッド


TAB4 1回 1行

TAB3 1回 2行

TAB1 2回 2行

TAB2 2回 2行

TAB5 2回 1行

実行計画を検討する別のチューニング案

①

②

③

④

73


OTN×ダイセミでスキルアップ!!

※OTN掲示版は、基本的にOracleユーザー有志からの回答となるため100%回答があるとは限りません。ただ、過去の履歴を見ると、質問の大多数に関してなんらかの回答が書き込まれております。

Oracle Technology Network(OTN)を御活用下さい。

・一般的な技術問題解決方法などを知りたい！・セミナ資料など技術コンテンツがほしい！

一般的技術問題解決にはOTN掲示版の

「データベース一般」をご活用ください

http://otn.oracle.co.jp/forum/index.jspa?categoryID=2

過去のセミナ資料、動画コンテンツはOTNの

「OTNセミナーオンデマンドコンテンツ」へ

http://www.oracle.com/technology/global/jp/ondemand/otn-seminar/index.html

※ダイセミ事務局にダイセミ資料を請求頂いても、お受けできない可能性がございますので予めご了承ください。ダイセミ資料はOTNコンテンツオンデマンドか、セミナ実施時間内にダウンロード頂くようお願い致します。

http://otn.oracle.co.jp/forum/index.jspa?categoryID=2


OTNセミナーオンデマンドコンテンツダイセミで実施された技術コンテンツを動画で配信中!!

ダイセミのライブ感はそのままに、お好きな時間で受講頂けます。

※掲載のコンテンツ内容は予告なく変更になる可能性があります。期間限定での配信コンテンツも含まれております。お早めにダウンロード頂くことをお勧めいたします。

OTN オンデマンド

最新情報つぶやき中

oracletechnetjp

・人気コンテンツは？

・お勧め情報

・公開予告など


Oracle エンジニアのための技術情報サイト

オラクルエンジニア通信http://blogs.oracle.com/oracle4engineer/

• 技術資料

• ダイセミの過去資料や製品ホワイトペーパー、スキルアップ資料などを多様な方法で検索できます

• キーワード検索、レベル別、カテゴリ別、製品・機能別

• コラム

• オラクル製品に関する技術コラムを毎週お届けします

• 決してニッチではなく、誰もが明日から使える技術の「あ、そうだったんだ！」をお届けします先月はこんな資料が人気でした

Oracle Database 11gR2 RAC インストレーション・ガイド ASM 版 Microsoft Windows x86-64

Oracle Database 11gR2 旧バージョンからのアップグレード

オラクルエンジニア通信

最新情報つぶやき中

oracletechnetjp

http://blogs.oracle.com/oracle4engineer/


オラクルクルクルキャンペーン

77

Enterprise Editionはここが違う!!

• 圧倒的なパフォーマンス!

• データベース管理がカンタン!

• データベースを止めなくていい!

• もちろん障害対策も万全!

Oracle Databaseのライセンス価格を大幅に抑えて

ご導入いただけます

詳しくはコチラ

http://www.oracle.co.jp/campaign/kurukuru/index.html

あのOracle Database Enterprise Editionが超おトク!!

お問い合わせフォームhttp://www.oracle.co.jp/inq_pl/INQUIRY/quest?rid=28

多くのお客様でサーバー使用期間とされる

5年間にライセンス期間を限定

• 期間途中で永久ライセンスへ差額移行

• 5年後に新規ライセンスを購入し継続利用

• 5年後に新システムへデータを移行

2010年11月30日まで


http://www.oracle.co.jp/inq_pl/INQUIRY/quest?rid=28

Oracle Direct 検索

あなたにいちばん近いオラクル

Oracle Directまずはお問合せください

Web問い合わせフォームフリーダイヤル

専用お問い合わせフォームにてご相談内容を承ります。

※フォームの入力には、Oracle Direct Seminar申込時と同じログインが必要となります。

※こちらから詳細確認のお電話を差し上げる場合がありますので、ご登録されている連絡先が最新のものになっているか、ご確認下さい。

0120－155－096

※月曜~金曜 9:00~12:00、13:00~18:00

（祝日および年末年始除く）

システムの検討・構築から運用まで、ITプロジェクト全般の相談窓口としてご支援いたします。

システム構成やライセンス/購入方法などお気軽にお問い合わせ下さい。

http://www.oracle.co.jp/inq_pl/INQUIRY/quest?rid=28

Documents

Oracle Direct SeminarプログラムやSQLを特定するのに必要十分な区分で管理用コメント の付加します。• 汎用的なSQLがコメントによって、“同一のSQL”でなくなってしまっては、本末転倒です。汎用

Oracle Direct SeminarプログラムやSQLを特定するのに必要十分な区分で管理用コメントの付加します。• 汎用的なSQLがコメントによって、“同一のSQL”でなくなってしまっては、本末転倒です。汎用