Oracle Database のオブティマイザ統計収集の実行履歴を確認できることをご存知ですか。

この記事ではDBMS_STATSパッケージを使用した統計収集操作レポートの作成についてご紹介いたします。

統計収集操作レポート

統計収集操作レポートは、Oracle Databaseの内部タスクである自動オプティマイザ統計収集やユーザが任意に実行するDBMS_STATS.GATHER_*_STATSプロシージャの実行履歴をリスト化したものです。

統計収集操作レポートには二種類のレポートがあり、「DBMS_STATS.REPORT_STATS_OPERATIONS」ファンクションまたは「DBMS_STATS.REPORT_SINGLE_STATS_OPERATION」ファンクションを使用して作成することができます。それぞれのレポートで確認できる内容は「表1. 統計収集操作レポート」に示します。

なお、これ以降、各ファンクションで作成されるレポートを「統計操作レポート」、「統計操作タスクレポート」と表記いたします。ご了承ください。

【補足】
これらのレポート表記はあくまで本記事における便宜上のものです。製品リファレンスを見ても、統一感を持ったレポート名表記を見つけられなかったためです。また二種類のレポートを区別するためには「統計収集操作レポート」という表記を少し簡略化したかったこともあります。
「AWRレポート」のような使いやすい名前があるといいですね。

表1. 統計収集操作レポート

レポート名 (この記事での表記)	説明
統計収集操作レポート	以下の二つのレポートの総称とします。
統計操作レポート	DBMS_STATS.REPORT_STATS_OPERATIONSで作成するレポートです。 任意の期間に実行された統計収集操作のリストを作成します。 自動オプティマイザ統計収集やユーザが実行したDBMS_STATS.GATHER_*_STATSプロシージャの実行履歴を確認することができます。
統計操作タスクレポート	DBMS_STATS.REPORT_SINGLE_STATS_OPERATIONファンクションで作成するレポートです。 統計操作レポートでリストされた個々の操作に対する詳細レポートを作成します。操作の中で統計収集の対象となったオブジェクトのリストを確認することができます。

統計収集操作レポートの作成例

統計収集操作レポートを作成してみます。

実行環境は以下のとおりです。

実行環境

OS	Oracle Linux 7.8 (x86-64)
Oracle バージョン	Oracle Database 19c Enterprise Edition
構成	シングル・インスタンス CDB
その他	Swingbench でSOEスキーマを作成。

HTML形式のレポートを作成するためにファクションの実行をスクリプト化します。
以下に各レポートに対するSQLスクリプトを示します。
SQLスクリプトはレポート作成の例を示すための最低限の設定で記述したものです。

統計操作タスクレポートの作成

実行するSQLスクリプトは以下のものです。

サンプルSQLスクリプト：generate_optstat_ops.sql

SET LONG 1000000 SET ECHO OFF SET TERMOUT OFF SET HEADING OFF SET MARKUP CSV ON QUOTE OFF SPOOL optstat_ops.html SELECT DBMS_STATS.REPORT_STATS_OPERATIONS( SINCE=>SYSDATE-1 ,UNTIL=>SYSDATE ,FORMAT=>'HTML' ) FROM DUAL; SPOOL OFF

【説明】

• 統計操作レポートの出力ファイル名は optstat_ops.html です。出力先はカレント・ディレクトリになります。
• レポートの期間はスクリプト実行した時点から直近1日（24時間）です。
• 「OPID=>540」でレポート対象の統計収集操作を指定しています。540は前項で出力した統計操作レポートから選択した値です。
• 「FORMAT=>HTML」でHTML形式のレポートを指定しています。
• 順序が前後しますが「SPOOL optstat_ops.html」で出力ファイル名を指定しています。FORMATに合わせて拡張子を「.html」にしています。

【補足】

• 「MARKUP CSV ON QUOTE OFF」は旧来、不要な空白行や行の長さを調節するために PAGESIZE と LINESIZE を駆使していた部分の代用です。
  本来は出力結果をCSV形式で出力するための設定ですが少々変則的な使い方になっています。
  スクリプトで実行しているSELECT文は単一列のため実質的にカンマが挿入されるべき文字の区切りがありません。結果的に生成されたHTMLソースが単一列としてコンソール画面の幅に制限されずに結果を得ることができています。また「QUOTE OFF」で出力値ごとの引用符を外すことで、HTMLソースの先頭と末尾に余計な引用符がつかないようにしています。
  
  

サンプルSQLスクリプトを実行環境に配置してSQL*Plusで実行してみます。

$ sqlplus / as sysdba SQL> @generate_optstat_ops.sql SQL> exit $ ls -l ./optstat*.html -rw-r--r-- 1 oracle oinstall 8528 Mar 1 20:50 optstat_ops.html

作成されたHTMLファイルをWEBブラウザで開いて確認します。

作成された統計操作レポートの例です。

続いて統計操作タスクレポートを作成してみます。

統計操作タスクレポートの作成

統計操作タスクレポートを作成するためには、DBMS_STATS.REPORT_SINGLE_STATS_OPERATIONに操作IDを指定する必要があります。

前項で作成した統計操作レポートの中から操作ID=540の操作について選択してみます。
この操作はContainer ID=4でSOEスキーマに対してgather_schema_statsプロシージャを実行しています。

統計操作レポートの手順と同様にSQLスクリプトを用意します。

サンプルSQLスクリプト：generate_optstat_tasks.sql

SET LONG 1000000 SET ECHO OFF SET TERMOUT OFF SET HEADING OFF SET MARKUP CSV ON QUOTE OFF SPOOL optstat_540.html SELECT DBMS_STATS.REPORT_SINGLE_STATS_OPERATION( OPID=>540 ,FORMAT=>'HTML') FROM DUAL; SPOOL OFF

【説明】

• 「OPID=>540」でレポート対象の統計収集操作を指定しています。540は前項で出力した統計操作レポートから選択した値です。
• 「FORMAT=>HTML」でHTML形式のレポートを指定しています。
• 順序が前後しますが「SPOOL optstat_540.html」で、出力ファイル名を指定しています。
  レポート対象の操作IDを識別できるよう操作ID（540）を埋め込むとともにFORMATに合わせて拡張子を「.html」にしています。

サンプルSQLスクリプトを実行環境に配置してSQL*Plusで実行してみます。

$ sqlplus / as sysdba SQL> @generate_optstat_task.sql SQL> exit $ ls -l ./optstat*.html -rw-r--r-- 1 oracle oinstall 34363 Mar 1 20:54 ./optstat_540.html

出力されたレポートの例はこちらです。行数が多いので冒頭のみの抜粋になっています。

おわりに

Oracle Database のオブティマイザ統計収集の実行履歴を確認する方法をご紹介しました。いかがでしたでしょうか。

皆さんの参考になれば幸いです。

以上