Slide 1

実践パフォーマンスチューニング

オプティマイザ活用編

日本オラクル株式会社

Agenda

•

オプティマイザとは

•

コストベース・オプティマイザでの運用管理

・SQL Serverからの移行アセスメント

・MySQLからの移行相談

・PostgreSQLからの移行相談

・Accessからの移行アセスメント

・Oracle Database バージョンアップ支援

・Oracle Developer/2000 Webアップグレード相談

・パフォーマンス・クリニック

・Oracle Database 構成相談

・Oracle Database 高可用性診断

・システム連携アセスメント

・システムセキュリティ診断

・簡易業務診断

・メインフレーム資産活用

SQL の実行計画とは?

•

SQL を実行するには、多数のステップが必要

•

データベースからデータを物理的に取り出すステップ

•

ユーザーが発行する文に返すデータ行の準備ステップ

•

『実行計画』：

文を実行するためにOracle が使用する

ステップの組み合せ

Oracle Database

取り出し

メモリ

ディスク

結果

実行計画の例

•

従業員表(EMP 表)と部門表(DEPT 表)から、各部門ごと

の従業員リストを作成するSQL の実行計画

SQL> set autotrace on

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

from emp e,dept d

where e.deptno=d.deptno;

Execution Plan

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

1 0

HASH JOIN

(Cost=5 Card=14 Bytes=392)

2 1

TABLE ACCESS (FULL) OF 'DEPT'

(Cost=2 Card=4 Bytes=44)

3 1

TABLE ACCESS (FULL) OF 'EMP'

(Cost=2 Card=14 Bytes=238)

結合方法

同じ表と索引でも、データの状況（返すべきデータが表に存在する割合など）

によっては、全表走査/索引走査 した方が速い

索引Key1

.

.

.

Row1

Row2

Row3

.

.

.

Row4

索引Key2

索引Key3

索引Key4

hit

hit

hit

※索引走査はSingle Block Read であり、1Block ずつの読み込み処理

全表走査はMulti Block Read であり、db_file_multiblock_read_count パラメータ個ずつの一括読み込み

索引走査 (INDEX SCAN)

全表走査 (FULL SCAN)

パターンA

パターンB

索引

表

データの状況により最適な実行計画は違う

hit

Row1

Row2

Row3

.

.

.

Row4

表

全表走査

で実行した方が

速い時もある

•

問合せの結果を生成する最も効率的な方法(物理的な

アクセス手順)を決定し実行計画を作成

•

2種類のオプティマイザ

－ 索引を利用するか

－ 全表スキャンを実行するか

－ どの結合方法を選択するか

－ どの順番で結合するか etc.

オプティマイザの役割

RBO：使用可能なアクセスパスを順序づける

ランキングに基づいて実行計画を作成

CBO：統計情報に基づきコストを見積もり、

最もコストの低い実行計画を作成

パス1：(索引検索)IND_ENAMEを使用した単一列索引検索

パス2：(索引検索)IND_SALを使用した範囲検索

パス3：(全表スキャン)索引を使用しないアクセスパス

Emp表へのすべてのアクセスパスの洗い出し

アクセスパスのランク付け

SELECT empno FROM scott.emp

WHERE ename = 'BLAKE' AND sal > 2000;

EMP表の索引名

索引種類

対象列

PK_EMP

主キー

empno

IND_ENAME

非一意索引

ename

IND_SAL

非一意索引

sal

例)

RBOのルールのアクセスパス選択動作例

パス1：IND_ENAMEを使用した単一列索引検索

<ランク9>

パス2：IND_SALを使用した範囲検索

<ランク11>

パス3：全表スキャン

<ランク15>

アクセスパスのランク付け

IND_ENAMEを使用した単一列索引検索の採用

一番ランクの高い

<ランク9>

を採用

実行計画の決定

RBOのルールのアクセスパス

選択動作例（2）

1 ROWIDによる単一行

2 クラスタ結合による単一行

3 一意／主キーをもつハッシュ・

クラスタ・キーによる単一行

4 一意／主キーによる単一行

5 クラスタ結合

6 ハッシュ・クラスタ・キー

7 索引付きのクラスタ・キー

8 複合索引

9 単一列索引

10 索引列の境界付きの範囲検索

11 索引列の境界なしの範囲検索

12 ソート／マージ結合

13 索引付きの列のMAXまたはMIN

14 索引付きの列のORDER BY

15 全表スキャン

•

