Oracle Database 11g： DBAと開発者のための主要な新機能 著者：Arup Nanda

SQL Performance Analyzer

データベースに発行された実際のSQL文を用いて、システム変更の影響を的確に予測する方法を学習します。

Oracle Database 11gをダウンロードする

このシリーズの 以前の記事では、データベースの実際のワークロードを取得し、必要に応じて再生する優れたツール、Database Replayについて学習しました。Database ReplayはReal Application Testingオプションの一部であり、データベースで実際に発生したワークロードを再生することができます。

Database Replayでは、取得したすべてのワークロードをデータベース上で再生します。では、再生したくない場合はどうすればいいのでしょうか。たとえば、アプリケーションのパフォーマンスや可用性に著しい影響があるので、SQL実行計画の変更方法とその結果生じるSQLパフォーマンスへの影響を知りたい場合があります。また、Database Replayは取得済みワークロードだけを再生します。本番環境でまだ実行されていないSQL文に対して、パラメータ変更が与える影響を把握したい場合もあります。

そのような場合に、Real Application Testingファミリの重要なコンポーネントであるSQL Performance Analyzer（SPA）が有効です。SPAにより、特定のSQLやすべてのSQLワークロードをいくつかのシステム変更例に対して再生できます。システム変更例とは、初期化パラメータの変更、オプティマイザによる統計情報のリフレッシュ、データベースのアップグレードなどです。その後、これらの影響を評価するための比較レポートを生成します。この記事では、こうした重要な問題を解決するSPAについて学習します。

問題サンプル

テストをしてみましょう。まず、解決する問題を定義します。

問題は典型的なものです。Oracleデータベースがインデックスを使用しないため、その理由を確かめることにしました。この問題を解決するため、Oracleデータベースに詳しいTim Gormanの記事『Searching for Intelligent Life in Oracle's CBO』を参照しました（この記事はさまざまな形式でWeb上に掲載されています）。

前述の記事での提案は、optimizer_index_cost_adjのパラメータ値をデフォルトの100より小さい値に変更するというものでした。また、値を特定するための計算式も記載されていました。計算式に基づいて値を計算したところ、今回は10になりました。しかし、ここで難問にぶつかります。この変更を加えることは、すべてのSQL文にとってメリットがあるのでしょうか。

Oracle Database 11gより前のリリースでは、すべてのSQL文を取得して、トレースをしながら実行し、実行計画を取得していました。これは膨大な時間がかかる上に、エラーもよく発生する方法でした。新しいリリースでは、この作業をする必要がありません。作業の代わりに、非常に簡単で効果的なSQL Performance Analyzerを使用すればいいのです。

最初に、検証のために、データベースで以下の問合せを実行します。

select /* CONTROL_QUERY11 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 33;
select /* CONTROL_QUERY12 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 350;
select /* CONTROL_QUERY13 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 351;
select /* CONTROL_QUERY14 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 534;
select /* CONTROL_QUERY15 */ sum(QUANTITY_SOLD) from sales where PROMO_ID = 999;

select /* CONTROL_QUERY21 */ sum(QUANTITY_SOLD) from sales where channel_id = 2;
select /* CONTROL_QUERY22 */ sum(QUANTITY_SOLD) from sales where channel_id = 3;
select /* CONTROL_QUERY23 */ sum(QUANTITY_SOLD) from sales where channel_id = 4;
select /* CONTROL_QUERY24 */ sum(QUANTITY_SOLD) from sales where channel_id = 5;
select /* CONTROL_QUERY25 */ sum(QUANTITY_SOLD) from sales where channel_id = 9;

これらの問合わせはアプリケーションで発行されているものとします。コメントをSQL文に入れておけば、後の検索に役立ちます。SQL文を実行すると、SPAをそれらのSQL文に対して使用できるようになります。

使い方

SPAの機能を最大限に活かす方法は、Oracle Enterprise Managerから使用することです（提供されたdbms_sqlpaパッケージからコマンドライン・オプションで使用することもできますが、Oracle Enterprise Managerの管理性のほうが優れています）。

使用するには、以下の手順を実行します。

