Database Support Blog

  • 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とJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。
文中の社名、商品名等は各社の商標または登録商標である場合があります。

関連している記事

  • Oracle Database
  • Exadata
2021.08.18

Exadataとは?3分で押さえるExadataのポイント

Exadataは2008年に初代モデルがリリースされ、世界中でデータベース基盤として活用されています。オンプレミス以外にクラウドでも利用可能で、新バージョンがリリースされる度に進化を遂げています。今回はExadata紹介1回目としてExadataをご存知でない方向けに3分で概要をお伝えします。

  • Oracle Database
2021.06.04

現場経験者が語る!21c以降へのバージョンアップで押さえておくべき仕様情報

Oracle Database 21c の登場以降、こんな相談が増えてきました。今回は21cの仕様変更や廃止機能等、主要なものをピックアップしてご紹介します。

  • Oracle Database
2021.05.13

Oracle Database 21c新機能から読み解く!コンバージドデータベースの世界と機能強化の傾向とは

今回は Oracle Database 21cの新機能情報をもとに、「機能強化の傾向」や「注力分野」等をアシスト独自の視点で探ってみました。今後のバージョンアップや移設計画に組み込める機能情報をお探しの方には必見です。

ページの先頭へ戻る