Oracle Real Time SQL Monitor
Oracle Real Time SQL Monitor
Oracle資料庫的實時SQL監控能讓你用來監控正在執行SQL語句的效能。預設情況下，當SQL語句使用 並行執行或當SQL語句的單個操作消耗了5秒的CPU或I/O時間就會自動啟動SQL監控。可以使用v $sql_monitor與v$sql_plan_monitor檢視來監控SQL語句執行的統計資訊。可以使用這些檢視時行關 聯來獲得被監控到的關於執行的其它資訊:
.v$active_session_history
.v$session
.v$session_longops
.v$sql
.v$sql_plan
在監控初始化之後，資料庫將會向動態效能檢視v$sql_monitor中增加條目。這個條目跟蹤SQL執行的 關鍵效能，包括：執行時間，CPU時間，讀取與寫入的次數，I/O等待時間和各種其它等待時間。這些 統計資訊當SQL語句執行時間會被實時重新整理，每秒生成一次。在執行結束後，監控資訊不會立即被刪 除，但在v$sql_monitor中至少保留一分鐘。當需要為新的SQL語句騰出空間時，會評估條目是否需要 被刪除來回收空間。
v$sql_monitor檢視包含v$sql檢視中統計資訊的一組子集。然而，不像v$sql，監控統計資訊不會對 多次執行進行累加。相反，v$sql_monitor中的一個條目被關聯到SQL語句的一個單獨操作。如果資料 庫監控相同SQL語句執行兩次，那麼在v$sql_monitor中會有兩次執行的統計資訊。
為了唯一標識相同SQL語句的兩次執行，會生成一個叫執行鍵的複合鍵。執行鍵是由三個屬性組成， 它們分別為v$sql_monitor中的:
.SQL識別符號用來標識SQL語句(SQL_ID)
.開始執行時間(SQL_EXEC_START)
.一個內部生成的識別符號用來確保主鍵是唯一的(SQL_EXEC_ID)
SQL執行計劃監控
真時SQL監控也包括監控SQL語句的執行計劃中的每一個操作的統計資訊。這些資料在v $sql_plan_monitor檢視中可以看到。類似於v$sql_monitor檢視，v$sql_plan_monitor檢視中的統計 資訊當SQL語句被執行時每秒會更新一次。這些統計資訊在SQL執行結束後是存在的，它的生命週期與 v$sql_monitor是一樣的。對於每個被監控的SQL語句，在v$sql_plan_monitor檢視中將會有多個條目 ，每個條目關聯執行計劃中的一個操作。
並行執行監控
Oracle資料庫會當SQL語句開始執行時會自動監控並行查詢，DML與DDL語句。v$sql_monitor與v $sql_plan_monitor檢視將會以單獨的條目來記錄並行執行中每個操作的監控資訊。
v$sql_monitor對於並行執行協調程式和每個並行執行伺服器程式有一個條目。對於每一個條目在v $sql_plan_monitor檢視中也有相關的條目。因為對於SQL語句並行執行的所分配的程式是相互協作的 ，這些條目共享相同的執行鍵（由sql_id,sql_exec_start與sql_exec_id組成）。因此可以聚合執行 鍵來判斷並行執行的整個統計資訊。
生成SQL監控報告
可以使用SQL監控報告來檢視SQL監控資料。SQL監控報告使用以下檢視中的資料:
.gv$sql_monitor
.gv$sql_plan_monitor
.gv$sql
.gv$sql_plan
.gv$active_session_history
.gv$session_longops
為了生成SQL監控報告，執行dbms_sqltune.report_sql_monitor過程:
SQL> set long 10000000
SQL> set longchunksize 10000000
SQL> set linesize 200
SQL> variable my_rept clob;
SQL> begin
2 :my_rept:=dbms_sqltune.report_sql_monitor();
3 end;
4 /
print :my_rept
PL/SQL procedure successfully completed.
SQL>
MY_REPT
---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ------------------------
SQL Monitoring Report
SQL Text
------------------------------
/* SQL Analyze(1) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */to_char(count("AAE064"
)),to_char(substrb(dump(min("AAE064"),16,0,32),1,120)),to_char(substrb(dump(max ("AAE064"),16,0,32),1,120)),to_char(count("AAB001")),to_char(substrb(dump(min ("AAB001"),16,0,32),1,120)),to_char(substrb(
dump(max("AAB001"),16,0,32),1,120)),to_char(count("AAB191")),to_char(substrb(dump(min ("AAB191"),16,
0,32),1,120)),to_char(substrb(dump(max("AAB191"),16,0,32),1,120)),to_char(count ("AAB190")),to_char(substrb(dump(min("AAB190"),16,0,32),1,120)),to_char(substrb(dump (max("AAB190"),16,0,32),1,120)),to_ch
ar(count("AAB211")),to_char(substrb(dump(min("AAB211"),16,0,32),1,120)),to_char(substrb (dump(max("AAB211"),16,0,32),1,120)),to_char(count("AAB212")),to_char(substrb(dump(min ("AAB212"),16,0,32),1,120))
,to_char(substrb(dump(max("AAB212"),16,0,32),1,120)),to_char(count("AAB213")),to_char (substrb(dump(
min("AAB213"),16,0,32),1,120)),to_char(substrb(dump(max ("AAB213"),16,0,32),1,120)),to_char(count("AAB214")),to_char(substrb(dump(min ("AAB214"),16,0,32),1,120)),to_char(substrb(dump(max("AAB214"),16,0,
32),1,120)),to_char(count("AAB215")),to_char(substrb(dump(min ("AAB215"),16,0,32),1,120)),to_char(substrb(dump(max("AAB215"),16,0,32),1,120)),to_char (count("AAE011")),to_char(substrb(dump(min("AAE011")
,16,0,32),1,120)),to_char(substrb(dump(max("AAE011"),16,0,32),1,120)),to_char(count ("AAE036")),to_c
har(substrb(dump(min("AAE036"),16,0,32),1,120)),to_char(substrb(dump(max ("AAE036"),16,0,32),1,120)),to_char(count("AAE017")),to_char(substrb(dump(min ("AAE017"),16,0,32),1,120)),to_char(substrb(dump(ma
x("AAE017"),16,0,32),1,120)),to_char(count("SJCQNY")),to_char(substrb(dump(min ("SJCQNY"),16,0,32),1,120)),to_char(substrb(dump(max("SJCQNY"),16,0,32),1,120)) from "LEMIS_4307"."AB13" t /*
NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV, NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (1837:1553)
SQL ID : 5k014mmtyr7qw
SQL Execution ID : 16777216
Execution Started : 10/17/2016 22:02:26
First Refresh Time : 10/17/2016 22:02:32
Last Refresh Time : 10/17/2016 22:02:55
Duration : 29s
Module/Action : DBMS_SCHEDULER/ORA$AT_OS_OPT_SY_1
Service : SYS$USERS
Program : oracle@sjjh (J001)
Fetch Calls : 1
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 28 | 28 | 0.09 | 1 | 307K |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=3367417341)
======================================================================================== ===========================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
======================================================================================== ===========================================
| 0 | SELECT STATEMENT | | | | 24 | +6 | 1 | 1 | | |
| 1 | SORT AGGREGATE | | 1 | | 28 | +2 | 1 | 1 | 34.48 | Cpu (10) |
| 2 | APPROXIMATE NDV AGGREGATE | | 29M | 89482 | 29 | +1 | 1 | 27M | 55.17 | Cpu (16) |
| 3 | TABLE ACCESS FULL | AB13 | 29M | 89482 | 25 | +5 | 1 | 29M | 10.34 | Cpu (3) |
======================================================================================== ===========================================
dbms_sqltune.report_sql_monitor過程接受幾種輸入引數來指定執行，報告的詳細級別與報告型別 ('TEXT','HTML'或'XML')。預設情況下，如果沒有指定引數將會對最後所監控的到SQL生成文字型別 的SQL監控報告。
SQL> desc dbms_sqltune.report_sql_monitor Parameter Type Mode Default? ------------------- -------- ---- -------- (RESULT) CLOB SQL_ID VARCHAR2 IN Y SESSION_ID NUMBER IN Y SESSION_SERIAL NUMBER IN Y SQL_EXEC_START DATE IN Y SQL_EXEC_ID NUMBER IN Y INST_ID NUMBER IN Y START_TIME_FILTER DATE IN Y END_TIME_FILTER DATE IN Y INSTANCE_ID_FILTER NUMBER IN Y PARALLEL_FILTER VARCHAR2 IN Y PLAN_LINE_FILTER NUMBER IN Y EVENT_DETAIL VARCHAR2 IN Y BUCKET_MAX_COUNT NUMBER IN Y BUCKET_INTERVAL NUMBER IN Y BASE_PATH VARCHAR2 IN Y LAST_REFRESH_TIME DATE IN Y REPORT_LEVEL VARCHAR2 IN Y TYPE VARCHAR2 IN Y SQL_PLAN_HASH_VALUE NUMBER IN Y
也可以使用以下方式來生成SQL監控報告:
SQL> set long 10000000
SQL> set longchunksize 10000000
SQL> set linesize 200
SQL> select dbms_sqltune.report_sql_monitor from dual;
REPORT_SQL_MONITOR
---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ------------------------
SQL Monitoring Report
SQL Text
------------------------------
/* SQL Analyze(1) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */to_char(count("MONEY_N
O")),to_char(substrb(dump(min("MONEY_NO"),16,0,32),1,120)),to_char(substrb(dump(max ("MONEY_NO"),16,0,32),1,120)),to_char(count("PAY_INFO_NO")),to_char(substrb(dump(min ("PAY_INFO_NO"),16,0,32),1,120)),
to_char(substrb(dump(max("PAY_INFO_NO"),16,0,32),1,120)),to_char(count ("PAY_MONEY")),to_char(substr
b(dump(min("PAY_MONEY"),16,0,32),1,120)),to_char(substrb(dump(max ("PAY_MONEY"),16,0,32),1,120)),to_char(count("MONEY_TYPE")),to_char(substrb(dump(min ("MONEY_TYPE"),16,0,32),1,120)),to_char(substrb(dum
p(max("MONEY_TYPE"),16,0,32),1,120)),to_char(count("CALC_MAN_SUM")),to_char(substrb (dump(min("CALC_MAN_SUM"),16,0,32),1,120)),to_char(substrb(dump(max ("CALC_MAN_SUM"),16,0,32),1,120)),to_char(count("C
ALC_BASE")),to_char(substrb(dump(min("CALC_BASE"),16,0,32),1,120)),to_char(substrb(dump (max("CALC_B
ASE"),16,0,32),1,120)),to_char(count("MONEY_ID")),to_char(substrb(dump(min ("MONEY_ID"),16,0,32),1,120)),to_char(substrb(dump(max ("MONEY_ID"),16,0,32),1,120)),to_char(count("CORP_ID")),to_char(substrb(
dump(min("CORP_ID"),16,0,32),1,120)),to_char(substrb(dump(max ("CORP_ID"),16,0,32),1,120)),to_char(count("PAYED_MONEY")),to_char(substrb(dump(min ("PAYED_MONEY"),16,0,32),1,120)),to_char(substrb(dump(ma
x("PAYED_MONEY"),16,0,32),1,120)),to_char(count("CALC_PRD")),to_char(substrb(dump(min ("CALC_PRD"),1
6,0,32),1,120)),to_char(substrb(dump(max("CALC_PRD"),16,0,32),1,120)),to_char(count ("SRC_TYPE")),to_char(substrb(dump(min("SRC_TYPE"),16,0,32),1,120)),to_char(substrb (dump(max("SRC_TYPE"),16,0,32),1,1
20)),to_char(count("PAYED_FLAG")),to_char(substrb(dump(min ("PAYED_FLAG"),16,0,32),1,120)),to_char(substrb(dump(max("PAYED_FLAG"),16,0,32),1,120)) from "SJGX_YB"."LV_CROPFUNDPAR" t /*
ACL,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV, NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (146:19)
SQL ID : 6sbw88979vmcv
SQL Execution ID : 16777216
Execution Started : 10/18/2016 22:01:04
First Refresh Time : 10/18/2016 22:01:11
Last Refresh Time : 10/18/2016 22:01:38
Duration : 34s
Module/Action : DBMS_SCHEDULER/ORA$AT_OS_OPT_SY_4
Service : SYS$USERS
Program : oracle@sjjh (J001)
Fetch Calls : 1
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 34 | 34 | 0.11 | 1 | 294K |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=3534073399)
======================================================================================== =====================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
======================================================================================== =====================================================
| 0 | SELECT STATEMENT | | | | 28 | +7 | 1 | 1 | | |
| 1 | SORT AGGREGATE | | 1 | | 33 | +2 | 1 | 1 | 40.00 | Cpu (14) |
| 2 | APPROXIMATE NDV AGGREGATE | | 24M | 80111 | 35 | +0 | 1 | 39M | 54.29 | Cpu (19) |
| 3 | MAT_VIEW ACCESS FULL | LV_CROPFUNDPAR | 24M | 80111 | 28 | +7 | 1 | 39M | 2.86 | Cpu (1) |
======================================================================================== =====================================================
在Global Information部分，Status顯示為DONE，說明SQL語句已經執行完成了。Time Active(s)列 顯示操作已經執行了多長時間。Start Active列，以秒為單位，代表SQL語句執行的開始。在這個報 告中，MAT_VIEW ACCESS FULL是ID 3是第一個被執行的(+7s Start Active)並且執行了28秒。
Start列顯示該操作被執行的次數。Rows(Actual)列顯示被處理的行數，Rows(Estim)列顯示最佳化器所 評估的行數。Memory與Temp列指示每個操作所消耗的記憶體與臨時空間的大小。
Activity(percent)與Activity Detail(sample #)是透過連線v$sql_plan_monitor與v $active_session_history檢視而得到。Activity(percent)顯示執行計劃中每個操作所佔資料庫時間 的百分比。Activity Detail(sample#)顯示了活動的屬性(比如CPU或等待事件)。在這個報告中，ID 2消耗了的資料庫時間百分比為54.29%（APPROXIMATE NDV AGGREGATE）。活動由19samples組成。最 後一列,Progress,顯示了v$session_longops檢視所監控到的操作資訊。
啟用與禁用SQL監控
SQL監控功能當statistics _level引數被設定為all或typical(預設值)時預設是開啟的。額外， control_management_pack_access引數必須設定為diagnostic+tuning(預設值),因為SQL監控是 Oracle資料庫最佳化包的一個功能。SQL監控對於所有執行時間長的查詢會自動啟動。
有兩種語句級別的hint可以用來強制或者阻止SQL語句被監控。為了強制SQL監控，使用monitor hint:
select /*+ MONITOR */ from dual;
這種hint只有當control_management_pack_access引數被設定為diagnostic+tuning時才生效。為了 阻止SQL語句被監控可以使用no_monitor hint。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2126721/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle Real Time SQL MonitoringOracleSQL
- 【SQL】Oracle SQL monitorSQLOracle
- 11g 新特性 real time apply ＋real time queryAPP
- oracle active data guard real-time apply特性OracleAPP
- Oracle 11g dataguard check real time applyOracleAPP
- ????Oracle dataguard enabling real time applyOracleAPP
- 查詢real-time apply、real-time query的檢視APP
- ORACLE裡的慢查跟蹤 Real Time SQL Monitoring 11GR1 新特性OracleSQL
- Oracle SQL調優系列之SQL Monitor ReportOracleSQL
- for the datagard and real time applyAPP
- steps to do real time apply in oracle 11gAPPOracle
- sql monitor中timestamp變數轉換可識別格式SQL變數
- Statement Tracer For Oracle 與 SQL Monitor 的比較OracleSQL
- Oracle11g使用sql_monitor實時監控sqlOracleSQL
- Oracle 12c RAC CSSD程式無法啟動real time模式OracleCSS模式
- Lecture 12 Real-time Ray Tracing
- sql monitor的使用(一)SQL
- Laravel 5.4 real-time facade 探究Laravel
- Lecture 05 Real-time Environment MappingAPP
- 【DG】Real-time query實時查詢操作
- Linux核心支援實時（Real-Time）(轉)Linux
- 列出oracle dbtime得sql語句OracleSQL
- 11g新動態效能檢視V$SQL_MONITOR,V$SQL_PLAN_MONITORSQL
- 【DataGuard】Oracle 11g DataGuard 新特性之 Active Standby：Real-Time Apply+QueryOracleAPP
- 【DataGuard】Oracle 11g物理Active Data Guard實時查詢（Real-time query）特性Oracle
- Oracle Real Application Clusters GFSOracleAPP
- Monitor RDBMS Session UGA and PGA Current And Maximum Usage Over TimeSession
- Real-Time C++電子書pdf下載C++
- Clock in a Linux Guest Runs More Slowly or Quickly Than Real TimeLinuxUI
- SQL Monitor Report 使用詳解SQL
- 建立Oracle 10gR2的local stream和downstream real-time apply 流複製Oracle 10gAPP
- oracle之 v$sql_monitor 監視正在執行的SQL語句的統計資訊OracleSQL
- 使用sql monitor獲取更加詳細的執行計劃 - dbms_sqltune.report_sql_monitorSQL
- Oracle 11g Health MonitorOracle
- plsql developer工具生成sql monitor reportSQLDeveloper
- 生成sql monitor active report指令碼SQL指令碼
- Monitor All SQL Queries in MySQL (alias mysql profiler)MySql
- Monitor Current SQL Running(10g)SQL