11g logo

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

by Arup Nanda Oracle ACE Director

SQLアクセス・アドバイザ

データからではなく、SQL実行の頻度や使用方法に基づいた、パーティション化や索引付け、マテリアライズド・ビューの作成により、スキーマ設計の改善を推奨する機能について学習します。

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

Oracle Database 10gは、最善策の決定を手伝う、多くのアドバイザを提供します。 その1つが、SQLチューニング・アドバイザです。これは、問合せのチューニングについてリコメンデーションを提供するツールで、処理の全体最適化を支援します。

しかし、このようなチューニング・シナリオではどうでしょうか。インデックスは問合せを確実に支援しますが、問合せが1回しか実行されないとします。 問合せはインデックスからのメリットを享受できるものの、インデックスを作成する負担は得られるメリットを上回ってしまいます。 それらを踏まえてシナリオを分析するには、問合せのアクセス頻度と理由を知る必要があります。

この種類の分析には、もう1つのアドバイザであるSQLアクセス・アドバイザが最適です。 Oracle Database 10gでは、インデックスやマテリアライズド・ビューなどを分析しますが、Oracle Database 11gのSQLアクセス・アドバイザはそれに加えて、表や問合せを分析し、最適なスキーマを設計する上で非常に役立つパーティション化戦略の可能性を特定することができるようになりました。 さらに、作成のコストやアクセス構造の維持に関する検討を含めた、ワークロード全体に対するリコメンデーションも提供できます。

この記事では、新しいSQLアクセス・アドバイザで典型的な問題を解決する方法について確認します（注：このデモでは、1つのSQL文で機能を説明しますが、オラクルでは、ワークロード全体をチューニングするために、SQLアクセス・アドバイザを使用することを推奨しています）。

問題

次に典型的な問題を示します。 以下のSQL文は、アプリケーションが発行したものです。 問合せによりリソースが集中し、遅延が発生します。

 
select store_id, guest_id, count(1) cnt
from res r, trans t
where r.res_id between 2 and 40
and t.res_id = r.res_id
group by store_id, guest_id
/

このSQLは、RES表とTRANS表の2つの表を参照しており、後者は前者の子表です。そこで、問合せのパフォーマンスを改善する解決策を探すように指示されたとします。SQLアクセス・アドバイザは、こうした作業に最適のツールです。

アドバイザは、コマンドラインとOracle Enterprise Manager Database Controlのどちらからも利用することができますが、GUIを利用してソリューションを視覚化し、単純なポイントやクリック操作によりタスクを減らすことがより有効です。

