Oracle10g新增DBMS_MONITOR包
Oracle10g新增DBMS_MONITOR包
在10g中，Oracle新增了DBMS_MONITOR包，使得使用者可以更加方便的設定TRACE和統計資訊。
這一篇介紹TRACE相關的過程。
DBMS_MONITOR包中提供了多種方法來開啟、關閉會話或整個例項的TRACE。
除了最常用的透過會話的SID和SERIAL#進行設定的SESSION_TRACE_ENABLE/SESSION_TRACE_DISABLE外，還包括設定資料庫例項上所有會話的DATABASE_TRACE_ENABLE/DATABASE_TRACE_DISABLE過程，透過客戶端ID表示設定會話TRACE的CLIENT_ID_TRACE_ENABLE/CLIENT_ID_TRACE_DISABLE、透過SERVICE_NAME, MODULE_NAME和ACTION_NAME進行設定的SERV_MOD_ACT_TRACE_ENABLE/SERV_MOD_ACT_TRACE_DISABLE。
看一個簡單的例子：
SQL> SELECT SID, SERIAL#, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS
2 FROM V$SESSION
3 WHERE SID IN
4 (SELECT SID
5 FROM V$MYSTAT
6 WHERE ROWNUM = 1);
SID SERIAL# SQL_TRAC SQL_T SQL_T
---------- ---------- -------- ----- -----
294 45413 DISABLED FALSE FALSE
在另外的會話設定當前會話的TRACE資訊：
SQL> SET SQLP 'SQL2> '
SQL2> CONN / AS SYSDBA
已連線。
SQL2> EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(294, 45413)
PL/SQL 過程已成功完成。
返回剛才的會話檢查TRACE狀態：
SQL> SELECT SID, SERIAL#, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS
2 FROM V$SESSION
3 WHERE SID = 294;
SID SERIAL# SQL_TRAC SQL_T SQL_T
---------- ---------- -------- ----- -----
294 45413 ENABLED TRUE FALSE
可以看到，預設情況下，設定等待事件，而不設定繫結變數。
SQL2> EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(294, 45413)
PL/SQL 過程已成功完成。
關閉後，再次檢查會話狀態：
SQL> SELECT SID, CLIENT_IDENTIFIER, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS
2 FROM V$SESSION
3 WHERE SID = 294;
SID CLIENT_IDE SQL_TRAC SQL_T SQL_T
---------- ---------- -------- ----- -----
294 DISABLED FALSE FALSE
SQL> EXEC DBMS_SESSION.SET_IDENTIFIER('MY_CLIENT')
PL/SQL 過程已成功完成。
SQL> SELECT SID, CLIENT_IDENTIFIER, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS
2 FROM V$SESSION
3 WHERE SID = 294;
SID CLIENT_IDE SQL_TRAC SQL_T SQL_T
---------- ---------- -------- ----- -----
294 MY_CLIENT DISABLED FALSE FALSE
下面在另一個會話中設定客戶端標識為MY_CLIENT的會話進行TRACE：
SQL2> EXEC DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('MY_CLIENT', TRUE, TRUE)
PL/SQL 過程已成功完成。
檢查會話TRACE設定狀態：
SQL> SELECT SID, CLIENT_IDENTIFIER, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS
2 FROM V$SESSION
3 WHERE SID = 294;
SID CLIENT_IDE SQL_TRAC SQL_T SQL_T
---------- ---------- -------- ----- -----
294 MY_CLIENT DISABLED FALSE FALSE
SQL> EXEC DBMS_SESSION.SET_IDENTIFIER('MY_CLIENT')
PL/SQL 過程已成功完成。
SQL> SELECT SID, CLIENT_IDENTIFIER, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS
2 FROM V$SESSION
3 WHERE SID = 294;
SID CLIENT_IDE SQL_TRAC SQL_T SQL_T
---------- ---------- -------- ----- -----
294 MY_CLIENT DISABLED FALSE FALSE
SQL> SELECT SPID FROM V$PROCESS
2 WHERE ADDR IN
3 (SELECT PADDR
4 FROM V$SESSION
5 WHERE SID = 294);
SPID
------------
11420
似乎設定SQL_TRACE並沒有生效，但是如果檢查TRACE檔案，就會發現事實並非如此：
bash-2.03$ tkprof testrac1_ora_11420.trc /export/home/oracle/output.log sys=no
TKPROF: Release 10.2.0.3.0 - Production on 星期日 4月 4 00:53:42 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
bash-2.03$ more /export/home/oracle/output.log
TKPROF: Release 10.2.0.3.0 - Production on 星期日 4月 4 00:53:42 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: testrac1_ora_11420.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SELECT SID, SERIAL#, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS
FROM V$SESSION
WHERE SID = 294
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 165.67 165.68
********************************************************************************
SELECT SID, CLIENT_IDENTIFIER, SQL_TRACE, SQL_TRACE_WAITS, SQL_TRACE_BINDS
FROM V$SESSION
WHERE SID = 294
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.02 0.01 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 0 0 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.02 0.01 0 0 0 4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS (cr=0 pr=0 pw=0 time=94 us)
1 FIXED TABLE FIXED INDEX X$KSUSE (ind:1) (cr=0 pr=0 pw=0 time=76 us)
1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=7 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 8 0.00 0.00
SQL*Net message from client 8 486.22 653.34
********************************************************************************
SELECT SPID FROM V$PROCESS
WHERE ADDR IN
(SELECT PADDR
FROM V$SESSION
WHERE SID = 294)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.05 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.05 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 76
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
.
.
.
********************************************************************************
Trace file: testrac1_ora_11420.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
7 user SQL statements in trace file.
178 internal SQL statements in trace file.
185 SQL statements in trace file.
33 unique SQL statements in trace file.
4000 lines in trace file.
1129 elapsed seconds in trace file.
可以看到，事實上當前會話的TRACE已經被啟用，最後執行的查詢SPID的語句已經出現在TRACE檔案中。
不過由於是透過CLIENT_ID方式設定的，因此無法從當前會話的狀態中看到。
SQL2> EXEC DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('MY_CLIENT')
PL/SQL 過程已成功完成。
另外兩種過程分別是設定整個資料庫中所有會話的TRACE狀態，以及透過SERVICE_NAME、MODULE_NAME和ACTION_NAME設定會話的TRACE狀態，其實和上面介紹的兩種方法十分類似，這裡就不再重複描述了。
在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包提供的兩種設定統計資訊的方法，都是針對具有相同型別的一組會話，這使得檢查或診斷一類使用者的行為時，更加的方便，更有針對性。
About Me
...............................................................................................................................
● 本文整理自網路
● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 聯絡我請加QQ好友(646634621)，註明新增緣由
● 於 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2142442/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle10g新增DBMS_MONITOR包（二）Oracle
- Oracle10g新增DBMS_MONITOR包（一）Oracle
- 【dbms包】DBMS_MONITOR
- Oracle10g新增DBMS_FILE_TRANSFER包（二）Oracle
- Oracle10g新增DBMS_FILE_TRANSFER包（一）Oracle
- 【最佳化】DBMS_MONITOR包之生成跟蹤檔案
- Oracle10G新增NO提示Oracle
- Using dbms_monitor
- 版本包新增地址
- 在Oracle10g 新增ASM磁碟組OracleASM
- Oracle10g新增CONVERT語法Oracle
- Oracle10g新增BLOCK CHANGE TRACKING功能OracleBloC
- 在Oracle10g RAC下新增ASM磁碟組OracleASM
- Tracing Enhancements Using DBMS_MONITOR
- 新增節點oracle10g rac(rhel4)_clusterwareOracle
- 新增節點oracle10g rac(rhel4)_databaseOracleDatabase
- Oracle10g新增的檢視dba_tablespace_usage_metricsOracle
- 新增節點oracle10g rac(rhel4)_instanceOracle
- 新增jar包到本地Maven倉庫JARMaven
- linux新增或刪除元件包Linux元件
- 【RAC】Oracle10g rac新增刪除節點命令參考Oracle
- Oracle10g RAC環境OCR的新增、刪除、備份Oracle
- Oracle10g RAC環境VoteDisk的新增、刪除、備份Oracle
- DBMS_MONITOR使用 (In 10g, 11g and Above)
- oracle11g有DBMS_MONITOR跟蹤TRUNCATE操作Oracle
- win10怎麼新增使用公文包功能_windows10右鍵新增公文包選項步驟Win10Windows
- eclipse 手動新增jar包到maven庫EclipseJARMaven
- 在pom檔案中新增jar包的方法JAR
- 如何給壓縮包檔案新增註釋
- centos5 64位上的ORACLE10g安裝需要的包CentOSOracle
- Python 分發包中新增額外檔案Python
- 資料包表開發技巧：自動為資料包表新增【小計】、【總計】行
- C#/VB.NET 在PDF中新增檔案包（Portfolio）C#
- Win10系統下新增信任軟體包的方法Win10
- 微軟OS升級包新增安全API 使用者可控DEP微軟API
- Tracing Enhancements Using DBMS_MONITOR (In 10g, 11g and Above)_293661.1
- MacOS 11.x-12.x完整離線安裝包 (新增Monterey)Mac
- 在pycharm中使用pip針對不同的編譯器新增包PyCharm編譯