Download pdf - Oracle Direct SeminarSQLのレスポンスはCBO(コストベースオプティマイザ)に大きく依存しています。単一のSQLに注目した場合、そのSQLのレスポンスは以下の図に表したインプット情報

<Insert Picture Here>

Oracle Direct Seminar

オラクルコンサルタントが語る統計情報管理の真髄-Part3-

日本オラクル株式会社

Copyright© 2010, Oracle. All rights reserved. 2

Agenda

• Introduction

• 統計情報の重要性

• 統計情報の管理

• Part1-2の振り返り

• 統計情報の概要

• 統計情報の重要性

• 統計情報の管理

• 統計情報管理の例

• Case Study

• DBアップグレードにおける統計情報管理

• SQL Plan Management(SPM)

• Appendix

無償技術サービスOracle Direct Concierge

http://www.oracle.com/lang/jp/direct/services.

html

・Oracle Database バージョンアップ支援・Oracle 構成相談(Sizing)サービス・パフォーマンス・クリニック・サービス・SQL Serverからの移行アセスメント

・DB2からの移行支援サービス・Sybaseからの移行支援サービス・MySQLからの移行相談サービス

・PostgreSQLからの移行相談サービス・Accessからの移行アセスメント

・Oracle Developer/2000 Webアップグレード相談・仮想化アセスメントサービス

・ビジネスインテリジェンス・エンタープライズエディション・アセスメントサービス

・簡易業務診断サービス



Introduction

Copyright© 2010, Oracle. All rights reserved.

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

4

目的とゴール

• 統計情報管理の重要さと適切な運用方法を身に付ける• なぜ統計情報の管理が必要なのかを理解する

• 統計情報の取得・運用時のポイントを理解し、適切な管理を行う



Part1-2の振り返り


統計情報概要

• 統計情報とは？統計情報とは、表や、索引、また使用している領域、カーディナリティ、データ分布などのデータ特性を表す情報です。CBOはこの情報を元にコストを計算し、実行計画を生成しま

す。

この統計でコストを計算！

オプティマイザ統計• 表統計

• 行数、データ・ブロック数、平均行長

• 列統計

• 列内の個別値数(NDV : Number of Distinct Values)

• 列内のNULL数

• データ分布(最大値 / 最小値 / ヒストグラム)

• 索引統計

• リーフブロック数

• レベル (ツリーの高さ)

• クラスタ化係数

• システム統計

• I/Oパフォーマンス

• CPUパフォーマンス

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

6


統計情報の詳細(表統計、列統計)

• 表統計各表ごとに収集された統計情報です。以下の項目が存在します。

• 行数

表に格納された行の数を表します。

• データ・ブロック数

表内に格納されたデータ・ブロックの数を表します。

• 平均行長

表内の行の平均の長さです(単位はバイト)。

• 列統計各表の列ごとに収集された統計情報です。以下の項目が存在します。

• 列内の個別値(NDV：Number of Distinct Values)数

列内に含まれる値の種類を表します。

• 列内のNULL数

列内に含まれるNULLの数を表します。

• データ配分(ヒストグラム)

列内に格納されたデータの分布度を表します。

7


統計情報の詳細(索引統計)

• 索引統計各索引ごとに収集された統計情報です。主に以下の項目が存在します。

• リーフ・ブロック数

索引に含まれるリーフ・ブロックの数を表します。

• レベル

ルート・ブロックからリーフ・ブロックまでの

階層数を表します。

• クラスタ化係数

索引が付けられている列のデータが表内で

どの程度分布しているかを表します。

補足)クラスタ化係数

クラスタ化係数は索引内の隣り合ったレコードが異なるデータ・ブロックへのポインターを持つ場合にカウント・アップされます。この値が大きいほど、索引が付けられた列のデータが表全体に分布している事を意味します。

取得する行数が尐ない場合でもクラスタ化係数が大きい場合は、表内の大半のデータ・ブロックにアクセスする必要があるため、INDEX SCANよりFULL SCANの方が効率的です。

リーフ・ブロック数

レベル

8


統計情報の詳細(システム統計)

[CBO情報の確認方法]

以下のイベントを使用すると、トレースファイルに統計情報を出力させる事が出来ます。1．イベントの開始：

alter session set events ‘ 10053 context forever, level 1 ‘

