在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状态，其实和上面介绍的两种方法十分类似，这里就不再重复描述了。
3261
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
