本コンテンツはORACLE MASTER Gold DBA 2019(Oracle Database AdministrationⅡ)の試験トピックであるOracle Multitenant Architectureのデータベース・パフォーマンスに関する内容をご紹介します。今回は「SQLチューニング・アドバイザについて」の問題を解きながら詳しく解説していきます。

————————————————-

それでは問題文を確認していきましょう。

SQLチューニング・アドバイザの機能について正しい内容を述べているものはどれですか(2つ選択してください)。

1.    チューニングの推奨事項の1つとして索引の作成についての情報が提供される。
2.    特定のワークロードに対してマテリアライズド・ビューやパーティショニングの推奨情報が提供される。
3.    データベース、またはOSレベルでの変更が発生した場合に、変更前、変更後のパフォーマンスの比較ができる。
4.    SQLチューニング・セットを使用して複数のSQL文をグループ化し、チューニング分析を行うことができる。
5.    分析した問合せのオプティマイザ統計に問題がないかチェックを行う。

(解説)

今回はSQL文の分析やチューニングに関するアドバイスを提示してくれる「SQLチューニング・アドバイザ」の機能について解説をしていきましょう。SQLのチューニング作業というのはデータベース管理を行う作業の中でも難易度が高い分野になりますが、Oracleデータベースではチューニングに関する作業を手助けしてくれる様々なツールが用意されています。その中の1つとしてSQLチューニング・アドバイザの機能があり、この機能を使用すると以下の4つの項目について診断結果を提示してくれます。

  • 統計の失効または欠落の検出
  • 実行計画のチューニング(SQLプロファイルの作成)
  • 索引の作成
  • SQL文の再構成(リライト)

各項目について解説をしていきましょう。

統計の失効または欠落の検出

SQLで問合せするオブジェクトに対して統計(※)の失効や欠落がないかチェックを行います。診断を行った結果、統計情報が適切でなかった場合は適切な統計を収集するための推奨事項を生成します。

(※)ここでいう統計というのは第11回で学習したオブジェクトのオプティマイザ統計のことです。

実行計画のチューニング(SQLプロファイルの作成)

SQLの実行履歴を確認し、見積りの検証やSQLのテストを行うことによって、オプティマイザ自身が実行計画を生成するために最も正確な情報を保持しているかどうかを確認します。検証結果から、より最適な実行計画を決定するための補助統計を含むSQLプロファイルを作成します。このSQLプロファイルを実装することで実行計画のチューニングを行うことが可能になります。

索引の作成

各表に対するアクセス効率を改善するために新しい索引が使用できるかどうかを検討します。索引作成が適切と判断できた場合は索引を作成する推奨事項を生成します。

SQL文の再構成(リライト)

パフォーマンスの低下につながる可能性があるSQL構文、意味または設計上の問題の識別を試みます。記述が適切でないSQL文を識別し、それらの再構成方法に関するアドバイスを提供します。

SQLチューニング・アドバイザは個々のSQL文を診断するツールになります。診断元となるSQL情報についてはユーザーが任意で実行するSQL文以外にも自動ワークロード・リポジトリ(AWR)や共有プール上にあるカーソル・キャッシュからも実行することが可能です。SQLチューニング・アドバイザは専用のパッケージであるDBMS_SQLTUNEパッケージを使用しますが、Oracle Enterprise Manager Cloud ControlやSQL*DeveloperといったGUIツールからも操作することが可能です。

SQLチューニング・アドバイザを使用した例としてSQL*Developerから実際に実行した内容を用いて詳細をみていきましょう。

  • SQLチューニング・アドバイザの実行ボタン

SQL*DeveloperからSQLチューニング・アドバイザを実行するにはワークシートを開いて分析対象のSQL文を入力後、次の赤枠にあるSQLチューニング・アドバイザの実行ボタンをクリックします。

  • 分析対象のSQL文

今回は検証用として事前にSHスキーマに「MYCUSTOMERS」と「COUNTRIES」という表を作成し、大量のデータを格納しておきました。SQLチューニング・アドバイザが問題点を検出できるかどうかの検証なので、該当オブジェクトに対してオプティマイザ統計は取らず、また適切な索引も作成していません。さらにヒント句を使って効率の悪い結合方法を選択するように記述しました。

  • 分析結果

