本コンテンツはORACLE MASTER Gold DBA 2019(Oracle Database AdministrationⅡ)の試験トピックであるOracle Multitenant Architectureのデータベース・パフォーマンスに関する内容をご紹介します。今回は「Oracleオプティマイザとオプティマイザ統計アドバイザについて」の問題を解きながら詳しく解説していきます。
————————————————-
それでは問題文を確認していきましょう。
Oracleのオプティマイザ統計とオプティマイザ統計アドバイザに関する内容で正しく述べているものを選択してください(2つ選択してください)。
1. SQLの実行計画が適切に生成されるためにオプティマイザ統計は必ず手動で取得する必要がある。
2. オプティマイザ統計は表や索引といったオブジェクトの詳細情報であり、データ格納後に1度だけ実行する必要がある。
3. オプティマイザ統計はデフォルトでは自動メンテナンス・タスクにより自動で取得される。
4. オプティマイザ統計アドバイザはオプティマイザ統計の品質や失効といった状態をチェックすることができる。
5. オプティマイザ統計アドバイザは統計情報に問題があったオブジェクトに対して自動的にアクセス制限を行う。
(解説)
今回はデータベースのパフォーマンス維持のために重要な情報であるオプティマイザ統計に関する内容をみていきましょう。クライアントからSQL文が実行されるとそのSQL文はオラクルのサーバー・プロセスによって解析され「実行計画」が生成されます。この実行計画というのは、対象のデータに対してもっとも効率良くアクセスするための手順や経路のような情報が含まれていると考えればよいと思います。この実行計画を生成するための元となる情報が「オプティマイザ統計」になります。このオプティマイザ統計が適切に取得できていない場合、実行計画も適切なものが生成できずSQLのパフォーマンス劣化などの問題が発生しやすくなります。
このオプティマイザ統計と実行計画の関係性について理解しやすいように身近な話に置き換えて考えてみたいと思います。例えば皆さんが九州から北海道まで移動しなければいけないとなった場合を想像してください。どのような移動手段(経路)を使うか？に加えて費用面や時間などの効率化も考えますよね。「飛行機」を使ったほうがいいか、「船」が効率がいいか、無難に「電車」か、電車であれば新幹線がいいのか、普通のローカル線がいいのか、「車」がいいのか「自転車」がいいのか、移動手段として沢山の選択肢があると思いますがその中からどのように最適なものを選んだり組み合わせたりするか考えてみてください。
おそらく皆さんは各乗り物の情報をチェックして最適な移動手段を組み合わせていくと思います。公共交通機関の時刻表だったりWebページを使って経路や時間など調べたり様々な情報をチェックした上で行動を開始すると思います。このとき確認した移動手段に関する情報が古いものだったり、誤った情報だったりした場合にどうなるか考えてみるといいでしょう。例えば「北海道までは1kmしかないので自転車のみを使えば早く到着できて、費用も一番効率がよい」という誤った情報をもとに九州を出発してしまったら・・いつまでたっても北海道にたどり着けないという事態が発生するかと思います。これをオプティマイザ統計とSQLの実行計画に置き換えてみたいと思います。
オプティマイザ統計というのは表や索引といったオブジェクトの情報になります。例えばデータ件数であったり
データ長や使用しているブロック数、異なる値(個別値)がどれぐらい格納されているかなど、様々な情報を持っています。このオプティマイザ統計の情報が適切でなかった場合として、少し極端な内容ではありますが１つ例をあげてみたいと思います。索引が作成されている表にデータが1億件入っていたとします。しかしこの表や索引のオプティマイザ統計の情報が古かったり、失効しているなどの問題があって表の中に10件のデータが入っているとデータベースが誤認識しているとします。このような状態で適切な実行計画が生成できるか？というのを考えていただければと思います。データベースはオプティマイザ統計の情報から「表には10件のデータがある」と見積もりをし、「10件の検索なら索引経由ではなく表に対してフルアクセス(全件検索)したほうが効率がよい」と判断したとしましょう。こうして表にフルアクセスを行う実行計画を用いてSQLを実行したところ、表の中に実際は1億件のデータが入っていたので全く検索処理が終了しないという事態が発生するわけです。「自転車で北海道に向けて出発したけどまったく到着できない！2000km以上ある(涙)。。参考にした情報が間違っていた・・」という内容と同じようなイメージだと考えると分かりやすいと思います。
ここまでの説明でオプティマイザ統計を正しく取得するということは重要であると理解できたのではないかと思います。オプティマイザ統計は実行計画のコスト値の計算に使用される重要な情報であるためOracle 10gのバージョンより自動で収集される機能が搭載されています。「自動メンテナンスタスク」という機能の中にオプティマイザ統計の自動収集が組み込まれており、特定の時間帯(メンテナンスウィンドウ)にて毎日1回収集される動作があります。メンテナンスウィンドウのデフォルトの時間帯は以下となっています(時間帯は変更可能)。
- 平日(月曜日～金曜日)の２２：００から４時間後の２：００の間まで
- 週末(土曜日、日曜日)の６：００から２０時間後の２：００の間まで
「なんだ、自動で取得されるなら気にすることないじゃないか」と思った方、そうではありません。あくまで統計収集が動作するのは設定された時間帯になるので、仮に自動統計収集の動作後にバッチ業務などで大量のデータ更新があった場合などはオプティマイザ統計の情報と実際の実測値に乖離が発生する可能性があります。その場合は別途手動でオプティマイザ統計を取得する必要があるということです。オプティマイザ統計を手動で取得するにはDBMS_STATSパッケージを使用して取得します。お客様の業務形態にあわせて、必要に応じて手動で取得することも検討してください。特にイレギュラーなメンテナンス作業などが発生した場合は要注意ですね。
DBMS_STATSパッケージでよく使用するプロシージャと簡単な説明を以下に記載しておきます。
その他オプションなどの詳細については以下のマニュアルを参考にするとよいでしょう。
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 19c
173 DBMS_STATS
オプティマイザ統計収集は非常に重要であるということで、Oracle 12cR2より「オプティマイザ統計アドバイザ」という機能も提供されています。こちらの機能は、現在使用しているオプティマイザ統計収集の方法が適切であるか、取得されているオプティマイザ統計の品質に問題がないかなど、より適切な状態となるようにアドバイスを提供してくる機能になります。こちらの機能がどのように動作するのかについても説明しておきましょう。オプティマイザ統計アドバイザは、使用しているOracle Databaseのリリースにおいてベスト・プラクティスな状態になるよう、あらかじめ定められているルールを元にチェックが行われます。Oracle Database 19cでは23個のルールが存在しており(※)、このルールを大きく分けると「SYSTEM」「OPERATION」「OBJECT」の3つのクラスに分類することができます。個々のルールについてはv$stats_advisor_rulesビューで確認することができます。3つのクラスについての説明は次のとおりです。
- SYSTEM (v$stats_advisor_rulesのRULE ID が1～6)
このクラスは、統計収集のプリファレンス、自動統計収集ジョブのステータス、SQL計画ディレクティブの使用などを確認します。 - OPERATION (v$stats_advisor_rulesのRULE ID が7～10)
このクラスは、統計収集操作に関してチェックを行うルールで、統計収集の際にデフォルト値を使用しているか、SET_%_STATSプロシージャ（ユーザー定義の統計設定）を使用して作成されているかなどを確認します。 - OBJECT (v$stats_advisor_rulesのRULE ID が11～23)
このクラスは、統計の品質、統計の失効、統計の不要な収集などを確認します。
(※) Oracle Databaseのリリース毎のベスト・プラクティスに基づくルールになるため、ルール内容は変更される場合があります
オプティマイザ統計アドバイザのアドバイザ・タスクはメンテナンス・ウィンドウで自動的に実行されますが、必要に応じて手動で実行することもできます。手動で実行する場合はDBMS_STATSパッケージを使用します。代表的なサブプログラムの説明について以下にまとめておきましょう。
実際にオプティマイザ統計アドバイザを手動で実行した結果も載せておきます。[アドバイザ・タスクの作成]→[タスクの実行]→[レポート結果の表示]と出来るだけ簡単な内容にしました。パッケージ操作の詳細は以下のopt_advisor.sqlを確認してください。
スクリプト：opt_advisor.sqlの内容
SET LONG 1000000
SET LONGCHUNKSIZE 100000
SET SERVEROUTPUT ON
SET LINE 300
SET PAGES 1000
DECLARE
task_name VARCHAR2(128) := ‘ADVISOR_TASK_1’ ;
exec_name VARCHAR2(128) := NULL ;
report CLOB := NULL ;
script CLOB := NULL ;
BEGIN
task_name := DBMS_STATS.CREATE_ADVISOR_TASK(task_name);
exec_name := DBMS_STATS.EXECUTE_ADVISOR_TASK(task_name);
report := DBMS_STATS.REPORT_ADVISOR_TASK(task_name);
DBMS_OUTPUT.PUT_LINE(report);
END;
/
— タスクを削除する場合は以下を実行してください
exec DBMS_STATS.DROP_ADVISOR_TASK(‘ADVISOR_TASK_1’);
解析を実施する前に、あらかじめ自動メンテナンス・タスクを無効化にし、HRスキーマのEMPLOYEES表のオプティマイザ統計を削除した状態で実行してみました。
$ . sqlplus system/oracle_4U@pdb1 ※プラガブルデータベースPDB1に接続
SQL> @opt_advisor.sql ※オプティマイザ統計アドバイザのスクリプト実行
GENERAL INFORMATION
——————————————————————————-
Task Name : ADVISOR_TASK_1
Execution Name : EXEC_74
Created : 04-16-24 01:00:52
Last Modified : 04-16-24 01:00:59
——————————————————————————-
SUMMARY
——————————————————————————-
For execution EXEC_74 of task ADVISOR_TASK_1, the Statistics Advisor has 4
finding(s). The findings are related to the
following rules: USEAUTOJOB,
USECONCURRENT, USEDEFAULTPARAMS, AVOIDSTALESTATS. Please refer to the finding
section for detailed information.
——————————————————————————-
FINDINGS
——————————————————————————-
Rule Name: UseAutoJob ※v$stats_advisor_rulesのRULE_IDが「1」の内容
Rule Description: Use Auto Job for Statistics ※自動統計収集ジョブを使用してくださいという説明
Collection
Finding: Automatic statistics collection is not enabled because statistics
collection is disabled for Automated Maintenance Tasks.
Recommendation: Enable automatic optimizer statistics collection using
DBMS_AUTO_TASK_ADMIN PL/SQL package or through Oracle
Enterprise Manager.
Example:
— Enable Optimizer Statistics Auto Task for Monday window:
BEGIN
dbms_auto_task_admin.enable(
client_name => ‘auto optimizer stats collection’, ※自動統計収集を有効化する方法例
operation => ‘auto optimizer stats job’,
client_name => ‘MONDAY_WINDOW’);
END;
Rationale: Automatic statistics job avoids the majority of the issues with
query optimizer statistics gathering. The
recommended options are
used and statistics gathered for those objects only that need new
statistics. The need for manual tasks for managing the statistics
is eliminated, which significantly reduces the chances of getting
poor execution
plans because of missing or stale statistics.
—————————————————-
…
—————————————————-
Rule Name: AvoidStaleStats ※v$stats_advisor_rulesのRULE_IDが「12」の内容
Rule Description: Avoid objects with stale or no statistics ※古くなった統計や統計が取得されていない
Finding: There are 1 object(s) with no statistics. オブジェクトの使用は避けてくださいという説明
Schema:
HR
Objects:
EMPLOYEES ※オプティマイザ統計が取得できていないオブジェクト情報
Recommendation: Gather Statistics on those objects with no statistics.
Example:
— Gathering statistics for tables with stale or no statistics in schema, SH:
exec dbms_stats.gather_schema_stats(‘SH’, options =>
‘GATHER AUTO’)
Rationale: Stale statistics or no statistics will result in bad plans.
—————————————————-
オプティマイザ統計アドバイザがルールを元に問題点を結果として出力してくれていることが分かるかと思います。
ここまで確認ができましたら正解の選択肢を選ぶことができるので改めて問題を確認していきましょう。
選択肢１は誤りです。オプティマイザ統計は自動メンテナンス・タスクにより自動で取得されますが、必要に応じて手動で取得する場合もあります。「必ず手動で取得」というわけではありません。
選択肢２も誤りです。前半の文章の内容は正しいですが、後半の「データ格納後に1度だけ実行」という部分が間違っています。まったくデータの更新処理がない場合はデータ格納後に1回実行すればよいですが、多くのデータにおいては日々更新がされると思います。オプティマイザ統計が古くなっていくと最適な実行計画を生成できなくなるため定期的に統計情報は取得する必要があります。
選択肢３は正しい内容になります。
選択肢４も正しい内容です。オプティマイザ統計アドバイザの説明そのものになります。
選択肢５は誤りです。オプティマイザ統計アドバイザは統計情報に問題があったオブジェクトに対して推奨事項のレポートや推奨されるアクションを実装するスクリプトの生成、また推奨するアクションを実装する機能がありますがオブジェクトに対してアクセス制限を行うような動作はありません。
以上を踏まえまして正解は ３、４ になります。
オプティマイザ統計がSQLパフォーマンスの維持のために非常に重要な情報であることが認識できたのではないかと思います。Oracle Databaseにはオプティマイザやチューニング関連の機能が他にも様々ありますが、今回の内容は重要な基礎部分が含まれるためしっかりと確認をしていただいて次のステップに進んでいただくとよいと思います。今回の講義はこれまでとします。お疲れ様でした。
————————————————-
【Oracle University講師によるORACLE MASTER Gold DBA 2019 試験トピック解説講座】トピック一覧
————————————————-
その他の ORACLE MASTER 試験トピック解説講座シリーズ：
【Oracle University 講師による ORACLE MASTER Silver SQL 2019 試験トピック解説講座】-トピック一覧-
————————————————-