1. Oracle Enterprise Manager Database Controlを起動し、「 Performance」タブをクリックします。ページ最下部までスクロールすると、以下のようなハイパーリンクがあります。
   
   
   
2. 「 Search Sessions」をクリックすると、以下のような画面が表示されます。
   
   
   
3. カーソル・キャッシュで実行したSQL文中からでパターンを検索します。SQL文に、CONTROL_QUERYn─というコメントがあるものを確認します。nは、11、12などになります。検索条件に該当する、文字列を入力します。これにより、以前実行したSQL文すべてを検出します。上記の画面に、「 Save to a new SQL Tuning Set」という ラジオ・ボタンを選択しSQL Tuning Se（t STS）名としてCONTROL1と入力します。
   
   
4. 「 SQL Tuning Sets」をクリックすると、SQL Tuning Setsページが表示されます。次に、 CONTROL1という名前のSTSを選択します。このページでは、STSを確認し、SQLを追加してドロップできます。STSページのスクリーンショットは、以下のとおりです。
   
   
   
5. Performanceページで、「 SQL Performance Analyzer」をクリックします。以下のようなメインSPAページが表示されます。
   
   
   
6. ご覧のとおり、SPAタスクはまだ定義されていないので、ここで定義します。このサンプルでは、optimizer_index_cost_adjパラメータの変更による影響を評価することが目的なので、「 Parameter Change」をクリックします。以下のようなSPA Task Definitionページが表示されます。
   
   
   
7. ページ内では、SPAタスクに関する情報を入力する必要があり、まず、 タスクに名前を付けます。このサンプルでは、STS1とします。
   
   
8. 次に、SQL Tuning Set名を入力します。隣にある懐中電灯型のアイコンをクリックし、CONTROL1という名前のSTSを選択します。
   
   
9. Parameter Changeセクションで、変更するパラメータを入力します。懐中電灯型のアイコンをクリックし、「 optimizer_index_cost_adj」パラメータを選択します。現行の値はすでに入力されているので、「 Changed Value」ボックスにターゲット値を入力します。
   
   
10. 次に、これらの変更を比較する方法を決定します。比較方法には、経過時間、CPU時間などがあります。このサンプルでは、「 Buffer Gets」を選択します。
    
    
11. 最後に、このSPAタスクを実行するようスケジューリングします。「 Immediately」ラジオ・ボタンを選択して、直ちに実行するようにします。
    
12. 詳細すべてを設定したら、「 Submit」をクリックすると、個別に監視できるジョブが作成されます。そのほか、以下の画面のとおり、このページからSPAタスクのステータスを監視することもできます。
    
    
    
13. 「 Refresh」をクリックして、現在のタスクのステータスを確認します。 Last Run Status列の下にあるアイコンを確認します。アイコンの詳細は、以下のとおりです。
    
    
    
14. 画面のアイコンは、タスクが実行中であることを示しています。「 Refresh」を何度かクリックします。アイコンが「 Completed」に変化したら（チェックマークが表示されたら）、SPAタスクは完了です。
    
    
15. SPA Task名（ STS1）をクリックして、比較メトリックを表示します。以下のような画面が表示されます。
    
    
    
16. Comparison Report列の下にメガネ型アイコンが表示されています。このアイコンをクリックすると、パラメータ変更前と後のSQL実行結果を比較できます。
    
    
17. これが、この機能の素晴らしい点です。以下のような画面が表示されます。
    
    
    

これこそが、求められていた機能です。左上部に、変更を加える前と後のSQL実行結果の比較が表示されます。総体的に、パフォーマンスが60%向上していることが明確に分かります。画面の右側には、実行計画を変更したSQL文がいくつあるかが明記されています。多くのSQL文の実行計画に変更が生じたことが分かります。

画面の下部は、このタスクで分析したSQL文のSQL IDが記載されています。SQL IDの前にある小さな矢印は、SQL文が改善されたか、悪化したかを示しており、SQL IDの次にある数字は影響率を示しています。これらの数字から、各SQL文のパラメータ変更による影響を明確に知ることができます。必要に応じて、SQL IDをクリックするとSQLを検証できます。最初に表示されたSQLは、もっとも影響のあったものです。

画面には、多くのSQLの統計情報が表示されています。そして、画面の下部には、実行計画の比較が表示されます。

