データベースアップグレードのベスト・プラクティス

データベースアップグレードのベスト・プラクティス -コストベース・オプティマイザのパフォーマンス最適化-

日本オラクル株式会社セールスコンサルティング統括本部基盤技術 SC本部運用管理ソリューションSC部田中学 (たなかまなぶ)

はじめに

「バージョンアップをしたら、SQLの実行計画が変わってしまった！」「10gからRBO（ルールベース・オプティマイザ）がサポートされないけど、どうしよう？」といったお悩みを、バージョンアップの時にはよく伺います。この資料はバージョンアップをご検討の皆様に、SQL チューニングの肝となるコストベース・オプティマイザを使いこなすための、Oracle Database 11gでのベストプラクティスをご紹介致します。

コストベース・オプティマイザの理解

SQL 処理の性能を管理・向上させるためには、SQL の実行計画や実行計画を作成するオプティマイザを理解することが重要です。

SQL の実行計画とは、そのSQL 文を実行するためにOracle Database が行う一連の手順です。例えば、データベースからデータを取り出す際に索引を使ってデータにアクセスするなどの手順があります。同じ表からデータを検索するとしても、該当のデータが少なければ索引を使用した検索のほうが効率的ですが、該当のデータが多い場合は索引を使用せずに検索したほうが効率的な検索となります。オプティマイザとは、このような様々な実行計画の候補の中から最も効率よくSQL を実行できる方法を判断するものです。

オプティマイザには、事前に定義されたルールに基づいて実行計画を作成するルールベース・オプティマイザ（RBO）と、統計情報に基づいて最もコストの低い実行計画を作成するコストベース・オプティマイザ（CBO）があります。しかし、Oracle Database 10g からは、ルールベース・オプティマイザはサポートされなくなりました。

サポート対象外の例：

初期化パラメータ
OPTIMIZER_MODEがRULE またはCHOOSE　→RBOを使用

10gR1以降、デフォルトでは ALL_ROWSです（CBOを使用）。
9iR2(9.2.0.X)までは CHOOSEがデフォルトでしたが、10gR1からはサポートされません。
SQL文のヒント
SQL文のヒントに、「Rule」が使用されている

/*+ rule */　という記述がSQLに埋め込まれている場合もサポート対象外になります。
バージョンアップされたシステムに多くみられますので注意が必要です。

コストベース・オプティマイザは、ルールベース・オプティマイザと比較して、新機能に対応していたり、データの偏りや量に基づいて柔軟に実行計画を立てられたりといったメリットがあります。

図1 コストベース・オプティマイザのメリット

しかし、統計情報が適切に取得されていないと、逆に性能劣化が発生するケースがまれに起きます。そのため、統計情報を適切に取得し、管理することは非常に重要です。

図2 コストベース・オプティマイザの注意点

▲ ページTOPに戻る

Oracle Database 11g での実行計画の管理

自動オプティマイザ統計

Oracle9i Database 以前のデータベース統計情報は、DBMS_STATS パッケージを使用して明示的に収集する必要がありました。表にデータを大量に挿入した場合など、統計情報が古くなった場合にも、明示的に統計情報を再収集する必要がありました。

Oracle Database 10g から、統計情報を自動で取得する「自動オプティマイザ統計収集」という新機能が登場しました。統計情報をまだ収集していないオブジェクトや、大幅にデータが更新されたオブジェクトに対して負荷の低い時間帯（デフォルトでは、平日の夜間と週末）に、自動的に統計情報が収集されます。この機能により、オプティマイザが常に適切な実行計画を作成することができるようになります。

また、統計情報が再収集された場合には、古い統計情報はSYSAUX 表領域内に自動で保存されるので、元の統計情報に戻したい時には、Oracle Enterprise Manager などから簡単にリストア可能です。古い統計情報はデフォルトで31日間保存されます。

図3 自動オプティマイザ統計収集のリストア

SQL 実行計画管理

Oracle Database 10g 以前は、コストベース・オプティマイザが新しい実行計画を作成すると、即座にその実行計画が使用されていました。通常は、新しい実行計画により性能が改善されるケースがほとんどですが、まれに新しい実行計画を使用したSQL が以前より性能が劣るケースもありました。

Oracle Database 11g から、この問題を解決する「SQL 計画管理」という新機能が登場しました（ ※1）。新しい実行計画が作成された時点で即時に変更するのではなく、新しい実行計画を一旦SQL 計画ベースラインに保存し、管理者が判断をしてから新しい実行計画を使用可能にする機能です。

SQL 計画管理の使用方法は以下の通りです。

初期化パラメータ　OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES をTRUE に設定し、実行計画の変化を管理したいSQL を実行
実行計画の変化を管理したいSQL の実行計画をSQL 計画ベースラインに取得します。初期化パラメータ　OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES は、デフォルトはFALSE で、セッションレベルでの変更も可能です。実行計画の取得が終わったら、この初期化パラメータはFALSE に戻しておきましょう。
初期化パラメータ OPTIMIZER_USE_SQL_PLAN_BASELINES をTRUE に設定
SQL 計画ベースラインを使用可能にします。SQL 計画ベースライン使用中は、1 で取得したSQL の実行計画を使用してSQL が実行されます。オプティマイザが同一のSQL に対して新しく実行計画を作成してもすぐにはその実行計画は使用されません。新しく作成された実行計画はSQL 計画ベースラインに保存されます。SQL 計画ベースラインに保存された新しい実行計画は、Oracle Enterprise Manager から確認すると「確定済」列が「NO」になっています。

