Click here to load reader

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

  • View
    3

  • Download
    0

Embed Size (px)

Text of Oracle Direct Seminar...

  • 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アップグレード相談 ・仮想化アセスメントサービス

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

    ・簡易業務診断サービス

  • Copyright© 2010, Oracle. All rights reserved. 3

    Introduction

  • Copyright© 2010, Oracle. All rights reserved.

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

    4

    目的とゴール

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

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

  • Copyright© 2010, Oracle. All rights reserved. 5

    Part1-2の振り返り

  • Copyright© 2010, Oracle. All rights reserved.

    統計情報概要

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

    す。

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

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

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

    • 列統計

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

    • 列内のNULL数

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

    • 索引統計

    • リーフブロック数

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

    • クラスタ化係数

    • システム統計

    • I/Oパフォーマンス

    • CPUパフォーマンス

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

    6

  • Copyright© 2010, Oracle. All rights reserved.

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

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

    • 行数

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

    • データ・ブロック数

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

    • 平均行長

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

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

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

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

    • 列内のNULL数

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

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

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

    7

  • Copyright© 2010, Oracle. All rights reserved.

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

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

    • リーフ・ブロック数

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

    • レベル

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

    階層数を表します。

    • クラスタ化係数

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

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

    補足)クラスタ化係数

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

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

    リーフ・ブロック数

    レベル

    8

  • Copyright© 2010, Oracle. All rights reserved.

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

    [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 を設定して情報を取得いただくことを弊社サポートセンター より提示させていただく場合があります。

  • Copyright© 2010, Oracle. All rights reserved.

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

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

    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

  • Copyright© 2010, Oracle. All rights reserved.

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

    11

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

    SQLテキスト

    パラメータ

    オブジェクト構造

    データの実態

    環境

    統計情報

    実行計画

    レスポンス

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

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

    今回の対象

  • Copyright© 2010, Oracle. All rights reserved.

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

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

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

    統計情報 データ件数:

    1000件

    SELECT EMP_ID FROM EMP

    WHERE EMP_ID = 50

    EMP

    12

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

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

    ※EMP_IDは主キー