LoginSignup
4
5

More than 1 year has passed since last update.

@fuzymon(Kurt)

Oracle DB:実行計画に影響を与える者たち

Last updated at Posted at 2022-04-14

Oracle DBのバージョンを変えたことがある方は、実行計画が急に変わって困ったことが１度はあると思います。
CBO(Cost Base Optimazer)で実行計画に影響を与える機能をまとめてみました。
内容は薄いので詳細は各機能を個別に調査してみてください。

基本:SQLの実行の流れ

  1. SQL解析(Parse)
    1.1. 構文チェック
    　SQL形式チェック

    1.2. セマンティクス・チェック
    　指定した表やカラムなどのオブジェクトが存在するか

    1.3. 共有プール・チェック
    　共有プールのV$SQLにアクセスし、
    　実行するSQLがすでに解析済(実行計画が存在する)かチェックをする。
    　既に解析済であれば、ソフトパース(実行計画の再利用)を行い、
    　そうでなければハードパースを行う。

  2. オプティマイズ
    　SQLをハードパースし、コストベースで最適な実行計画を作成する。
    　解析された実行計画は共有プールのV$SQLに保存される。

  3. SQLの実行

統計情報

ハードパース時作成される実行計画のインプットとなる統計情報は以下のようなものがある。

  • 表統計(DBA_TAB_STATISTICS)：行数、ブロック数、平均行長
  • 列統計(DBA_TAB_COL_STATISTICS)：列値の種類、NULLの数、データ分布
  • 索引統計(DBA_IND_STATISTICS)：リーフ・ブロック数、階層数、クラスタ化係数
  • システム統計(AUX_STATS$)：CPUパフォーマンスと使用率、I/Oパフォーマンスと使用率

統計情報の収集方法は以下の6つ。

統計情報（表統計、索引統計）は１０％を超えるレコード更新がされると無効（失効）される。この状態でハードパースが行われると性能障害になるリスクが高まります。
大量にデータが更新される処理のあとには、統計情報を再取得することを検討してください。

最適な実行計画を目指す機能たち

基本は統計情報を元に実行計画を作成しますが、実データと乖離のある統計情報による不適切な実行計画が作成されないようにOracleには便利な機能(時に問題児)があります。

バインド変数系

バインド変数化されたSQLは範囲条件の場合は5%のデータがあるという判定が行われindexを採用する実行計画になることが多いらしいです。(情報が古いので19cではわかりません。)
門外不出のOracle現場ワザ

バインド変数化されたSQLに対して、更によい実行計画を形成できるような機能があります。

  • バインドピーク機能(Bind Peek)
    バインド変数に値を入れた状態でハードパースする。
    バインド変数の値によって取得件数が大きく異なる場合、SQL実行が遅くなるリスクがある。
    設定 : _OPTIM_PEEK_USER_BINDS

  • 適応カーソル共有(Adaptive Cursor Sharing)
    バインドピークの問題を緩和するための機能。
    バインド変数に設定された値を監視し、必要に応じて新しい実行計画を追加作成する
    設定 : _OPTIMIZER_ADAPTIVE_CURSOR_SHARING

  • 動的統計の拡張
    11.2.0.4から有効
    OPTIMIZER_DYNAMIC_SAMPLINGに"11"が追加。11のとき、動的統計を収集するかどうかをOracleが自動的に決定します。
    Oracle® Databaseパフォーマンス・チューニング・ガイド
    11gリリース2 (11.2)

  • 適応問い合わせ最適化(Adaptive Query Optimization)
    適応計画と自動再最適化の機能
    12.1.0.1から有効
    適応計画
    ハードパース時に対象表の統計情報の一部（行数等）を取得し、統計情報と乖離があれば統計情報に基づいた実行計画からサンプリングから得た実行計画に切り替える。
    設定値：OPTIMIZER_ADAPTIVE_PLANS
    自動再最適化
    SQL の実行時に収集された統計情報が乖離している場合、次のソフトパース時に新しく収集された統計情報を使用して実行計画を作成
    後に説明するカーディナリティ・フィードバックの機能も内包している。
    設定値：OPTIMIZER_ADAPTIVE_STATISTICS
    徹底解説！Oracle Database 12cのすべて Vol.5

  • カーディナリティ・フィードバック(Cardinality Feedback)
    11.2.0.1から有効
    実行計画を生成するオプティマイザのインプット情報に、実データのカーディナリティ（データの種類の多さ）情報を加えることによって、より最適な実行計画を生成する機能。
    設定値：_optimizer_use_feedback
    Output Place : Cardinality Feedback（カーディナリティ　フィードバック)とは

SQL編集関係

ハードパース時により早く実行可能なSQLに自動変換してくれる機能たち
これらは統計情報と実データの乖離には依存せず、単純にSQLチューニングしてくれるイメージ。

バージョンアップで実行計画が変わってしまったら

状況に応じていずれかの対策を取る。

1. Oracleのオプティマイザの一部機能だけOFF/ONにする。

オプティマイザのどの機能で実行計画への影響が出ているか分かれば、その機能を前の状態に戻す。
ただし、ON/OFF設定が可能な機能に限る。

2. 特定SQLのオプティマイザの一部機能だけOFF/ONにする

#1でシステム全体の機能設定を変更できない場合は、SQLのヒント句で機能をON/OFFすることで、対象を特定SQLだけに絞る。

3. システムのオプティマイザのバージョン設定を戻す

どのオプティマイズの機能が影響しているかわからないが、バージョン戻せば直る場合

オプティマイザのバージョンの確認
SHOW PARAMETER optimizer_features_enable
オプティマイザのバージョンの変更
ALTER SYSTEM SET OPTIMIZER_FEATURES_ENABLE='12.1.0.2';
4. 特定SQLのオプティマイザのバージョン設定だけを戻す

#3でシステム全体の機能設定を変更できない場合は、SQLのヒント句で機能をON/OFFすることで、対象を特定SQLだけに絞る。

オプティマイザのバージョンの変更
/*+ OPTIMIZER_FEATURES_ENABLE('12.1.0.2') */
5. 実行計画の固定化

Sqlplus等の外部コンソールから元の実行計画で動くSQLを発行する。そこで出来た、実行計画をSPMで固定化する。
共有プールから実行計画がキャッシュアウトしたり、キャッシュクリアされた場合は再度同じことを実施する必要があるため、運用には向かない。暫定対策レベル。

Qiita:Oracle12.2でSPMを使用して実行計画を固定化する

ハードパースが発生しなくても動作する機能「カーディナリティ・フィードバック」などが原因だと、この対策は無効です。

6. SQLの修正、ヒント句で元の実行計画になるように頑張る。

工数大。。

バージョンアップ前に

Oracleは主に従来の統計情報運用では性能が発揮できないケースに対する機能を拡充しているため、統計情報の取得がうまく出来ていないテーブルでSQLが安定しないものがあれば、そのSQLだけ機能をONにした方が良さそう。
バージョンアップ前でOracleのオプティマイザの新機能に必要性を感じないのであれば、オプティマイザのバージョンはバージョンアップ前の設定にするのが安全。

4
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
5