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_reptPL/SQL procedure successfully completed.SQL>
MY_REPT
---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ------------------------
SQL Monitoring ReportSQL 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 : 1Global 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 ReportSQL 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。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址