1 说明

Real-time database operations monitoring allows database administrators to easily monitor and troubleshoot performance problems in long running jobs. This feature helps make long running database operations like a batch job, an ETL (extraction, loading, and transformation) operation, or a scheduler job, transparent so that administrators can see exactly what the operation is doing and at what time. It does this by tracking the SQL and PL/SQL commands that make up a database operation along with their time lines.

用于监控长时间运行的事务，包括job，ETL相关操作，SQL查询等。可以很方便DBA监控和解决相关性能问题。

在11G中，Real-Time SQL Monitoring可以让你监控单个SQL或PL-SQL的执行情况。从12c开始，提供了实时监控更为复杂的操作。例如，并行查询，DML,DDL等语句。默认情况下，当SQL语句并行运行时，或在单个执行中消耗至少5秒的CPU或I / O时间时，Real-Time SQL Monitoring就会自动启动。

下图是数据库操作的架构图

1.1 监控数据库操作

使用DBMS_SQL_MONITOR包来开始，结束监控数据的操作，然后会生成一份报告用于查询操作的详细信息。

Program Unit	Description
REPORT_SQL_MONITOR	This function accepts several input parameters to specify the execution, the level of detail in the report, and the report type. If no parameters are specified, then the function generates a text report for the last execution that was monitored. http://www.cndba.cn/Expect-le/article/2041
BEGIN_OPERATION	This function associates a session with a database operation.
END_OPERATION	This function disassociates a session from the specified database operation execution.

查看监控数据库操作的相关视图

V$SQL_MONITOR, V$SQL_PLAN_MONITOR和V$SQL_MONITOR_SESSTAT

更详细的信息请查看官方文档说明

http://docs.oracle.com/database/121/TGSQL/tgsql_monit.htm#TGSQL94599

1.2 启用和禁用监控数据库操作

默认上开启的，会自动监控长时间运行的查询。statistics_level需要设置为TYPICAL（默认值）或ALL。但是前提条件是CONTROL_MANAGEMENT_PACK_ACCESS必须是DIAGNOSTIC+TUNING（默认值）。

设置方式如下：

SQL> SHOW PARAMETER statistics_level


NAME             TYPE        VALUE
----------------------------------- ----------- -----
statistics_level       string      BASIC

 

SQL> ALTER SYSTEM SET STATISTICS_LEVEL='TYPICAL';

1.2.1 启用和禁用语句级别的监控

虽然系统级别启用了监控，但是可以通过HINT来禁用，启用语句的监控。

如：

--启用

SELECT /*+ MONITOR */ SYSDATE FROM DUAL;

--禁用

SQL> SELECT * /*+NO_MONITOR*/ FROM sales ORDER BY time_id;

2 实验

2.1 创建表并插入数据

drop table cndba1;
drop table cndba2;


CREATE TABLE cndba1 AS
SELECT level AS id,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 1000000;
 

CREATE TABLE cndba2 AS
SELECT level AS id,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 1000000;

2.2 开始监控

VARIABLE l_dbop_eid NUMBER;
BEGIN
  :l_dbop_eid := DBMS_SQL_MONITOR.begin_operation (
                   dbop_name       => 'db_op_1',
                   dbop_eid        => :l_dbop_eid,
                   forced_tracking => DBMS_SQL_MONITOR.force_tracking
                 );
END;
/

2.3 对表进行操作

对cndba1进行查询，对cndba2删除1000行数据

BEGIN
  FOR cur_rec IN (SELECT * FROM cndba1) LOOP
    NULL;
  END LOOP;


  delete from cndba2 where rownum<=1000;
  commit;
END;
/

2.4 结束监控操作

BEGIN
  DBMS_SQL_MONITOR.end_operation (
    dbop_name       => 'db_op_1',
    dbop_eid        => :l_dbop_eid
  );
END;
/

2.5 查看监控的状态

SET LINESIZE 200
SELECT dbop_name, dbop_exec_id, status
FROM   v$sql_monitor
WHERE  username = 'LEI';
DBOP_NAME       DBOP_EXEC_ID STATUS
-------------   ----------------- ------------ 
db_op_1        1    DONE
 	       0    DONE (ALL ROWS)

2.6 查看监控报告

--HTML格式的

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL ./report_sql_monitor.htm
SELECT DBMS_SQL_MONITOR.report_sql_monitor(
  dbop_name    => 'db_op_1',
  type         => 'HTML',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF

效果图：

如果是ACTIVE格式，需要联网才能查看，因为需要下载一些公用代码。

ACTIVE效果图：非常好看，统计的信息也非常的全

3 总结

是对11g的Real-Time SQL Monitoring特性一个提升，提供更为详细的信息。对于性能的开销和瓶颈非常容易查看。

Real-Time Database Operations Monitoring

签到成功

CNDBA社区

Orcle 12c 新特性---Real-Time Database Operations Monitoring