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的结构：Name Type Nullable Default Comments ------------------------- -------------- -------- ------- -------- KEY NUMBER Y STATUS VARCHAR2(19) Y USER# NUMBER Y USERNAME VARCHAR2(30) Y MODULE VARCHAR2(64) Y ACTION VARCHAR2(64) Y SERVICE_NAME VARCHAR2(64) Y CLIENT_IDENTIFIER VARCHAR2(64) Y CLIENT_INFO VARCHAR2(64) Y PROGRAM VARCHAR2(48) Y PLSQL_ENTRY_OBJECT_ID NUMBER Y PLSQL_ENTRY_SUBPROGRAM_ID NUMBER Y PLSQL_OBJECT_ID NUMBER Y PLSQL_SUBPROGRAM_ID NUMBER Y FIRST_REFRESH_TIME DATE Y LAST_REFRESH_TIME DATE Y REFRESH_COUNT NUMBER Y SID NUMBER Y PROCESS_NAME VARCHAR2(5) Y SQL_ID VARCHAR2(13) Y SQL_TEXT VARCHAR2(2000) Y IS_FULL_SQLTEXT VARCHAR2(1) Y SQL_EXEC_START DATE Y SQL_EXEC_ID NUMBER Y SQL_PLAN_HASH_VALUE NUMBER Y EXACT_MATCHING_SIGNATURE NUMBER Y FORCE_MATCHING_SIGNATURE NUMBER Y SQL_CHILD_ADDRESS RAW(8) Y SESSION_SERIAL# NUMBER Y PX_IS_CROSS_INSTANCE VARCHAR2(1) Y PX_MAXDOP NUMBER Y PX_MAXDOP_INSTANCES NUMBER Y PX_SERVERS_REQUESTED NUMBER Y PX_SERVERS_ALLOCATED NUMBER Y PX_SERVER# NUMBER Y PX_SERVER_GROUP NUMBER Y PX_SERVER_SET NUMBER Y PX_QCINST_ID NUMBER Y PX_QCSID NUMBER Y ERROR_NUMBER VARCHAR2(40) Y ERROR_FACILITY VARCHAR2(4) Y ERROR_MESSAGE VARCHAR2(256) Y BINDS_XML CLOB Y OTHER_XML CLOB Y ELAPSED_TIME NUMBER Y QUEUING_TIME NUMBER Y CPU_TIME NUMBER Y FETCHES NUMBER Y BUFFER_GETS NUMBER Y DISK_READS NUMBER Y DIRECT_WRITES NUMBER Y IO_INTERCONNECT_BYTES NUMBER Y PHYSICAL_READ_REQUESTS NUMBER Y PHYSICAL_READ_BYTES NUMBER Y PHYSICAL_WRITE_REQUESTS NUMBER Y PHYSICAL_WRITE_BYTES NUMBER Y APPLICATION_WAIT_TIME NUMBER Y CONCURRENCY_WAIT_TIME NUMBER Y CLUSTER_WAIT_TIME NUMBER Y USER_IO_WAIT_TIME NUMBER Y PLSQL_EXEC_TIME NUMBER Y JAVA_EXEC_TIME NUMBER Y RM_LAST_ACTION VARCHAR2(48) Y RM_LAST_ACTION_REASON VARCHAR2(30) Y RM_LAST_ACTION_TIME DATE Y RM_CONSUMER_GROUP VARCHAR2(30) Y SQL> 注意这里的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：SQL> show parameter statistics_levelNAME TYPE VALUE------------------------------------ ----------- ------------------------------statistics_level string TYPICALSQL> 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_SETTABLE---------------------------------------------------------------- -------------- ------------- ---------------- ----------------SQL Monitoring ENABLED ENABLED TYPICAL YES同时CONTROL_MANAGEMENT_PACK_ACCESS参数必须是DIAGNOSTIC+TUNING（这是缺省设置）：SQL> show parameter control_manageNAME 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包来实现：SQL> set long 10000000SQL> set longchunksize 10000000SQL> set linesize 200SQL> select dbms_sqltune.report_sql_monitor from dual;REPORT_SQL_MONITOR-------------------------------------------------------------------------------------SQL Monitoring ReportSQL Text------------------------------select /*+ monitor */ count(*) from emp where sal > 5000Global Information------------------------------ Status : DONE (ALL ROWS) Instance ID : 1 Session : SCOTT (142:43) SQL ID : 2bhgjhbypqw17 SQL Execution ID : 16777216 Execution Started : 10/29/2018 23:46:53 First Refresh Time : 10/29/2018 23:46:53 Last Refresh Time : 10/29/2018 23:46:53 Duration : .001s Module/Action : SQL*Plus/- Service : SYS$USERS Program : sqlplus@prod (TNS V1-V3) Fetch Calls : 1Global Stats======================================| Elapsed | Cpu | Fetch | Buffer || Time(s) | Time(s) | Calls | Gets |======================================| 0.00 | 0.00 | 1 | 7 |======================================SQL Plan Monitoring Details (Plan Hash Value=2083865914)==========================================================================================================================| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail || | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |==========================================================================================================================| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | || 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | || 2 | TABLE ACCESS FULL | EMP | 1 | 3 | | | 1 | | | |==========================================================================================================================
