概述

在11G中，Real-Time SQL Monitoring可以让你监控单个SQL或PL-SQL的执行情况。从12c开始，提供了实时监控更为复杂的操作。12c中，Real-time database operations该特性允许你监控混合的操作包括一些SQL和PL/SQL。通过DBMS_SQL_MONITOR这个包去实现。

Oracle数据库默认会启动SQL监控，数据库会在满足以下任一条件时自动监视相关操作。

相关语句单次执行至少消耗了5秒的CPU或I/O时间
SQL语句并行执行
SQL语句指定了/ + MONITOR / 提示
指定了相关事件，sql_id。

--查看数据库是否启用sql monitor (默认启用：TYPICAL,或者ALL)
show parameter statistics_level
--为相关sql_id设置事件
ALTER SYSTEM SET EVENTS 'sql_monitor [sql: 5hc07qvt8v737|sql: 9ht3ba3arrzt3] force=true';

监控启动后，数据库将操作的元数据存储在AWR和ASH中。数据库实时刷新统计信息，每秒一次。

每个受监控的数据库操作在V\$SQL_MONITOR视图中都有一个条目。此条目跟踪为执行收集的关键性能指标，包括已用时间、CPU 时间、读取和写入次数、I/O 等待时间和各种其他等待时间。该V$SQL_PLAN_MONITOR视图包括被监控的 SQL 语句的执行计划中每个操作的监控统计信息。可以使用DBMS_SQL_MONITOR.REPORT_SQL_MONITOR或者OEM查看报告。

DBMS_SQL_MONITOR (Oracle12c+)

该包主要用于启动复杂数据库语句及打印相关监控报告。常用的子程序如下：
子程序|描述
—- |—-
BEGIN_OPERATION| 启动监控，12.2开始，可以指定session_id和session_num开始监控会话。
END_OPERATION | 结束监控
REPORT_SQL_MONITOR | 打印详细报告，相关语句的监控信息。此报告可以打印：
• 数据库监控的最后一次操作(默认)
• 指定会话执行。会话有气会话ID和可选的序列号(-1是当前会话)表示.
• 尤其最后只的sql_id
• 具体执行组合,sql_id,sql_exec_start和sql_exec_id
• 由标识的特点数据库操作的最后一次dbop_name。
• 由组合具体执行的dbop_name,dbop_exec_id.
使用type参数指定输出类型，默认text，html等。

监控视图

表名	描述
DBA_HIST_REPORTS	此视图显示有关在自动工作负载存储库 (AWR) 中捕获的 XML 报告的元数据。每个 XML 报告都包含有关组件某些活动的详细信息。例如，SQL Monitor 报告包含有关特定数据库操作的详细报告。
DBA_HIST_REPORTS_DETAILS	此视图显示有关在 AWR 中捕获的每个报告的详细信息。每个报告的元数据显示在DBA_HIST_REPORTS视图中，而实际报告在DBA_HIST_REPORTS_DETAILS视图中可用。
V$SQL_MONITOR	此视图包含有关简单和复合数据库操作的全局高级信息。
V$SQL_MONITOR_SESSTAT	此视图包含数据库操作中涉及的所有会话的统计信息。(累计)
V$SQL_PLAN_MONITOR	该视图包含监控 SQL 语句执行计划中每个步骤的监控统计信息。

以上视图可以结合下面视图一起使用

V$ACTIVE_SESSION_HISTORY
V$SESSION
V$SESSION_LONGOPS
V$SQL
V$SQL_PLAN

监控语句:语句级

--启用
SELECT /*+ MONITOR */  prod_id, AVG(amount_sold), AVG(quantity_sold) 
FROM   sales
GROUP BY prod_id
ORDER BY prod_id;
--关闭
SELECT /*+ NO_MONITOR */  prod_id, AVG(amount_sold), AVG(quantity_sold) 
FROM   sales
GROUP BY prod_id
ORDER BY prod_id;

监控语句：复合操作(Oracle12c+)

--1.开始监控,返回操作id，以便后续结束
VARIABLE exec_id NUMBER; 
BEGIN 
  :exec_id := DBMS_SQL_MONITOR.BEGIN_OPERATION ( dbop_name => 'SHOP' , forced_tracking => 'Y'); 
END; 
/
--2.执行相关操作
--3.结束
BEGIN
  DBMS_SQL_MONITOR.END_OPERATION ( dbop_name => 'SHOP', dbop_eid => :exec_id );
END;
/
--4.查看状态，后续可导出报告
COL STATUS FORMAT a10
COL DBOP_NAME FORMAT a10
COL CON_NAME FORMAT a5
SELECT STATUS, SQL_ID, DBOP_NAME, DBOP_EXEC_ID,
       TO_CHAR(ELAPSED_TIME/1000000,'000.00') AS ELA_SEC 
FROM   V$SQL_MONITOR
WHERE  DBOP_NAME = 'SHOP';
--5.生成报告
SET TRIMSPOOL ON
SET TRIM ON
SET PAGES 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SPOOL /tmp/shop.html
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(dbop_name=>'SHOP',report_level=>'ALL',TYPE=>'active') 
FROM   DUAL;
SPOOL OFF

