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包提供的两种设置统计信息的方法，都是针对具有相同类型的一组会话，这使得检查或诊断一类用户的行为时，更加的方便，更有针对性。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-631326/ ，如需转载，请注明出处，否则将追究法律责任。
