Oracle10g新增DBMS_MONITOR包（二）

 

在10g中，Oracle新增了DBMS_MONITOR包，使得用户可以更加方便的设置TRACE和统计信息。

这一篇介绍统计相关的过程。

 

 

 

除了提供TRACE的开启和关闭外，DBMS_MONITOR包还可以开启关闭对指定会话的统计功能。

SQL> SELECT SID, SERIAL#, CLIENT_IDENTIFIER

  2  FROM V$SESSION

  3  WHERE SID IN

  4  (SELECT SID

  5  FROM V$MYSTAT

  6  WHERE ROWNUM = 1);

       SID    SERIAL# CLIENT_IDENTIFIER

---------- ---------- ------------------------------

       308      58723

SQL> SELECT * FROM V$CLIENT_STATS;

未选定行

SQL> EXEC DBMS_SESSION.SET_IDENTIFIER('MY_CLIENT')

PL/SQL过程已成功完成。

SQL> SELECT * FROM V$CLIENT_STATS;

未选定行

下面在另外的会话启动CLIENT_ID为MY_CLIENT客户端标识的统计功能：

SQL> SET SQLP 'SQL2> '

SQL2> EXEC DBMS_MONITOR.CLIENT_ID_STAT_ENABLE('MY_CLIENT')

PL/SQL过程已成功完成。

这是回到刚才的会话检查统计信息：

SQL> SELECT * FROM V$CLIENT_STATS;

CLIENT_IDENTIFIER                 STAT_ID STAT_NAME                             VALUE

------------------------------ ---------- -------------------------------- ----------

MY_CLIENT                      2882015696 user calls                                1

MY_CLIENT                      3649082374 DB time                                 777

MY_CLIENT                      2748282437 DB CPU                                  777

MY_CLIENT                        63887964 parse count (total)                       1

MY_CLIENT                      1431595225 parse time elapsed                      151

MY_CLIENT                      2453370665 execute count                             2

MY_CLIENT                      2821698184 sql execute elapsed time                277

MY_CLIENT                        85052502 opened cursors cumulative                 1

MY_CLIENT                      3143187968 session logical reads                     0

MY_CLIENT                      2263124246 physical reads                            0

MY_CLIENT                      1190468109 physical writes                           0

MY_CLIENT                      1236385760 redo size                                 0

MY_CLIENT                       582481098 user commits                              0

MY_CLIENT                      3211650785 workarea executions - optimal             0

MY_CLIENT                       798730793 workarea executions - onepass             0

MY_CLIENT                      3804491469 workarea executions - multipass           0

MY_CLIENT                      3678609077 session cursor cache hits                 0

MY_CLIENT                      3671147913 user rollbacks                            0

MY_CLIENT                       916801489 db block changes                          0

MY_CLIENT                      2877738702 gc cr blocks received                     0

MY_CLIENT                      1759426133 gc cr block receive time                  0

MY_CLIENT                       326482564 gc current blocks received                0

MY_CLIENT                      1388758753 gc current block receive time             0

MY_CLIENT                      2432034337 cluster wait time                         0

MY_CLIENT                      3868577743 concurrency wait time                     0

MY_CLIENT                      1099569955 application wait time                     0

MY_CLIENT                      3332107451 user I/O wait time                        0

已选择27行。

SQL> SELECT * FROM V$CLIENT_STATS;

CLIENT_IDENTIFIER                 STAT_ID STAT_NAME                             VALUE

------------------------------ ---------- -------------------------------- ----------

MY_CLIENT                      2882015696 user calls                                5

MY_CLIENT                      3649082374 DB time                                2837

MY_CLIENT                      2748282437 DB CPU                                 2837

MY_CLIENT                        63887964 parse count (total)                       2

MY_CLIENT                      1431595225 parse time elapsed                      251

MY_CLIENT                      2453370665 execute count                             6

MY_CLIENT                      2821698184 sql execute elapsed time               1801

MY_CLIENT                        85052502 opened cursors cumulative                 2

MY_CLIENT                      3143187968 session logical reads                     0

MY_CLIENT                      2263124246 physical reads                            0

MY_CLIENT                      1190468109 physical writes                           0

MY_CLIENT                      1236385760 redo size                                 0

MY_CLIENT                       582481098 user commits                              0

MY_CLIENT                      3211650785 workarea executions - optimal             0

MY_CLIENT                       798730793 workarea executions - onepass             0

MY_CLIENT                      3804491469 workarea executions - multipass           0

MY_CLIENT                      3678609077 session cursor cache hits                 1

MY_CLIENT                      3671147913 user rollbacks                            0

MY_CLIENT                       916801489 db block changes                          0

MY_CLIENT                      2877738702 gc cr blocks received                     0

MY_CLIENT                      1759426133 gc cr block receive time                  0

MY_CLIENT                       326482564 gc current blocks received                0

MY_CLIENT                      1388758753 gc current block receive time             0

MY_CLIENT                      2432034337 cluster wait time                         0

MY_CLIENT                      3868577743 concurrency wait time                     0

MY_CLIENT                      1099569955 application wait time                     0

MY_CLIENT                      3332107451 user I/O wait time                        0

已选择27行。

可以看到，会话的统计信息根据用户的操作而不断的更新。

