トランケートの非同期グローバル索引メンテナンスについて [SQL・DDL]
以前、「トランケートについて知っておくべきこと」の中でグローバル索引のメンテナンス(update global indexesオプション）について書いた。当時11gR2を使っていたため、12cの新機能である「非同期グローバル索引メンテナンス(Asynchronous Global Index Maintenance)」については記載しなかった。今日はこの機能についてメモを残しておく。残念ながら実機で検証した訳ではないため、あくまで机上で調べた内容に基づき、推測した部分がある点、留意頂きたい。
11gR2までは、truncate/drop partition ... update global indexを発行した際、グローバル索引のメンテナンスが完了するまではコマンドの完了を待たなければならなかった。この処理は、グローバル索引のリーフブロックの不要となった各ROWIDエントリを削除（削除フラグを立てる）する処理を含むため、非常に時間がかかることがあった。一方、12cR1からはトランケート時のグローバル索引メンテナンスは「非同期」に実行されるのがデフォルトの挙動となった。つまり、truncate partition ... update global indexを発行した際、索引のメタデータのみメンテナンスして実際の索引メンテナンスを「先送り」にするのである。このため、トランケート処理は即時に完了し、さらに、グローバル索引はUSABLEのままなので、通常通りにクエリによる正しい問い合わせ結果を得ることができる。実際のグローバル索引のメンテナンスは、以下の方法で実行できる。
(1)SYS.PMO_DEFERRED_GIDX_MAINT_JOBジョブ
・・・索引クリーンアップの自動スケジューラジョブ(毎日2:00に実行)
(2)DBMS_SCHEDULER.RUN_JOB
・・・上記(1)を手動で起動
(3)DBMS_PART.CLEANUP_GIDXプロシージャ
・・・必要な索引すべて行う、または特定の索引だけ指定してクリーンアップを行う
(4)ALTER INDEX REBUILD [PARTITION]コマンド
・・・索引全体をリビルドする（従来の方法）
(5)ALTER INDEX [PARTITION] COALESCE CLEANUPコマンド
・・・索引のorphanedエントリをクリーンアップする
以上が概略である。さて、この非同期グローバル索引メンテナンス、どのような仕組みで実現されているのだろうか考えてみよう。
まず、truncate partitionした後のグローバル索引の状態であるが、これは一切メンテナンスがされない。つまり、索引のリーフブロックに記録されたROWIDは、そのまま残っている。したがって、トランケートされたパーティション内の行を参照していたROWIDは、参照先がない状態、すなわち親なし(orphaned)エントリが含まれる状態となる。グローバル索引にorphandエントリが含まれるかは、USER_INDEXES.ORPHANED_ENTRIESで確認できる（YESなら含まれる）。このような状態で一体クエリはどのように正しい問い合わせ結果を返すことができるのだろうか。
その答えを知るには、以下について理解しておく必要がある。
まず、truncate partitionした後のメタデータの更新とは、具体的にはグローバル索引のオブジェクトIDと、トランケートしたオブジェクトIDの関連を保持することである。これは、sys.index_orphand_entry$で確認することができる。indexobj#にグローバル索引のオブジェクトID、tabpartobj#にトランケート対象のオブジェクトIDが格納される。
次に、ROWIDについてである。ROWIDはレコードを一意に識別するIDであり、18バイトで構成される。先頭から、6バイトがオブジェクトID、次の3バイトがデータファイル番号、次の6バイトがブロック番号、次の3バイトが行番号という具合である。したがって、グローバル索引のリーフブロックに格納されるROWIDを見れば、それがどのパーティションに属しているかわかるのである。
そして、TBL$OR$IDX$PART$NUM関数である。この関数の仕様は非公開であるが、引数に表の名前とROWIDを与えると、そのROWIDがorphanedかどうかを判別することができる（orphandでなければ1を返す）ようだ。これは、おそらくROWIDからその参照先のパーティションのオブジェクトIDを識別し、それを上記のsys.index_orphand_entry$の情報からorphanedかどうかを判別していると思われる。
さて、上記を踏まえた上で、非同期グローバル索引メンテナンスが行われたグローバル索引を用いたレンジスキャンがどのように動くか考えてみよう。まず、クエリは検索条件からグローバル索引のブランチを通り、リーフブロックに到達し、そこから横にリーフブロックをたどる動きをする。この際、リーフブロックに格納されるROWIDを1つずつTBL$OR$IDX$PART$NUM関数を用いたfilterでorphanedエントリを取り除く（実行計画のpredicateにはfilterが現れる）。そして得られたROWIDの集合について、パーティションへアクセスしそれぞれに対応する行を取得する。実際は、USER_INDEXES.ORPHANED_ENTRIESがNOの場合は、このfilter処理は不要といったロジックは組み込まれているのだろう。
このアーキテクチャでは、orphanedエントリが多くなればなるほど、filter処理が増えることとなる。そのため、返却されるレコード数に対して、必要となる処理時間が延びていくことが想定される。また、INSERT時にリーフブロックが再利用されるのはUNIQUE制約のあるときのみであるらしい（参考１）ため、いくらパーティショントランケートしても、グローバル索引は小さくならず、むしろレコードの増加に伴って単調に増加していってしまう可能性がある。これを避けるために、上記5つのいずれかの方法でorpahnedエントリを定期的に綺麗に削除しておく必要があるだろう。ある意味、非同期による副作用といえる。
実際、Oracleのコミュニティでも以下のスレッド（2018.11)で12cのグローバル索引が肥大化する問題についての議論がされていた。SYS.PMO_DEFERRED_GIDX_MAINT_JOBによる自動メンテナンスが走っているにもかかわらず、である。
Index size of partitioned table is growing
今後、同様の事象があちこちで発生してくるのかもしれない。11gからのハード更改でこの挙動が問題となる場合は、従来の動作にするために"_fast_index_maintenance"=falseとすることも一考の価値があるかもしれない。ただし、隠しパラメータなので、利用についてはサポートに確認した方がよいだろう。
以上
◆参考文献
[1]12c Asynchronous Global Index Maintenance Part II by Richard Foote
[2]12cマニュアル Database VLDB and Partitioning Guide, Asynchronous Global Index Maintenance for Dropping and Truncating Partitions
[3]津島博士のパフォーマンス講座 第43回 パーティションについて（３）
[4]How to Drop/Truncate Multiple Partitions in Oracle 12C (ドキュメントID 1482264.1)
11gR2までは、truncate/drop partition ... update global indexを発行した際、グローバル索引のメンテナンスが完了するまではコマンドの完了を待たなければならなかった。この処理は、グローバル索引のリーフブロックの不要となった各ROWIDエントリを削除（削除フラグを立てる）する処理を含むため、非常に時間がかかることがあった。一方、12cR1からはトランケート時のグローバル索引メンテナンスは「非同期」に実行されるのがデフォルトの挙動となった。つまり、truncate partition ... update global indexを発行した際、索引のメタデータのみメンテナンスして実際の索引メンテナンスを「先送り」にするのである。このため、トランケート処理は即時に完了し、さらに、グローバル索引はUSABLEのままなので、通常通りにクエリによる正しい問い合わせ結果を得ることができる。実際のグローバル索引のメンテナンスは、以下の方法で実行できる。
(1)SYS.PMO_DEFERRED_GIDX_MAINT_JOBジョブ
・・・索引クリーンアップの自動スケジューラジョブ(毎日2:00に実行)
(2)DBMS_SCHEDULER.RUN_JOB
・・・上記(1)を手動で起動
(3)DBMS_PART.CLEANUP_GIDXプロシージャ
・・・必要な索引すべて行う、または特定の索引だけ指定してクリーンアップを行う
(4)ALTER INDEX REBUILD [PARTITION]コマンド
・・・索引全体をリビルドする（従来の方法）
(5)ALTER INDEX [PARTITION] COALESCE CLEANUPコマンド
・・・索引のorphanedエントリをクリーンアップする
以上が概略である。さて、この非同期グローバル索引メンテナンス、どのような仕組みで実現されているのだろうか考えてみよう。
まず、truncate partitionした後のグローバル索引の状態であるが、これは一切メンテナンスがされない。つまり、索引のリーフブロックに記録されたROWIDは、そのまま残っている。したがって、トランケートされたパーティション内の行を参照していたROWIDは、参照先がない状態、すなわち親なし(orphaned)エントリが含まれる状態となる。グローバル索引にorphandエントリが含まれるかは、USER_INDEXES.ORPHANED_ENTRIESで確認できる（YESなら含まれる）。このような状態で一体クエリはどのように正しい問い合わせ結果を返すことができるのだろうか。
その答えを知るには、以下について理解しておく必要がある。
まず、truncate partitionした後のメタデータの更新とは、具体的にはグローバル索引のオブジェクトIDと、トランケートしたオブジェクトIDの関連を保持することである。これは、sys.index_orphand_entry$で確認することができる。indexobj#にグローバル索引のオブジェクトID、tabpartobj#にトランケート対象のオブジェクトIDが格納される。
次に、ROWIDについてである。ROWIDはレコードを一意に識別するIDであり、18バイトで構成される。先頭から、6バイトがオブジェクトID、次の3バイトがデータファイル番号、次の6バイトがブロック番号、次の3バイトが行番号という具合である。したがって、グローバル索引のリーフブロックに格納されるROWIDを見れば、それがどのパーティションに属しているかわかるのである。
そして、TBL$OR$IDX$PART$NUM関数である。この関数の仕様は非公開であるが、引数に表の名前とROWIDを与えると、そのROWIDがorphanedかどうかを判別することができる（orphandでなければ1を返す）ようだ。これは、おそらくROWIDからその参照先のパーティションのオブジェクトIDを識別し、それを上記のsys.index_orphand_entry$の情報からorphanedかどうかを判別していると思われる。
さて、上記を踏まえた上で、非同期グローバル索引メンテナンスが行われたグローバル索引を用いたレンジスキャンがどのように動くか考えてみよう。まず、クエリは検索条件からグローバル索引のブランチを通り、リーフブロックに到達し、そこから横にリーフブロックをたどる動きをする。この際、リーフブロックに格納されるROWIDを1つずつTBL$OR$IDX$PART$NUM関数を用いたfilterでorphanedエントリを取り除く（実行計画のpredicateにはfilterが現れる）。そして得られたROWIDの集合について、パーティションへアクセスしそれぞれに対応する行を取得する。実際は、USER_INDEXES.ORPHANED_ENTRIESがNOの場合は、このfilter処理は不要といったロジックは組み込まれているのだろう。
このアーキテクチャでは、orphanedエントリが多くなればなるほど、filter処理が増えることとなる。そのため、返却されるレコード数に対して、必要となる処理時間が延びていくことが想定される。また、INSERT時にリーフブロックが再利用されるのはUNIQUE制約のあるときのみであるらしい（参考１）ため、いくらパーティショントランケートしても、グローバル索引は小さくならず、むしろレコードの増加に伴って単調に増加していってしまう可能性がある。これを避けるために、上記5つのいずれかの方法でorpahnedエントリを定期的に綺麗に削除しておく必要があるだろう。ある意味、非同期による副作用といえる。
実際、Oracleのコミュニティでも以下のスレッド（2018.11)で12cのグローバル索引が肥大化する問題についての議論がされていた。SYS.PMO_DEFERRED_GIDX_MAINT_JOBによる自動メンテナンスが走っているにもかかわらず、である。
Index size of partitioned table is growing
今後、同様の事象があちこちで発生してくるのかもしれない。11gからのハード更改でこの挙動が問題となる場合は、従来の動作にするために"_fast_index_maintenance"=falseとすることも一考の価値があるかもしれない。ただし、隠しパラメータなので、利用についてはサポートに確認した方がよいだろう。
以上
◆参考文献
[1]12c Asynchronous Global Index Maintenance Part II by Richard Foote
[2]12cマニュアル Database VLDB and Partitioning Guide, Asynchronous Global Index Maintenance for Dropping and Truncating Partitions
[3]津島博士のパフォーマンス講座 第43回 パーティションについて（３）
[4]How to Drop/Truncate Multiple Partitions in Oracle 12C (ドキュメントID 1482264.1)
SQLチューニングについて思うこと [オプティマイザ]
OracleのDBAとして永遠の課題と思っていることの一つにSQLの性能をどう担保していくべきか、という話があると思っている。今回は日ごろ感じている出口のないこの問題と、最近手にしたSQLチューニングの本でとても良い本があったので、紹介しておきたい。
１．はじめに
運用中のシステムでSQLの遅延が発生した際、実行計画の変化が原因のケースがある。多くは統計情報が最新化され、ハードパースが走った際にオプティマイザが実行計画を変更してしまう、というケースである。オプティマイザが良かれと思って実行計画を変更する訳であるが、不幸にも極端に性能が悪くなることが多々発生する。大規模システムにおいて、性能が極端に悪化するリスクは避けなければならない。このようなことから、多くの場合、以下の方法を複合的に採用することにより安定的な性能（すなわち変動しない実行計画）を（完全ではないにしろ）実現している。
(1)SQLにヒントを埋め込み、実行計画を固定化する
(2)統計情報の変動を抑えるために、統計をロックする
(3)SPM等の実行計画を管理する手法を使う
２．ヒントについて
説明するまでもないが、ヒントはSQL文の中に実行計画を指定するためのコメントを入れることにより、オプティマイザが設計者の意図したとおりの実行計画を選択し易いようにする構文である。多くの場合、駆動表や結合順、結合方法、使用する索引、パラレル度、などを指定するために利用される。これにより、統計情報がどのような状態にかかわらず、意図した実行計画に固定することが可能となる。
一見、これにより問題は解決するようであるが、この手法を積極的に採用するには以下の点で議論がある
・SQLにヒントを入れ、維持し続けるためのコスト（DBA体制の維持費）の問題
・SQLに非機能のロジックが埋め込まれることによるAP保守性低下の懸念（アップグレード時にヒント見直しなど）
・統計情報が適切に取得されていれば、オプティマイザはヒントなしでも良い実行計画が選択できる、という考え方の排除（Oracleの製品開発投資に対し高いライセンスを払っている訳で、それを活用していないのではないか）
従って、ヒントを入れるにしても実際はすべてのSQLにヒントを入れるのではなく、重要なSQLや運用中遅延が発生したSQLに限りいれているというところが多いのが実情だろう。
３．統計情報のロックについて
一方、統計をロックにより実行計画を安定化させる手法は、ヒントより間接的に実行計画を安定化させる方法である。これもシステムによってさまざまな方法があるが、概ね以下の３通りの手法があるだろう。
・ある時点の統計でロックする（特にテンポラリのテーブルなどゆらぎの大きいものなど）
・適当な値を設定（dbms_stats.set_table_stats等）しロックする
・NULL統計（デフォルト統計）としロックする
統計ロックは、ある時点の統計で統計情報をロックし、自動統計取得対象外とする手法である。これにより、統計情報がかわらないので、当然、オプティマイザの挙動は安定する。また、統計情報の取得にかかわるシステムの負荷を少なくできることは利点である。しかし、少し考えるといろいろな懸念があることがわかる。どの時点の統計でロックすべきか（本番環境の統計を正とすべきか、性能試験で実績の積んだテストデータの統計にするのか）。統計のリリース方法はどうするのか。カラム追加時の統計はどうするのか。日付や数値等のカラム統計は、ロックしてしまうと最大・最小値などのヒストグラムが実情に合わなくなるが、はたして問題ないのか、など懸念は尽きない。
適当な値を設定する方法は、NUM_ROWSやAVG_ROW_LENGTH,BLOKCS等をdbms_statsで設定する方法である。これは比較的容易に統計を設定することができ、かつ、DDLと一緒にリリースできるため、管理の容易である。しかし、どこまで設定するべきか、という議論となると、答えは難しい。結局、オプティマイザがどこまで設定すれば良いという答えは出ないため、管理の容易性を優先し、表の主要な統計のみを設定するにとどめるしかない。しかも、この手法はそもそもOracle非推奨である（dbms_statsパッケージの説明に記載されている）。
NULL統計は統計がNULL（欠落）しているときのデフォルトの挙動をあえて利用する手法である。実は統計NULLの場合は、テーブルのセグメントサイズ（HWM下ブロックサイズ）をベースに1行100バイトと仮定してNUM_ROWSを推定する。セレクティビティも同様に等価は0.01（索引アクセスなら0.004）、等価以外は0.05（索引アクセスなら0.009）等、決めうちの値をもとに推定するので、それなりの精度でオプティマイザは動いてくれる。しかし、セグメントサイズは動的に変更されるので、その意味でNULL統計ロックは統計をロックできていない。したがって、ハードパースのタイミングや再起動のタイミングで実行計画が変更してしまうことは十分起こりうる手法である。また、今は12cのマニュアルからNULL統計の記載がなくなった。内部的な挙動は今も同じらしいが、NULL統計の挙動についてサポートに問い合わせてもまともな回答が返ってこないことから、基本的に使わない方が良いだろう。
４．SPMについて
SPM（SQL Plan Management）とはSQLの実行計画を管理するための機能で、Oracle11gより提供されている（それまではプラン・スタビリティという機能があった）。SPMでは、SQLの実行計画を事前に保存（SQL計画ベースライン）しておき、SQLの実行計画が変動しないようにすることができる。
SPMの実態は内部的にはヒントの集合であり、それがSQL（正確にはSQL_ID）毎にOracle内部（SYSAUX）に格納されているに過ぎない。オプティマイザはハードパースの際に、このベースラインの実行計画とハッシュ値で比較し、一致していなければ格納されたヒントで再度ハードバースし、ベースラインの実行計画で実行する。このため、SPMではハードパースが２回実行されることとなる。
APに手の入れられない状況、例えばシステムの運用開始の直前や、パッケージ製品の内部SQLのチューニングであれば、SPMを用いて局所的な対処を行えることが有用であろう。逆に、システムの全業務SQLに対してSQL計画ベースラインで管理しようとすると、ヒントの代わりにこのベースラインを維持管理していく必要があるだろう。
５．結局どうしたらよいのか
Oracleのセミナ等では結局ケースバイケースです、といった結論で終わることが多い。しかし、安定した性能、ひいてはDBAの生活の質の向上を考えるなら、おのずと答えが見えてくる。SQL性能にまつわる多くの問題は実行計画の変動であることを考えれば、その変動する要因を極力取り除くことが有効であると考える。それは以下①～⑥の流れで発生することから、それぞれに対してブロックするような対策を考えてゆけばよい。
①データの変動→許容する
②自動統計→許容する
③統計情報が変更される
→上で述べた統計ロックの手法を採用し変更されないようにする
※特に一時テーブルや超大規模なテーブル等
④ライブラリキャッシュからエージアウト
→バインド変数を使うことを徹底する（コーディング規約等）
→共有プールに十分なメモリを確保する（shared_pool_size）
→カーソルをPIN(dbms_shared_pool.keep)しage outを防ぐ
⑤ハードパースが走る
→ヒントを使い、実行計画を明示的に指定する
→dynamic samplingは無効化し、サンプル統計取得による実行計画の変動を抑える
→12c新機能である適応問い合わせ最適化は停止(OPTIMIZER_ADAPTIVE_REPORTING_ONLY=TRUE)し、実行計画が変更されないようにする
→ハードパースが走っても実行計画が変動しないようSPMを採用する
⑥実行計画が変わる
６．最後はやはりヒント
しかし、上記のように対策したとしても、最後のチューニングはやはりヒントであろう。ヒントはSQLに埋めるため、局所的な対処が可能（他への影響をほとんど考慮する必要ない）ことから、唯一無二のチューニング方法となることが多い。また、ヒントには実行計画をかなり細かく記載することができるため、非常に強力である。しかし、それを正確に理解し、SQLに入れられるレベルにはかなり個人差があることを理解しておかなければならない。現場でいろいろなヒントを見るが、意図が不明なもの、中には間違ったものまであったりする（恐らくわかってはいるものの、そのためにAPを修正する訳にもいかず、ずっと残ってしまっている状態なのだろう）。
私の経験上、多くのSQLのチューニングで使用するのは、実行順を指定するORDERED,LEADING、結合方法を指定するUSE_NL, USE_HASH、索引を指定するINDEX、フルスキャンを指定するFULLくらいである。ヒントの使い方自体はそれ程難しいことはない。その一番難しいところは、あるべき実行計画をイメージする部分である。業務（APの開発者）であれば、直感的にどのテーブルから、どのような順番でアクセスすべきかイメージできるだろう。しかし、SQLと実行計画だけ渡されてチューニングするとなると、遅い原因はわかっても、あるべき結合順をイメージするのは難しいため、結局思考錯誤で対応することが多かった。
そんな中で出会ったのが、Kevin Meade氏のOracle SQL Performance Tuning and Optimization: Its all about the Cardinalitiesである。カーディナリティからSQLの結合順を導くFRP法(Filtered Rows Percentage Method)について記している。これは、クエリの実行計画の中で、常に処理する行数をできる限り少なく制御する、つまり、別の言葉でいうと、クエリの実行段階のできるだけ早い段階で、より多くの行を取り除く、という方法である。
基本的に、表の結合順をFilterによる絞り込み率が大きい順に並ぶように実行計画をチューニングするという考え方である。例えば3つの表があれば、結合順は6通りあるが、各表の絞り込み率が大きい（より絞り込みができる）表を駆動表に、その後は絞り込みができる順に表を結合していく。もちろん、実際は結合可能な表の順番に制約があるのでその制約を加味した上での話である。
この本の５章にヒントについての記載があるが、この思想が興味深い。そもそもヒントはチューニングの手段として考えるべきではなく、他の方法がどうしても有効でない場合に使うべきである、というのだ。つまり、ヒントは実行計画を強制するものではなく、オプティマイザが最適な実行計画を導き出すために何が問題で何を変更する必要があるのかを解析するためのツールとして認識すべきである、という見方である。また、SQLチューニングの３つの要素～カーディナリティ、駆動表と結合順、結合方法～を制御するため、ヒントの中で最も重要なもの３つは以下であると述べている。
・CARDINALITY / OPT_ESTIMEATE
・OREDERD / LEADING
・NO_INDEX
なお、本書の一章は無償で公開されている。ここに著者の提唱するFRPの具体的なやり方が記載されているので、一読の価値はあるだろう。また、本書の中で利用されているスクリプトもここからダウンロードできる。SQLチューニングの際に取得すべき情報は、チューニングを依頼する際にこれだけはそろえてほしい情報が記載されている。現場ではあいまいな情報だけで無茶な依頼が来ることもあるので、こういった基本事項を明文化しておくことは大切なことだと感じる。
http://www.orafaq.com/forum/m/624976
Oracle SQL Performance Tuning and Optimization v26 chapter 1.pdf
→本書の１章 駆動表と結合順 が読めます
scripts.rar
→本書の中で利用されているスクリプト一式
Information needed in Tuning a SQL Query.docx
→SQLチューニングの際に取得すべき情報
以上
◆参考
１．はじめに
運用中のシステムでSQLの遅延が発生した際、実行計画の変化が原因のケースがある。多くは統計情報が最新化され、ハードパースが走った際にオプティマイザが実行計画を変更してしまう、というケースである。オプティマイザが良かれと思って実行計画を変更する訳であるが、不幸にも極端に性能が悪くなることが多々発生する。大規模システムにおいて、性能が極端に悪化するリスクは避けなければならない。このようなことから、多くの場合、以下の方法を複合的に採用することにより安定的な性能（すなわち変動しない実行計画）を（完全ではないにしろ）実現している。
(1)SQLにヒントを埋め込み、実行計画を固定化する
(2)統計情報の変動を抑えるために、統計をロックする
(3)SPM等の実行計画を管理する手法を使う
２．ヒントについて
説明するまでもないが、ヒントはSQL文の中に実行計画を指定するためのコメントを入れることにより、オプティマイザが設計者の意図したとおりの実行計画を選択し易いようにする構文である。多くの場合、駆動表や結合順、結合方法、使用する索引、パラレル度、などを指定するために利用される。これにより、統計情報がどのような状態にかかわらず、意図した実行計画に固定することが可能となる。
一見、これにより問題は解決するようであるが、この手法を積極的に採用するには以下の点で議論がある
・SQLにヒントを入れ、維持し続けるためのコスト（DBA体制の維持費）の問題
・SQLに非機能のロジックが埋め込まれることによるAP保守性低下の懸念（アップグレード時にヒント見直しなど）
・統計情報が適切に取得されていれば、オプティマイザはヒントなしでも良い実行計画が選択できる、という考え方の排除（Oracleの製品開発投資に対し高いライセンスを払っている訳で、それを活用していないのではないか）
従って、ヒントを入れるにしても実際はすべてのSQLにヒントを入れるのではなく、重要なSQLや運用中遅延が発生したSQLに限りいれているというところが多いのが実情だろう。
３．統計情報のロックについて
一方、統計をロックにより実行計画を安定化させる手法は、ヒントより間接的に実行計画を安定化させる方法である。これもシステムによってさまざまな方法があるが、概ね以下の３通りの手法があるだろう。
・ある時点の統計でロックする（特にテンポラリのテーブルなどゆらぎの大きいものなど）
・適当な値を設定（dbms_stats.set_table_stats等）しロックする
・NULL統計（デフォルト統計）としロックする
統計ロックは、ある時点の統計で統計情報をロックし、自動統計取得対象外とする手法である。これにより、統計情報がかわらないので、当然、オプティマイザの挙動は安定する。また、統計情報の取得にかかわるシステムの負荷を少なくできることは利点である。しかし、少し考えるといろいろな懸念があることがわかる。どの時点の統計でロックすべきか（本番環境の統計を正とすべきか、性能試験で実績の積んだテストデータの統計にするのか）。統計のリリース方法はどうするのか。カラム追加時の統計はどうするのか。日付や数値等のカラム統計は、ロックしてしまうと最大・最小値などのヒストグラムが実情に合わなくなるが、はたして問題ないのか、など懸念は尽きない。
適当な値を設定する方法は、NUM_ROWSやAVG_ROW_LENGTH,BLOKCS等をdbms_statsで設定する方法である。これは比較的容易に統計を設定することができ、かつ、DDLと一緒にリリースできるため、管理の容易である。しかし、どこまで設定するべきか、という議論となると、答えは難しい。結局、オプティマイザがどこまで設定すれば良いという答えは出ないため、管理の容易性を優先し、表の主要な統計のみを設定するにとどめるしかない。しかも、この手法はそもそもOracle非推奨である（dbms_statsパッケージの説明に記載されている）。
NULL統計は統計がNULL（欠落）しているときのデフォルトの挙動をあえて利用する手法である。実は統計NULLの場合は、テーブルのセグメントサイズ（HWM下ブロックサイズ）をベースに1行100バイトと仮定してNUM_ROWSを推定する。セレクティビティも同様に等価は0.01（索引アクセスなら0.004）、等価以外は0.05（索引アクセスなら0.009）等、決めうちの値をもとに推定するので、それなりの精度でオプティマイザは動いてくれる。しかし、セグメントサイズは動的に変更されるので、その意味でNULL統計ロックは統計をロックできていない。したがって、ハードパースのタイミングや再起動のタイミングで実行計画が変更してしまうことは十分起こりうる手法である。また、今は12cのマニュアルからNULL統計の記載がなくなった。内部的な挙動は今も同じらしいが、NULL統計の挙動についてサポートに問い合わせてもまともな回答が返ってこないことから、基本的に使わない方が良いだろう。
４．SPMについて
SPM（SQL Plan Management）とはSQLの実行計画を管理するための機能で、Oracle11gより提供されている（それまではプラン・スタビリティという機能があった）。SPMでは、SQLの実行計画を事前に保存（SQL計画ベースライン）しておき、SQLの実行計画が変動しないようにすることができる。
SPMの実態は内部的にはヒントの集合であり、それがSQL（正確にはSQL_ID）毎にOracle内部（SYSAUX）に格納されているに過ぎない。オプティマイザはハードパースの際に、このベースラインの実行計画とハッシュ値で比較し、一致していなければ格納されたヒントで再度ハードバースし、ベースラインの実行計画で実行する。このため、SPMではハードパースが２回実行されることとなる。
APに手の入れられない状況、例えばシステムの運用開始の直前や、パッケージ製品の内部SQLのチューニングであれば、SPMを用いて局所的な対処を行えることが有用であろう。逆に、システムの全業務SQLに対してSQL計画ベースラインで管理しようとすると、ヒントの代わりにこのベースラインを維持管理していく必要があるだろう。
５．結局どうしたらよいのか
Oracleのセミナ等では結局ケースバイケースです、といった結論で終わることが多い。しかし、安定した性能、ひいてはDBAの生活の質の向上を考えるなら、おのずと答えが見えてくる。SQL性能にまつわる多くの問題は実行計画の変動であることを考えれば、その変動する要因を極力取り除くことが有効であると考える。それは以下①～⑥の流れで発生することから、それぞれに対してブロックするような対策を考えてゆけばよい。
①データの変動→許容する
②自動統計→許容する
③統計情報が変更される
→上で述べた統計ロックの手法を採用し変更されないようにする
※特に一時テーブルや超大規模なテーブル等
④ライブラリキャッシュからエージアウト
→バインド変数を使うことを徹底する（コーディング規約等）
→共有プールに十分なメモリを確保する（shared_pool_size）
→カーソルをPIN(dbms_shared_pool.keep)しage outを防ぐ
⑤ハードパースが走る
→ヒントを使い、実行計画を明示的に指定する
→dynamic samplingは無効化し、サンプル統計取得による実行計画の変動を抑える
→12c新機能である適応問い合わせ最適化は停止(OPTIMIZER_ADAPTIVE_REPORTING_ONLY=TRUE)し、実行計画が変更されないようにする
→ハードパースが走っても実行計画が変動しないようSPMを採用する
⑥実行計画が変わる
６．最後はやはりヒント
しかし、上記のように対策したとしても、最後のチューニングはやはりヒントであろう。ヒントはSQLに埋めるため、局所的な対処が可能（他への影響をほとんど考慮する必要ない）ことから、唯一無二のチューニング方法となることが多い。また、ヒントには実行計画をかなり細かく記載することができるため、非常に強力である。しかし、それを正確に理解し、SQLに入れられるレベルにはかなり個人差があることを理解しておかなければならない。現場でいろいろなヒントを見るが、意図が不明なもの、中には間違ったものまであったりする（恐らくわかってはいるものの、そのためにAPを修正する訳にもいかず、ずっと残ってしまっている状態なのだろう）。
私の経験上、多くのSQLのチューニングで使用するのは、実行順を指定するORDERED,LEADING、結合方法を指定するUSE_NL, USE_HASH、索引を指定するINDEX、フルスキャンを指定するFULLくらいである。ヒントの使い方自体はそれ程難しいことはない。その一番難しいところは、あるべき実行計画をイメージする部分である。業務（APの開発者）であれば、直感的にどのテーブルから、どのような順番でアクセスすべきかイメージできるだろう。しかし、SQLと実行計画だけ渡されてチューニングするとなると、遅い原因はわかっても、あるべき結合順をイメージするのは難しいため、結局思考錯誤で対応することが多かった。
そんな中で出会ったのが、Kevin Meade氏のOracle SQL Performance Tuning and Optimization: Its all about the Cardinalitiesである。カーディナリティからSQLの結合順を導くFRP法(Filtered Rows Percentage Method)について記している。これは、クエリの実行計画の中で、常に処理する行数をできる限り少なく制御する、つまり、別の言葉でいうと、クエリの実行段階のできるだけ早い段階で、より多くの行を取り除く、という方法である。
基本的に、表の結合順をFilterによる絞り込み率が大きい順に並ぶように実行計画をチューニングするという考え方である。例えば3つの表があれば、結合順は6通りあるが、各表の絞り込み率が大きい（より絞り込みができる）表を駆動表に、その後は絞り込みができる順に表を結合していく。もちろん、実際は結合可能な表の順番に制約があるのでその制約を加味した上での話である。
この本の５章にヒントについての記載があるが、この思想が興味深い。そもそもヒントはチューニングの手段として考えるべきではなく、他の方法がどうしても有効でない場合に使うべきである、というのだ。つまり、ヒントは実行計画を強制するものではなく、オプティマイザが最適な実行計画を導き出すために何が問題で何を変更する必要があるのかを解析するためのツールとして認識すべきである、という見方である。また、SQLチューニングの３つの要素～カーディナリティ、駆動表と結合順、結合方法～を制御するため、ヒントの中で最も重要なもの３つは以下であると述べている。
・CARDINALITY / OPT_ESTIMEATE
・OREDERD / LEADING
・NO_INDEX
なお、本書の一章は無償で公開されている。ここに著者の提唱するFRPの具体的なやり方が記載されているので、一読の価値はあるだろう。また、本書の中で利用されているスクリプトもここからダウンロードできる。SQLチューニングの際に取得すべき情報は、チューニングを依頼する際にこれだけはそろえてほしい情報が記載されている。現場ではあいまいな情報だけで無茶な依頼が来ることもあるので、こういった基本事項を明文化しておくことは大切なことだと感じる。
http://www.orafaq.com/forum/m/624976
Oracle SQL Performance Tuning and Optimization v26 chapter 1.pdf
→本書の１章 駆動表と結合順 が読めます
scripts.rar
→本書の中で利用されているスクリプト一式
Information needed in Tuning a SQL Query.docx
→SQLチューニングの際に取得すべき情報
以上
◆参考
Oracle SQL Performance Tuning and Optimization: It's All About the Cardinalities
- 作者: Kevin Meade
- 出版社/メーカー: Createspace Independent Pub
- 発売日: 2014/09/16
- メディア: ペーパーバック
スマートスキャンの結果不正に遭遇 [SQL・DDL]
世の中10連休であるが、ほとんどExa19cへのアップグレードで費やしてしまった自分にとって、もはや改元とかなにか遠い世界のことのように過ぎ去ってしまった。この話は別に書くこととし、今回はExadataの結果不正に遭遇した話をしたい。
さて、最近、業務TmからIS NOT NULLを条件指定したSQLの結果にNULL列が含まれているという問い合わせがあった。SQLを見てみると、以下のようにとてもシンプルなSELECT文（もちろん実際はテーブルはemp表ではないが）
select * from emp where job is not null
まさかと思い、自分で実行してみると確かに結果にjob列がNULLの行が含まれていた。確かに（1行だけであったが）NULLの行が入っていた。autotraceとって見ると、predicateにstorageが出ていたので、ストレージサーバへのオフロード（スマートスキャン）が走っていそうだった。SQLトレースで見たところ、確かにスマートスキャンが走っていた。そこで、試しにcell_offload_processingをfalseにして実行してみたところ、なんとNULLの行が消え、あるべき結果となった。スマートスキャンの挙動がおかしいのである。
サポートに確認したところ、ESS(Exadata Storage Server Software)の不具合であることが判明。条件はスマートスキャン、IS NOT NULL、行連鎖が発生していること。実際にこのテーブルは255カラム以上だったので、INSERT時に行連鎖が発生する。
なぜ行連鎖がこの不具合の挙動に関係するのか？本来このような行連鎖ブロックはスマートスキャンにはならず、DBサーバへブロックをそのまま返却し、DBサーバ側でfilter処理を行うべきものである。おそらく不具合はここの挙動が正常に動かなかったことによると考えられる。
ちなみにこの不具合、既にパッチ、というかESSの特定のバージョン以上で修正されているため、ESSを最新にすれば防ぐことができる。運よく、今回のGWでたまたまESSバージョンアップを予定していたので、19.2.0にしたらこの事象は発生しなくなった。
今回は開発中に検出できたのでまだ良かったが、この手の不具合は運用中に検出したら結構痛い。ストレージサーバ側のオフロード機能が充実するのは嬉しいことではあるが、不具合の確率も高まることは避けられない。不具合に遭遇して調べると、多くの場合は既知不具合でパッチも用意されていたりするのである。その意味でESS（だけではないが）のバージョンはなるべく新しくしておきたい。また、停止の影響を局所化する上でもベンダには個別パッチ提供を引き続きお願いしたいところである。
なお、スマートスキャンの結果不正については、以下のドキュメントに解析に必要なログ取得方法が記載されている。SQLをいくつかの初期化パラメータ（上記のcell_offload_processing含め）を変更しながらSQLを実行するものと、結果不正となった行のブロックダンプを取得するものである。使うことがないに越したことはないが、Exadataを扱うDBAとしては、このようなドキュメントがあることは知っておいたほうが良いかもしれない。
Exadata: How to diagnose smart scan and wrong results (ドキュメントID 1260804.1)
以上
さて、最近、業務TmからIS NOT NULLを条件指定したSQLの結果にNULL列が含まれているという問い合わせがあった。SQLを見てみると、以下のようにとてもシンプルなSELECT文（もちろん実際はテーブルはemp表ではないが）
select * from emp where job is not null
まさかと思い、自分で実行してみると確かに結果にjob列がNULLの行が含まれていた。確かに（1行だけであったが）NULLの行が入っていた。autotraceとって見ると、predicateにstorageが出ていたので、ストレージサーバへのオフロード（スマートスキャン）が走っていそうだった。SQLトレースで見たところ、確かにスマートスキャンが走っていた。そこで、試しにcell_offload_processingをfalseにして実行してみたところ、なんとNULLの行が消え、あるべき結果となった。スマートスキャンの挙動がおかしいのである。
サポートに確認したところ、ESS(Exadata Storage Server Software)の不具合であることが判明。条件はスマートスキャン、IS NOT NULL、行連鎖が発生していること。実際にこのテーブルは255カラム以上だったので、INSERT時に行連鎖が発生する。
なぜ行連鎖がこの不具合の挙動に関係するのか？本来このような行連鎖ブロックはスマートスキャンにはならず、DBサーバへブロックをそのまま返却し、DBサーバ側でfilter処理を行うべきものである。おそらく不具合はここの挙動が正常に動かなかったことによると考えられる。
ちなみにこの不具合、既にパッチ、というかESSの特定のバージョン以上で修正されているため、ESSを最新にすれば防ぐことができる。運よく、今回のGWでたまたまESSバージョンアップを予定していたので、19.2.0にしたらこの事象は発生しなくなった。
今回は開発中に検出できたのでまだ良かったが、この手の不具合は運用中に検出したら結構痛い。ストレージサーバ側のオフロード機能が充実するのは嬉しいことではあるが、不具合の確率も高まることは避けられない。不具合に遭遇して調べると、多くの場合は既知不具合でパッチも用意されていたりするのである。その意味でESS（だけではないが）のバージョンはなるべく新しくしておきたい。また、停止の影響を局所化する上でもベンダには個別パッチ提供を引き続きお願いしたいところである。
なお、スマートスキャンの結果不正については、以下のドキュメントに解析に必要なログ取得方法が記載されている。SQLをいくつかの初期化パラメータ（上記のcell_offload_processing含め）を変更しながらSQLを実行するものと、結果不正となった行のブロックダンプを取得するものである。使うことがないに越したことはないが、Exadataを扱うDBAとしては、このようなドキュメントがあることは知っておいたほうが良いかもしれない。
Exadata: How to diagnose smart scan and wrong results (ドキュメントID 1260804.1)
以上
