前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >【SQL】历史SQL监控(Historical SQL Monitoring ) 功能（12c）

【SQL】历史SQL监控(Historical SQL Monitoring ) 功能（12c）

作者头像
SQLplusDB
发布于 2020-03-26 10:25:53
1.6K0
发布于 2020-03-26 10:25:53
举报
文章被收录于专栏：Oracle数据库技术Oracle数据库技术Oracle数据库技术

概述

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/

本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2016-08-26，如有侵权请联系 cloudcommunity@tencent.com 删除
sql
数据库
oracle
腾讯云测试服务

本文分享自 Oracle数据库技术 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

sql
数据库
oracle
腾讯云测试服务
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • 概述
  • 历史SQL监控使用的例子
  • 关于"Automatic Report Capturing"
  • 相关视图和程序包
  • 相关参数
  • 参考
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
产品介绍
精选特惠 用云无忧
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论