Oracle Real Time SQL Monitor

94次阅读

共计 11614 个字符，预计需要花费 30 分钟才能阅读完成。

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。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-10/136208.htm

正文完

星哥说事-微信公众号