AWRに格納された過去のSQL監視結果をレポートする

SQL MonitorはSQL性能を分析するのに欠かせない機能で、SQLが流れたそばから、なんなら流れている最中からでも待機イベントやカーディナリティ、パラレルクエリの処理の偏り具合などがわかる超便利機能ですが、監視データがメモリ上の循環バッファにあるため一定数以上たまると古いものから消えてしまい、DB再起動ですべて消えてしまうのがOracle Database 11gの頃の難点でした。

Oracle Database 12cでは、意外と知られていないようなのですが、SQL Monitorの監視データもAWRに格納されるようになっています。つまり履歴がディスクに残ります。すべての結果が必ずしも残るわけではありませんが、過去のSQL性能トラブルを分析する際にとても便利で、筆者自身もある障害対応で、「とあるSQL文が過去数日間のあいだ、どれくらいの頻度で実行されていたか」を特定するのに成功しました。

以下は、調べたいSQLのSQL_IDがわかってる場合の例

SQL> select report_id, GENERATION_TIME, key1 from dba_hist_reports where component_name='sqlmonitor' and key1='5j5wp4d5s9pqz';


 REPORT_ID GENERATION_TIME     KEY1
---------- ------------------- --------------------
       382 2015-07-01 02:51:59 5j5wp4d5s9pqz
       531 2015-07-01 10:51:33 5j5wp4d5s9pqz
       
       
SQL> select dbms_auto_report.report_repository_detail(382, 'TEXT') report from dual;

REPORT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
：
：
：

なお、SQL MonitorはTuning Packの機能です。