2．統計情報を取得したい表がFROM句に含まれたSQLを実行ex) SELECT * FROM EMP;

3．イベントの終了：alter session set events ‘ 10053 trace name context off ‘ ;

• システム統計データベース全体で収集された統計情報です。主に以下の項目が存在します。

• I/Oパフォーマンスと使用率

データベース全体のI/O転送速度やディスク・リード時などの統計情報を表します。

• CPUパフォーマンスと使用率

CPU使用率などのCPUパフォーマンスに関する統計情報を表します。

9

※event 10053 は、Cost Base Optimizer(CBO)の動作をトレースするイベントです。CBO に関連する動作、及び、パフォーマンス障害に関する調査の際に event 10053 を設定して情報を取得いただくことを弊社サポートセンターより提示させていただく場合があります。


統計情報が出力されたトレースファイル

****************

QUERY BLOCK TEXT

****************

select e.empno, e.ename, d.dname from emp e, dept d

where e.deptno=d.deptno

*********************

QUERY BLOCK SIGNATURE

*********************

qb name was generated

signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0

fro(0): flg=0 objn=11205 hint_alias="D"@"SEL$1"

fro(1): flg=0 objn=11207 hint_alias="E"@"SEL$1"

*****************************

SYSTEM STATISTICS INFORMATION

*****************************

Using NOWORKLOAD Stats

CPUSPEED: 722 millions instruction/sec

IOTFRSPEED: 4096 bytes per millisecond (default is 4096)

IOSEEKTIM: 10 milliseconds (default is 10)

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

Table: DEPT Alias: D

#Rows: 4 #Blks: 5 AvgRowLen: 18.00

