【SQL】歷史SQL監控(Historical SQL Monitoring ) 功能（12c）
- 2020 年 3 月 26 日
- 筆記
概述
Oracle 11g版本 推出了實時SQL監控功能(Real-Time SQL Monitoring)，用於實時地監視執行中SQL的性能；Oracle 12c 對其進一步進行了擴展，推出了歷史SQL監控功能(Historical SQL Monitoring)。
該功能類似於ASH資訊的履歷管理（DBAHISTACTIVESESSHISTORY），通過後台進程定期地把SQL的監視資訊保存到資料庫表中，以供事後分析SQL相關的問題。
歷史SQL監控使用的例子
首先通過以下歷史SQL監控使用的例子來初步了解一下該功能。
測試例：(12.1.0.2.0) 1.準備數據
SQL> conn /as sysdba Connected. SQL> create user teacherwhat identified by teacherwhat;User created.SQL> grant dba to teacherwhat;Grant succeeded.SQL> conn teacherwhat/teacherwhat Connected. SQL> create table SQLMON(a varchar2(3),b varchar2(10),c varchar2(5));Table created.SQL> begin for i in 1..1000 loop insert into SQLMON values('A', i, 'A'); insert into SQLMON values('B', i, 'B'); end loop; end; / 2 3 4 5 6 7 8 PL/SQL procedure successfully completed.SQL> create index SQLMON_I on SQLMON(a,b,c);Index created.SQL> exec dbms_stats.gather_table_stats(OWNNAME => NULL, TABNAME => 'SQLMON', CASCADE => TRUE);PL/SQL procedure successfully completed.
2.執行監視對象SQL文
--通過指定/*+ MONITOR */ Hint 使執行SQL成為SQL monitor功能監視對象 SQL> select/*+ MONITOR */ count(*) from SQLMON where a='A'; COUNT(*) ---------- 1000
3.查看實時SQL監控的結果 (同11g)
SQL> SELECT sql_id, hash_value, substr(sql_text,1,40) sql_text FROM v$sql WHERE sql_text like 'select/*+ MONITOR */ count(*)%'; 2 3 SQL_ID HASH_VALUE ------------- ---------- SQL_TEXT -------------------------------------------------------------------------------- bq20d7r8f6qa6 3504560454 select/*+ MONITOR */ count(*) from SQLMOSQL> SET LONG 1000000 SQL> SET LONGCHUNKSIZE 1000000 SQL> SET LINESIZE 1000 SQL> SET PAGESIZE 0 SQL> SET TRIM ON SQL> SET TRIMSPOOL ON SQL> SET ECHO OFF SQL> SET FEEDBACK OFF SQL> SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => 'bq20d7r8f6qa6', type => 'TEXT') AS report FROM dual; SQL Monitoring ReportSQL Text ------------------------------ select/*+ MONITOR */ count(*) from SQLMON where a='A'Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : TEACHERWHAT (237:24669) SQL ID : bq20d7r8f6qa6 SQL Execution ID : 16777216 Execution Started : 08/25/2016 21:07:26 First Refresh Time : 08/25/2016 21:07:26 Last Refresh Time : 08/25/2016 21:07:26 Duration : .000518s Module/Action : SQL*Plus/- Service : SYS$USERS Program : sqlplus@db12102 (TNS V1-V3) Fetch Calls : 1Global Stats ======================================= | Elapsed | Other | Fetch | Buffer | | Time(s) | Waits(s) | Calls | Gets | ======================================= | 0.00 | 0.00 | 1 | 12 | =======================================SQL Plan Monitoring Details (Plan Hash Value=1559208047) ================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ================================================================================================================================= | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | | | 2 | INDEX FAST FULL SCAN | SQLMON_I | 1000 | 3 | 1 | +0 | 1 | 1000 | | | =================================================================================================================================
4.查看歷史SQL監控報告的結果
可以通過視圖DBAHISTREPORTS和DBAHISTREPORTSDETAILS來查看歷史的SQL監控結果，也可以通過DBMSAUTOREPORT.REPORTREPOSITORY_DETAIL包查看結果。
DBA_HIST_REPORTS ：SQL Monitor、DBOP、Real-Time ADDM等報告的資訊 DBA_HIST_REPORTS_DETAILS：各個報告的纖細資訊
4.1 通過SQLID或者時間等其他條件，DBAHISTREPORTS中查找歷史SQL監控報告的REPORTID。
SQL> SET LINESIZE 1000 SQL> col sql_id format a20 SQL> col sql_exec_id format a20 SQL> col sql_exec_start format a20 SQL> SELECT report_id, key1 sql_id, key2 sql_exec_id, key3 sql_exec_start FROM dba_hist_reports where key1 ='bq20d7r8f6qa6'; 2 REPORT_ID SQL_ID SQL_EXEC_ID SQL_EXEC_START ---------- -------------------- -------------------- -------------------- 20 bq20d7r8f6qa6 16777216 08:25:2016 21:07:26
4.2.通過DBMSAUTOREPORT.REPORTREPOSITORYDETAIL包來顯示查看實時SQL監控結果。
---通過指定REPORT_ID來獲得歷史SQL監控報告。 SQL> set long 10000000 longchunksize 10000000 pages 0 LINESIZE 1000 SQL> SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => 20, TYPE => 'text') FROM dual; SQL Monitoring ReportSQL Text ------------------------------ select/*+ MONITOR */ count(*) from SQLMON where a='A'Global Information ------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : TEACHERWHAT (237:24669) SQL ID : bq20d7r8f6qa6 SQL Execution ID : 16777216 Execution Started : 08/25/2016 21:07:26 First Refresh Time : 08/25/2016 21:07:26 Last Refresh Time : 08/25/2016 21:07:26 Duration : .000518s Module/Action : SQL*Plus/- Service : SYS$USERS Program : sqlplus@db12102 (TNS V1-V3) Fetch Calls : 1Global Stats ======================================= | Elapsed | Other | Fetch | Buffer | | Time(s) | Waits(s) | Calls | Gets | ======================================= | 0.00 | 0.00 | 1 | 12 | =======================================SQL Plan Monitoring Details (Plan Hash Value=1559208047) ================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ================================================================================================================================= | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | | | 2 | INDEX FAST FULL SCAN | SQLMON_I | 1000 | 3 | 1 | +0 | 1 | 1000 | | | =================================================================================================================================
通過上面的方法，我們可以對某些過去的SQL進行查看，這對某些問題的事後調查非常有用。
下面我們進一步了解該功能
關於"Automatic Report Capturing"
歷史SQL監控(Historical SQL Monitoring ) 功能是12c新功能"Automatic Report Capturing"的一部分，通過MMON_SLAVE後台進程定期地把實時SQL監控的資訊保存到資料庫的相關表中。
下面我們通過以下測試，進一步了解一下這個動作。
測試：(12.1.0.2.0 Single Instance)
1.對MMON_SLAVE的'Automatic Report Flush'動作設置SQL Trace。
SQL> set line 180 SQL> col PRIMARY_ID format a20 SQL> col QUALIFIER_ID1 format a20 SQL> SQL> select trace_type, primary_id, qualifier_id1, waits, binds from DBA_ENABLED_TRACES;no rows selectedSQL> exec dbms_monitor.serv_mod_act_trace_enable - (service_name=>'SYS$BACKGROUND',- module_name=>'MMON_SLAVE',- action_name=>'Automatic Report Flush',- waits => true, - binds => true);> > > > >PL/SQL procedure successfully completed.SQL> select trace_type, primary_id, qualifier_id1, waits, binds from DBA_ENABLED_TRACES;TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 WAITS BINDS --------------------- -------------------- -------------------- ----- ----- SERVICE_MODULE_ACTION SYS$BACKGROUND MMON_SLAVE TRUE TRUE
2.查看trace路徑，可以看到每隔一分鐘系統生成一個Mxxx的跟蹤文件。
[oracle@db12102 trace]$ pwd /u01/app/oracle/diag/rdbms/ora12102/ora12102/trace [oracle@db12102 trace]$ ls -larth total 192K drwxr-x--- 16 oracle oinstall 4.0K Apr 10 20:18 .. -rw-r----- 1 oracle oinstall 137 Aug 26 12:15 ora12102_m001_7311.trm -rw-r----- 1 oracle oinstall 8.6K Aug 26 12:15 ora12102_m001_7311.trc -rw-r----- 1 oracle oinstall 56 Aug 26 12:16 ora12102_m001_7317.trm -rw-r----- 1 oracle oinstall 2.2K Aug 26 12:16 ora12102_m001_7317.trc -rw-r----- 1 oracle oinstall 83 Aug 26 12:17 ora12102_m001_7332.trm -rw-r----- 1 oracle oinstall 2.2K Aug 26 12:17 ora12102_m001_7332.trc -rw-r----- 1 oracle oinstall 83 Aug 26 12:18 ora12102_m001_7340.trm -rw-r----- 1 oracle oinstall 2.2K Aug 26 12:18 ora12102_m001_7340.trc -rw-r----- 1 oracle oinstall 73 Aug 26 12:19 ora12102_m001_7355.trm -rw-r----- 1 oracle oinstall 2.2K Aug 26 12:19 ora12102_m001_7355.trc ... -rw-r----- 1 oracle oinstall 83 Aug 26 12:36 ora12102_m001_7501.trm -rw-r----- 1 oracle oinstall 2.2K Aug 26 12:36 ora12102_m001_7501.trc -rw-r----- 1 oracle oinstall 73 Aug 26 12:37 ora12102_m001_7508.trm -rw-r----- 1 oracle oinstall 2.2K Aug 26 12:37 ora12102_m001_7508.trc drwxr-x--- 2 oracle oinstall 12K Aug 26 12:37 .[oracle@db12102 trace]$
3.查看Mxxx的跟蹤文件的內容，我們可以看到執行的操作內容。
[oracle@db12102 trace]$ tkprof ora12102_m001_7508.trc ora12102_m001_7508.out [oracle@db12102 trace]$ cat ora12102_m001_7508.out
Mxxx進程執行的SQL如下（整形後）：
SELECT sql_id, sql_exec_id, dbop_name, dbop_exec_id, To_char(sql_exec_start, 'mm:dd:yyyy hh24:mi:ss'), To_char(first_refresh_time, 'mm:dd:yyyy hh24:mi:ss'), To_char(last_refresh_time, 'mm:dd:yyyy hh24:mi:ss'), elapsed_time, px_servers_allocated, sid, session_serial#, KEY, con_id FROM v$sql_monitor WHERE report_id = 0 AND status != 'EXECUTING' AND status != 'QUEUED' AND px_qcsid IS NULL AND last_refresh_time > (SELECT Nvl(last_cycle_time, sysdate - ( 5 / 1440 )) FROM v$sys_report_stats);
根據上面的內容，我們可以發現，在默認的情況下，MMON_SLAVE後台進程會作以下的工作：
・每隔1分鐘檢查實時SQL監控資訊（v$sql_monitor視圖）。 ・保存對象為：不是正在執行也也不在排隊的 SQL。
4.接著測試，執行監視對象SQL文然後查看跟蹤文件中的內容。
SQL> conn teacherwhat/teacherwhat Connected. SQL> select/*+ MONITOR */ count(*) from SQLMON where a='A'; COUNT(*) ---------- 1000SQL> SET LINESIZE 1000 col sql_id format a20 col sql_exec_id format a20 col sql_exec_start format a20SQL> SQL> SQL> SQL> SELECT report_id, key1 sql_id, key2 sql_exec_id, key3 sql_exec_start FROM dba_hist_reports where key1 ='bq20d7r8f6qa6'; 2 REPORT_ID SQL_ID SQL_EXEC_ID SQL_EXEC_START ---------- -------------------- -------------------- -------------------- 20 bq20d7r8f6qa6 16777216 08:25:2016 21:07:26 21 bq20d7r8f6qa6 16777217 08:26:2016 13:10:41 ★
5.查看相關的跟蹤文件，我們可以看到，當發現相關的實時SQL監控的資訊需要保存到資料庫表中時，Oracle會調用sys.dbmsautoreportinternal.isavereport的程式包把相關資訊保存到相關的內部表（WRP$REPORTS、WRP$REPORTSDETAILS）中。
具體動作如下：
BEGIN sys.dbms_auto_report_internal.i_save_report (:rep_ref, :snap_id, :pr_class, :rep_id, :suc); END;INSERT INTO WRP$_REPORTS ...INSERT INTO WRP$_REPORTS_DETAILS ...INSERT INTO WRP$_REPORTS_TIME_BANDS ...
其中，wrp$表的更新即反映為如下的相關的視圖：
・DBA_HIST_REPORTS ← INT$DBA_HIST_REPORTS ← wrp$_reports ・DBA_HIST_REPORTS_DETAILS ← INT$DBA_HIST_REPORTS_DETAILS ← wrp$_reports_detail
相關視圖和程式包
歷史SQL監控的相關視圖主要包括以下
DBA_HIST_REPORTS_CONTROL：報告的相關控制資訊 DBA_HIST_REPORTS ：SQL Monitor、DBOP、Real-Time ADDM等報告的資訊 DBA_HIST_REPORTS_DETAILS：各個報告的詳細資訊
通過DBAHISTREPORTS_CONTROL視圖的內容我們可以知道，歷史SQL監控報告的模式可以分為2種：
REGULAR模式 ：根據DB時間預算，每隔一分鐘捕獲一次 FULL_CAPTURE模式：不考慮DB時間預算，每隔一分鐘捕獲一次，捕獲更多的資訊。 /開啟FULL_CAPTURE模式 SQL> exec DBMS_AUTO_REPORT.START_REPORT_CAPTURE; /關閉FULL_CAPTURE模式 SQL> exec DBMS_AUTO_REPORT.FINISH_REPORT_CAPTURE;
參考： Database Reference 12c >DBAHISTREPORTS_CONTROL
列EXECUTION_MODE：Mode of execution of automatic report capture. Possible values: REGULAR: Regular per-minute report capture subject to DBTIME budget FULL_CAPTURE: Report capture will be run per minute without the DBTIME budget constraints and is provided to capture a more comprehensive set of reportsNOTE: The FULL_CAPTURE mode can be started and ended respectively by executing the START_REPORT_CAPTURE and FINISH_REPORT_CAPTURE APIs in the DBMS_AUTO_REPORT package. At all other times, the execution mode should be REGULAR.
參考： Database Reference 12c
>DBAHISTREPORTS_CONTROL >DBAHISTREPORTS >DBAHISTREPORTS_DETAILS
另外，和這個功能密切相關的程式包DBMSAUTOREPORT，也可參考在線文檔。
Database PL/SQL Packages and Types Reference >31 DBMSAUTOREPORT
相關參數
我們可以通過下面的方法查看歷史SQL監控功能(Historical SQL Monitoring ) 的相關參數。
SQL> set pagesize 100 SQL> set linesize 200 SQL> col Parameter format a40 SQL> col Description format a35 SQL> col Value format a40 SQL> select a.ksppinm "Parameter",a.KSPPDESC "Description",b.ksppstvl "Value" from x$ksppi a, x$ksppcv b where a.indx = b.indx and a.ksppinm like '%_report_capture%'; 2 Parameter Description Value ------------------------------------- ----------------------------------- --------- _report_capture_cycle_time Time (in sec) between two cycles of 60 report capture daemon_report_capture_dbtime_percent_cutoff 100X Percent of system db time daem 50 on is allowed over 10 cycles_report_capture_timeband_length Length of time band (in hours) in t 1 he reports time bands table_report_capture_recharge_window No of report capture cycles after w 10 hich db time is recharged
另外，在很多案例中由於Bug的影響，MMON進程會因為這個功能而消耗大量的CPU以及記憶體，或者發生ORA-12850錯誤（RAC環境）等問題，所以當你遭遇到相關問題時，可以通過設定reportcapturecycletime=0來禁用Automatic Report Capturing功能，從而停止歷史SQL監控(Historical SQL Monitoring ) 功能來迴避相關的問題。
例： SQL> alter system set "_report_capture_cycle_time"=0;
參考
Database Reference 12c
>DBAHISTREPORTS_CONTROL
>DBAHISTREPORTS
>DBAHISTREPORTS_DETAILS
Database PL/SQL Packages and Types Reference
>31 DBMSAUTOREPORT
Historical SQL Monitor reports in 12c! https://mauro-pagano.com/2015/05/04/historical-sql-monitor-reports-in-12c/ Top
Executions SQL Monitoring style reports in SQLd360 https://mauro-pagano.com/2016/01/14/top-executions-sql-monitoring-styles-reports-in-sqld360/