Oracle Enterprise ManagerのSQLアクセス・アドバイザを使用してSQLの問題を解決するには、以下の手順に従います。

  1. 最初の作業はもちろん、Oracle Enterprise Managerを起動することです。 データベースのHomeページで、ページの下部までスクロールすると、以下の図のような複数のハイパーリンクがあります。

    図 1
  2. このメニューで「 Advisor Central」をクリックすると、以下のような画面が表示されます。 ここでは、画面の上部のみを示します。

    図 2
  3. 「 SQL Advisors」をクリックすると、以下のような画面が表示されます。

    図 3
  4. この画面では、SQLアクセス・アドバイザのセッションをスケジューリングし、オプションを指定できます。 アドバイザは処理のために、まずはSQL文を収集する必要があります。 一番簡単なオプションは、Current and Recent SQL Activityを指定して共有プールから収集する方法です。 このオプションを選択すると、共有プール内にキャッシュされたSQL文すべてを取得して分析できます。

    もっとも、すべての文ではなく、特定のセットだけを共有プールから取得したい場合もあるでしょう。 それには、別の画面で"SQLチューニング・セット"を作成してから、この画面でセット名を参照指定する必要があります。

    ほかにも、発生を予測した論理的なシナリオに基づき、ワークロードを合成して実施行したい場合もあるかもしれません。 まだ実行されていないSQL文は、共有プールに存在しません。 代わりにこれらの文を作成して、特別な表に格納する必要があります。 3つ目のオプション（ Create a Hypothetical Workload...）にて、スキーマ名と一緒に、この表名を指定してください。

    この記事では、SQL文を共有プールから取得します。 そこで、デフォルト選択として画面に表示されている最初のオプションを選びます。

  5. ただし、すべての文ではなく、重要なものだけを選択します。 たとえば、アプリケーション・ユーザーであるユーザーSCOTTが実行したSQLを分析したい場合です。 そのほかのユーザーは非定型SQL文を発行しており、これらを分析から除外したいと考えてます。 この場合、以下の図にある Filter Optionsの前に付いた"+"サインをクリックします。

    図 4
  6. この画面で、ユーザーを入力するためのテキスト・ボックスにSCOTTと入力し、「 Include only SQL...」ラジオ・ボタン（デフォルト）を選択します。 同様に、ほかのユーザーを排除することもできます。 たとえば、SYS、SYSTEM、およびSYSMAN以外のユーザーのアクティビティすべてを取得したい場合は、これらのユーザーをテキスト・ボックスに入力し、「 Exclude all SQL statements...」ボタンをクリックします。
  7. アクセスする表は、モジュールID、アクション、さらにSQL文の特定の文字列によってフィルタリングできます。 これにより、必要な文だけを分析できます。 全SQLのキャッシュから小さなサブセットを選びだせば、分析は早くなります。 この例では、ユーザーSCOTTは1つの文だけを発行するとします。 そうでない場合は、フィルタリング条件を追加して、分析するセットを最初に問題として取り上げていた1つのSQL文に減らします。
  8. 「 Next」をクリックします。 以下のような確認画面が表示されます（画面の上部のみを表示）。

    図 5
  9. この画面では、どの種類のリコメンデーションを検索するか指定します。 たとえば、今回は、アドバイザにインデックス、マテリアライズド・ビュー、およびパーティション化を検索させたいので、それぞれの用語の横にあるボックスにチェックを入れます。 Advisor Modeでは、2つから選択できます。1つはデフォルト設定のLimited Modeで、負荷の高いSQL文のみ処理されます。 その結果、処理は速くなり、内容に見合った成果がもたらされます。 全SQLを分析する場合は、Comprehensive Modeを使用します（この例では、SQL文は1つなので、これらのモードの選択に意味はありません）。
  10. 画面の下半分には、SQL文の優先度や使用する表領域など、高度なオプションがあります。 デフォルトのままにしてください（これ以降も同様です）。「 Next」をクリックし、スケジューリング画面を表示します。 「 Run Immediately」を選択し、「 Next」をクリックします。
  11. 「 Submit」をクリックします。 これで、スケジューラのジョブが作成されます。 この画面のページ上部にあるジョブのハイパーリンクをクリックします。 ジョブは Runningと表示されます。
  12. 「 Refresh」を繰り返しクリックし、 Last Run Status列の下にある値が SUCCEEDEDに変化するまで繰り返します。
  13. 次にデータベースのHomeページに戻り、ステップ1と同様に「 Advisor Central」をクリックします。以下の図にあるとおり、 SQL Access Advisor行が表示されることを確認します。

    図 6
  14. この画面は、SQLアクセス・アドバイザのタスクが COMPLETEDであることを示しています。 次に、「 View Result」ボタンをクリックします。 以下のような画面が表示されます。

    図 7
  15. 画面にあるとおりです。 SQLアクセス・アドバイザはSQL文を分析しながら、問合せのパフォーマンスを10倍も向上できるソリューションを発見しました。 特定のリコメンデーションの内容を確認するには、「 Recommendations」タブをクリックして、以下のような詳細画面を表示させます。

    図 8
  16. この画面は、若干高度ですが、多くの優れた情報が表示されています。 たとえば、IDが1である文に対し、Actions列の下には2つのリコメンデーションがあり、隣のAction Types列では色の付いた四角形で処理の種類を示しています。 そのすぐ下にあるアイコン・ガイドで、処理の内容がインデックスおよびパーティションに対するものであることが分かります。 いずれも、何倍ものパフォーマンス改善を実現しました。

    SQL文の改善内容を正確に知るには、「 ID」をクリックして、以下の画面を表示させます。 この分析ではSQL文を1つしか使用していないので、1つだけが表示されます。 複数ある場合は、すべてが表示されます。

    図 9
  17. 上記の画面にあるRecommendation ID列のハイパーリンクをクリックし、以下にあるような詳細なリコメンデーションを表示させます。

    図 10
  18. 画面には、非常に明確なソリューションの詳細が提供されています。 リコメンデーションは、2つです。 1つはパーティション化された表を作成する方法で、もう1つはインデックスを使用する方法です。 次に、インデックスはすでに存在することが分かったため、インデックスを維持するようアドバイスしています。

    Action列の下にある「 PARTITION TABLE」をクリックすると、Oracleデータベースが実際に生成するパーティション化された表を作成するスクリプトを確認できます。 ただし、クリックする前に、テキスト・ボックスに表領域の名前を入力してください。 これにより、SQLアクセス・アドバイザはスクリプト構築の際に、その表領域名を使用します。 

    Rem 
