Oracle CBOの計算方法

Oracle CBOの計算方法
http://www.robios.org/blog/archives/000005.html

コストの計算方法

表全走査 (Full Table Scan):
[ブロック数] / [DB_FILE_MULTIBLOCK_READ_COUNT]

索引一意走査 (Index Unique Scan):
([索引階層数] + 1{ROWIDによる表走査}) * [OPTIMIZER_INDEX_COST_ADJ] / 100

索引範囲走査 (Index Range Scan):
([索引階層数] - 1{リーフ分を控除} + [リーフブロック数] * [Filtering Factor] + [Clustering Factor] * [Filtering Factor]) * [OPTIMIZER_INDEX_COST_ADJ] / 100

ブロック数
表を構成するブロックの総数。ALL_TABLES/DBA_TABLESで確認。
DB_FILE_MULTIBLOCK_READ_COUNT
初期パラメータ。全表走査の際1回の読み取りで何ブロックを同時に取得するか。初期値は8。
索引階層数
B*Tree索引(通常の索引)の階層数。例えば3階層であれば、ルート、ブランチ、リーフ、4階層であれば、ルート、ブランチ、ブランチ、リーフとなる。索引分析後ALL_INDEXES/DBA_INDEXESで確認可能。
リーフ数
B*Tree索引のリーフ総数。最下層ノードの数。
Filtering Factor
全件数に占める、検索取得数の割合。詳細は後述。
Clustering Factor
索引の、それぞれのリーフが参照する表ブロックの総和。索引分析後ALL_INDEXES/DBA_INDEXESで確認可能。詳細は後述。
OPTIMIZER_INDEX_COST_ADJ
初期値は100。詳細は後述。

上記式で、表全走査、索引走査それぞれのコストを計算し(索引が複数あればそれらもそれぞれ計算)、少ない方法にて実行計画を立てる。

Filtering Factor

Filtering Factor (FF) は、取得を行おうとしている件数が全件数の何割か、を示す値である。実際に検索を行っていないため、この値は分析により得られた結果を元に推理される。

表と索引分析後、Oracleがその表と索引に対して既に知っていることは、


* 表の全件数

* 項目の最大値と最小値

* 項目の一意な値の数 (ALL_TAB_COLUMNS等のDISTINCT_KEYS)


である。

索引項目をCOL1、またN、Mを定数(バインド変数ではない)としたとき、FFの値は、検索方法により下記のように決まる。

COL1 = N
FF = 1 / [COL1の一意な値の数]
COL1 > N
FF = (MAX(COL1) - N) / (MAX(COL1) - MIN(COL1))
COL1 < N
FF = (N - MIN(COL1)) / (MAX(COL1) - MIN(COL1))
COL1 between N and M
FF = (M - N) / (MAX(COL1) - MIN(COL1))

例:

表T1の項目COL1には1から10000までの10000件のデータが一意に保存され、索引付けもされている。このとき、MAX(COL1)は10000、MIN(COL1)は1、COL1の一意な値は10000件である。

COL1 = 5000の時、
FF = 1 / 10000 = 0.0001

COL1 > 9000の時、
FF = (10000 - 9000) / (10000 - 1) = 1000 / 9999 = 0.100... = 0.1

COL1 < 9000の時、
FF = (9000 - 1) / (10000 - 1) = 0.899... = 0.9

COL1 between 2000 and 4000の時、
FF = (4000 - 2000) / (10000 - 1) = 0.200... = 0.2

以上の様に、取得が予想される件数の割合を推理することができる。ただし、例に示したような値の分布が均等である項目では推理値は実際の値に非常に近い(若しくは一致する)が、値の分布が非常に偏っている場合は、推理値は実際の値と大きくかけ離れる場合がある。その場合、適当ではない実行計画を立てる等の不都合が起きる。詳細は後述。

ところで、一意な場合のFFはあまり意味をなさない。コストは結局のところ索引階層数 + 1となり、FFの影響を受けないからである。

Clustering Factor

索引のそれぞれのリーフが参照する表ブロックの総和を示す。意味としては、索引を全スキャンした際、いくつの表ブロックにアクセスする必要があるか、という事。Clustering FactorにFiltering Factorを掛けると、索引レンジスキャンした際に、いくつの表ブロックにアクセスする必要があるか、となる。

Clustering Factorは、索引対象の値が表内で分散している場合(例: 1, 2, 3, 1, 2, 3, 1, 2, 3...)、リーフが参照する表ブロックが増えるため、高くなる。逆に、索引対象の値が表内で偏っている場合(例: 1, 1, 1, 2, 2, 2, 3, 3, 3, 3...)、リーフが参照するブロックが少ないため、低くなる。言葉を変えると、同じブロックに索引対象の同じ値が入っていればいるほど、低くなる。

確認は、索引の分析をした後、ALL_INDEXES/DBA_INDEXESのCLUSTERING_FACTORを調べれば可能。

OPTIMIZER_INDEX_COST_ADJ

索引走査における、DBブロック取得コストを調整するパラメータ。1から100(パーセント)をとる。初期値は100、すなわち、調整はしない。

ある環境にて、索引走査にて取得される索引/表ブロックの多数がキャッシュされており、今後もキャッシュされ続けるとする。この時、索引走査での索引/表ブロック取得が、キャッシュヒットにより平均で通常の1/2の時間で可能であれば、索引走査のコスト算出は調整されるべきである。この場合、 OPTIMIZER_INDEX_COST_ADJを50とすることで、1/2に応じた調整が行われる。

OPTIMIZER_INDEX_COST_ADJを使った調整は、索引走査のコストを大きく変化させるため、環境によっては大きなパフォーマンス改善が可能である。ただし、システムパラメータのため、変更の際は十分注意が必要である。

続く...