SQL文の内容(構文)だけで、実行計画が決まる。データの

内容は見ていない

•

OLTPタイプの処理を最適化するように設計されている

•

データの中身により、RBOよりも高速なパスが存在する

•

表の数が多くなると、最適なSQLの作成コストが増加

•

直感的でアドホックな検索は向かない

RBOの問題点

検索対象が多い場合も、インデックススキャンをしてしまう

索引Key1

.

.

.

Row1

Row2

Row3

.

.

.

Row4

索引Key2

索引Key3

索引Key4

Row1

Row2

Row3

.

.

.

Row4

※索引走査はSingle Block Readであり、1Blockずつの読み込み処理

全表走査はMulti Block Readであり、db_file_multiblock_read_countパラメータ個ずつの一括読み込み

索引走査 (INDEX SCAN)

全表走査 (FULL SCAN)

ランク9

ランク15

索引

表

表

本来は全表走査

で実行すべき！

コストベース・オプティマイザのメリット

データの変動

_{データの変動}

ルールベース・オプティマイザ

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

初期

性能

レスポンス

初期

性能

レスポンス

データの変動

に伴う性能劣化

性能劣化が顕著になり

SQLチューニングが必要

実行計画A

実行計画B

データの変動

に伴う性能劣化

データの変動に伴い、

性能が劣化する可能性が存在する

データの変動を考慮して、

最適な実行計画が使用されるよう

実行計画が自動で変化する

2つのオプティマイザ比較

ルールベース・

オプティマイザ (RBO)

コストベース・

オプティマイザ (CBO)

使用可能なアクセスパスを順

序づけるランキングに基づい

て実行計画を作成

統計情報に基づきコストを見

積もり、

最もコストの低い

実行

計画を作成

概要

• 新機能を使える

• OLTP、DSS 共に有効

• データの偏りや量に基づい

て最適な実行計画を作成

特徴

備考

• 新機能には対応しない

• OLTP 向き

• データの偏りや量は無視し

て実行計画を作成

• Oracle10g 以降は

サポートされない

• 統計情報の取得が必要

Agenda

•

オプティマイザとは

•

コストベース・オプティマイザでの運用管理

・SQL Serverからの移行アセスメント

・MySQLからの移行相談

・PostgreSQLからの移行相談

・Accessからの移行アセスメント

・Oracle Database バージョンアップ支援

・Oracle Developer/2000 Webアップグレード相談

・パフォーマンス・クリニック

・Oracle Database 構成相談

・Oracle Database 高可用性診断

・システム連携アセスメント

・システムセキュリティ診断

・簡易業務診断

・メインフレーム資産活用

無償技術サービスOracle

Direct

Concierge

コストベース・オプティマイザと統計情報

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

統計情報

と

初期化パラメータ

の

情報を利用してアクセスコストを見積もり、実行計画を作成する

初期化パラメータ：

DB_FILE_MULTIBLOCK_READ_COUNT など

統計情報：表の行数、索引の大きさ、など

統計情報を適切に収集することが重要

• DBMS_STATSパッケージ

を利用（Oracle 8.1以降）

例) 表ごとの統計を収集

例) スキーマ内のすべてのオブジェクトの統計を収集

例)データベース内のすべてのオブジェクトの統計を収集

•

ANALYZEコマンド

全行の統計情報を取得

ANALYZE TABLE 表名COMPUTE STATISTICS;

EXECUTE DBMS_STATS.GATHER_

TABLE

_STATS（‘SCOTT’,’EMP’）;

EXECUTE DBMS_STATS.GATHER_

SCHEMA

_STATS(‘SCOTT’);

EXECUTE DBMS_STATS.GATHER_

DATABASE

_STATS();

コストベース・オプティマイザの注意点

•

原因

→統計情報が適切に取得されていない

•

データが大幅に更新され、取得した

統計情報が古くなっている

•

適切な精度(サンプルサイズ)で

収集されていない

etc

統計収集後、SQL 実行計画が変化し、性能が劣化した！

データの変動

初期

性能

レスポンス

実行計画A

実行計画B

データの変動

に伴う性能劣化

②

性能劣化

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

•

Oracle 10g からは、オプティマイザが適切な実行計画を

作成するのに必要な

統計情報を自動で取得

•

初期化パラメータstatistics_level がTYPICAL(デフォルト)または、

ALL

•

統計情報は、平日の夜間と週末に自動収集

•

DBMS_STATS パッケージにおいてoptionsに‘GATHER AUTO’

