SQL実行計画を改善する拡張統計(列グループ統計)の取得方法

クエリが実行される際、CBO（コストベースオプティマイザ）は統計情報を使用してアクセスパスコストを計算し、実行計画を生成します。

この際、同一表内の複数列に対してANDやORなどを含む複雑な述語があると、標準的に取得される統計情報では、適切な実行計画を生成できないケースがあります。

適切な実行計画が選択されない場合、クエリは期待したパフォーマンスを出せない可能性があるため、このようなケースでは拡張統計の取得が有効です。

CBOが実行計画を生成する際に考慮する要因に、セレクティビティとカーディナリティがあります。

たとえば、10000行のデータが存在するテーブルの内、「WHERE 性別 = 男」が1行を選択する場合はINDEX SCANを行い、9999行を選択する場合は索引がある場合でもFULL TABLE SCANを行います。

この1/10000行や9999/10000行（条件に合った行の割合）を セレクティビティ 、クエリによって戻される行数を カーディナリティ と言います。

CBOはカーディナリティを予測し、実行計画を生成します。列ごとのデータ分布状況（値の種類）は ヒストグラム統計 である程度把握できますが、同一の表から行の選択を行う際に、WHERE TAB1.COL1 = A AND TAB1.COL2 = B のようなクエリを実行するケースでは、CBOが正確にカーディナリティを予測できないケースがあります。

このようなケースでは列同士（COL1とCOL2）の組み合わせで統計情報を収集する、Oracle Database 11gR1から追加された「 拡張統計 」が有効です。

テーブル名"SPORTS"には以下のような10000件のデータがあります。人間が判断をする場合、COL2列とCOL3列には相関関係があり、サッカー／野球／テニスは球技、ボクシングは格闘技、将棋はマインドスポーツに分類していることがわかります。

しかし、CBOはこのような列と列の相関関係を把握できないため、たとえば、WHERE COL2 = '球技' AND COL3 = 'テニス'とした場合、TableRows * (Sel-COL2 * Sel-COL3)でカーディナリティを予測します。

つまり、実際にはテニスは球技ですので2041行が返りますが、CBOは"10000*(6069/10000 * 2041/10000)≒1239"で見積りを行ってしまい、以下のようにRowsの値が実際の行数とは大きく異なるケースがあります。

このように見積りと実際の行数が異なる場合、効率的な実行計画が生成できずにパフォーマンスダウンが発生してしまう可能性があります。

上述のような列同士に相関関係がある場合、COL2とCOL3の組み合わせ（列グループ）で統計を取得することが有効です。列グループで取得された統計を拡張統計と呼びます。

拡張統計を取得するには DBMS_STATS.CREATE_EXTENDED_STATS プロシージャ使用して列グループを作成します。たとえば、COL2とCOL3列で構成される列グループCOL2_3をSPORTS表に追加する場合は次のように実行します。

上述の例では確認のために手動で統計情報を取得しましたが、列グループを作成すると、以降は日次で実行される自動オプティマイザ統計収集でも取得の対象となります。

拡張統計が取得される列グループは、以下のSQLで確認できます。削除には DBMS_STATS.DROP_EXTENDED_STATS プロシージャを使用します。

拡張統計を取得することで、同一表内の複数列に対してANDやORを使用するようなクエリでも、CBOは適切な実行計画を生成できるようになります。

なお、Oracle Database 12cからはオプティマイザの機能が強化されているため、拡張統計を取得していなくても適切な実行計画が生成されるケースがあります。

動的統計 （動的サンプリング）をレベル11に設定すると（デフォルトは2）オプティマイザが必要だと判断したタイミングで自動的に統計が取得されるようになり、 SQL計画ディレクティブ を有効にすることで（デフォルトで有効）、自動的に列グループを考慮した実行計画を生成してくれることがあります。

これらの機能により以前のリリースよりも柔軟にオプティマイザは対応してくれますが、基本的にはよく使われる列グループに対しては明示的に拡張統計を取得することをおすすめします。

筆者情報

• 大野 高志

サービス事業部 サポートセンター

2007年にアシスト入社後、Oracle Databaseのサポート業務に従事、現在はサポートの傍ら、未解決のトラブルを一つでも多く減らせるよう、サポートセンターに蓄積されているノウハウを社内外に伝える活動を行っている。