「高度なSQL実行計画の取得」を実践する：しばちょう先生の試して納得！ DBAへの道（改）（4）（2/3 ページ）

[柴田長，日本オラクル株式会社]

演習2：「dbms_sqltune」パッケージ内の全プロシージャとファンクションを確認する

　「SQL*Plus」から「TRY」ユーザーでデータベースに接続し、「desc」コマンドを実行します。

$ sqlplus TRY/TRY12345
SQL>
desc DBMS_SQLTUNE
　
FUNCTION ACCEPT_SQL_PROFILE RETURNS VARCHAR2
 引数名                         タイプ                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TASK_NAME                      VARCHAR2                IN
 OBJECT_ID                      NUMBER                  IN     DEFAULT
 NAME                           VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 CATEGORY                       VARCHAR2                IN     DEFAULT
 TASK_OWNER                     VARCHAR2                IN     DEFAULT
 REPLACE                        BOOLEAN                 IN     DEFAULT
 FORCE_MATCH                    BOOLEAN                 IN     DEFAULT
 PROFILE_TYPE                   VARCHAR2                IN     DEFAULT
PROCEDURE ACCEPT_SQL_PROFILE
 引数名                         タイプ                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TASK_NAME                      VARCHAR2                IN
　
...(省略)...
　
FUNCTION REPORT_SQL_MONITOR RETURNS CLOB
 引数名                         タイプ                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN     DEFAULT
 SESSION_ID                     NUMBER                  IN     DEFAULT
 SESSION_SERIAL                 NUMBER                  IN     DEFAULT
 SQL_EXEC_START                 DATE                    IN     DEFAULT
 SQL_EXEC_ID                    NUMBER                  IN     DEFAULT
 INST_ID                        NUMBER                  IN     DEFAULT
 START_TIME_FILTER              DATE                    IN     DEFAULT
 END_TIME_FILTER                DATE                    IN     DEFAULT
 INSTANCE_ID_FILTER             NUMBER                  IN     DEFAULT
 PARALLEL_FILTER                VARCHAR2                IN     DEFAULT
 PLAN_LINE_FILTER               NUMBER                  IN     DEFAULT
 EVENT_DETAIL                   VARCHAR2                IN     DEFAULT
 BUCKET_MAX_COUNT               NUMBER                  IN     DEFAULT
 BUCKET_INTERVAL                NUMBER                  IN     DEFAULT
 BASE_PATH                      VARCHAR2                IN     DEFAULT
 LAST_REFRESH_TIME              DATE                    IN     DEFAULT
 REPORT_LEVEL                   VARCHAR2                IN     DEFAULT
 TYPE                           VARCHAR2                IN     DEFAULT
 SQL_PLAN_HASH_VALUE            NUMBER                  IN     DEFAULT

　それぞれのPL/SQLパッケージ内に含まれるプロシージャ名やファンクション名、またはそれらの引数を忘れてしまったというときに便利なのが「desc[ribe]」コマンドです。このコマンドは表の列定義を確認するときに使用するのでご存じだと思いますが、このような使い方もできるので覚えておいてください。

　ここでは、次の演習で使用する「dbms_sqltune.report_sql_monitor」ファンクションの出力部分を抜粋してあります。引数名とそのデータ型はここで確認できますが、各引数が何を意味するのかについては、マニュアル「PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス」で確認しておくようにしてください。

演習3：「dbms_sqltune.report_sql_monitor」ファンクションでリアルタイムSQL監視レポートを生成する

　「SYS」ユーザーで「SELECT_CATALOG_ROLE」ロールを「TRY」ユーザーに付与したあと、「TRY」ユーザーにて「dbms_sqltune.report_sql_monitor」ファンクションを実行します。

$ sqlplus / as sysdba
SQL>  
/* SELECT_CATALOG_ROLEロールを付与 */
grant SELECT_CATALOG_ROLE to TRY ;
　
/* TRYユーザーで接続 */
connect TRY/TRY12345
　
/* 対象のSELECT文を実行 */
set trimspool on
set trim on
set pages 0
select /* practiceSQL1 */ COL1 || ' : ' || COL2 "Record" from TBL2 where ROWNUM <=3 ;
　
/* リアルタイムSQL監視のアクティブレポートを生成 */
set linesize 1000
set long 1000000
set longchunksize 1000000
spool sqlmon_active.html
select dbms_sqltune.report_sql_monitor(sql_id=>'2vrywhbx1jxtb', type=>'active') from dual;
spool off

　「dbms_sqltune.report_sql_monitor」ファンクションの引数「TYPE」パラメーターに「html」または「active」を設定するとHTML形式のレポートが出力されるので、SQL*Plusの「spool」コマンドを使用して「.html」拡張子のファイルを出力します。このHTMLファイルをWebブラウザで開くと、Oracle Enterprise Managerの管理画面のようなユーザーインタフェースでレポートの内容を確認できます。

　ところで、ここで皆さんは無事にアクティブレポートを取得し、正しく表示させることはできましたか？　上記と同じSELECT文を実行したのに、以下のような空っぽのレポートが表示されてしまったかと思います（図1）。

図1　

　ごめんなさいね。皆さんにリアルタイムSQL監視レポートを正しく理解するためにあえて失敗していただきました。

　空っぽのレポートが出力されてしまう原因は2つあります。1つは対象のSQL文を実行してから時間が経過してしまい、メモリ上から情報がパージされてしまったケース。もう1つは、対象のSQL文の実行時間が5秒未満で完了するために、監視の対象から外れているケースです。今回は、直前に対象のSELECT文を実行していることから後者の理由が該当します。

　マニュアル「SQLチューニング・ガイド」の「リアルタイムなSQL監視およびリアルタイムなデータベース操作」の項に記載がある通り、デフォルトでは「SQL文がパラレルで実行される場合」、または「1回の実行で5秒以上のCPU時間またはI/O時間を消費している場合」に自動的に監視対象になります。つまり、5秒未満で完了するSQL文は監視対象から外れます。

　「監視する」ということは、「監視するためのCPU処理時間が必要」になります。1回の実行が数ミリ秒程度であっても、何百ものユーザーから何度も繰り返し実行されるようなオンライントランザクション系のクエリで毎回監視してしまうと、監視するためのCPU処理時間が積もり積もって大きな負荷になってしまいます。これを避ける目的で、このようなデフォルトの動作になっていると推定されます。

　ということで、次の演習では「5秒未満で完了するSQL文」でも強制的に監視対象とする方法を実践しましょう。