会话的统计信息可以通过CLIENT_ID_STAT_DISABLE过程关闭：

SQL2> EXEC DBMS_MONITOR.CLIENT_ID_STAT_DISABLE('MY_CLIENT')

PL/SQL过程已成功完成。

这时，V$CLIENT_STAT视图中的统计信息消失：

SQL> SELECT * FROM V$CLIENT_STATS;

未选定行

那么DBMS_MONITOR包提供的这种统计功能和V$MYSTAT提供的有何不同之处，V$MYSTAT或者V$SESSTAT提供的是会话级别的统计，而CLIENT_ID_STAT_ENABLE提供的是指定客户端标识符的统计功能，任何会话只要设置了指定统计的标识符，都会被累计到统计值之中。而只要更改了CLIENT_IDENTIFIER，这个会话的统计信息就不会再被统计。

下面看看SERV_MOD_ACT_STAT_ENABLE过程：

SQL> CONN TEST/TEST@TESTRAC1

已连接。

SQL> SELECT SID, SERIAL#, SERVICE_NAME, MODULE

  2  FROM V$SESSION

  3  WHERE SID IN

  4  (SELECT SID

  5  FROM V$MYSTAT

  6  WHERE ROWNUM = 1);

       SID    SERIAL# SERVICE_NAME         MODULE

---------- ---------- -------------------- ------------------------------

       308      58725 testrac              SQL*Plus

SQL> SELECT * FROM V$SERV_MOD_ACT_STATS;

未选定行

执行SERV_MOD_ACT_STAT_ENABLE过程，对使用testrac为服务名的SQLPLUS连接设置统计信息：

SQL2> EXEC DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE('testrac', 'SQL*Plus')

PL/SQL过程已成功完成。

再次检查对应的视图：

SQL> SELECT * FROM V$SERV_MOD_ACT_STATS;

AGGREGATION_TYPE SERVICE_NAME MODULE   ACTION    STAT_ID STAT_NAME                       VALUE

---------------- ------------ -------- ------ ---------- ------------------------------- -----

SERVICE_MODULE   testrac      SQL*Plus        2882015696 user calls                          9

SERVICE_MODULE   testrac      SQL*Plus        3649082374 DB time                          8740

SERVICE_MODULE   testrac      SQL*Plus        2748282437 DB CPU                           8740

SERVICE_MODULE   testrac      SQL*Plus          63887964 parse count (total)                 3

SERVICE_MODULE   testrac      SQL*Plus        1431595225 parse time elapsed               2329

SERVICE_MODULE   testrac      SQL*Plus        2453370665 execute count                      10

SERVICE_MODULE   testrac      SQL*Plus        2821698184 sql execute elapsed time         5137

SERVICE_MODULE   testrac      SQL*Plus          85052502 opened cursors cumulative           3

SERVICE_MODULE   testrac      SQL*Plus        3143187968 session logical reads               0

SERVICE_MODULE   testrac      SQL*Plus        2263124246 physical reads                      0

SERVICE_MODULE   testrac      SQL*Plus        1190468109 physical writes                     0

SERVICE_MODULE   testrac      SQL*Plus        1236385760 redo size                           0

SERVICE_MODULE   testrac      SQL*Plus         582481098 user commits                        0

SERVICE_MODULE   testrac      SQL*Plus        3211650785 workarea executions - optimal       0

SERVICE_MODULE   testrac      SQL*Plus         798730793 workarea executions - onepass       0

SERVICE_MODULE   testrac      SQL*Plus        3804491469 workarea executions - multipass     0

SERVICE_MODULE   testrac      SQL*Plus        3678609077 session cursor cache hits           1

SERVICE_MODULE   testrac      SQL*Plus        3671147913 user rollbacks                      0

SERVICE_MODULE   testrac      SQL*Plus         916801489 db block changes                    0

SERVICE_MODULE   testrac      SQL*Plus        2877738702 gc cr blocks received               0

SERVICE_MODULE   testrac      SQL*Plus        1759426133 gc cr block receive time            0

SERVICE_MODULE   testrac      SQL*Plus         326482564 gc current blocks received          0

SERVICE_MODULE   testrac      SQL*Plus        1388758753 gc current block receive time       0

SERVICE_MODULE   testrac      SQL*Plus        2432034337 cluster wait time                   0

SERVICE_MODULE   testrac      SQL*Plus        3868577743 concurrency wait time            1122

SERVICE_MODULE   testrac      SQL*Plus        1099569955 application wait time               0

SERVICE_MODULE   testrac      SQL*Plus        3332107451 user I/O wait time                  0

已选择27行。

利用SERV_MOD_ACT_STAT_DISABLE过程进行统计信息的关闭：

SQL2> EXEC DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE('testrac', 'SQL*Plus')

PL/SQL过程已成功完成。

这时统计信息被关闭：

SQL> SELECT * FROM V$SERV_MOD_ACT_STATS;

未选定行

和CLIENT_ID方式一样，这种统计信息是对应所有通过指定服务名登陆，且MODULE_NAME与指定的MODULE一致的会话的。因此DBMS_MONITOR包提供的两种设置统计信息的方法，都是针对具有相同类型的一组会话，这使得检查或诊断一类用户的行为时，更加的方便，更有针对性。