17.Sql Monitor RePort

1.Sql Monitor's 监控的前提条件

Sql Monitor 是监控一条Sql是怎样执行的，当启用SQL Monitor功能时，当满足以下任何条件时，数据库会自动监控简单的数据库操作：

• A SQL statement or PL/SQL subprogram has consumed at least 5 seconds of CPU or I/O time in a single execution. (cpu或者I/O执行时间超过5秒)
• A SQL statement executes in parallel.   -- 并行执行时
• A SQL statement specifies the /*+ MONITOR */ hint.    -- 使用了/*+ monitor */ 语法
• The event sql_monitor specifies a list of SQL IDs for the statements to be monitored.  -- 使用sql_monitor事件指定要监控的的sql id

    ALTER SYSTEM SET EVENTS 'sql_monitor [sql: 5hc07qvt8v737|sql: 9ht3ba3arrzt3] force=true'

2. Sql Monitor's 监控的目的

　　对于简单的操作，实时SQL监控有助于确定语句将时间花在哪里

3.Sql Monitor's 工作原理

　　如上图所示，DBMS_SQL_MONITOR包定义了数据库操作。启动监控后，数据库在AWR中存储有关数据库操作的元数据，并在AWR和ASH中存储数据。当每个受监控语句执行时，数据库几乎实时刷新监控统计数据，通常每秒一次。数据库将操作数据（语句和元数据）存储在SGA中。操作完成后，数据库将SQL Monitor报告写入磁盘，可以使用DBA_HIST_REPORTS视图进行查询。

　　每个受监控的数据库操作在V$SQL_MONITOR视图中都有一个条目。此条目跟踪为执行收集的关键性能指标，包括耗时、CPU时间、读数和写入次数、I/O等待时间以及各种其他等待时间。V$SQL_PLAN_MONITOR视图包括被监控的SQL语句执行计划中每个操作的监控统计信息。您可以使用DBMS_SQL_MONITOR.REPORT_SQL_MONITOR、Oracle Enterprise Manager Cloud Control（Cloud Control）或EM Express访问报告。

4.启用Sql Monitor需要开启如下数据库参数

　　当STATISTICS_LEVEL初始化参数设置为toTYPICAL（默认值）或ALL，默认启用SQL监控功能。对于所有长期运行的查询，SQL监控会自动启动。

　　由于SQL监控是Oracle数据库调整包的一项功能，因此必须将CONTROL_MANAGEMENT_PACK_ACCESS初始化参数设置为DIAGNOSTIC+TUNING（默认值），该默认一般不用改

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS;  

SQL> ALTER SYSTEM SET STATISTICS_LEVEL='TYPICAL';    # 这里如果统计信息的level是basic级别要给它改成typical

5.实例

SELECT /*+ MONITOR */ s.prod_id, c.cust_last_name FROM sales s, customers c ORDER BY prod_id;   #这里在一个会话执行一条语句，然后再用dba_sql_monitor包进行监控生产一个active 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(
         report_level => 'ALL', 
         TYPE         => 'active') 
FROM   DUAL;
SPOOL OFF

如果要根据sql_id生成Sql monitor Report，可以这样写：

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(
         sql_id => '74qqqwntwzxb1',     #这里可以加上某一条的sql id值。
         report_level => 'ALL', 
         TYPE         => 'active') 
FROM   DUAL;
SPOOL OFF

这里的sql_id可以通过视图v$sql_monitor视图查看,查到sql_id后，把sql_id带到上面的包函数中，生茶Sql monitor Report.

select sql_id,sql_text from v$sql_monitor where sql_text='语句';

这里可以看看生成的Sql monitor Report报告(long_sql.html)

参考：https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/monitoring-database-operations.html#GUID-4048D00E-2635-42C8-A37D-71EFAC619062