监控示例：生成报告

开始监控

SELECT /*+ MONITOR */ s.prod_id, c.cust_last_name FROM sh.sales s, sh.customers c where s.CUST_ID=c.CUST_ID ORDER BY prod_id;

Oracle11.2生成报告

--生成text报告
SELECT DBMS_SQLTUNE.report_sql_monitor(
  type         => 'text',
  report_level => 'ALL') AS report
FROM dual;
--or
SET LONG 1000000
VARIABLE my_rept CLOB;
BEGIN
  :my_rept := DBMS_SQLTUNE.REPORT_SQL_MONITOR(
                report_level => 'ALL', 
                type         => 'text');
END;
/
PRINT :my_rept
--创建一个活动报告,html格式的图例
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET TRIM ON
SET PAGES 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SPOOL /tmp/long_sql.html
SELECT DBMS_SQLTUNE.report_sql_monitor(
  type         => 'ACTIVE',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF

Oracle12+生成报告

--生成text报告
SELECT DBMS_SQL_MONITOR.report_sql_monitor(
  type         => 'text',
  report_level => 'ALL') AS report
FROM dual;
--or
SET LONG 1000000
VARIABLE my_rept CLOB;
BEGIN
  :my_rept := DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
                report_level => 'ALL', 
                type         => 'text');
END;
/
PRINT :my_rept
--创建一个活动报告,html格式的图例
SET FEEDBACK OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
SET TRIM ON
SET PAGES 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SPOOL /tmp/long_sql.html
SELECT DBMS_SQL_MONITOR.report_sql_monitor(
  type         => 'ACTIVE',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF

报告参考(两版本区别)：

http://blog.itpub.net/29487349/viewspace-2795727/

程序详情

DBMS_SQL_MONITOR(DBMS_SQLTUNE).REPORT_SQL_MONITOR

-- 12.1+
DBMS_SQL_MONITOR.REPORT_SQL_MONITOR (
   sql_id                    IN VARCHAR2 DEFAULT  NULL,
   dbop_name                 IN VARCHAR2 DEFAULT  NULL,
   dbop_exec_id              IN NUMBER   DEFAULT  NULL,
   session_id                IN NUMBER   DEFAULT  NULL,
   session_serial            IN NUMBER   DEFAULT  NULL,
   sql_exec_start            IN DATE     DEFAULT  NULL,
   sql_exec_id               IN NUMBER   DEFAULT  NULL,
   inst_id                   IN NUMBER   DEFAULT  NULL,
   start_time_filter         IN DATE     DEFAULT  NULL,
   end_time_filter           IN DATE     DEFAULT  NULL,
   instance_id_filter        IN NUMBER   DEFAULT  NULL,
   parallel_filter           IN VARCHAR2 DEFAULT  NULL,
   plan_line_filter          IN NUMBER   DEFAULT  NULL,
   event_detail              IN VARCHAR2 DEFAULT  'YES',
   bucket_max_count          IN NUMBER   DEFAULT  128,
   bucket_interval           IN NUMBER   DEFAULT  NULL,
   base_path                 IN VARCHAR2 DEFAULT  NULL,
   last_refresh_time         IN DATE     DEFAULT  NULL,
   report_level              IN VARCHAR2 DEFAULT 'TYPICAL',
   type                      IN VARCHAR2 DEFAULT 'TEXT',
   sql_plan_hash_value       IN NUMBER   DEFAULT  NULL,
   con_name                  IN VARCHAR2 DEFAULT  NULL)
  RETURN CLOB; 
--11.2
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
   sql_id                    IN VARCHAR2  DEFAULT  NULL,
   session_id                IN NUMBER    DEFAULT  NULL,
   session_serial            IN NUMBER    DEFAULT  NULL,
   sql_exec_start            IN DATE      DEFAULT  NULL,
   sql_exec_id               IN NUMBER    DEFAULT  NULL,
   inst_id                   IN NUMBER    DEFAULT  NULL,
   start_time_filter         IN DATE      DEFAULT  NULL,
   end_time_filter           IN DATE      DEFAULT  NULL,
   instance_id_filter        IN NUMBER    DEFAULT  NULL,
   parallel_filter           IN VARCHAR2  DEFAULT  NULL,
   plan_line_filter          IN NUMBER    DEFAULT  NULL,
   event_detail              IN VARCHAR2  DEFAULT  'YES',
   bucket_max_count          IN NUMBER    DEFAULT  128,
   bucket_interval           IN NUMBER    DEFAULT  NULL,
   base_path                 IN VARCHAR2  DEFAULT  NULL,
   last_refresh_time         IN DATE      DEFAULT  NULL,
   report_level              IN VARCHAR2  DEFAULT 'TYPICAL',
   type                      IN VARCHAR2  DEFAULT 'TEXT',
   sql_plan_hash_value       IN NUMBER    DEFAULT  NULL)
RETURN CLOB;

【SQL】Oracle数据库监控sql执行情况

概述