SQLチューニング・アドバイザで分析した結果が表示される項目です。「実装タイプ」の下のところに「統計」「SQLプロファイル」「索引」「SQLの再ビルド」という項目があります。この4項目が先に説明させていただいたSQLチューニング・アドバイザが分析できる内容に該当します。各項目をクリックすると分析結果が確認できるので今回の操作で実際に出力された内容を参考例として記載しておきましょう。

「統計」

「SQLプロファイル」

「索引」

「SQLの再ビルド」

※今回の分析では結果無し

「SQLの再ビルド」以外はSQL処理の改善案がSQLチューニング・アドバイザから報告されていることが分かりました。推奨されている事項を実装する場合は該当の推奨項目を選択した状態で「SQLスクリプトの実行」ボタンをクリックすると実装されます。また、実装操作がどのような処理で行われるかについては「SQLスクリプト・ワークシートを開く」をクリックすると確認可能です。次の画像を参考にしてください。

さらに「実装タイプ」を選択した状態で「詳細」のタブをクリックすると各推奨事項の詳細情報や推奨事項を実装した場合にどれだけ改善されるかの比較情報に加え、実装前後の実行計画も確認することができます。今回の検証結果では、SQLプロファイルを適用することでElapsed Time(s)が95%改善されるとの表示があり、また実行計画(※1)もネステッド・ループ結合からマージ結合に変更されCost値(※2)もかなり低くなることが確認できました。以下「詳細」情報の一部抜粋です。

GENERAL INFORMATION SECTION
——————————————————————————-
…

Schema Name    : SH
Container Name  : PDB1
SQL ID               : 48zzcrg7ukwxr
SQL Text            : select /*+ use_nl(c) leading(co c) */ c.cust_id,
                           c.cust_last_name, co.country_name
                           from sh.mycustomers c join sh.countries co
                           on (c.country_id = co.country_id)
                           where cust_id between 20000 and 20010

——————————————————————————-
FINDINGS SECTION (3 findings)
——————————————————————————-

1- Statistics Finding
———————
  表”SH”.”MYCUSTOMERS”は分析されませんでした。

  Recommendation
  ————–
  – この表に対するオプティマイザ統計の収集を検討してください。
    execute dbms_stats.gather_table_stats(ownname => ‘SH’, tabname =>
            ‘MYCUSTOMERS’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);

  Rationale
  ———
    適切な実行計画を選択するには、最新のオプティマイザ統計が必要です。


2- SQL Profile Finding (see explain plans section below)
——————————————————–
  この文により適している可能性のある実行計画が見つかりました。

  Recommendation (estimated benefit: 95.65%)
  ——————————————
  – 推奨されるSQLプロファイルの承認を検討してください。
    execute dbms_sqltune.accept_sql_profile(task_name => ‘staName58123’,
            task_owner => ‘SH’, replace => TRUE);

  Validation results
  ——————
  SQL profileは、その計画と元の計画の両方を実行し、それぞれの実行統計を測定することにより
  テストされました。片方が短時間で完了した場合、計画は部分的に実行された可能性があります。 

                                           Original Plan   With SQL Profile   % Improved
                                           —————  ——————-  —————
  Completion Status:                COMPLETE            COMPLETE
  Elapsed Time (s):                  14.243051                .630101        95.57 %
  CPU Time (s):                       13.896476                .602107        95.66 %
  User I/O Time (s):                              0                          0
  Buffer Gets:                             4449536                 193460        95.65 %
  Physical Read Requests:                      0                          0
  Physical Write Requests:                      0                          0
  Physical Read Bytes:                           0                          0
  Physical Write Bytes:                           0                          0
  Rows Processed:                            1408                    1408
  Fetches:                                        1408                    1408
  Executions:                                         1                         1

…

3- Index Finding (see explain plans section below)
————————————————–
  索引を1つ以上作成すると、この文の実行計画を改善できます。

  Recommendation (estimated benefit: 99.99%)
  ——————————————
  – 物理スキーマ設計を改善するAccess Advisorの実行か、推奨される索引の作成を検討してください。
    create index SH.IDX$$_000C0001 on SH.MYCUSTOMERS(“CUST_ID”,”COUNTRY_ID”,”CUST_LAST_NAME”);

  Rationale
  ———
    推奨される索引を作成すると、この文の実行計画が大きく改善されます。ただし、単一の文では
    なく代理SQLワークロードを使用>した”Access Advisor”の実行が適切な場合もあります。
    この処理により、索引メンテナンス・オーバーヘッドおよび追加領域消費が考慮された包括的な
    索引推奨事項を取得できます。

