Oracleのアップグレード前後でパフォーマンスをコントロールするには
この記事を読むとできるようになること
アップグレード後にパフォーマンス劣化(SQL性能が悪化する)が起こる理由が分かる
パフォーマンス劣化を防ぐためにどのような性能テストを行えば良いかが分かる
Oracleのアップグレードの概要（おさらい）
「クイック・スタート・ガイド」によると、アップグレードは以下のような手順で行うのでした。
手順１：データベースとアプリケーション動作保証を検証する
手順２：Oracle Databaseを最新のリリース更新と一緒にインストールする
手順３：AutoUpgrade機能を使用してアップグレードする
手順４：正しい機能、オプション、パックを使用してテストする
また、実際はハードウェアの買い替えも同時のことが多く、新ハードウェアのデータベースへのデータ移行とバージョンアップを同時に行うことが多いのでした。
パフォーマンス劣化が起こる理由（おさらい）
Oracle製品バージョンが上がることで、同じ設定ファイルや統計情報でも動作が変化することがあります。 以下のようなものがSQL性能に影響します。
初期化パラメータ
オプティマイザの仕様
統計情報
SQLプロファイル(※1)
SPM(※2)
(※1)SQLプロファイル：個別のSQLに付加できる補助的な統計情報。Tuning Packライセンスが必要です。
(※2)SPM(SQL Plan Management)：SQLの実行計画を固定化することができるツール。実行計画を確実に固定化するためにはSPMを使うしかないが、乱発すると管理も大変になるので注意が必要。
パフォーマンスをコントロールするには？
アップグレードの「手順４」にあるとおり「性能テスト」を行います。 以下のような手順で行います。
１．アップグレード後の新環境に相当するテスト環境を作ります。
テスト環境には、なるべく本番環境と同じ量のデータを載せます。
初期化パラメータ設定はなるべくデフォルトを使います。(古いバージョンの致命的な不具合は新バージョンで修正されているケースが多いです)
統計情報を最新化します。(統計情報がゼロの状態や失効している状態だと本来のSQL性能が出ません)
２．旧環境のSQLを収集し、テスト環境で実行・分析します。
旧環境の重要なSQLと実行計画や実行時性能情報を保存します
テスト環境でSQLを実行し、実行計画や実行時性能情報を取得します
旧環境とテスト環境の実行計画や実行時性能情報を比較します
テスト環境でSQLをチューニングします
テスト環境の作成はどのようにするか？
テスト環境の作成に使える方法は主に以下があります。
DataGuardのスナップショット・スタンバイを使う
一時的にスナップショット・スタンバイ(※3)にすることで本番データベースと同じ環境をテスト環境として使用できます。 クラウド環境にDataGuard環境を作れば、テストをしている間だけの費用負担で済みます。
(※3)スナップショット・スタンバイ：プライマリ・データベースからはREDOの受信のみを行い、適用をしない。スナップショット・スタンバイは本番とは切り離されていて更新も可能なのでテスト環境として利用できる。テストが終了してフィジカル・スタンバイに戻せば、REDO適用を再開する。
(参考)
RMANのデータベース複製機能を使う
OracleのRMANを使って本番データベースのコピー環境を作ります。
(参考)
テスト環境の設定はどのようにするか？
初期化パラメータはなるべくデフォルトを使う
Oracle製品の挙動を変えるために旧バージョンであえて設定していたものが新バージョンでは邪魔になってしまうことがあります。まずは新バージョンのデフォルトでテストします。
統計情報はどのようにするか？
テスト環境を作成したら、「オブジェクト統計(※4)」と「ディクショナリ統計(※5)」を収集します。
データが本番環境と同等なら本番環境の統計情報を移行しても良いです。
固定オブジェクト統計(※6)は一定の暖気(負荷をかけてメモリにデータを載せる)が済んでから収集します。
システム統計(※7)は一般的にはデフォルトで良いとされています。(収集しない方が良い)
(※4)オブジェクト統計：表統計、列統計、索引統計(「データベース・オブジェクト」の統計）
(※5)ディクショナリ統計：SYS、SYSTEMが所有するディクショナリ表の統計 (※6)固定オブジェクト統計：動的パフォーマンス表関連の統計
(※7)システム統計：CPUやI/O性能のようなH/W関連の統計
(参考)
旧環境でSQLを収集するには？
SQLチューニング・セット(STS)を使ってSQLと実行計画や実行時統計を収集できる
Enterprise Editionで使うことができます。
本番環境でSQLを収集し、テスト環境へ持っていくことができます。
テスト環境でSQLを再生して分析します。
(参考)
V$SQLTEXTを検索して自前でSQLを収集できる(手間はかかります)
V$SQLを使って対象のSQLを絞り込み、V$SQLTEXTを使ってSQL文を取り出します。
バインド変数はV$SQL_BIND_CAPTUREから取り出せます。
取り出したSQLとバインド変数をテスト環境へ持っていきます。
テスト環境でSQLを再生して分析します。(再生用のSQLはカスタムスクリプトを開発するなどして自前で準備します)
(参考)
テスト環境でSQLを再生するには？
SQL Performance Analyzer(SPA)と使ってSQLの実行計画を確認できる
STSと連携すれば、旧環境と新環境のSQLの実行計画を比較分析できます。
データベース・リプレイを使うと、本番環境でSQLワークロードを取得し、テスト環境でそのワークロードをリプレイ(再生)できます。
Enterprise Edition＋Real Application Testingライセンスが必要です。
(参考)
V$SQLTEXTを検索して収集したSQLをもとに再生用のSQLを作成する(手間はかかります)
カスタムスクリプトの開発が必要ですが、V$SQLTEXTとV$SQL_BIND_CAPTUREから取得した情報を基に再生用のSQLを組んでSQL*Plusで実行することもできます。
テスト結果を分析するには？
Automatic Workload Repository(AWR)を使ってテスト結果を比較分析できる
Enterprise Edition＋Diagnostics Packが必要です。
SQL実行ログを分析するカスタムスクリプトを開発し、比較分析することもできる(手間はかかります)
SQLをチューニングするには？
SQLチューニング・アドバイザを使って改善案を出させることができる
Tuning Packライセンスが必要です。
以下のようなタイプの改善を出力します：
オブジェクト統計の収集
索引の作成
SQL文のリライト
SQLプロファイルの作成
など
SQLの実行計画を自分で分析してチューニングすることもできる(手間はかかります)
SQLの実行計画を固定化するには？
SQL Plan Management(SPM)を使ってSQLの実行計画を固定化することができる
どうしても特定の実行計画に固定化したいケースではSPMを使うしかありませんが、固定化対象のSQLが数十数百と多くなってしまうと管理も大変になります。
SPMを使うのはなるべく最小限にとどめるようにし、なるべくSQL文の書き方自体の改善で対処するようにします。
気を付ける点は？
パラメータはなるべくデフォルトにする
新しいデータベースがリリースされる際、基本的にデフォルト設定がお薦めのパラメータ設定となっています。旧バージョンで致命的な製品不具合があってパラメータ設定で回避していたようなケースは、新バージョンでは修正済みかもしれません。旧環境のパラメータ設定をそのまま新環境に持っていく前に、まずはドキュメントを参照して正しく状況把握しましょう。一旦不適切なパラメータ設定で本番稼働してしまった後で変更するのは大変になります。
ディクショナリ統計の収集を行う
アップグレード作業は、具体的にはデータ・ディクショナリの更新です。アップグレード作業の前にディクショナリ統計を収集しておくと、アップグレード当日にディクショナリ統計の収集をスキップでき時間短縮になります。 アップグレード後にもディクショナリ統計を収集します。
固定オブジェクト統計は、本番稼働後しばらくたってから収集する
固定オブジェクトはデータベースの統計ではなくメモリ構造の統計です。したがって、データベースが再起動された直後やメモリの状態が空の状態で収集してしまうと役に立ちません。 本番稼働後に代表的なワークロードを実行した後で、固定オブジェクト統計を再収集することが推奨となっていますが、具体的には本番稼働後1週間後に取得すれば良いケースが多いです。(※8)
(※8)DBMS_SCHEDULERでスケジュール登録しておくと確実に取得できます。
システム統計はデフォルトを使う
システム統計は、I/OやCPUのパフォーマンスや使用率などの統計です。システム統計を使用すると、問合せオプティマイザが実行計画の選択時にI/OコストとCPUコストをより正確に見積もることができる、とされていますが、デフォルトを使用するのが良いケースが多いようです。 システム統計を取得する場合もデフォルトを使った時と比較して判断します。
オブジェクト統計をいつ収集するか？
移行やアップグレードのような大きなイベントの時は統計情報を最新化しておいたほうが良いケースが多いと思いますが、アップグレード直後に統計情報を収集するのが良いケースもあれば、一旦別のシステムから統計情報を移行して(統計情報を収集し直すよりも速い)本番稼働を急いだ方が良いケースもあります。ハードウェアリソースの空き状況とダウンタイムを考慮して計画／テストします。
まとめ
移行やアップグレードはプロジェクトとして作業するケースが多いと思います。便利なツールは多数ありますが、便利そうなツールほど費用がかかります。できる範囲で便利なツールを購入しつつ、足りないところはカスタムスクリプトを開発するなど知恵と工夫で乗り切りましょう。
また、費用と労力をかけてテスト品質がある程度良くなると、それ以上完璧なテストにするのはなかなか難しいです(テスト品質で80点を取る費用と労力と同じだけかけてもなかなか100点にはならない)。テスト品質にこだわりすぎるよりも、運用対処方法を用意したり性能モニタリング態勢を構えることでもユーザー影響が出ないようにできますので、本番システムの安定稼働に向けて複数の視点で総合的に検討していきましょう。
執筆者プロフィール：小澤 雅弘
DBMSベンダー、ISPなどでDB関連のプロジェクトを20数年経験。得意領域はDBのパフォーマンスチューニング。
この公式ブロガーの関連記事
お問合せはお気軽に
https://service.shiftinc.jp/contact/
SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/
SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/
SHIFTの導入事例
https://service.shiftinc.jp/case/
お役立ち資料はこちら
https://service.shiftinc.jp/resources/
SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/
PHOTO：UnsplashのAnnie Spratt