システム移行では、移行期間中はデータベースの統計情報に注目しておらず、移行後に「特定の処理が遅延するようになった」といったパフォーマンスのトラブルが発生して、初めて統計情報に向き合う事例が散見されます。その原因は、旧システムで最適化されていた統計情報が新しい環境ではリセットされてしまい、一から新たに構築する必要がある点にあります。
本稿では、これまで見えづらかった統計情報の仕組みについて理解を深めることを目的とし、リアルテックジャパンがSAPシステム移行時にどのように統計情報の対処をしているかを解説したいと思います。
SQLServer編とOracle編の2回に分けてご紹介する予定であり、本稿はOracle編です。
前提
Oracleの統計情報を解説する前に、本稿で説明する内容について前提を示しておきます。
- 統計情報とは、ディスクベース(データをディスクで管理)のコストベース・オプティマイザ(CBO)で利用されるオプティマイザ統計を対象とします。詳細は前回のSQLServer編を参照ください。
- SAPシステム移行と統計情報の関係性については、前回のSQLServer編を参照ください。データベースがOracleでも同様です。
- 特に断わりのない限り、移行方法は同機種間のデータベース非依存の方法(R3loadベース)、SAPはERP(NW7.0以上)、Oracleは10.2以上を前提とします。
- 対処方法は顧客要件や環境によって異なるため、SAP環境下の標準的な事例としてご認識ください。
Oracleの統計情報とその対処方法
SAP環境でのOracleの統計情報は、自動化機能を無効にし、定期的に手動によるメンテナンスを行うコンセプトです。統計情報に関する多くのパラメータや機能によるマニュアル調整が必要となり、SQLServerと比べて複雑です。SAPレベルから統計を制御/管理するための専用のツールやトランザクションが多数提供されています。
テーブルとインデックス
Oracleのテーブルはヒープ構造であり、ソートされていない限り、実データは特定の順序で並べられていません。Insertが発生すると、Oracleは散在する空き領域のあるブロックにデータを順次格納します。インデックスは、階層構造の末端のリーフブロックにキー値とその行に対応するROWIDが格納されており、主キーのインデックスと、主キー以外のインデックスは構造的に同等です。これはSAPで使用するOracleのテーブルとインデックスも変わりません。SAPでは1つのプライマリインデックスと、複数のセカンダリインデックスを保持します。
統計情報
Oracleの統計情報は、DBMS_STATSパッケージ(SAPが提供するデータベース管理ツールBRCONNECTからコールされる)で取得され、自動的にSYSAUX 表領域に保存されます。Oracleの統計には以下のものがあり、各統計がオプティマイザのコスト計算で使用されるため、すべての統計に対して範囲や目的を押さえ適切に対応することが重要となります。
- テーブル統計
- インデックス統計
- 列統計
- システム統計
- ディクショナリ統計
統計に関係する移行作業は概ね以下の流れです。
- 旧Oracleからデータをエクスポート
- 新Oracleにデータをインポート
- 統計関連テーブルのデータ削除
- 統計の自動更新ジョブの停止
- 統計制御テーブルの初期化/統計の固定化
- 統計パラメータの設定
- 統計更新
- 統計更新(ディクショナリ/システム)
- 統計更新のスケジュール
<ステップ①：旧Oracleからデータをエクスポート>
実際と乖離するサイズ見積りや間違った実行計画が選択される可能性があるため、エクスポートをする際は、統計情報を事前に確認しておくことが望ましく、状況によってエクスポート前に統計情報を更新します。エクスポートは「ソートあり(主キーの順で並び替え)」または「ソートなし(順次読み込み)」のどちらかで行うことができ、テーブル単位で制御することが可能です。SAPのデフォルト動作は「ソートなし」が推奨ですが、後述のインポートにも影響する場合があるため、移行時間とパフォーマンスの観点から「ソートあり」と「ソートなし」を使い分けることが重要です。Oracleのテーブルはヒープ構造であるため、オプティマイザ次第ですが、「ソートあり」はテーブルスキャン＋ソートまたはインデックススキャン、「ソートなし」はテーブルスキャンによってデータを読み込みファイルに書き込みます。読み込み時に大規模なソートやインデックスによるデータ検索が発生すると、ディスクI/Oが増え、エクスポート時間が長くなる傾向があります。そのため、不必要なインデックスアクセスに対して、意図的にマルチブロックリード係数(後述)を増やしたり、またはヒント句を付加することでテーブルスキャンを強制する場合があります。
<ステップ②：新Oracleにデータをインポート>
インポートステップは次の順で処理されます。
- テーブルの作成
- データのインポート
- プライマリインデックスの作成
- セカンダリインデックスの作成 (※存在する場合)
データのインポートでは、ダイレクト・パス・ロードを使用したデータの一括ロードが行われ、バッファキャッシュをバイパスして直接データベースに書き込みます。Insertのように一部使用中のブロックを使用せず、ハイウォーターマーク(HWM)以降の連続した領域に高速に格納します。ソートなしのエクスポートデータを使用したインポートでは、インデックス統計のクラスタリング係数が最適化されず、データがバッファキャッシュ経由でないときはディスクアクセスが非効率となる可能性があります。SQLServerと同様にインデックスの作成では、自動的にインデックス統計も作成されます。このとき取得される統計の精度はフルスキャン(サンプリング100%)です。プライマリとセカンダリインデックス両方とも作成時はデータが存在するため、フルスキャンのインデックス統計が作成されます。しかしテーブル統計は作成されませんので、データのインポートが完了した時点で統計情報は不完全な状態です。
<ステップ③：統計関連テーブルのデータ削除>
以下のテーブルのデータを削除します。
- DBSTATHORA
- DBSTAIHORA
- DBSTATTORA
- DBSTATIORA
上記はSAPが使用する領域統計に分類されるものであり、正確にはOracleのオプティマイザ統計ではなく、オプティマイザ統計の一部のコピーが格納されます。DBSTATHORAとDBSTAIHORAは、テーブルとインデックス統計情報の履歴データとして、アプリケーションモニタ(トランザクションST07)で使用されます。DBSTATTORAとDBSTATIORAは、システム移行のDBSIZE.XMLの生成やテーブル再編成およびアップグレードで、テーブルやインデックスの正確なサイズを必要とする場合に使用されます。4テーブルとも、BRCONNECTによる統計情報の更新と同時に統計情報からその一部が自動的にコピーされます。インポート直後は旧システムのデータが残っているため削除します。ステップ⑦の統計更新のタイミングで新環境のデータを作成し直します。なお、トランザクションDBACOCKPITまたはDB02のSpaceモニタで使用されるデータは、標準ジョブSAP_COLLECTOR_FOR_PERMONITORによってテーブルMONIに収集されたものであり、こちらとは関係ありませんのでご注意ください。
<ステップ④：統計の自動更新ジョブの停止>
以下の自動メンテナンスジョブを停止します。
- 自動オプティマイザ統計収集(*)
- 自動セグメント・アドバイザ
- 自動SQLチューニング・アドバイザ
上記ジョブ(*)は、事前定義された時間枠にデータベース内に統計がない場合、または統計が古い場合にすべてのデータベースオブジェクトで統計が自動的に収集されます。SAP環境下では、BRCONNECTを使用した統計情報の管理が必須であるため、Oracleの標準機能による統計更新を無効化します。
<ステップ⑤：統計制御テーブルの初期化/統計の固定化>
統計情報の更新は、たとえば一部のテーブルにとっては精度が不十分であったり、変動が激しく頻繁に更新する必要がない場合に、個別のサンプリングや追加のヒストグラム収集、および統計の固定化といった例外ルールを適用する必要があります。SAPではそのような例外ルールを適用する仕組みとして、テーブルDBSTATCを提供しています。DBSTATCはBRCONNECTによる統計更新の際にアクセスされ、統計更新はDBSTATCに定義されたルールに従って行われます。DBSTATCのエントリはデフォルトでは最新ではありません。最初にSAPから提供されているスクリプトを実行してDBSTATCを初期化します(全エントリの削除＆最新ルールの登録)。さらに別のスクリプトを実行して、DBSTATCの更新と追加登録、DBMS_STATSパッケージによる統計情報の固定化を行います。その結果、ステップ⑦の統計更新のタイミングで一部のテーブルはDBSTATCのルールに従って統計更新が行わるようになります。またDBSTATCは、環境に合せて必要に応じて自由にカスタマイズすることが可能です(トランザクションDB21による編集あるいはDB直接)。そのため、旧システムで環境固有の例外ルールが追加されており新環境でそれが必要である場合は、DBSTATCに追加でカスタマイズを加える必要があります。なお、DBSTATCは一度登録すれば終わりではなく、定期的にまたはSAP/Oracleのアップグレード後に最新バージョンをインポートすることが重要です。
[RELATED_POSTS]
<ステップ⑥：統計パラメータの設定>
以下の初期化パラメータを設定します。
- optimizer_adaptive_features(*) = 初期化ファイルから削除（12.2以上）、FALSE（12.1の一部）
- optimizer_adaptive_plans(*) = FASLE（12.2以上 または 12.1の一部）
- optimizer_adaptive_statistics(*) = FASLE（12.2以上 または 12.1の一部）
- optimizer_dynamic_sampling(*) = 初期化ファイルから削除
- optimizer_features_enable = 初期化ファイルから削除
- optimizer_index_caching = 初期化ファイルから削除
- optimizer_index_cost_adj = 20
- optimizer_mode = 初期化ファイルから削除
上記は、オプティマイザの動作に影響する主要なパラメータのうち、SAPより推奨が指定されているものです。一部の隠しパラメータ(_optimizer～)についてもSAP推奨が指定されていますが、インストーラ(SWPM)が自動的に設定するため考慮が不要です。これらのパラメータのうち、統計の自動化機能に関わるパラメータ(*)は特に重要です。12から新しい機能として「適応問合せ最適化」が追加されており、これは統計情報が実際のデータ状況と乖離している場合に実行計画を自動的に補正してくれるもので、optimizer_adaptive_featuresで制御します。12.2ではこの機能が「適応計画」と「適応統計」に分けられ、optimizer_adaptive_featuresは廃止され、それぞれoptimizer_adaptive_plansとoptimizer_adaptive_statisticsで制御する形になりました。optimizer_dynamic_samplingは、動的統計(11以下は動的サンプリング)に対する制御が可能であり、これはオプティマイザがSQLを解析する際に指定されたレベルに応じて自動的に統計情報を補完する機能です。これらの自動化機能は、実行計画や統計情報を改善できる反面、SQL実行時のオーバーヘッドによるパフォーマンス問題の要因になりやすく、SAP環境下では無効化することが推奨されています。動的統計についてはデフォルト(テーブルの統計情報が存在しない場合に適応)のままとしますが、SAP環境下では事前に統計情報を作成することが大前提であるため大きな影響はないと考えられます。なお、パラメータ全般に言えることですが、旧システムで意図的に推奨と異なる設定を行っている場合は新システムでどのようにするか判断した上で対処します。
<ステップ⑦：統計更新>
ステップ②で説明したとおり、インポート後はテーブル統計が欠落した状態です。そのため、BRCONNECTを実行して手動で全テーブルに対して統計更新を行います。但し、インデックス統計は既に作成されているため、オプションを指定してインデックス統計は更新しないようにする必要があります。この操作によってテーブル統計、列統計が作成されます。
BRCONNECTでは下記の内部ルールに基づいて処理が行われます。
- DBSTATCに統計の作成/更新を禁止しているテーブルが定義されている場合、そのテーブルの統計情報を削除する
- DBSTATCに統計の作成/更新が無視されているテーブルが定義されている場合、そのテーブルの統計情報を作成・更新・削除しない
- DBSTATCに統計の作成/更新が無条件に許可されているテーブルが定義されている場合、そのテーブルの統計情報を更新する
- 前の3ステップに該当しないテーブルに対して、テーブルDBA_TAB_MODIFICATIONSから挿入・削除・更新の変更数を読み取り、以下のいずれかの計算式が真である場合に統計が古いと判断され、そのテーブルの統計情報を更新する
- old rows + inserted rows >= old rows * (100 + threshold) / 100
- old rows + updated rows >= old rows * (100 + threshold) / 100
- old rows - deleted rows <= old rows * 100 / (100 + threshold)
- #old rows : テーブル行数（BRCONNECT開始時点のDBA_TABLESのNUM_ROWS）
- #threshold : 変更数の閾値（BRCONNECTのオプションで指定した値か、指定がない場合はBRToolsの初期化パラメータで指定した値※デフォルト50）
※DBA_TAB_MODIFICATIONSはOracle側でリアルタイムに更新されないため、BRCONNECTが実行時にそれを更新することで常に最新の変更数でチェックします
※サンプリングやヒストグラム収集などの条件は、DBSTATCに定義された値か、DBSTATCに定義がない場合は既定の条件を使用します
- 収集した統計情報の結果をテーブルDBSTATTORAにコピーし、一部をテーブルDBSTATHORA、DBSTATIORA、DBSTAIHORAにコピーする
なお、DBSTATCのチェックとデータ変更数のチェックをスキップして、強制的に統計更新を行うことも可能です。
たとえば、S/4HANAの移行時にコンバージョンフェーズの長期化抑制のために全テーブルに対して強制的に統計更新を行います。これはデータベースをHANAへ置き換えるため、Oracleの統計情報は今後不要となる前提で移行時間の最適化のために実施します。
<ステップ⑧：統計更新(ディクショナリ/システム)>
続いて、ディクショナリ統計とシステム統計を更新します。
ディクショナリ統計とは、Oracleディクショナリ(SYSまたはSYSTEMのオブジェクト) に対する統計です。SAPインストール時に自動的に登録されますが、定期的またはSAP/Oracleのアップグレードなどディクショナリで重要な変更が行われた際に更新することが推奨されています。BRCONNECTによって更新することができ、動的パフォーマンステーブル(X$テーブル、固定オブジェクト)も同時に更新されます。
システム統計とは、システムのハードウェア属性(I/Oパフォーマンスまたはプロセッサ率、およびそれらの比率など)に対する統計です。オプティマイザでは、テーブルやインデックスなどの統計に加えてこれらのシステム固有のパフォーマンス能力を考慮することで、より正確にI/OコストとCPUコストが計算され、より的確な実行計画を生成できるようになります。システム統計には「ワークロードなし(NOWORKLOAD)」と「ワークロードあり(WORKLOAD)」の2種類のモードがあります。NOWORKLOAD統計はシステム固有のパフォーマンス値(手動の測定が不要で容易に決定可能)が登録され、WORKLOAD統計は実際のシステム負荷に依存したパフォーマンス値(典型的なシステム負荷を一定時間測定が必要)が登録されます。
具体的には以下のもので、テーブルSYS.AUX_STATS$に格納されています。
- NOWORKLOAD統計
- CPUSPEEDNW（1秒あたりのCPU平均サイクル数）
- IOSEEKTIM（ディスクリードあたりのオーバーヘッド時間）
- IOTFRSPEED（I/O転送速度）
- WORKLOAD統計
- SREADTIM（平均シングルブロックリード時間）
- MREADTIM（平均マルチブロックリード時間）
- CPUSPEED（1秒あたりのCPU平均サイクル数）
- MBRC（マルチブロックリード時の1回あたりの平均読み込みブロック数）
- MAXTHR（最大I/Oスループット）
- SLAVETHR（パラレルスレープごとの平均I/Oスループット）
SAP環境下では、NOWORKLOAD統計が推奨であり、SAPインストール時にNOWORKLOAD統計が自動的に登録されます。BRCONNECTによる更新後に、テーブルSYS.AUX_STATS$の更新ステータスと更新日時、統計結果をチェックします。通常はNOWORKLOAD統計値にデータがセットされており、WORKLOAD統計値は空の状態です。システム統計は、定期的またはOracleの変更やアップグレード、さらにCPUやディスクの追加など重要なハードウェア変更が行われた際に更新することが重要です。
なお、システム統計に関係する重要な指標として、マルチブロックリード係数があります。マルチブロックリード係数とは、フルテーブルスキャンまたはインデックス高速フルスキャン中に、一度のI/Oで読み取られるデータブロック数を表したもので、複数ブロックの読込アクセスに影響を与えます。オプティマイザによるフルスキャンのコスト見積りは、データブロック数とマルチブロックリード係数を組み合わせて計算され、この値が大きいほど、フルスキャンのコストが低く見積もられ、インデックスアクセスよりフルスキャンが選択されやすくなります。マルチブロックリード係数は、NOWORKLOAD統計下ではパラメータDB_FILE_MULTIBLOCK_READ_COUNTが使用され、WORKLOAD統計下ではMBRC統計値(上記項目)が使用されます。但し、10.2以降のNOWORKLOAD統計下では、隠しパラメータ(_DB_FILE_OPTIMIZER_READ_COUNT)が使用されるようになります。従来のDB_FILE_MULTIBLOCK_READ_COUNTは初期化プロファイルに明示的に設定した場合のみ、こちらが使用されます。
この変更は、実際の複数ブロック読込時のI/Oサイズとコスト見積りのI/Oサイズを分離することで、実際のI/Oスループットの向上とフルスキャンのコストを不必要に低く見積もることへの対応と考えられます。そのため、DB_FILE_MULTIBLOCK_READ_COUNTを活用していた旧システムを新システム(Oracleアップグレード)へ移行した場合は、マルチブロックリード係数の動作に注意する必要があります。
<ステップ⑨：統計更新のスケジュール>
最後に、統計更新を定期的に実行するためにトランザクションDB13を使用してスケジュールします。SAPでは毎日1回以上の頻度で更新することが推奨されており、特定の要件がなければ旧システムのスケジュール設定を通常引き継ぎます。
まとめ
統計情報はシステムを移行する上で重要な要素であり、適切なメンテナンスと注意が必要です。Oracleの統計はその仕組みやSAPレベルの扱い方について専門的な知識が求められます。
SAPシステムの移行やアップグレードでパフォーマンス問題が発生しないか心配されている方も、すでにSAPのパフォーマンス問題に直面している方も、何か疑問点がございましたらリアルテックジャパンにお気軽にご相談ください。
