More than 5 years have passed since last update.

SQLチューニングについて(2) オプティマイザのインプット情報について

Last updated at 2018-06-23Posted at 2018-06-23

オプティマイザのインプット情報とは

　DBの頭脳であるオプティマイザは、処理の実行計画を算出する際にある情報を参考にします。それがインプット情報です。
　
　インプット情報とは一種類の情報ではなく、いくつかの種類によって構成されています。
　代表的なものは以下のようなものになります。

# 各種情報について　実行計画の算出にあたり、オプティマイザが使用する各種インプット情報についてのまとめです。

統計情報は表や列、索引についての情報ですが、必ずしも最新の情報を保持しているとは限りません。
統計情報は常に更新されるものではないですが、実データは常に更新され続けるので、統計情報と実データ状況の２つのデータの間には、乖離が発生することがあります。

オプティマイザは実データではなく統計情報に基づいて実行計画を算出するため、統計情報が誤った状態であれば実行計画も誤ったものが算出されてしまいます。
したがって、SQLチューニングの際には統計情報を確認し、現在の実データとの乖離がないことを確認することが必要です。

テーブル名	概要
ALL_TAB_STATISTICS	現行のユーザーがアクセスできる表についてのオプティマイザ統計情報を示します
ALL_IND_STATISTICS	現行のユーザーがアクセスできる表の索引についてのオプティマイザ統計を示します
ALL_TAB_COLUMNS	現行のユーザーがアクセスできる表、ビューおよびクラスタの列を示します

※テーブル名クリックによりリファレンスに遷移

　アクセス対象となる表についての情報です。以下のようなものがあります。

統計情報	概要	参照元	参照元：列名
格納行数	表の行数	ALL_TAB_STATISTICS	num_rows
データベースブロック数	HMWまでのブロック数	ALL_TAB_STATISTICS	blocks
平均レコードサイズ	１行当たりの平均サイズ	ALL_TAB_STATISTICS	avg_row_len

　アクセス対象となる索引についての情報です。以下のようなものがあります。

統計情報	概要	参照元	参照元：列名
リーフブロック数	索引のブロック数	ALL_IND_STATISTICS	leaf_blocks
索引の高さ	Bツリーの索引の高さ	ALL_IND_STATISTICS	blevel
クラスタリングファクタ	索引列データの表での分散度合い	ALL_IND_STATISTICS	clustering_factor

　アクセス対象となる列についての情報です。以下のようなものがあります。

統計情報	概要	参照元	参照元：列名
列に存在する値の種類	列内の値の種類。NDVとも。	ALL_TAB_COLUMNS]	num_distinct
列内のnull数	列内のnullの数	ALL_TAB_COLUMNS]	num_nulls
ヒストグラム	列データの分布状況	ALL_TAB_COLUMNS]	histgram

　システムのハードウェア特性をもとにコスト算出を最適化するためのインプット情報です。
　通常のSQLチューニングでは、ほとんど意識する必要はありません。

## SQLテキスト実際に発行されるSQLテキストそのものです。不適切な記述方法がされている場合、適切な実行計画を作成することができませんのでテキストそのものを見直すことが必要です。

　SQLを作成するにあたり注意すべき点の代表例は、以下のようなものです。

## 初期化パラメータ　オプティマイザ関連のパラメータ設定です。データベース設計時に適切な設定を行うことが求められます。　一時的なパフォーマンス問題のために本項目を修正することは、あまり現実的ではありません。
# 実行計画作成までの流れ

　実行計画の作成はその環境により千差万別といえますが、ここでイメージを作るために一例を挙げます。
　以下のようなSQLを発行した際、オプティマイザが実行計画を作成するまでの過程イメージを記載します。

実行するSQLイメージ


select 
  A.emp_name
from
  table_A A, 
  table_B B
where
  A.id  = B.id

CPU速度やIO関連の情報を確認する (システム統計情報、初期化パラメータの取得)

物理的なレイヤでの問題として、データファイルへのアクセスなどに伴うコスト情報を取得します。
SQLがアクセスしている table_A 表と table_B 表の情報を確認する(表統計情報、列統計情報、索引統計情報の取得)

　アクセス対象となる表、列、索引についての統計情報を取得します。
二つの表に対して単一アクセスの場合のアクセスパスを決定する(各種統計情報の使用)

　取得した統計情報などをもとに、単一アクセスの際のパスとそのコストを計算します。
　table_B においては id 列を参照していますが、そのコスト計算が以下のように行われれば索引スキャンが行われます。

二つの表に対して結合方法と結合順序を決定する(各種統計情報の使用)

　表の結合についてのコスト計算を行います。
　table_A における id列の参照については以下のようなコスト計算がなされたとします。

　表の結合においてはコストの低いものをベース（外部表）にした結合を行うことが望ましいので、table_B を外部表、table_Aを内部表としたネステッドループ結合を行うことなどを決定します。

本記事を執筆するにあたり、以下の情報サイト・書籍などを参考にさせていただいています。

情報サイト

書籍