Rem Repartitioning table "SCOTT"."TRANS"
Rem 

SET SERVEROUTPUT ON
SET ECHO ON

Rem 
Rem Creating new partitioned table
Rem 
CREATE TABLE "SCOTT"."TRANS1" 
(    "TRANS_ID" NUMBER, 
    "RES_ID" NUMBER, 
    "TRANS_DATE" DATE, 
    "AMT" NUMBER, 
    "STORE_ID" NUMBER(3,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" 
PARTITION BY RANGE ("RES_ID") INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000)
);

begin
dbms_stats.gather_table_stats('"SCOTT"', '"TRANS1"', NULL, dbms_stats.auto_sample_size);
end;
/

Rem 
Rem Copying constraints to new partitioned table
Rem 
ALTER TABLE "SCOTT"."TRANS1" MODIFY ("TRANS_ID" NOT NULL ENABLE);

Rem 
Rem Copying referential constraints to new partitioned table
Rem 
ALTER TABLE "SCOTT"."TRANS1" ADD CONSTRAINT "FK_TRANS_011" FOREIGN KEY ("RES_ID")
     REFERENCES "SCOTT"."RES" ("RES_ID") ENABLE;

Rem 
Rem Populating new partitioned table with data from original table
Rem 
INSERT /*+ APPEND */ INTO "SCOTT"."TRANS1"
SELECT * FROM "SCOTT"."TRANS";
COMMIT;

Rem 
Rem Renaming tables to give new partitioned table the original table name
Rem 
ALTER TABLE "SCOTT"."TRANS" RENAME TO "TRANS11";
ALTER TABLE "SCOTT"."TRANS1" RENAME TO "TRANS";
    スクリプトは実際に新しい表を作成し、元の表と一致するよう名前を変更します。
  19. 最後のDetailsタブには、タスクに関する興味深い内容が示されています。 分析上、重要ではありませんが、アドバイザがどのようにしてその結論に達したのかを知る貴重なヒントが提供されており、自身の思考プロセスの助けとなる場合があります。 画面は2つの部分に別れており、1つは以下の画面にあるとおり、 Workload and Task Optionsです。

    図 11
  20. 画面の下部には、一種のタスクの実行ログが表示されています。 アドバイザでは、すべてのSQL文を処理できない場合があります。 SQL文が破棄された場合、 Invalid SQL String: Statements discardedでカウントされて表示されます。 なぜ一部のSQL文だけが分析されたのかについて、以下に理由が示されます。

    図 12

高度なオプション

上記のステップ10で、高度な設定があると述べました。 次に、どのような設定なのかを確認していきます。

Advanced Optionsの左横にある＋サインをクリックし、以下の画面を表示させます。

図 13


この画面では、インデックスが作成される表領域の名前を入力したり、どのスキーマに作成するかを決定したりできます。 パーティション化のアドバイスについては、表領域を作成するパーティションを指定するなどができます。

