DB Magazine 2005年2月号 の特集記事「オプティマイザ」徹底研究

DB ORACLE

これを読んでいると、結構知らないことがあったので以下にメモ(知らないことだけメモしてるので（記事の内容全てを整理しているわけではありません）。
ちなみに対象DBはOracle10g(10.0.3.0)なのでお仕事で使っているOracle9iにそのまま適用できるかどうかは不明です。

列値にばらつきがある場合のヒストグラム統計取得

analyze table テーブル名 compute statistics for all columns size パケットサイズ;
ex. analyze table emp compute statistics for all columns size 20;

大量のデータを格納している場合、compute statisticsを'estimate statistics 10%'にするほうが良い。
パケットサイズの指定が何故20が適切なのかは説明を読んでもよく分からず。読み直しが必要。

db_file_multiblock_read_countは「32」が適切

これはORACLE MASTERのパフォチューでも出てきたのですが、なぜ32なのかという説明があった。

オプティマイザの判断に影響を与える初期化パラメータの例

PARALLEL_EXECUTION_ENABLED
OPTIMIZER_FEATURES_ENABLE
CPU_COUNT
ACTIVE_INSTANCE_COUNT
PARALLEL_THREADS_PER_CPU
HASH_AREA_SIZE
BITMAP_MERGE_AREA_SIZE
SORT_AREA_SIZE
SORT_AREA_RETAINED_SIZE
DB_FILE_MULTIBLOCK_READ_COUNT
PGA_AGGREGATE_TARGET
OPTIMIZER_MODE
CURSOR_SHARING
STAR_TRANSFORMATION_ENABLED
OPTIMIZER_INDEX_COLST_ADJ
OPTIMIZER_INDEX_CACHING
QUERY_REWRITE_ENABLED
QUERY_REWRITE_INTEGRITY
WORKAREA_SIZE_POLICY
OPTIMIZER_DYNAMIC_SAMPLING
STATISTICS_LEVEL
SKIP_UNUSUABLE_INDEXES
_OPTIM_PEEK_USER_BINDS（隠しパラメータ）

この中でも設定次第で効果が出そうな部分ということで以下のパラメータが取り上げられている

  • OPTIMIZER_INDEX_COLST_ADJ

索引ブロックが何％くらいキャッシュヒットすると考えるか？デフォルト0%。大きいほどオプティマイザはインデックスアクセスのコストを低く見積もる。

  • OPTIMIZER_INDEX_CACHING

索引アクセスコストを通常の何％で計算するか？デフォルト100%。小さくするほどオプティマイザは索引アクセスのコストを低く見積もる。

例.
OPTIMIZER_INDEX_CACHING=90
OPTIMIZER_INDEX_COST_ADJ=25

索引を用意しているにもかかわらず、フルスキャンの発生率が高い場合は上記２パラメータの設定が良いらしい。

表数の結合は「６個」を基準にしたほうが良いらしい。

ORACLEの表同士の結合順序数の最大は2000となっている。9iまではOPTIMIZER_MAX_PERMUTATIONSパラメータで設定変更ができたが、10gからは廃止になった。
ここで表数が6個の場合、6!=720となる。7個の場合、7!=5040となるため、最大数が6個となる。