Real-Time SQL Monitoring
2016-07-22 08:49 abce 阅读(937) 评论(0) 编辑 收藏 举报Real-Time SQL Monitoring可以在sql运行的时候监控其性能。 缺省情况下，单个sql执行花费的CPU或I/O时间超过5秒或sql并行执行的时候，Real-Time SQL Monitoring会自动启动。
可以通过视图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中增加一个entry，包含性能统计信息。执行结束后，对应的entry不会立即删除，而是会保留一分钟。 v$sql_monitor和v$sql不同，前者中的一个entry对应一个单独执行的语句；后者是累积的结果。
对于那些执行计划特多的查询sql，如果超出了隐含参数"_sqlmon_max_planlines"的设置，默认是300，sql monitor为了减少开销（cpu和内存）就不会再监控。
可以动态修改该参数：
SQL> alter system set "_sqlmon_max_planlines"=500 scope=both;
1.Real-Time SQL Monitoring主要包含以下方面的内容：
(1).SQL Plan Monitoring 实时sql监控也包含监控执行计划中每一步操作的统计信息。可以通过v$sql_plan_monitor查看。统计信息的保存周期和v$sql_monitor类似。v$sql_plan_monitor中对应一条sql会有多个entry。
(2).Parallel Execution Monitoring 并行查询、并行ddl、并行dml会被实时sql监控自动监控。
2.产生实时sql监控报告
实时sql监控报告会涉及以下视图：
gv$sql_monitor、gv$sql_plan_monitor、gv$sql、gv$sql_plan、gv$active_session_history、gv$session_longops
dbms_sqltune.report_sql_monitor可以返回一个指定sql的监控报告
SQL> var my_rept clob; SQL> begin 2 :my_rept := dbms_sqltune.report_sql_monitor(); #默认是text格式，还有html、xml格式 3 end; 4 / PL/SQL procedure successfully completed. SQL> print :my_rept
以html格式查看某个sql的sql monitor报告：
SET LONG 1000000 SET FEEDBACK OFF spool monitor_sql.html SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>''0tqfh0cggfg0v',type=> 'HTML') AS report FROM dual; spool off
以text格式查看某个sql的sql monitor报告：
SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SET LINESIZE 1000 SET PAGESIZE 0 SET TRIM ON SET TRIMSPOOL ON SET ECHO OFF SET FEEDBACK OFF SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '<sql_id>', type => 'TEXT') AS report FROM dual;
TEXT结果示例：
set long 10000000
set longchunksize 10000000
set linesize 200
select dbms_sqltune.report_sql_monitor from dual;
SQL Text
----------------------------------------------------------------------------------------
select * from (select O_ORDERDATE, sum(O_TOTALPRICE)
from orders o, lineitem l
where l.l_orderkey = o.o_orderkey
group by o_orderdate
order by o_orderdate) where rownum < 100
----------------------------------------------------------------------------------------
Global Information
Status : EXECUTING #正在执行
Instance ID : 1
Session ID : 980
SQL ID : br4m75c20p97h
SQL Execution ID : 16777219
Plan Hash Value : 2992965678
Execution Started : 06/07/2007 08:36:42
First Refresh Time : 06/07/2007 08:36:46
Last Refresh Time : 06/07/2007 08:40:02
-----------------------------------------------------------------------------------
| Elapsed | Cpu | IO | Application | Other | Buffer | Reads | Writes |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | | |
-----------------------------------------------------------------------------------
| 198 | 140 | 56 | 0.31 | 1.44 | 1195K | 1264K | 84630 |
-----------------------------------------------------------------------------------
SQL Plan Monitoring Details
# Time Active(s): 该步操作持续的active的时间，单位是秒
# Start Active: 该步操作在执行计划中相对于sql开始执行时的时间，单位是秒
=======================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start |
| | | | (Estim) | | Active(s) | Active |
=======================================================================================
| 0 | SELECT STATEMENT | | | 125K | | |
| 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 2406 | 125K | | |
| 3 | SORT GROUP BY STOPKEY | | 2406 | 125K | 99 | +101 |
| -> 4 | HASH JOIN | | 8984K | 123K | 189 | +12 |
| | | | | | | |
| 5 | INDEX FAST FULL SCAN | I_L_OKEY | 8984K | 63191 | 82 | +1 |
| | | | | | | |
| 6 | PARTITION RANGE ALL | | 44913K | 57676 | 94 | +84 |
| 7 | PARTITION HASH ALL | | 44913K | 57676 | 94 | +84 |
| 8 | TABLE ACCESS FULL | ORDERS | 44913K | 57676 | 95 | +84 |
| | | | | | | |
| | | | | | | |
=======================================================================================
continuation of above table
# Starts：表示在执行计划中运行的次数
# Rows (Actual): 产生的行数
# Activity (percent)：所用的数据库时间占整个执行计划的百分比
# Activity Detail(sample #)：显示活动的本质，比如cpu、等待事件
=======================================================================================
Starts | Rows | Memory | Temp | Activity | Activity Detail | Progress |
| (Actual) | | | (percent) | (sample #) | |
=======================================================================================
1 | | | | | | |
1 | | | | | | |
1 | | | | | | |
1 | 0 | | | 4.02 | Cpu (8) | |
1 | 28130K | 10000K | 724M | 25.13 | Cpu (48) | 87% |
| | | | | direct path read temp (2) | |
1 | 32734K | | | 34.17 | Cpu (58) | 100% |
| | | | | direct path read (10) | |
1 | 45000K | | | | | |
84 | 45000K | | | | | |
672 | 45000K | | | 36.68 | Cpu (28) | |
| | | | | reliable message (3) | |
| | | | | direct path read (42) | |
=======================================================================================
列出v$sql_monitor中的sql语句：
SET LINESIZE 300 COLUMN sql_text FORMAT A100 SELECT sql_id, status, sql_text FROM v$sql_monitor;
3.开启/关闭实时sql监控
当参数statistic_level设置为typical、all的时候，实时sql自动监控就自动开启了。
同时control_management_pack_access要被设置为DIAGNOSTIC+TUNING，因为sql监控属于调优包组件。
也可以使用hint开启/关闭：
select /+monitor+/ from dual; select /+no_monitor+/ from dual;
