Oracle 11g实时SQL监控

来源：互联网发布：辐射4 光源优化编辑：程序博客网时间：2024/06/05 18:33

作者：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_monitorName                     Null?    Type------------------------ -------- ------------------KEY                               NUMBERSTATUS                            VARCHAR2(19)FIRST_REFRESH_TIME                DATELAST_REFRESH_TIME                 DATEREFRESH_COUNT                     NUMBERSID                               NUMBERPROCESS_NAME                      VARCHAR2(5)SQL_ID                            VARCHAR2(13)SQL_EXEC_START                    DATE SQL_EXEC_ID                       NUMBERSQL_PLAN_HASH_VALUE               NUMBERSQL_CHILD_ADDRESS                 RAW(8)SESSION_SERIAL#                   NUMBERPX_SERVER#                        NUMBERPX_SERVER_GROUP                   NUMBERPX_SERVER_SET                     NUMBERPX_QCINST_ID                      NUMBERPX_QCSID                          NUMBERELAPSED_TIME                      NUMBER CPU_TIME                          NUMBERFETCHES                           NUMBER BUFFER_GETS                       NUMBERDISK_READS                        NUMBERDIRECT_WRITES                     NUMBERAPPLICATION_WAIT_TIME             NUMBERCONCURRENCY_WAIT_TIME             NUMBERCLUSTER_WAIT_TIME                 NUMBERUSER_IO_WAIT_TIME                 NUMBERPLSQL_EXEC_TIME                   NUMBERJAVA_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 -

0 0