专栏首页Oracle数据库技术【SQL】历史SQL监控(Historical SQL Monitoring ) 功能(12c)

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

概述

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/

本文分享自微信公众号 - Oracle数据库技术(TeacherWhat)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2016-08-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【SQL Performance】实时SQL监控功能(Real-Time SQL Monitoring)

    实时SQL监控功能(Real-Time SQL Monitoring)是Oracle11g推出的功能,通过这个功能可以实时地监视执行中的SQL性能。

    TeacherWhat
  • 自适应游标共享(Adaptive Cursor Sharing)(二)

    本文为自适应游标共享(Adaptive Cursor Sharing)功能的第二部分,主要介绍ACS有效时的状况例子,以及ACS处理流程。

    TeacherWhat
  • 【SQL 基础】游标(curosr)(二)如何调查子游标的增加

    通过v$sqlsharedcursor视图我们看到,子游标(CHILD_NUMBER:1)产生的原因是BINDLENGTHUPGRADEABLE,即: 新执行S...

    TeacherWhat
  • SQL 审核 - z3 产品理念与功能介绍

    我们都知道,在 DBA 所优化的数据库环境中,绝大多数性能问题其实是由于 SQL 编写不当导致的,一个开发环境中,众多的程序员难免引入一个又一个的或初级或高端的...

    数据和云
  • 【DB笔试面试603】在Oracle中,固定SQL执行计划的方法有哪些?

    在实际项目中,通常在开发环境下,一些SQL执行没有任何功能问题,而当到了生产环境或生产环境的数据量发生较大的变量时,其SQL的执行效率非常低。此时如果更改SQL...

    小麦苗DBA宝典
  • 执行计划:如何手工创建Profile维持SQL计划的稳定性

    在上一篇:《执行计划:Oracle的Profile特性与SQL执行计划的稳定性》,向大家介绍了什么是 SQL Profiles 及其作用,如何使用 SQL Tu...

    数据和云
  • 为什么用尽了办法你的系统性能还是不见改善

    随着业务数据的增长,以及新业务的推出,很多企业都面临着系统性能的问题,并且日益凸显。我们曾遇到很多这样的用户,似乎用尽了所有招数,但性能就是不见改善,问题到底出...

    数据和云
  • 通过闪回事务查看数据dml的情况 (r2笔记69天)

    昨天有一个网友问我,怎么能够查询一个表中最后一条插入的记录,我大概回复了,可以通过闪回事务来实现,但是得看什么时候插入的数据,也需要一定的运气。 如果通过闪回事...

    jeanron100
  • Oracle Real Time SQL Monitoring

    术语说明 TableQueue,消息缓冲区,在并行操作中使用,用于PX进程之间的通信,或者PX进程与QC进程之间的通信,是内存中的一些page,每个消息缓冲区的...

    沃趣科技
  • 【SQL Performance】实时SQL监控功能(Real-Time SQL Monitoring)

    实时SQL监控功能(Real-Time SQL Monitoring)是Oracle11g推出的功能,通过这个功能可以实时地监视执行中的SQL性能。

    TeacherWhat

扫码关注云+社区

领取腾讯云代金券