図4 SQL 計画管理
新しいSQL の実行計画を展開
Oracle Enterprise Manager から、新しく作成されたSQL を選択して「展開」ボタンをクリックすることで、オプティマイザが作成した新しい実行計画を使用可能な状態にすることができます。その際に、SQL が現在使用している既存の実行計画と新しい実行計画を比較し、実行時間やBuffer Gets などの比較レポートを確認することが可能です。

図5 実行計画の比較レポート

SQL 計画ベースライン内にSQL の新しい実行計画が作成されたかどうかを確認する方法として、新規の実行計画が作成されたことを予め設定したメールアドレスにアラート送信することが可能です（ ※2）。これにより、必要なタイミングで確実に実行計画のチェックを簡単に行えます。メール通知の設定方法は、Oracle Enterprise Manager で以下のようなSQL を使用したユーザー定義メトリック（ ※3）を作成します。
Copy
```
SELECT COUNT(*)
  FROM dba_sql_plan_baselines
 WHERE ACCEPTED='NO'
   AND LAST_MODIFIED > sysdate - 1;
```

ご参考

実行計画変更のリスク対策として、実行計画を固定させるストアド・アウトラインという機能がありましたが、Oracle Database 11g からは非推奨となり今後のバージョンではサポートされなくなる予定です。

「Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス 11gリリース1（11.1）」　より抜粋
「今後のリリースではSQL計画管理が使用されるため、ストアド・アウトラインはサポートされなくなります。Oracle Database 11g リリース1（11.1）では、ストアド・アウトラインは以前のリリースと同じように動作します。ただし、新しいアプリケーションでは、SQL計画管理を使用することをお薦めします。」
「Oracle Databaseアップグレード・ガイド11g リリース1（11.1）」より抜粋
「Oracle Database 11g リリース1（11.1）では、ストアド・アウトラインの使用は非推奨となっています。かわりに、SQL計画管理機能を使用する必要があります。」

▲ ページTOPに戻る

パフォーマンス監視のベストプラクティス

運用監視

システムにかかる負荷は日々変化しており、カットオーバー時には想定していなかった負荷状況になることはよくあることです。リクエスト数が増加したり、データ量が増加したり、アプリケーションが追加されたり、設定が変更されたり、様々な要因から影響を受けることで、性能が劣化するケースも少なくありません。

そのような性能問題を事前に回避するために、運用監視は不可欠です。データベースが原因の性能問題の発生を迅速に検知して原因を特定・対処したり、問題の兆候を検知して事前に対処したりといった、プロアクティブなメンテナンスやチューニングの実施がシステムの安定稼動につながります。

データベースの運用監視には、状態監視、エラー監視、領域監視、性能監視などがあります。

Oracle Enterprise Manager では、監視項目の見える化機能を利用したり、しきい値監視やメール等によるアラート通知などの性能監視を的確に行ったりすることで、性能関連のトラブルを未然に防止できます（ ※2）。

図6 レスポンス時間の監視

さらに、Oracle Enterprise Manager Grid Control が使用できる場合には、性能情報やリソース情報などを元に、定期的にキャパシティ・レポートを作成することが可能です。

性能診断

パフォーマンスに関する情報などを監視した際に、性能に問題あると判明した場合には、「自動データベース診断モニター（ADDM）（ ※4）」を使用したデータベースの稼動診断を使用することをお勧めします。ADDM は、デフォルトで1時間に1回自動的に実行され、データベースに性能問題等がある場合には、ADDM の診断結果と解決策のアドバイスが提示されます。

図7 データベースの自動診断

ADDM の診断の結果、性能問題の根本原因がSQL にあると診断された場合には、さらにそこから「SQL チューニング・アドバイザ（ ※5）」を使用して、SQL のチューニングのアドバイスを受けることが可能です。

図8 チューニングに関する推奨

さらに、Oracle Database 11g では、「自動オプティマイザ統計収集」と同様のタイミングで「SQL チューニング・アドバイザ」を自動実行させる「自動SQL チューニング」という新機能が登場しました。自動SQL チューニングでは、データベースが自動で収集した情報の中から負荷の高いSQL をチューニング候補として検出し、その高負荷SQL文に対して自動的にSQLチューニング・アドバイザを実行します。

※1 SQL 計画管理は、Oracle Database 11gR1（11.1.0.X）以上　Enterprise Edition　が必要です。

※2 メール通知にはOracle Diagnostics Pack のライセンスが必要です。

※3 ユーザー定義メトリックの詳細については、「Oracle Enterprise Manager アドバンスト構成」 14 ユーザー定義メトリックをご参照ください。

※4 自動データベース診断モニター（ADDM）を使用するには、Oracle Diagnostics Pack のライセンスが必要です。

※5 SQL チューニング・アドバイザ、自動SQL チューニングを使用するには、Oracle Diagnostics Pack とOracle Tuning Pack のライセンスが必要です。

▲ ページTOPに戻る

「Oracle Database バージョンアップ支援サービス」ご案内

Oracle Directでは、移行を検討されている現在のデータベース状況や、移行検討先のデータベースバージョンなどのヒアリング項目を基に、安全にバージョンアップしていただくための方法について無償でアドバイスいたします。

Oracle Database バージョンアップ支援サービス

ヒアリング項目

既存Oracle DatabaseのVersion/Edition
既存/新規：O/S
アプリケーション開発言語（Java、VBなど）
既存サーバのデータ量（「ざっくりと何GB」程度で結構です）
アプリケーション形態: WEB、C/S等
バージョンアップの動機（ハードウェア・リプレース、パフォーマンス改善、新機能の利用等）
バージョンアップ時の懸念点（アプリケーションの互換性、パフォーマンス劣化等）

▲ ページTOPに戻る

Oracle Database バージョンアップ支援サービスについて、ご不明点やサービスご希望の方は、お気軽にOracle Directまでお問い合わせください。