…

 

 

(※1)今回はORACLE MASTER Gold DBAの試験対策ということで実行計画の見方や結合方法の種類・特徴などについての詳細説明は割愛します。弊社のパフォーマンスチューニング系のコースで取り扱っていますので是非ご検討ください。

(※2)Cost値とは実行される作業単位を数値化したものになります。ディスクI/O、CPU使用量、メモリ使用量が作業単位として使用されます。実行計画を解析する際に合わせて確認する情報になります。

ここまでがSQLチューニング・アドバイザに関する説明になりますが、あわせて確認しておきたいチューニング機能があります。今回のSQLチューニング・アドバイザの検証結果で「索引」の推奨事項のところに ” Access Advisorの実行” という内容が出ています。こちらは「SQLアクセス・アドバイザ」という別のSQLチューニングツールの機能になります。「SQLチューニング・アドバイザ」と「SQLアクセス・アドバイザ」の機能について動作の違いを以下にまとめたので確認してください。

推奨事項の違いもありますが、動作の違いについてもう1つ重要な点があります。SQLチューニング・アドバイザは個々のSQL文についての分析結果を出力しますが、SQLアクセス・アドバイザは特定のワークロード処理(複数のSQL処理)を考慮した分析結果の出力が可能であるという点になります。SQLチューニング・アドバイザが索引作成を推奨事項として表示した場合、分析した個別のSQLに対しての情報はありますが、他のSQL処理の影響についての情報は出力できません。複数のSQL処理についての影響も考慮したいという場合にはSQLアクセス・アドバイザを使用してワークロード全体に対しての推奨事項を分析させるとよいでしょう。尚、SQLチューニング・アドバイザの索引作成の推奨はBツリー索引のみですがSQLアクセス・アドバイザはBツリー索引に加えてビットマップ索引やファンクション索引の作成についての推奨も出力してくれます。

ここまで確認できましたら問題を解くことができるので改めて選択肢を見ていきましょう。

選択肢１は正しい内容です。推奨事項の１つとして索引作成の情報が提供されます。

選択肢２は誤りですね。マテリアライズド・ビューやパーティション化の推奨事項を出してくれるのはSQLアクセス・アドバイザの機能になります。

選択肢３は誤りの選択肢になりますが少し補足しておきましょう。データベースやOSレベルで何らかの変更があった場合にSQLのパフォーマンスに影響が出ないか？という心配をしたことはないでしょうか。特にデータベースのパラメータを変更する、スキーマに変更を加える、アップグレードをするなど、変更前・変更後でパフォーマンスに影響が出ないか検証が必要になったという経験をされた方もいるかと思います。その際に使用できるOracleデータベースのツールとして「SQLパフォーマンス・アナライザ」や「データベース・リプレイ」といった機能があります。環境の変更前後でのパフォーマンス比較を行うことができるツールがあることも併せて押さえておきましょう。

選択肢４も誤りです。SQLチューニング・アドバイザは個々のSQL文の解析に使用できるツールになるので「複数のSQLをグループ化し、チューニング分析」という点が間違っています。

選択肢５は正しい内容です。オプティマイザ統計が適切に取得されているか確認して推奨事項を出してくれます。

以上を踏まえまして正解は １、５ になります。

SQLのパフォーマンス・チューニングという作業は、SQLやデータベースの応用的な知識が必要となるため非常に難しい分野になりますが、OracleデータベースにはSQLチューニングを手助けしてくれる様々なツールがあります。これらのツールも有効に活用しながらチューニングの分野にもどんどんチャレンジしていけると素晴らしいですね。今回の講義はこれで終了になります。お疲れ様でした。

————————————————-

【Oracle University講師によるORACLE MASTER Gold DBA 2019 試験トピック解説講座】トピック一覧

————————————————-

その他の ORACLE MASTER 試験トピック解説講座シリーズ：

【Oracle University 講師による ORACLE MASTER Silver SQL 2019 試験トピック解説講座】-トピック一覧-

————————————————-