实时监控SQL
Oracle数据库的实时SQL监控特性能够在SQL语句执行的时候监控其性能,默认情况下,当SQL语句并行运行时、或者单个执行时至少消耗5秒的CPU或者I/O时,SQL监控将会自动启动。
使用视图V$sql_monitor和v$sql_plan_monitor监控SQL语句执行的统计信息,然后结合下面的视图得到被监控SQL语句的额外信息:
- v$active_session_history
- v$session
- v$session_longops
- v$sql
- v$sql_plan
在启动监视之后,数据库将一个条目添加进v$sql_monitor视图,该条目追踪执行时收集的关键性能指标,包括:运行时间、CPU时间、读和写的次数,I/O等待时间和其他等待时间。当语句执行时,这些统计信息近乎实时刷新,通常是每秒一次。执行结束之后,监控信息不会被立即删除,而是会保存在v$sql_monitor至少一分钟。该条目最终将会被删除, 这样它的空间将会被回收,以用来监视新的SQL语句。
v$sql_monitor视图的统计信息是V$sql视图的子集,但是,与v$sql不同,监控统计信息不会累积,相反,一条条目对应一次SQL语句的执行,如果数据库监控同一条SQL语句执行两次,那么每次执行都会在v$sql_monitor产生一个条目。
为了唯一识别同一条语句多次执行,在v$sql_monitor视图中,使用符合主键(sql_id,sql_exec_start,sql_exec_id)来识别。
1 监视SQL计划 & 监视并行执行
可以从v$sql_monitor和v$sql_plan_monitor查看。
2 生成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 Monitoring Report
SQL Text
------------------------------
SELECT /*+ monitor*/ * FROM scott.emp
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (143:7921)
SQL ID : 6f7k26xugr165
SQL Execution ID : 16777216
Execution Started : 11/21/2017 11:13:25
First Refresh Time : 11/21/2017 11:13:25
Last Refresh Time : 11/21/2017 11:13:25
Duration : .040035s
Module/Action : PL/SQL Developer/SQL Window - SELECT dbms_rowid.r
Service : orcl.hc.cn
Program : plsqldev.exe
Fetch Calls : 1
Global Stats
=================================================================
| Elapsed | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=================================================================
| 0.04 | 0.02 | 0.02 | 1 | 3 | 2 | 16384 |
=================================================================
SQL Plan Monitoring Details (Plan Hash Value=3956160932)
========================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
========================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | |
| 1 | TABLE ACCESS FULL | EMP | 14 | 3 | | | 1 | | 2 | 16384 | | |
========================================================================================================================================
3 启用/禁用SQL监视
默认情况下,当statistics_level初始化参数设置为all或typical(默认值)时,SQL监视特性是启用的。
除此之外,control_managment_pack_access参数必须被设置为diagnostic+tuning(默认值),因为SQL监视是Oracle数据库优化包的特性。SQL监视为所有运行时间较长的查询自动启用。
语句级别的启用和禁用,使用hint提示实现;
select /*+ monitor*/ from scott.emp;
或select /*+ no_monitor*/ from scott.emp;
参考:官方文档