AWRスナップショットからリアルタイムSQL監視レポートの取得とイベントを仕掛けて特定SQL IDを監視対象とする方法

実行計画のどの部分が遅いか分析するために役立つリアルタイムSQL監視ですが、11gR2まではメモリ上からなくなってしまうとリストから消えてしまい取得し直し。また、シリアル実行で実行時間が短いSQLの場合はmonitorヒント句が必要でした。
ここでは、12cより使用可能なAWRからのリアルタイムSQL監視レポートの取得方法と、ヒント句を使わず特定SQL IDのみmonitor指定する方法について記載します。

1. AWRからのリアルタイムSQL監視レポートの取得方法

前提として対象SQLのSQLIDはわかっているものとします

1.dba_hist_reportsから該当のSQLのレポートが取得されているか確認

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
SELECT snap_id,report_id,period_start_time
FROM 
    dba_hist_reports 
WHERE 
    component_name = 'sqlmonitor' 
    AND period_start_time BETWEEN To_date('2023/05/18 00:00:00','YYYY/MM/DD HH24:MI:SS') AND To_date('2023/05/20 05:00:00','YYYY/MM/DD HH24:MI:SS') 
    --key1にはSQLIDを指定します
    AND key1='bk95v91hn3dps'
ORDER BY 1
;


   SNAP_ID  REPORT_ID PERIOD_START_TIME
---------- ---------- -------------------
       954       8886 2023/05/18 01:21:29
       955       8894 2023/05/18 02:21:30
       956       8904 2023/05/18 03:21:31
       957       8913 2023/05/18 04:21:01

2.DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAILにreportidを指定してリアルタイムSQL監視レポートを出力する

set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
select dbms_auto_report.report_repository_detail(954, 'TEXT') report from dual;

2. イベントを仕掛けて特定のSQLIDを監視対象として指定する

リアルタイムSQL監視のモニタリングされる条件
・並列実行
・少なくとも5秒のI/OまたはCPU時間のリソース消費
・MONITORヒント句が付与されている

このため、実行時間が短いシリアル実行のSQLについては、MONITORヒント句を利用することが多いのですが、いつもヒント句を入れられるわけではありません。
その場合の方法としてイベントを仕掛けて特定のSQLIDを監視対象として指定する方法があります。

ALTER SYSTEM SET EVENTS 'sql_monitor [sql:my sql_id] force=true';

参考

How To Get Historical SQL Monitor Report For SQL Statements (Doc ID 2555350.1)

Real-Time SQL Monitoring: a MUST for SQL Tuning
https://blogs.oracle.com/coretec/post/oracle-database-real-time-sql-monitoring-one-of-the-most-important-tools#8