Database Support Blog

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

  • Oracle Database
2016.07.14

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

拡張統計(列グループ統計)の取得方法

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

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

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

なぜ適切な実行計画が選択されないのか?

セレクティビティ(Selectivity)とカーディナリティ(Cardinality)

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から追加された「 拡張統計 」が有効です。

CBOは列同士の相関関係を考慮しない

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

SPORTS表COL2列の内訳

▲SPORTS表COL2列の内訳

SPORTS表COL3列の内訳

▲SPORTS表COL3列の内訳

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

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

CBOは列同士の相関関係を考慮しない

▲CBOは列同士の相関関係を考慮しない

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

拡張統計を取得する

上述のような列同士に相関関係がある場合、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のサポート業務に従事、現在はサポートの傍ら、未解決のトラブルを一つでも多く減らせるよう、サポートセンターに蓄積されているノウハウを社内外に伝える活動を行っている。

関連している記事

  • Oracle Database
2016.12.09

【Oracle Database】2016年にサポートにお問い合わせをいただいたORAエラー TOP5

2016年にアシストのサポートセンターにお問い合わせいただいたORAエラーのTOP5をご紹介します。

  • Oracle Database
2016.11.16

【Oracle Database】パフォーマンスダウンの原因追求に必要な情報取得サンプルスクリプト

パフォーマンスダウンやCPU/メモリ負荷高騰時には、セッション/プロセス単位で情報を確認する必要があります。原因追求に必要な情報を取得できるサンプルスクリプトを提供します。

  • Oracle Database
2016.10.27

【Oracle Database】うるう秒の対応

Oracle Databaseでのうるう秒(閏秒)の対応方法について解説します。2017年元旦にトラブルとならないよう、準備しましょう。

アシストサポートセンターのご紹介 Oracle Database研修

ページの先頭へ戻る