Real-Time SQL Monitoring

Real-Time SQL Monitoring可以在sql运行的时候监控其性能。 缺省情况下，单个sql执行花费的CPU或I/O时间超过5秒或sql并行执行的时候，Real-Time SQL Monitoring会自动启动。

可以通过视图v$sql_monitor、v$sql_plan_monitor来查看sql语句运行时的统计信息。

在结合以下视图，可以获取更多的信息： v$active_session_history、v$session、v$session_longops、v$sql、v$sql_plan

 

一旦开始监控，就会在v$sql_monitor中增加一个entry，包含性能统计信息。执行结束后，对应的entry不会立即删除，而是会保留一分钟。 v$sql_monitor和v$sql不同，前者中的一个entry对应一个单独执行的语句；后者是累积的结果。

 

对于那些执行计划特多的查询sql，如果超出了隐含参数"_sqlmon_max_planlines"的设置，默认是300，sql monitor为了减少开销（cpu和内存）就不会再监控。
可以动态修改该参数：

?

1	SQL> alter system set "_sqlmon_max_planlines"=500 scope=both;

 

1.Real-Time SQL Monitoring主要包含以下方面的内容：

(1).SQL Plan Monitoring 实时sql监控也包含监控执行计划中每一步操作的统计信息。可以通过v$sql_plan_monitor查看。统计信息的保存周期和v$sql_monitor类似。v$sql_plan_monitor中对应一条sql会有多个entry。

(2).Parallel Execution Monitoring 并行查询、并行ddl、并行dml会被实时sql监控自动监控。

 

2.产生实时sql监控报告

实时sql监控报告会涉及以下视图：

gv$sql_monitor、gv$sql_plan_monitor、gv$sql、gv$sql_plan、gv$active_session_history、gv$session_longops

 

dbms_sqltune.report_sql_monitor可以返回一个指定sql的监控报告

?

1 2 3 4 5 6 7 8 9

SQL> var my_rept clob; SQL> begin   2     :my_rept := dbms_sqltune.report_sql_monitor(); #默认是text格式，还有html、xml格式   3  end;   4  /   PL/SQL procedure successfully completed.   SQL> print :my_rept

 

以html格式查看某个sql的sql monitor报告：

?

1 2 3 4 5 6

SET LONG 1000000 SET FEEDBACK OFF spool monitor_sql.html SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>''0tqfh0cggfg0v',type=> 'HTML') AS report FROM dual; spool off

 

以text格式查看某个sql的sql monitor报告：

?

1 2 3 4 5 6 7 8 9 10 11

SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SET LINESIZE 1000 SET PAGESIZE 0 SET TRIM ON SET TRIMSPOOL ON SET ECHO OFF SET FEEDBACK OFF   SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '<sql_id>', type => 'TEXT') AS report FROM dual;

TEXT结果示例：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

set long 10000000 set longchunksize 10000000 set linesize 200 select dbms_sqltune.report_sql_monitor from dual;    SQL Text ---------------------------------------------------------------------------------------- select * from (select O_ORDERDATE, sum(O_TOTALPRICE)                from  orders o, lineitem l                where l.l_orderkey = o.o_orderkey                group by o_orderdate                order by o_orderdate) where rownum < 100 ----------------------------------------------------------------------------------------    Global Information  Status              :  EXECUTING       #正在执行  Instance ID         :  1  Session ID          :  980  SQL ID              :  br4m75c20p97h  SQL Execution ID    :  16777219  Plan Hash Value     :  2992965678  Execution Started   :  06/07/2007 08:36:42  First Refresh Time  :  06/07/2007 08:36:46  Last Refresh Time   :  06/07/2007 08:40:02    ----------------------------------------------------------------------------------- | Elapsed |   Cpu   |    IO    | Application |  Other   | Buffer | Reads | Writes | | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  |       |        | ----------------------------------------------------------------------------------- |     198 |     140 |       56 |        0.31 |     1.44 |  1195K | 1264K |  84630 | -----------------------------------------------------------------------------------    SQL Plan Monitoring Details # Time Active(s): 该步操作持续的active的时间，单位是秒 # Start Active: 该步操作在执行计划中相对于sql开始执行时的时间，单位是秒 ======================================================================================= | Id   |         Operation          |   Name   |  Rows   | Cost  |   Time    | Start  | |      |                            |          | (Estim) |       | Active(s) | Active | ======================================================================================= |    0 | SELECT STATEMENT           |          |         |  125K |           |        | |    1 |   COUNT STOPKEY            |          |         |       |           |        | |    2 |    VIEW                    |          |    2406 |  125K |           |        | |    3 |     SORT GROUP BY STOPKEY  |          |    2406 |  125K |        99 |   +101 | | -> 4 |      HASH JOIN             |          |   8984K |  123K |       189 |    +12 | |      |                            |          |         |       |           |        | |    5 |       INDEX FAST FULL SCAN | I_L_OKEY |   8984K | 63191 |        82 |     +1 | |      |                            |          |         |       |           |        | |    6 |       PARTITION RANGE ALL  |          |  44913K | 57676 |        94 |    +84 | |    7 |        PARTITION HASH ALL  |          |  44913K | 57676 |        94 |    +84 | |    8 |         TABLE ACCESS FULL  | ORDERS   |  44913K | 57676 |        95 |    +84 | |      |                            |          |         |       |           |        | |      |                            |          |         |       |           |        | =======================================================================================    continuation of above table # Starts：表示在执行计划中运行的次数 # Rows (Actual): 产生的行数 # Activity (percent)：所用的数据库时间占整个执行计划的百分比 # Activity Detail(sample #)：显示活动的本质，比如cpu、等待事件 =======================================================================================  Starts |   Rows   | Memory | Temp | Activity  |      Activity Detail      | Progress |         | (Actual) |        |      | (percent) |        (sample #)         |          | =======================================================================================       1 |          |        |      |           |                           |          |       1 |          |        |      |           |                           |          |       1 |          |        |      |           |                           |          |       1 |        0 |        |      |      4.02 | Cpu (8)                   |          |       1 |   28130K | 10000K | 724M |     25.13 | Cpu (48)                  | 87%      |         |          |        |      |           | direct path read temp (2) |          |       1 |   32734K |        |      |     34.17 | Cpu (58)                  | 100%     |         |          |        |      |           | direct path read (10)     |          |       1 |   45000K |        |      |           |                           |          |      84 |   45000K |        |      |           |                           |          |     672 |   45000K |        |      |     36.68 | Cpu (28)                  |          |         |          |        |      |           | reliable message (3)      |          |         |          |        |      |           | direct path read (42)     |          | =======================================================================================

 

列出v$sql_monitor中的sql语句：

?

1 2 3

SET LINESIZE 300 COLUMN sql_text FORMAT A100 SELECT sql_id, status, sql_text FROM v$sql_monitor;

 

3.开启/关闭实时sql监控
当参数statistic_level设置为typical、all的时候，实时sql自动监控就自动开启了。
同时control_management_pack_access要被设置为DIAGNOSTIC+TUNING，因为sql监控属于调优包组件。

也可以使用hint开启/关闭：

?

1 2	select /+monitor+/ from dual; select /+no_monitor+/ from dual;