これから分かるように、インデックスはあまりバッファを使用しませんが、本当にそうでしょうか。別のSQLを見てみましょう。

前の31.95%の向上と比較して、ここでは0.48%と向上率はわずかです。これはなぜでしょうか。答えを知るには、SQL IDをクリックして、以下のような画面を表示させます。

この画面から、何が変わったのかが明確に分かります。CPU時間のせいで、経過時間が実質0.504秒から1.022秒に上がっています。どうしてでしょうか。データ分布パターンを見ると、promo_idが以下のように分布していました。

SQL> select promo_id, count(1) cnt from sales group by promo_id;
 
   PROMO_ID        CNT
---------- ----------
       534          1
       999     887837
       350      18022
        33       2074
       351      10910
           ----------
sum            918844

promo_id 999が表中に887,837回も登場しており、表のほぼ97%を占めています。インデックス・スキャンを含めるよう計画を変更したとき、問合せに負荷がかかりすぎたのです。全表スキャンの方がよかったということです。つまり、総体的な影響はプラスだったのですが、条件によってはマイナスだったのです。パラメータを変更するかどうかを決定する際、プラスになると同時にマイナスにもなるような、これらのSQL文の重要性を考慮する必要があります。

これまでの手順で、データベースのパラメータに非常に重要な変更を加えたときの影響を予測しようとしました。SPAを使用すれば、潜在的なパフォーマンスの影響を予測あるいは"想像"する必要はありません。そうではなく、データベースに対してアプリケーションが実行するSQL文を用いて客観的に計測できます。

別の例を見てみましょう。パラメータに変更を加えた後、パフォーマンスは改善せずに悪化しました。スクリーンショットは、以下のとおりです。

SQL文のパフォーマンスは、すべて悪化しています。問題を改善するには、SQL Plan Management（ この記事を参照）を活用します。SQL Plan Management （SPM）によって、すぐれた実行計画をベースラインとして選択できるので、実行計画が安定します。オプティマイザは選択した実行計画を常に使用します。このベースラインとした実行計画は、無効化されるか新しい実行計画を選択するまで使用されます。悪化したSQLのもう1つの解決方法として、SQL Tuning Advisorがあります。SQL Tuning Advisorは、SQLチューニング・リコメンデーションやパフォーマンス改善のためのインデックス作成などを提案します。

ユースケース

SPAは、さまざまなシナリオにおいて使用される、ほかに類のないツールです。データベース・バージョンのアップグレード、データベース・パッチセットの適用、データベースのパラメータ変更、およびオプティマイザのパラメータ変更は、そのごく一部です。

たとえば、オプティマイザのパラメータを10.2から11.1にアップグレードする場合、この変更がSQL文にどのような影響を与えるのでしょうか。これを確認するのに最適なツールが、SPAです。前章の手順の途中から、 Database Parameter Changesではなく「 Optimizer Changes」を選択します。すると、以下のような画面が表示されます。

この画面で、適切な情報とターゲットとなるオプティマイザのバージョンを選択し、残りのステップを実行します。

結論

この新しいツールをいつ使うのが最適でしょうか。変更を加えるときであればいつでもというのがその答えです。実際のSQL文を見られないDatabase Replayとは異なり、SPAを使用すると特定のSQLやアプリケーションのSQLワークロード全体に対する結果を確認することができます。良い点と悪い点を検証し、アプリケーションのパフォーマンスを損ねることのない、考えられる最良の代案へと到達できます。これまで結論に到達できなかったのは、選択肢が白と黒といったように明確ではなく、グレーだったからです。SPAは、このようなグレーな状況をいずれかの方向へ導き、結論を出しやすくします。

---

Arup Nanda（ arup@proligence.com）は、Oracleデータベーステクノロジのあらゆる分野で12年以上の経験を持つ優秀なOracle DBAであり、2003年の『Oracle Magazine』で"DBA of the Year"に選ばれました。Arupは、オラクル関連のイベントの講演や雑誌の寄稿を頻繁におこなっている Oracle ACE Directorです。また、『 RMAN Recipes for Oracle Database 11g: A Problem Solution Approach』を含む4冊の書籍を共同で執筆しています。