#### 概述
> 在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
--檢視資料庫是否啟用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

#### 監控語句:語句級

``` sql
--啟用
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+)

``` sql
--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
```

#### 監控示例：生成報告

##### 開始監控
``` sql
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生成報告
``` sql
--生成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+生成報告
``` sql
--生成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

``` sql
-- 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;
```

#### 參考：
- https://docs.oracle.com/database/121/ARPLS/d_sql_monitor.htm#ARPLS74789
- https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sqltun.htm#ARPLS68444
- https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/monitoring-database-operations.html#GUID-11748F62-BE24-4511-B77B-F28D5E443A0A

【SQL】Oracle資料庫監控sql執行情況

相關文章