作者：dbtan
链接：http://www.dbtan.com/2010/04/real-time-sql-monitoring-in-oracle11g.html
Oracle 11g实时SQL监控：
前面提到，在Oracle Database 11g中，v$session视图增加了一些新的字段，这其中包括SQL_EXEC_START和SQL_EXEC_ID，这两个字段实际上代表了Oracle 11g的一个新特性：实时的SQL监控（Real Time SQL Monitoring）。
在Oracle 11g之前的版本，长时间运行的SQL可以通过监控v$session_longops来观察，当某个操作执行时间超过6秒，就会被记录在v$session_longops中，通常可以监控到全表扫描、全索引扫描、哈希联接、并行查询等操作；而在Oracle 11g中，当SQL并行执行时，会立即被实时监控到，或者当SQL单进程运行时，如果消耗超过5秒的CPU或I/O时间，它也会被监控到。监控数据被记录在v$sql_monitor视图中，当然也可以通过Oracle
11g新增的package DBMS_MONITOR来主动对SQL执行监控部署。
来看一下主要视图v$sql_monitor的结构：
sys@CCDB> desc v$sql_monitor Name Null? Type ------------------------ -------- ------------------ KEY NUMBER STATUS VARCHAR2(19) FIRST_REFRESH_TIME DATE LAST_REFRESH_TIME DATE REFRESH_COUNT NUMBER SID NUMBER PROCESS_NAME VARCHAR2(5) SQL_ID VARCHAR2(13) SQL_EXEC_START DATE SQL_EXEC_ID NUMBER SQL_PLAN_HASH_VALUE NUMBER SQL_CHILD_ADDRESS RAW(8) SESSION_SERIAL# NUMBER PX_SERVER# NUMBER PX_SERVER_GROUP NUMBER PX_SERVER_SET NUMBER PX_QCINST_ID NUMBER PX_QCSID NUMBER ELAPSED_TIME NUMBER CPU_TIME NUMBER FETCHES NUMBER BUFFER_GETS NUMBER DISK_READS NUMBER DIRECT_WRITES NUMBER APPLICATION_WAIT_TIME NUMBER CONCURRENCY_WAIT_TIME NUMBER CLUSTER_WAIT_TIME NUMBER USER_IO_WAIT_TIME NUMBER PLSQL_EXEC_TIME NUMBER JAVA_EXEC_TIME NUMBER
注意这里的SQL_EXEC_ID就是v$session视图中新增字段的来源。这个视图还记录了SQL的CPU_TIME以及BUFFER_GETS等重要信息，对于诊断SQL性能问题具有极大的帮助。结合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收集的信息每秒刷新一次，接近实时，当SQL执行完毕，信息并不会立即从v$sql_monitor中删除，至少会保留1分钟，v$sql_plan_monitor视图中的执行计划信息也是每秒更新一次，当SQL执行完毕，它们同样至少被保留1分钟。
实时SQL监控需要statistics_level初始化参数设置为TYPICAL或ALL：
sys@CCDB> show parameter statistics_level
NAME TYPE VALUE
--------------------- ------------ -------------
statistics_level string TYPICAL
sys@CCDB> select statistics_name,session_status,system_status,activation_level,session_settable
2 from v$statistics_level
3 where statistics_name = 'SQL Monitoring';
STATISTICS_NAME SESSION_STATUS SYSTEM_STATUS ACTIVATION_LEVEL SESSION_S
----------------- ---------------- ---------------- ------------------ ---------
SQL Monitoring ENABLED ENABLED TYPICAL YES
同时CONTROL_MANAGEMENT_PACK_ACCESS参数必须是DIAGNOSTIC+TUNING（这是缺省设置）：
sys@CCDB> show parameter control_manage
NAME TYPE VALUE
------------------------------------ ------------ ----------------------
control_management_pack_access string DIAGNOSTIC+TUNING
在如上设置下，数据库会启动自动的实时SQL监控，Oracle还提供Hints可以强制制定对SQL执行监控或者不允许监控，这两个Hints是monitor与no_monitor。
强制对某个SQL使用实时监控可以如下改写SQL：
select /*+ monitor */ count(*) from emp where sal > 5000;
指定不执行实时监控：
select /*+ no_monitor */ count(*) from emp where sal > 5000;
查看数据库中已经生成的监控信息可以使用DBMS_SQLTUNE包来实现：
sys@CCDB> set long 10000000
sys@CCDB> set longchunksize 10000000
sys@CCDB> set linesize 200
sys@CCDB> select dbms_sqltune.report_sql_monitor from dual;
REPORT_SQL_MONITOR
-----------------------------------
SQL Monitoring Report
SQL Text
--------------------------------------------------------------
--------------------------------------------------------------
Global Information
Status : DONE (ALL ROWS)
Instance ID : 1
Session ID : 982
SQL ID : gn3h2qrqdfwru
SQL Execution ID : 16777216
Plan Hash Value : 2959114426
Execution Started : 02/28/2010 14:02:37
First Refresh Time : 02/28/2010 14:02:41
Last Refresh Time : 02/28/2010 14:02:42
--------------------------------------------------------------------
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Reads |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | |
--------------------------------------------------------------------
| 3.46 | 0.13 | 2.38 | 0.94 | 1 | 1752 | 1214 |
--------------------------------------------------------------------
SQL Plan Monitoring Details
============================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Starts | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (percent) | (sample #) |
============================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +5 | 1 | 1 | | |
| 1 | SORT AGGREGATE | | | | 1 | +5 | 1 | 1 | | |
| 2 | APPROXIMATE NDV AGGREGATE | | | | 2 | +4 | 1 | 58610 | | |
REPORT_SQL_MONITOR
--------------------------------------------------------------------------------------------------------------------------------------------------
| 3 | TABLE ACCESS FULL | | | | 5 | +1 | 1 | 86645 | 100.00 | Cpu (1) |
| | | | | | | | | | | db file scattered read (3) |
============================================================================================================================================
这是Oracle数据库在自动化诊断方面的又一增强。
- The End -