Oracle10g新增DBMS_MONITOR包（二）

在10g中，Oracle新增了DBMS_MONITOR包，使得使用者可以更加方便的設定TRACE和統計資訊。

這一篇介紹統計相關的過程。

Oracle10g新增DBMS_MONITOR包（一）：http://yangtingkun.itpub.net/post/468/498878

除了提供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包提供的兩種設定統計資訊的方法，都是針對具有相同型別的一組會話，這使得檢查或診斷一類使用者的行為時，更加的方便，更有針對性。