もっとも重要な要素は、 Consider access structures creation costs recommendationsチェック・ボックスです。 このボックスにチェックを入れると、SQLアクセス・アドバイザはインデックス自体の作成における負荷を検討するようになります。 たとえば、10の新しいインデックスが潜在的に関連していたなら、その負荷からSQLアクセス・アドバイザは作成をアドバイスしないかもしれません。

これ以外にも、この画面では、インデックスの最大サイズを指定できます。

SQLチューニング・アドバイザとの違い

冒頭では、SQLチューニング・アドバイザとの違いを簡単にしか説明しなかったので、ここでは詳細について確認します。 簡単なデモを利用して、その違いについて説明します。

SQL Advisors画面から「 SQL Tuning Advisor」を選択して実行します。 実行が完了したら、以下のような画面に結果が表示されます。

図 14


次に、リコメンデーションを確認するため「 View」をクリックすると、以下のような画面が表示されます。

図 15


リコメンデーションを詳細に確認します。 TRANS表のRES_ID列へのインデックス作成です。しかし、SQLアクセス・アドバイザではそのリコメンデーションはおこなわれていません。 その代わり、次の理由から表をパーティション化するよう推奨しています。 アクセス・パターンや利用可能なデータから、SQLアクセス・アドバイザは列にインデックスを構築するよりもパーティション化する方がより効率的であると判断したのです。 これは、SQLチューニング・アドバイザのリコメンデーションと比べて、より"現実的"な内容です。

SQLチューニング・アドバイザのリコメンデーションは、以下の4つの目的のいずれかです。

  • 見つからないか、古い統計情報を持ったオブジェクトの統計情報を収集する
  • データの偏り、複雑な条件、またはオプティマイザによる古い統計情報をすべて把握する
  • パフォーマンスの最適化のためにSQLを再構築する
  • 新しいインデックスのリコメンデーションを作成する

こうしたリコメンデーションは1つのSQL文に対するもので、ワークロード全体に対するものではありません。よって、SQLチューニング・アドバイザは、高負荷またはビジネスに不可欠な問合せに対し、時々使用する程度がいいのです。 SQLアクセス・アドバイザに比べて、SQLチューニング・アドバイザは大幅なパフォーマンス改善をもたらすインデックスだけを推奨するため、基準がより甘くなります。 また、SQLチューニング・アドバイザはパーティション・アドバイザを持っていません。

ユースケース

SQLチューニング・アドバイザは、問合せだけでなく、スキーマのチューニングでも役立ちます。 ベスト・プラクティスとして、この戦略を用いて効果的なSQLチューニング計画を策定できます。

  1. 高負荷なSQL文を検索、さらにはワークロード全体を評価する
  2. SQL Tuning Setに推定される文を入力する
  3. SQLチューニング・アドバイザおよびSQLアクセス・アドバイザの両方を使用して分析する
  4. 分析結果を取得し、リコメンデーションを確認する
  5. SQLパフォーマンス・アナライザにリコメンデーションを取り入れる（ この記事を参照）
  6. SQLパフォーマンス・アナライザで変更前と変更後を検証し、ベスト・ソリューションを出す
  7. 最適なスキーマ設計を得るまでタスクを繰り返す
  8. 最高のスキーマ設計を得られたら、SQL Plan Managementのベースラインを使用して計画を確定する（ この記事で解説）

結論

データベース構造のチューニングは、もっとも労力がかかる作業ですが、同時にもっとも実りのあるタスクでもあります。 同様に、パーティション化は効果的なチューニングの手段ですが、パーティションの選択は簡単に決定できません。 SQLアクセス・アドバイザは、こうしたプロセスで必要なサポートを提供します。

"Oracle Database 11g ：DBAと開発者のための主要な機能"ホームページに戻る
Arup Nanda Arup Nanda（ arup@proligence.com）は、Oracle Databaseテクノロジのあらゆる分野で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冊の書籍を共同で執筆しています。