Column (#1): DEPTNO(NUMBER)

AvgLen: 3.00 NDV: 4 Nulls: 0 Density: 0.25 Min: 10 Max: 40

Index Stats::

Index: PK_DEPT Col#: 1

LVLS: 0 #LB: 1 #DK: 4 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00

実行したクエリ

システム統計

表統計

列統計

索引統計

10


統計情報とSQLのレスポンス

11


SQLテキスト

パラメータ

オブジェクト構造

データの実態

環境

統計情報

実行計画

レスポンス

• 統計情報とSQLのレスポンスSQLのレスポンスはCBO(コストベースオプティマイザ)に大きく依存しています。単一のSQLに注目した場合、そのSQLのレスポンスは以下の図に表したインプット情報

に基づき決定されます。ここで統計情報以外のインプットは固定的である事が多いため、統計情報の変動がSQLのレスポンスに及ぼす影響は大きいと言えます。

今回の対象


統計情報はいつ使われる??


• 統計情報が使用されるタイミング統計情報はCBOがSQLの実行計画を決定する際のインプットの一部となります。CBOはオブジェクトの統計情報を利用して、発行されたSQLで取得すべきデータを最も効率よく検索するための判断を行います。

統計情報データ件数：

1000件

SELECT EMP_ID FROM EMP

WHERE EMP_ID = 50

EMP

12

テーブルのデータ件数に対して、

取得するデータ数が尐ないので索引を利用

※EMP_IDは主キー

IND_EMP

索引統計


Hard Parseと統計情報

• 統計情報はHard Parse時のみ使用されるSQLの解析のフェーズでは下図の通りSoft ParseとHard Parseのどちらかが行われます。Soft Parseが行われた場合はCBOが関与する事はないので、統計情報とは無関係にキャッシュ上の実行計画が利用されます。つまり、統計情報を再取得した後にCBOに新たな実行計画を生成させるためには、キャッシュ上の既存の実行計画をフラッシュする必要があります。

13

実行計画

サーバー・プロセス

キャッシュ上に同じSQLが存在するか

Soft Parse

共有プール

Hard Parse

Hard Parseでは

新たに実行計画を生成するため、統計情報を使用


なぜ統計情報に注目??

• 統計情報変化の影響範囲SQLテキストなど他のCBOインプットに比べて、統計情報が変化した時の影響範囲はそのオブジェクトを参照しているすべてのSQLになるため、影響範囲が広いと言えます。また、複数のSQLから参照されているため、パフォーマンスチューニングのように1つ1つのSQLに対して統計情報を調整する事が困難です。

データ件数が変化

実データデータ件数：

10件


10件


10000件


10件

• 様々なSQLからあるオブジェクトの統計情報が参照される

• 統計情報と実データとの差異があるとパフォーマンスに影響を与える

• 影響範囲が大きい

14


「適切なタイミング」での統計情報取得

• 定期的な取得≠適切な取得実データと統計情報間の差異を尐なくするためには、「適切なタイミング」での統計情報の取得が重要です。「適切なタイミング」というのは、定期的に統計情報を取得する事ではありません。統計情報の再取得によって、パフォーマンスが悪化する可能性もあるため、どの時点の統計情報を取得するかを十分に考慮する必要があります。

：データの変動

時間

データ量

：統計情報の取得

定期的に取得していても、取得のタイミングによってデータ量が大きく異なる

15


統計情報管理のポイント

良い統計情報＝「適切なタイミング」で取得された統計情報

CBOに効率の良い実行計画を生成させるためには、実データとの差異が尐

ない”質の高い統計情報”を取得する事が重要

良くない統計情報＝統計情報と実データ間に差異がある状態

CBOが効率の悪い実行計画を選択する一因となる

•統計情報取得対象のオブジェクトを参照しているすべてのSQLのレスポンス

に影響を与えるのでとても重要

統計情報を管理することはSQLの性能を維持するためにとても重要

でも、いったいどうやって統計情報を管理すればいい？

•どんなことを考慮して統計情報管理方針を決定すればよいのか


統計情報管理のアプローチ統計情報管理にあたって考えること

1

2

3

統計情報を管理するにあたり、以下のことを決定する必要があります。

統計情報の定期取得有無

統計情報の取得タイミング

統計情報の取得頻度

統計情報の取得方法

統計情報の取得対象4

5 統計情報はどうやって取得する？？

統計情報を取得しないといけないオブジェクトって？？

統計情報はどれくらい頻繁に取得する？？

統計情報はいつ取得する？？

統計情報は定期取得する？しない？？



ポイント① データの変動の傾向

ポイント② 人的要因

ポイント③ システム特性

ポイント④ ユーザーのニーズ（優先業務）

運用しているシステムにおいて、データが更新される時間帯とその変動形態を把握します。

データ変動の傾向を把握することにより、統計情報取得タイミングなどを決定する上での参考になります。

まず、以下のポイントを把握します

システムの運用にかかわるコストやリソース（人員）状況を把握します。

人的要因を把握することにより、統計情報取得間隔などを決定する上での参考になります。

システムのリソース負荷の多い時間帯、システムの用途などを把握します。

システム特性を把握することにより、統計情報取得方法や頻度、定期取得の有無などを決定する上での参考になります。

システムを使用するユーザーの優先業務を把握します。

ユーザーのニーズを把握することにより、どのSQLのパフォーマンスを維持するための統計情報を取得するべきかを考慮し、取得タイミングなどを決定する参考になります。



•定期取得するのがよさそうなデータ変動型は？

•一回のみの取得で十分そうなデータ変動型は？

•データ増減タイミングを考慮して取得する必要がありそうなデータ変動型は？

常に増加常に減少増減常に一定

a b c d

a b

c

d

ポイント① データ変動の傾向

運用しているシステムにおいて、データが更新される時間帯とその変動形態を把握します。

主に以下のパターンのデータ変動が考えられます。

これらのデータ変動型の中で、、、


統計情報管理のアプローチ管理を決定する上で考慮する必要のある４つのポイント

統計情報収集の管理をする上で、人的要因を考慮することがポイントです。主に以下の様な事項が考えられます。

運用コスト(金、人、モノ)

人員的リソース不足

スキル(運用者)

統計情報取得にかかる時間

ポイント② 人的要因

リソース（運用者の時間）不足でも統計情報収集が可能自動統計情報収集機能を使用することで、運用者が手動で実行することなく統計情報収集が可能。

統計情報取得したいけど時間がない・・・



最適な統計情報収集方法や頻度などを判断する上で、システムの特性は重要な考慮ポイントとなります。

• H/Wのリソース状況（高負荷な時間帯）

• 運用上の制限

• システムの種類/用途

• 検証環境の有無

ポイント③ システム特性

• 検証環境で最適な統計情報を取得して、その統計情報を本番環境へ移行しよう

• 運用を続けてきたシステムではもともとの統計情報取得方針があるのでその方針を引き継ごう

• この時間帯はリソース負荷の高いので統計情報取得はひかえよう

• 新規システムはデータ変動傾向がまだわからないから、まずは定期取得で運用してみてから決めよう



一般的に、一つのオブジェクトへたくさんの問い合わせを行っています。

あるSQLに対する統計情報としては効果的でも、他のSQLに対しては逆効果になる場合もあり

ます。そんな場合には、優先度の高い業務を把握し、その業務の問い合わせに対する実行計画が効率のいいものになるように統計情報を取得する必要があります。

ポイント④ ユーザーのニーズ（優先業務）

レポートが作成したい

OLTPの処理をしたい

両方のニーズに答えるのは厳しい・・・今はどちらの処理を優先しようか??



これまでの統計情報管理アプローチのポイントについて考えることで、統計情報管理方針

を決定していくことができます。以下に、統計情報管理項目が、どのアプローチ・ポイントを考慮することで決定しやすいかを、表に表しています。

※以下の表は、あくまで参考です。それぞれのシステムの運用状況や環境次第で、考慮すべきポイントの重要度も変わってくるので、ご注意ください。

ポイント①

データ変動の傾向

ポイント②

人的要因

ポイント③

システムの特性

ポイント④

ユーザーのニーズ

定期取得の有無 ◎ ○ ○

取得タイミング ◎ ○ ○

取得頻度 ○ ◎ ○

取得対象 ○ ◎

取得方法 ◎ ○ ○


• 自動統計収集 Oracle Database は1日に1回、定期的に統計情報の取得を自動ジョブとして行います。

• 手動統計収集自動統計収集とは別にDBMS_STATSパッケージ、もしくはANALYZEコマンドを使用す

る事で、手動で統計情報を取得することが可能です。

• 動的サンプリング動的サンプリングを使用すると、CBOが実行計画を生成する際に統計情報の状態を

確認します。確認の結果、統計情報が使用できない、もしくは統計情報が古すぎると判断された場合に、自動的に必要な統計情報をサンプリングする事が可能です。

Oracle Database自動収集手動収集

統計情報管理の手法統計情報取得の方法


• Oracle Database は以下ルールに従って定期的に統計情報の収集を行っています。なお、自動統計収集はデフォルトで有効ですが、STATISTICS_LEVEL=BASIC（デフォルトはTYPICAL）と設定すると自動統計情報収集は無効となります。

[タイミング]

月-金曜日の毎日22-6時の間

土曜日0時-月曜日0時の間

[条件]

統計情報が未収集のテーブル

表内の行数の10%以上が変更され、統計情報が失効しているテーブル

自動収集

統計情報管理の手法自動統計収集


• Oracle Database ではDBMS_STATSパッケージ、ANALYZEコマンドを使用する事により、手動で統計情報の収集を行う事ができます。 DBMS_STATSパッケージ

以下の単位で統計情報を取得する事が可能

• 表

• スキーマ

• 索引

• データベース(10g～)

DBMS_STATS.GATHER_DATABASE_STATS (

estimate_percent => 50

degree => DBMS_STATS.AUTO_DEGREE

)

サンプリング率

パラレル度数(DBMS_STATS.AUTO_DEGREE：Oracleが適切な並列度を指定)

ANALYZE TABLE <table_name> estimate statistics sample 50% ;サンプリング率

統計情報管理の手法手動統計収集

ANALYZEコマンド

以下の単位で統計情報を取得する事が可能

• 表

• 索引


• 動的サンプリングとは？ SQLの実行時（ハードパース時）に動的に統計情報をサンプリングし、その結果を元に

実行計画を生成した時に収集される統計のこと

以下の場合が有効

表結合を含む検索で1つのテーブルのみ統計情報を取得していない

データの計算などに一時的使用される作業表を参照する

• 設定方法初期化パラメータOPTIMIZER_DYNAMIC_SAMPLINGにより制御されます

デフォルト値のレベルは2(レベル0から10の設定が可能)です。レベルによってサンプリ

ングするブロック数が増えます

動的サンプリングのメリット•統計情報が失効していた場合に、自動的に統計情報を取得するため、実行計画のブレを少なくすることができる。

動的サンプリングのデメリット•動的サンプリングによるオーバーヘッドが大きいため、頻繁に動的サンプリングが実施されると、パフォーマンス劣化につながる。

統計情報管理の手法動的サンプリング


統計情報管理の手法統計のロック

• DBMS_STATSパッケージを使用する事により、揮発性の高い表のSQL

パフォーマンスが気になる場合、統計情報をロックすることが出来ます。これにより、データ量の変動に影響される可能性が尐なくなり、安定したパフォーマンスを発揮することが可能になります。

統計のロックの使いどころ•データ変動に伴い、最適なタイミングで収集されたオプティマイザ統計情報を使用したい場合

DBMS_STATS.GATHER_TABLE_STATS(

ownname => ‘SCOTT’,

tabname => ‘EMP’

)

DBMS_STATS.LOCK_TABLE_STATS(

ownname => ‘SCOTT’,

tabname => ‘EMP’

)

データ変動に伴い、最適なタイミングで収集されたオプティマイザ統計情報を使用したい場合

最適なデータ変動時に統計情報を取得した後、その統計情報をロックします



Case Study


1. シナリオ説明とポイント

Case Study のポイント

統計情報の管理方法のプロセスを理解し、それに沿って管理を実施する

Case Study のシナリオ

Miracle社では統計情報の取得をデフォルトの自動統計収集で行っており、パフォーマンスは

安定していた。しかし、ある日普段とは異なるデータ変動が発生したためにパフォーマンスが劣化してしまった。Miracle社では以下のような条件が存在する。

・パフォーマンスは問題が発生した以前の状態と同様のレベルで安定させたい。

・パフォーマンス劣化以前まで、Miracle社で扱う普段のデータ変動に合わせるためにはデフォルトの自動統計収集で十分であった。しかし、今回は普段とは違うデータ変

動が発生している。そして、この傾向は今後も継続するとみられる。

・今後、このようなパフォーマンス問題が起きないように分析を行い対処したい。

これらの条件を満たすにはどのような統計情報の管理を行えば良いだろうか??


障害発生時運用フロー

障害発生時運用フロー

障害発生時の解決の糸口を見出すには、まず問題点を検出し、その問題点に特化した解決策を適用することが重要です。障害発生時は以下3つのフェーズに分けることができます。

障害発生 Phase①

解決

Phase①切りわけ切り分けフェーズでは、現状で発生した事柄から、何が問題になっているのか把握します。

Phase②原因究明原因究明フェーズでは、把握した問題を分析して原因を突き止めます。

Phase③解決解決フェーズでは、分析後、明らかになった原因に対する解決策の考案、実施をします。

Phase② Phase③


障害発生時運用フロー～Phase①～

Phase①切り分け

切り分けフェーズでは、問題がどこで発生しているのかを切り分けます。

障害の切り分け

障害の切り分けは一般的に、アプリケーション、DB、OS、H/W（N/W含む）、のどの部分で問題が発生しているか切り分けます。

今回のケーススタディでは、DB層の統計情報が問題でパフォーマンス劣化が発生しているという障害の切り分けが出来ています。

① ② ③


障害発生時運用フロー～Phase②～

Phase②原因究明

原因究明フェーズでは以下を実施します。

問題の原因追及

問題の原因追求をします。問題がどうして発生しているかを突き止めます。

統計情報が問題の原因であった場合･･･

統計情報が問題の場合、主に以下の3つの原因が考えられます。統計情報と実データとの差異必要な統計情報が取得されていない統計情報取得と他の処理の同時実行によるリソース高騰

現在発生している問題が、どの原因に当てはまるかを調査します。

① ② ③


障害発生時運用フロー～Phase③～

Phase③解決

解決フェーズでは以下を実施します。

問題の解決

問題の原因を解決する解決案を考案し、実施します。

① ② ③

統計情報が問題の原因であった場合･･･

原因解決のため、対策を立案するにあたり、以下の項目を考慮します。統計情報取得有無取得タイミング取得頻度取得対象取得方法


Case Study障害発生時運用フロー

Phase①問題切りわけ

Phase②原因究明

Phase③解決

パフォーマンス劣化の原因が統計情報にあることが発覚している

分析を行うために、問題が起きている統計情報をExportする一時的に問題に対処するために、統計情報のバックアップをリストア後、ロックする分析を行い、原因を究明する

統計情報管理のポイントを考慮して、解決策を立案する


Phase②:原因究明～分析の準備～

統計表を作成し、現在の統計情報を退避しておきます。

現在の統計情報をExportする

DBMS_STATS.CREATE_STAT_TABLE (

ownname => ‘ORACLE’,

stattab =>’SAVE_STATS_ORACLE’

);

1．ORACLEスキーマの統計情報保存用の統計表SAVE_STATS_ORACLE表を作成する

DBMS_STATS.EXPORT_TABLE_STAT (


stattab => ’SAVE_STATS_ORACLE’

);

2．統計表SAVE_STATS_ORACLE表にORACLEスキーマの全ての統計情報をExportする

統計表

統計表

全てのOracleスキーマオブジェクト

Export


Phase②:原因究明～一時的な対処～

正常なパフォーマンスであった時点(4/26 22時)の統計情報をリストアします。

以前の統計情報をリストアする

DBMS_STATS.RESTORE_SCHEMA_STATS (


as_of_timestamp => ’20100426 22:00:00 000000’

);

4/26の22時時点のパフォーマンスは正常であったと仮定

4/26の22時時点

リストア

現在

原因の分析中に再度、統計情報収集が行われて同じ問題が再発することを防ぐために、統計情報をロックします。（他のスキーマの統計情報は問題ないと判断できる場合）

リストアした統計情報をロックする

DBMS_STATS.LOCK_SCHEMA_STATS (

ownname => ‘ORACLE’

);


Phase②:原因究明～分析～

MAMI社で扱うデータの変動

データ量

0時 24時

1日の間に増減するデータの推移

現在：統計情報の取得をデフォルトの自動統計収集で行っているデフォルトの自動統計収集は事前定義された時間に、統計情報が失効している表を対象にして、自動的に統計情報の収集を行う

問題：データの変動タイミングが変化したため、最適な統計情報を取得出来ていない

対策：データの変動を考慮して、統計情報の取得を行う必要がある



データ変動を考慮した統計情報取得を行うには具体的にどうすれば良いか??

データ量

0時 24時

ポイント取得タイミングが違う統計情報を比較する

A

B

C

：統計情報の取得タイミング

A

データの件数が最も増加した付近

B

データ件数が平均値付近

C

データの件数が最も減尐した付近

データ件数が多い時ほど、システムへの負荷が高いため、Aで統計情報を取得する事によって、データ件数の変動が起きてもパフォーマンスを安定させる事が可能

最適なタイミング Aでの取得



統計情報を取得するタイミングが明確になったため、統計情報を取得する

→オブジェクトごとにデータ変動の周期が違う場合はどうする??

[最適なタイミング]

14時付近

Object 1 Object 2 Object 3


24時付近


22時付近

オブジェクトによって、統計情報取得の最適なタイミングが異なっている。このような場合は下記のような方法が考えられるが、どちらの管理策を実施すべきだろうか??

オブジェクト毎に統計情報を取得する??

一括で統計情報を取得する??



ポイント統計情報取得の負荷を減尐させるタイミングを発見する

Object 1 Object 2 Object 3

先に挙げた方法では、以下のようなデメリットが存在する。

オブジェクト毎に統計情報を取得

システム負荷、もしくは人的リソースの負荷が大きく現実的ではない

一括で統計情報を取得する??

オブジェクトによっては最適なタイミングからはずれた統計情報が取得される可能性がある

一括で取得した後に、重要なオブジェクトに関しては別途統計情報の取得を行う


14時付近


24時付近


22時付近

23時に一括で取得14時に個別に取得


Phase③:解決～解決策の立案～

自動統計収集の適用時間を変更して、毎日23時に統計情報を取得するようにジョブを組み込む

Object 2 Object 3

Object 11．統計情報が存在する場合は統計情報をロックを解除する2．対象のオブジェクトの統計情報を取得する3．取得した統計情報をロックする

一括での統計情報取得

個別での統計情報取得 23時に一括で取得

14時に個別に取得


Phase③:解決～解決策の実施準備～

原因の分析中に再度、統計情報収集が行われて同じ問題が再発することを防ぐために、統計情報をロックします。（他のスキーマの統計情報は問題ないと判断できる場合）

ロックをした統計情報のロックを解除する

DBMS_STATS.UNLOCK_SCHEMA_STATS (

ownname => ‘ORACLE’

);

ロック解除


Phase③:解決～解決策の実施～

自動統計収集の適用時間を変更して、毎日23時に統計情報を取得するようにジョブを組み込む

一括での統計情報取得

実行コマンド

[DBMS_SCHEDULER.SET_ATTRIBUTE]

Scheduleオブジェクトの属性を変更するために使用するプロシージャ。ここではウィンドウの実行日時の変更を行うために利用している。

[DBMS_SCHEDULER.SET_ATTRIBUTEのfreq属性]

freq属性を変更する事で実行日時を指定出来ます。ここでは平日の23時に時間が指定されています。

EXEC DBMS_SCHEDULER.SET_ATTRIBUTE(

‘WEEKNIGHT_WINDOW’, #ウィンドウ名‘repeat_interval’, #次に実行する日時を戻すファンクション‘freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=23;byminute=0; bysecond=0‘ #実行日時);


Phase③:解決～解決策の実施～

1．統計情報がロックされている場合は統計情報をロックを解除する

個別での統計情報取得

EXEC DBMS_STATS.UNLOCK_TABLE_STATS(‘OBJECT1’,’ORACLE’)

3．取得した統計情報をロックする

2．対象のオブジェクトの統計情報を取得する(取得し直す)

EXEC DBMS_STATS.LOCK_TABLE_STATS(‘OBJECT1’,’ORACLE’)

EXEC DBMS_STATS.GATHER_TABLE_STATS(

TABNAME=>‘OBJECT1’,


CASCADE=>TRUE

);

平日の14時に以下の手順を実施

NewOld


5. Overview

Case Study のポイント

ポイント障害が発生した際、原因を究明し問題がどうして発生しているかを把握する

ポイント原因解決案の立案にあたり、統計情報管理のポイントを考慮する

統計情報が問題であった場合、統計情報と実データとの差異、必要な統計情報が取得されていない、統計情報取得と他の処理の同時実行によるリソース高騰などの原因に当てはまるかの調査をし、問題の分岐点を把握します。

統計情報管理のポイントを抑えることで、通常管理時だけでなく、障害発生時においても解決策を立案することができます。統計情報取得タイミング、取得頻度、取得対象、取得方法などを考慮することによって、問題にあった対処方法を実施することが可能です。

統計情報の管理方法のプロセスを理解し、それに沿って管理を実施する



11g新機能：SQL Plan Management


実行計画の変動による性能影響

48

統計情報を取得する際のリスクとして、実行計画が変動してしまう事が挙げられます。この原因は統計情報が実行計画を生成する際の重要なインプットである事に起因します。

Oracle Database 11gから導入されたSQL Plan Management(SPM)を用いる事で、実行計画の変動による性能影響を回避する事が可能


統計情報の再取得により、統計情報が変動

実行計画統計情報

統計情報の変動により、実行計画が変動

SQLのパフォーマンスが変動


SPM(SQL Plan Management)とは??

49

機能の概要オプティマイザ自信が実行計画を時間経過とともに履歴として記録し、評価する管理メカニズム複数の実行計画からSQL計画ベースラインを構築し、その中から最適な計画を選択


SPMの実行フェーズ

50

取得

選択

改良

実行計画を取得し、計画履歴として記録既存計画からのベースラインの作成

格納された計画履歴に基づいてパフォーマンス低下する可能性を回避する実行計画を選択

新しい実行計画のパフォーマンスを評価し、より優れたパフォーマンスの計画をSQL計画ベースラインに組み込み


SQL計画ベースラインの取得

51

SYSAUX

SQL管理ベース(SMB)CBO

SYS_SQL_PLAN_1

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE=TRUE

サーバー・プロセス

[計画履歴]

SQLテキストアウトラインバインド変数・・・

SQL発行計画履歴の取得

パラメータを有効すると、オプティマイザは新しい実行計画が発行されるごとに計画を取得し、計画履歴として管理SQL計画履歴の取得対象は再解析・再実行されたSQL

(1度ParseされたSQLのSQL_IDをログに格納し、認識)


SQL計画ベースラインの選択

52

SYSAUX

SQL管理ベース(SMB)

CBO

SYS_SQL_PLAN_1

SYS_SQL_PLAN_2

SYS_SQL_PLAN_3

SYS_SQL_PLAN_4

SQL計画ベースライン

SQL計画履歴

格納されたSQL計画履歴に基づいて計画の変更を検出し、SQL文のパフォーマンスが低下する可能性を回避する計画を選択SQL計画履歴の取得対象は再解析・再実行されたSQL

各SQLごとの最初に取得

された計画履歴、もしくは次ページに示す方法で承認された計画履歴

各SQLごとの2番目以降

に取得され、かつ承認されていない計画履歴

SQL計画ベースラインの中でコストが最も低いものを選択

一致した計画を使用

CBOが構築した計画

比較

一致一致しない

追加

OPTIMIZER_USE_SQL_PLAN_BASELINE=TRUE


SQL計画ベースラインの改良

53

新しい計画のパフォーマンスを評価し、より優れたパフォーマンス計画をSQL計画ベースラインに組み込み計画の改良には以下の方法が存在

-手動による計画のロード(SQLチューニング・セット、カーソル・キャッシュ)

-手動による実行計画の承認(DBMS_SPM.ALTER_SQL_PLAN_BASELIN)

-DBMS_SPM.EVOLVE_SQL_PLAN_BASELINEファンクションの使用-SQLチューニング・アドバイザ-固定SQL計画ベースラインの使用

固定SQL計画ベースラインSQL計画ベースラインのFIX属性がYESになっている計画が使用可能である場合、CBOはその計画を優先的に使用します。また、固定SQL計画ベースラインに対しては新しい計画が自動的に追加されないため、追加の際は手動による計画のロードが必要になります。


DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

54

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

Evolve SQL Plan Baseline Report

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

Inputs:

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

SQL_HANDLE = SYS_SQL_593bc74fca8e6738

PLAN_NAME =

TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

VERIFY = YES

COMMIT = YES

PLAN: SYS_SQL_593bc74fca8e6738

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

Plan was verified: Time used .07 seconds

Passed performance criterion: Compound improvement >= 7.32.

Baseline Plan Test Plan Improve. Ratio

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

Execution Status: COMPLETE COMPLETE

Rows Processed: 40 40

Elapsed Time(ms): 23 8 2.88

CPUT Time(ms): 23 8 2.88

Buffer Gets: 450 61 7.38

・・・・・・・・・・


SPM ～Summary～

55

実行計画の変動リスクに対応可能

複数の実行計画を保存し、それらを比較する事で最適な実行計画を選択する事が可能

SPMを使用する事によるHard Parse時の負荷の増加複数の実行計画の中から最適な実行計画を選択するスキルが必要

SPMのメリット

SPMのデメリット

SPMを用いた統計情報の管理方法はPart4で紹介


Part3のポイント

56

ポイント1

ポイント2

なぜ統計情報の管理が重要なのかを理解する

統計情報の取得・運用時のポイントを理解し、適切な管理を行う

統計情報はCBOが実行計画を生成する際の重要なインプット情報であり、統計情報と実データ間に差異があるとCBOが効率の悪い実行計画を生成するようになっ

てしまいます。このため、実データとの差異が尐ない、質の高い統計情報を使用する事が必要になってきます。

統計情報の取得や管理を行ううえで、一般的なアプローチとそのポイント、もしくは管理手法を身に付け、それらを実際のシステム状況に合わせて応用していく事によって、質の高い統計情報を維持する事が可能になります。


Summary

57


10件


10000件


10000件


10000件

不一致一致

Part1

・統計情報管理が必要な理由

・統計情報と実データの差異が生む問題

Part2

・統計情報管理のポイント

・統計情報管理のケーススタディ

Part3

・複雑な統計情報管理のケーススタディ・全体のまとめ

適切な統計情報管理


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 オンデマンド


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

60

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年後に新システムへデータを移行


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


以上の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはできません。以下の事項は、マテリアルやコード、機能を提供することをコミットメント（確約）するものではないため、購買決定を行う際の判断材料になさらないで下さい。オラクル製品に関して記載されている機能の開発、リリースおよび時期については、弊社の裁量により決定されます

。

OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。文中の社名、商品名等は各社の商標または登録商標である場合があります。