を指定するのと同様

・統計情報をまだ収集していないオブジェクト

・10% 以上データが更新されたオブジェクト

11gからの自動オプティマイザ統計収集

自動メンテナンスタスクが対応するデフォルトのウィンドウ：

MONDAY_WINDOW から SUNDAY_WINDOWまで

－月曜日～金曜日 それぞれの日に22時から翌2時までウィンドウがオープン

－土曜日・日曜日はAM6時から20時間（翌2時まで）オープン

自動メンテナンスタスク

－GATHER_STATS_JOBの内容が変更

－自動統計収集はデフォルトで有効。

(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; /

10gとちょっと変わ

りました

10gとちょっと

変わりました

自動オプティマイザ統計収集の設定変更

「サーバー」タブ > Oracle Scheduler 「自動化メンテナンス・タスク」 > 構成

統計の履歴の保存期間

サンプルサイズ

何%統計情報が失効したら、再

度統計情報を収集するか

統計の収集タイミングの変更

•

CBOで運用する限り避けられないリスク

統計収集後、SQL実行計画が変化し、性能が劣化した！

•

統計収集対象に「漏れ」がないこと

•

列に対するヒストグラムも含め、

必要なオブジェクトが必要なレベル

で統計収集されていること

•

適切な精度(サンプルサイズ)で

収集されていること

•

データの変動に応じた適切な頻度

で再収集されていること

•

システム状況の変動に応じて

システム統計が収集されていること

CBOで運用する上で最低限注意すべき事項

CBOで運用する際のリスク

統計情報収集後に一部のSQL性能が劣化したことが判明

•

現在の統計値をエクスポートして退避

•

適切な性能が出ていた過去の時点の統計情報をインポート

•

エクスポートして退避した統計値を、テスト環境にインポートして解析

•

問題分析中は新たに統計情報を収集しないようにする

•

テスト環境での分析結果を受け、必要な対策を行った上で統計収集を

再開

統計収集後、SQL実行計画が変化し、性能が劣化する現象は

起こり得ることとして運用を設計しておく

<参考>

Oracle10gからは、デフォルトで統計履歴が自動保存され、

過去31日前までのどの時点にでも戻すことが可能

CBOで運用する際のリスク回避方法：１

変化する前の統計情報に戻す

自動オプティマイザ統計収集のリストア

「サーバー」タブ > 問合せオプティマイザ 「オプティマイザ統計の管理」 > オプティマイザ統計のリストア

・オブジェクトの統計情報を新しく取得すると、前の統計情報は後にリストアする

ケースに備えて自動的に SYSAUX 表領域に保存される

・Oracle Enterprise Manager から簡単にリストア可能

・デフォルトで31日間分保存

•

実行頻度が極めて高い

•

そのSQLの遅延がアプリケーション全体の性能に重大な影響を与える

•

運用中の実行計画変動のリスクを限りなくゼロにしたい

•

開発環境での実行計画を本番環境で確実に再現する必要がある

•

Oracleのバージョンアップを考えているが、バージョンアップ後も

現在の実行計画を確実に保持したい

統計情報の変化に影響を受けないよう、実行計画を固定する

ヒントを使用

ストアド・アウトラインを使用

（11gでは非推奨）

CBOで運用する際のリスク回避方法：２

CBO で運用する際のリスク回避方法3

実行計画が変わった場合は、検証をしてから適応させる

SQL計画管理：SQL Plan Management(SPM)

SQL Management Base

実行（運用時）

実行（システム変更後）

Plan History

Plan Baseline

プランB

プランA

プランA

常にPlan Baselineにある

実行計画が使用される

プランA

SYSAUX表領域

プラン変更を検証し、パ

フォーマンスが低下する

場合は防止

•

SQLの実行計画を

「SQL Management Base」

で管理

•

使用する実行計画を、

「Plan Baseline」

に指定

•

変更があった実行計画は

「Plan History」

に記録

EE

SQL 計画管理

「サーバー」タブ > 問合せオプティマイザ 「SQL計画管理」 > SQL計画ベースライン

TRUE にすることでBaselineを取得

SQL 計画管理

「サーバー」タブ > 問合せオプティマイザ 「SQL計画管理」 > SQL計画ベースライン

ベースラインに保存したSQL の実行計画に

新しい実行計画の候補があると新たに表示される。

「確定済」列の値は「NO」になっており、まだこの

実行計画は使用されていない

EE

EE

SQL 計画管理

新しい実行計画は索引を使用する実行計画

SQL 計画管理

「サーバー」タブ > 問合せオプティマイザ 「SQL計画管理」 > SQL計画ベースライン

新しい実行計画を展開して、使用可能に。

展開時にSQL 実行のコスト等を比較可能。

SQL 計画管理

「サーバー」タブ > 問合せオプティマイザ 「SQL計画管理」 > SQL計画ベースライン

新しい実行計画も「確定済」となり、使用可能に。

１つのSQL に使用可能な実行計画が複数ある場合は、

もっともコストが低いものが選択される。

EE

SQL 計画管理を活用したアップグレード・ソリューション

DB をシャットダウンし、

アップグレード

1

2

3

4

5

10.2.0.4

=> 11.1.0.6

11g のDB を起動

初期化パラメータ

OPTIMIZER_FEATURES_ENABLE =

10.2.0.4

初期化パラメータ

OPTIMIZER_FEATURES_ENABLE =

11.1.0.6

初期化パラメータ

OPTIMIZER_FEATURES_ENABLE =

10.2.0.4

アプリケーションを実行し、

10g のSQL の実行計画で

ベースラインを取得

データベースを再起動

ベースラインが有効化され、アップグレード前

の実行計画を使用してSQL を実行

初期化パラメータ

OPTIMIZER_FEATURES_ENABLE =

11.1.0.6

ベースライン

新規の実行計画の通知

•

SQL 計画ベースラインを使用しているSQL は、新規の実

行計画が見つかっても自動的にはそれを使用しません

•

Oracle Diagnostics Pack の機能を使用すると、新規の

実行計画が見つかった場合に、アラート通知を受けること

が可能です

•

メールでの通知も可能

Diag Pack

アラート通知の設定方法（1）

Diag Pack

「ホーム」タブ > 関連リンク 「ユーザー定義メトリック」 > 「作成」

直近の24時間で、ACCEPTED 列が“

NO”に

なっているベースライン内のエントリ数を検索

EE

アラート通知の設定方法（2）

Diag Pack

「ホーム」タブ > 関連リンク 「ユーザー定義メトリック」 > 「作成」

前のページのSQL の結果が

1以上であれば、アラートを生成

24時間毎に監視

EE

ストアドアウトラインからSPMへ移行

11gR2ではストアドアウトラインをSPMのSQLベースラインへ移行が可能です

11gR2よりストアドアウトラインからSQLベースラインへ簡単に移行が

できるようになりました

ストアドアウトラインとはヒント句の集合であり、運用中に実行計画が

変わることのリスクを出来る限り少なくするための機能

(今後のバージョンではサポートされなくなる予定)

DBMS_SPM.MIGRATE_STORED_OUTLINEの実行により移行

SPMへ移行することにより

•

新しく作成された実行計画のパフォーマンスを検証した後、割り当てが可能

•

複数の実行計画を利用可能にすることにより、一方が使用不可になったとしても

パフォーマンス劣化を防止できる

•

特定のSQL文に複数の実行計画を管理できる

まとめ

・SQL Serverからの移行アセスメント

・MySQLからの移行相談

・PostgreSQLからの移行相談

・Accessからの移行アセスメント

・Oracle Database バージョンアップ支援

・Oracle Developer/2000 Webアップグレード相談

・パフォーマンス・クリニック

・Oracle Database 構成相談

・Oracle Database 高可用性診断

・システム連携アセスメント

・システムセキュリティ診断

・簡易業務診断

・メインフレーム資産活用

パフォーマンス最適化のために

•

コストベース・オプティマイザと統計情報の理解

•

ルールベース・オプティマイザは10g から未サポート

•

自動オプティマイザ統計による適切な統計情報収集

•

SQL 実行計画の効果的な管理

•

SQL 計画管理を活用して、実行計画の変化に対応

•

11g へのアップグレードにも活用可能

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プロジェクト全般の相談窓口としてご支援いたします。

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

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

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

はできません。以下の事項は、マテリアルやコード、機能を提供することをコミットメ

ント（確約）するものではないため、購買決定を行う際の判断材料になさらないで下

さい。オラクル製品に関して記載されている機能の開発、リリースおよび時期につ

いては、弊社の裁量により決定されます。

Oracle、PeopleSoft、JD Edwards、及びSiebelは、米国オラクル・コーポレーション及びその子会社、関連会社の

登録商標です。その他の名称はそれぞれの会社の商標の可能性があります。