|
在11g以前的版本,SQL的运行情况可以通过监控v$session_longops来了解,当某个操作执行时间超过6秒,就会被v$session_longops感知,通常可以监控到比如全表扫描,全索引扫描,哈希联接,并行查询等;在11g中,当sql并行运行时,马上会被real-time monitor到,当sql单进程运行时,如果运行时间超过5秒,它也会被监控到。
可以通过v$sql_monitor与v$sql_plan_monitor视图查看sql执行的统计信息，可以联合v$active_session_history，v$session，v$session_longops，v$sql, v$sql_plan等视图，查看sql更多的信息。v$sql_monitor收集关键的一些指标，比如：elapsed time, CPU time, number of reads and writes, I/O wait time and various other wait times等，这些信息是每秒刷新一次，当sql执行完比，并不会立即把它从v$sql_monitor中删除，至少保留1分钟，real-time sql monitor也包括收集sql执行计划的统计信息，可以通过v$sql_plan_monitor视图来查看被监控sql的执行计划，这些统计数据也是每秒更新一次，当sql执行完结，它们至少被保留1分钟。
如何生成sql监控报表：
方法一：
variable my_rept CLOB;
BEGIN
:my_rept :=DBMS_SQLTUNE.REPORT_SQL_MONITOR();
END;
/
print :my_rept
方法二：
set long 10000000
set longchunksize 10000000
set linesize 200
select dbms_sqltune.report_sql_monitor from dual;
如何激活或禁止real-time sql monitor?
real-time sql monitor需要statistics_level参数等于all或typical，且CONTROL_MANAGEMENT_PACK_ACCESS参数必须是DIAGNOSTIC+TUNING（默认就是如此），还有两个语句级的hints可以激活或禁止real-time sql monitor:/*+ monitor */与/*+ no_monitor */，这两个参数也必须在CONTROL_MANAGEMENT_PACK_ACCESS参数是DIAGNOSTIC+TUNING下才生效，案例：
强制sql使用实时监控：
select /*+ monitor */ count(*) from test where title = 'abc';
取消sql使用实时监控：
select /*+ no_monitor */ count(*) from test where title = 'abc';
Sys@ORA11G> set long 10000000
Sys@ORA11G> set longchunksize 10000000
Sys@ORA11G> set linesize 200
Sys@ORA11G> select dbms_sqltune.report_sql_monitor from dual;
REPORT_SQL_MONITOR
--------------------------------------------------------------
SQL Monitoring Report SQL Text
--------------------------------------------------------------
SELECT COUNT(*) FROM TEST WHERE OBJECT_ID = :B1
-------------------------------------------------------------- Global Information
Status : DONE (ALL ROWS)
Instance ID : 1
Session ID : 122
SQL ID : 2ywfyn7r0ywky
SQL Execution ID : 16777216
Plan Hash Value : 1950795681
Execution Started : 08/16/2007 15:48:24
First Refresh Time : 08/16/2007 15:48:28
Last Refresh Time : 08/16/2007 15:48:30 --------------------------------------------------------------------
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Reads |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | |
--------------------------------------------------------------------
| 4.30 | 1.36 | 0.01 | 2.93 | 1 | 94869 | 94613 |
-------------------------------------------------------------------- SQL Plan Monitoring Details
==================================================================
| Id | Operation | Name | Rows | Cost | Time |
| | | | (Estim) | | Active(s) |
==================================================================
| 0 | SELECT STATEMENT | | | 35310 | 1 |
| 1 | SORT AGGREGATE | | 1 | | 1 |
| 2 | TABLE ACCESS FULL | TEST | 126 | 35310 | 5 |
================================================================== 接上表:
===========================================================
Start | Starts | Rows | Activity | Activity Detail |
Active | | (Actual) | (percent) | (sample #) |
===========================================================
+6 | 1 | 1 | | |
+6 | 1 | 1 | | |
+2 | 1 | 0 | 100.00 | Cpu (5) |
===========================================================
|
