在11g以前的版本,SQL的运行情况可以通过监控v$session_longops来了解,当某个操作执行时间超过6秒,就会被v$session_longops感知,通常可以监控到比如全表扫描,全索引扫描,哈希联接,并行查询等;在11g中,当sql并行运行时,马上会被real-time monitor到,当sql单进程运行时,如果运行时间超过5秒,它也会被监控到。
[@more@]可以通过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) |
===========================================================
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16648/viewspace-962079/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16648/viewspace-962079/