（轉）Oracle EVENT && ORADEBUG
--首先介紹下獲得當前SESSION的SID、SERIAL#的幾種方法：
1、select sid from v$mystat where rownum = 1;
2、select sid from v$session where audsid = userenv('sessionid');
3、select sid from v$session where audsid = sys_context('userenv','sessionid');
4、select sid from v$session where sid = dbms_support.mysid;--需要單獨安裝包
D:Oracleora92rdbmsadmin>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 6月 5 11:20:03 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn /as sysdba
已連線。
SQL> @dbmssupp.sql
程式包已建立。
程式包主體已建立。
SQL> GRANT EXECUTE ON dbms_support TO PUBLIC;
授權成功。
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;
同義詞已建立。
SQL> conn test/test
已連線。
SQL> select sid from v$session where sid = dbms_support.mysid;
一、ORACLE EVENT
1、“SQL TRACE”
是Oracle提供的用於進行SQL跟蹤的手段，是強有力的輔助診斷工具。在日常的資料庫問題診斷和解決中，“SQL TRACE”是
非常常用的方法。
在全域性啟用
在引數檔案(pfile/spfile)中指定: SQL_TRACE = true
在全域性啟用SQL_TRACE會導致所有程式的活動被跟蹤，包括後臺程式及所有使用者程式，這通常會導致比較嚴重的效能問題，
所以在生產環境中要謹慎使用。
在當前session級設定
SQL> alter session set SQL_TRACE=true;
會話已更改。
SQL> select * from test;
COL1 COL2 COL3
-------------------- -------------------- --------------------
00001 00002 00003
00004 00005 00006
SQL> alter session set SQL_TRACE=false;
會話已更改。
跟蹤其它使用者程式
SQL> exec dbms_system.set_SQL_TRACE_in_session(sid,seial#,true);
SQL> exec dbms_system.set_SQL_TRACE_in_session(sid,seial#,false);
2. 10046事件
10046事件是Oracle提供的內部事件，是對SQL_TRACE的增強.
10046事件可以設定以下四個級別:
1 - 啟用標準的SQL_TRACE功能,等價於SQL_TRACE。
4 - Level 1 加上繫結值(bind values)
8 - Level 1 + 等待事件跟蹤
12 - Level 1 + Level 4 + Level 8
a． 在全域性設定
在引數檔案中增加:
EVENT="10046 trace name context forever,level 12"
此設定對所有使用者的所有程式生效、包括後臺程式.
b． 對當前session設定
SQL> alter session set events '10046 trace name context forever';
SQL> alter session set events '10046 trace name context forever, level 8';
SQL> alter session set events '10046 trace name context off';
c． 對其他使用者session設定
SQL> exec dbms_system.set_ev(sid,seial#,10046,12,'');
3、利用DBMS_SUPPORT包
更多內容參看：http://zhouwf0726.itpub.net/post/9689/291636
SQL>EXECUTE dbms_support.start_trace;
SQL>EXECUTE dbms_support.start_trace (BINDS=>true);
SQL>EXECUTE dbms_support.start_trace (WAITS=>true);
SQL>EXECUTE dbms_support.start_trace_in_session(25,4328,waits->true,binds=>true);
SQL>EXECUTE dbms_support.stop_trace;
SQL>EXECUTE dbms_support.stop_trace_in_session (25, 4328);
給出幾個跟蹤指定的SESSION的例子：
SESSION 1:
SQL> SHOW USER
USER 為"TEST"
SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID=(select sid from v$mystat where
rownum = 1);
SID SERIAL#
---------- ----------
12 6
SESSION 2:
SQL> SHOW USER
USER 為"SYS"
SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(12,6,TRUE);
PL/SQL 過程已成功完成。
SESSION 1:
SQL> set serveroutput on
SQL> declare
2 event_level number;
3 begin
4 for event_number in 10000..10999 loop
5 sys.dbms_system.read_ev(event_number, event_level);
6 if (event_level > 0) then
7 sys.dbms_output.put_line('Event '||to_char(event_number)||' is set at level '||to_char(event_level));
8 end if;
9 end loop;
10 end;
11 /
Event 10046 is set at level 1
PL/SQL 過程已成功完成。
SQL> SELECT * FROM TEST;
COL1 COL2 COL3
-------------------- -------------------- --------------------
00001 00002 00003
00004 00005 00006
SQL> SELECT P1.VALUE||''||P2.VALUE||'_ORA_'||P.SPID FILENAME
2 FROM
3 V$PROCESS P,
4 V$SESSION S,
5 V$PARAMETER P1,
6 V$PARAMETER P2
7 WHERE P1.NAME = 'user_dump_dest'
8 AND P2.NAME = 'db_name'
9 AND P.ADDR = S.PADDR
10 AND S.AUDSID = USERENV ('SESSIONID');
FILENAME
--------------------------------------------------------------------------------
D:oracleadminorcludumporcl_ORA_3204
3204:(可以用tkprof格式化後分析)
PARSING IN CURSOR #1 len=18 dep=0 uid=61 ct=3 lid=61 tim=5017207740 hv=3157870488 ad='650e4cf0'
SELECT * FROM TEST
END OF STMT
PARSE #1:c=93750,e=602584,p=1,cr=19,cu=0,mis=1,r=0,dep=0,og=4,tim=5017207733
EXEC #1:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=5017238223
FETCH #1:c=0,e=442,p=1,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=5017244533
FETCH #1:c=0,e=34,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=4,tim=5017250817
*** 2007-06-05 09:40:41.000
SESSION 2:
SQL> SHOW USER
USER 為"SYS"
SQL> EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(12,6,FALSE);
SESSION 1:
SQL> declare
2 event_level number;
3 begin
4 for event_number in 10000..10999 loop
5 sys.dbms_system.read_ev(event_number, event_level);
6 if (event_level > 0) then
7 sys.dbms_output.put_line('Event '||to_char(event_number)||' is set at level '||to_char(event_level));
8 end if;
9 end loop;
10 end;
11 /
PL/SQL 過程已成功完成。
SESSION 2:
SQL> exec dbms_system.set_ev(12,6,10046,12,'');
PL/SQL procedure successfully completed
SESSION 1:
SQL> declare
2 event_level number;
3 begin
4 for event_number in 10000..10999 loop
5 sys.dbms_system.read_ev(event_number, event_level);
6 if (event_level > 0) then
7 sys.dbms_output.put_line('Event '||to_char(event_number)||' is set at level '||to_char(event_level));
8 end if;
9 end loop;
10 end;
11 /
Event 10046 is set at level 12
PL/SQL 過程已成功完成。
SQL> SELECT P1.VALUE||''||P2.VALUE||'_ORA_'||P.SPID FILENAME
2 FROM
3 V$PROCESS P,
4 V$SESSION S,
5 V$PARAMETER P1,
6 V$PARAMETER P2
7 WHERE P1.NAME = 'user_dump_dest'
8 AND P2.NAME = 'db_name'
9 AND P.ADDR = S.PADDR
10 AND S.AUDSID = USERENV ('SESSIONID');
FILENAME
--------------------------------------------------------------------------------
D:oracleadminorcludumporcl_ORA_3204
3204:
PARSING IN CURSOR #1 len=25 dep=0 uid=61 ct=3 lid=61 tim=6781738649 hv=1275046628 ad='64ef8a4c'
select count(*) from test
END OF STMT
PARSE #1:c=0,e=652,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=6781738640
BINDS #1:
EXEC #1:c=0,e=117,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=6781738874
WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=91,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=6781739037
WAIT #1: nam='SQL*Net message from client' ela= 233 p1=1111838976 p2=1 p3=0
FETCH #1:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=6781739404
WAIT #1: nam='SQL*Net message to client' ela= 3 p1=1111838976 p2=1 p3=0
SESSION 2:
SQL> exec dbms_system.set_ev(12,6,10046,0,'');
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET TRACEFILE_IDENTIFIER=...;
select d.value||'/'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
from (select p.spid
from v$mystat m, v$session s,v$process p
where m.statistic# = 1 and
s.sid = m.sid and
p.addr = s.paddr ) p,
( select t.instance
from v$thread t,v$parameter v
where v.name = 'thread' and
(v.value = 0 or t.thread# = to_number(v.value))) i,
( select value from v$parameter where name = 'user_dump_dest') d
/
獲得診斷事件列表：
大部分的診斷事件的數值都是在10000至10999範圍內，使用如下的指令碼可以檢視到所有的診斷事件：
SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/
在UNIX系統中，可以在$ORACLE_HOME/rdbms/mesg/oraus.msg這個檔案中找到所有的診斷事件的名稱和定義。使用如下指令碼可以輸出所有
的詳細的診斷事件的資訊：
event=10000
while [ $event -ne 10999 ]
do
event=`expr $event + 1`
oerr ora $event
done
樣本輸出檔案如下：
10001, 00000, "control file crash event1"
// *Document: NO
// *Cause:
// *Action:
10002, 00000, "control file crash event2"
// *Document: NO
// *Cause:
// *Action:
10003, 00000, "control file crash event3"
// *Document: NO
// *Cause:
// *Action:
10004, 00000, "block recovery testing - internal error"
// *Document: NO
// *Cause:
// *Action:
二、ORADEBUG
ORADEBUG
ORADEBUG is an undocumented debugging utility supplied with Oracle
For more general information see ORADEBUG introduction
In Oracle 9.2 commands include
|
HELP command
The ORADEBUG HELP command lists the commands available within ORADEBUG
These vary by release and platform. Commands appearing in this help do not necessarily work for the release/platform. on which the database is running
For example in Oracle 9.2.0.1 (Windows 2000) the command
ORADEBUG HELP
returns the following
| Command | Arguments | Description |
| HELP | [command] | Describe one or all commands |
| SETMYPID | Debug current process | |
| SETOSPID | <ospid> | Set OS pid of process to debug |
| SETORAPID | <orapid> ['force'] | Set Oracle pid of process to debug |
| DUMP | <dump_name> <lvl> [addr] | Invoke named dump |
| DUMPSGA | [bytes] | Dump fixed SGA |
| DUMPLIST | Print a list of available dumps | |
| EVENT | <text> | Set trace event in process |
| SESSION_EVENT | <text> | Set trace event in session |
| DUMPVAR | <p|s|uga> <name> [level] | Print/dump a fixed PGA/SGA/UGA variable |
| SETVAR | <p|s|uga> <name> <value> | Modify a fixed PGA/SGA/UGA variable |
| PEEK | <addr> <len> [level] | Print/Dump memory |
| POKE | <addr> <len> <value> | Modify memory |
| WAKEUP | <orapid> | Wake up Oracle process |
| SUSPEND | Suspend execution | |
| RESUME | Resume execution | |
| FLUSH | Flush pending writes to trace file | |
| CLOSE_TRACE | Close trace file | |
| TRACEFILE_NAME | Get name of trace file | |
| LKDEBUG | Invoke global enqueue service debugger | |
| NSDBX | Invoke CGS name-service debugger | |
| -G | <Inst-List | def | all> | Parallel oradebug command prefix |
| -R | <Inst-List | def | all> | Parallel oradebug prefix (return output) |
| SETINST | <instance# .. | all> | Set instance list in double quotes |
| SGATOFILE | <SGA dump dir> | Dump SGA to file; dirname in double quotes |
| DMPCOWSGA | <SGA dump dir> | Dump & map SGA as COW; dirname in double quotes |
| MAPCOWSGA | <SGA dump dir> | Map SGA as COW; dirname in double quotes |
| HANGANALYZE | [level] | Analyze system hang |
| FFBEGIN | Flash Freeze the Instance | |
| FFDEREGISTER | FF deregister instance from cluster | |
| FFTERMINST | Call exit and terminate instance | |
| FFRESUMEINST | Resume the flash frozen instance | |
| FFSTATUS | Flash freeze status of instance | |
| SKDSTTPCS | <ifname> <ofname> | Helps translate PCs to names |
| WATCH | <address> <len> <self|exist|all|target> | Watch a region of memory |
| DELETE | <local|global|target> watchpoint <id> | Delete a watchpoint |
| SHOW | <local|global|target> watchpoints | Show watchpoints |
| CORE | Dump core without crashing process | |
| UNLIMIT | Unlimit the size of the trace file | |
| PROCSTAT | Dump process statistics | |
| CALL | <func> [arg1] ... [argn] | Invoke function with arguments |
SETMYPID command
Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process
Once a process has been selected, this will be used as the ORADEBUG process until another process is selected
The SETMYPID command selects the current process as the ORADEBUG process
For example
ORADEBUG SETMYPID
ORADEBUG SETMYPID can be used to select the current process to run systemwide commands such as dumps
Do not use ORADEBUG SETMYPID if you intend to use the ORADEBUG SUSPEND command
SETORAPID command
Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process
Once a process has been selected, this will be used as the ORADEBUG process until another process is selected
The SETORAPID command selects another process using the Oracle PID as the ORADEBUG process
The syntax is
ORADEBUG SETORAPID pid
where pid is the Oracle process ID of the target process For example
ORADEBUG SETORAPID 9
The Oracle process id for a process can be found in V$PROCESS.PID
To obtain the Oracle process ID for a foreground process use
SELECT pid FROM v$process
WHERE addr =
(
SELECT paddr FROM v$session
WHERE sid = DBMS_SUPPORT.MYSID
);
Alternatively, if the DBMS_SUPPORT package is not available use
SELECT pid FROM v$process
WHERE addr =
(
SELECT paddr FROM v$session
WHERE sid =
(
SELECT sid FROM v$mystat WHERE ROWNUM = 1
)
);
To obtain the process ID for a background process e.g. SMON use
SELECT pid FROM v$process
WHERE addr =
(
SELECT paddr FROM v$bgprocess
WHERE name = 'SMON'
);
To obtain the process ID for a dispatcher process e.g. D000 use
SELECT pid FROM v$process
WHERE addr =
(
SELECT paddr FROM v$dispatcher
WHERE name = 'D000'
);
To obtain the process ID for a shared server process e.g. S000 use
SELECT pid FROM v$process
WHERE addr =
(
SELECT paddr FROM v$shared_server
WHERE name = 'S000'
);
To obtain the process ID for a job queue process e.g. job 21 use
SELECT pid FROM v$process
WHERE addr =
(
SELECT paddr FROM v$session
WHERE sid =
(
SELECT sid FROM dba_jobs_running WHERE job = 21
)
);
To obtain the process ID for a parallel execution slave e.g. P000 use
SELECT pid FROM v$px_process
WHERE server_name = 'P000';
SETOSPID command
Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process
Once a process has been selected, this will be used as the ORADEBUG process until another process is selected
The SETOSPID command selects the another process using the operating system PID as the ORADEBUG process
The syntax is
ORADEBUG SETOSPID pid
where pid is the operating system process ID of the target process For example
ORADEBUG SETOSPID 34345
The operating system process ID is the PID on Unix systems and the thread number on Windows NT/2000 systems
On Unix the PID of interest may have been identified using a top or ps command
TRACEFILE_NAME command
This command prints the name of the current trace file e.g.
ORADEBUG TRACEFILE_NAME
For example
/export/home/admin/SS92003/udump/ss92003_ora_14917.trc
This command does not work on Windows 2000 (Oracle 9.2)
UNLIMIT command
In Oracle 8.1.5 and below the maximum size of the trace file is restricted by default. This means that large dumps (LIBRARY_CACHE, BUFFERS) may fail.
To remove the limitation on the size of the trace file use
ORADEBUG UNLIMIT
In Oracle 8.1.6 and above the maximum size of the trace file defaults to UNLIMITED
FLUSH command
To flush the current contents of the trace buffer to the trace file use
ORADEBUG FLUSH
CLOSE_TRACE command
To close the current trace file use
ORADEBUG CLOSE_TRACE
SUSPEND command
This command suspends the current process
First select a process using SETORAPID or SETOSPID
Do not use SETMYPID as the current ORADEBUG process will hang and cannot be resumed even from another ORADEBUG process
For example the command
ORADEBUG SUSPEND
suspends the current process
The command
ORADEBUG RESUME
resumes the current process
While the process is suspended ORADEBUG can be used to take dumps of the current process state e.g. global area, heap, subheaps etc.
This example demonstrates how to take a heap dump during a large (sorting) query
This example requires two sessions, session 1 logged on SYS AS SYSDBA and session 2 which executes the query. In session 2 identify the PID using
SELECT pid FROM v$process
WHERE addr IN
(
SELECT paddr FROM v$session
WHERE sid = dbms_support.mysid
);
In this example the PID was 12
In session 1 set the Oracle PID using
ORADEBUG SETORAPID 12
In session 2 start the query
SELECT ... FROM t1 ORDER BY ....
In session 1 suspend session 2
ORADEBUG SUSPEND
The query in session 2 will be suspended
In session 1 run the heap dump
ORADEBUG DUMP HEAPDUMP 1
The heapdump will show the memory structures allocated for the sort. At this point further dumps e.g. subheap dumps can be taken.
In session 1 resume session 2
ORADEBUG RESUME
The query in session 2 will resume execution
RESUME command
This command resumes the current process
First select a process using SETORAPID or SETOSPID
Do not use SETMYPID as the current ORADEBUG process will hang and cannot be resumed even from another ORADEBUG process
For example the command
ORADEBUG SUSPEND
suspends the current process
The command
ORADEBUG RESUME
resumes the current process
While the process is suspended ORADEBUG can be used to take dumps of the current process state e.g. global area, heap, subheaps etc.
See SUSPEND for an example of use of the SUSPEND and RESUME commands
WAKEUP command
To wake up a process use
ORADEBUG WAKEUP pid
For example to wake up SMON, first obtain the PID using
SELECT pid FROM v$process
WHERE addr =
(
SELECT paddr FROM v$bgprocess
WHERE name = 'SMON'
);
If the PID is 6 then send a wakeup call using
ORADEBUG WAKEUP 6
DUMPLIST command
To list the dumps available in ORADEBUG use
ORADEBUG DUMPLIST pid
For example in Oracle 9.2 (Windows 2000) this command returns the following
|
DUMP command
To perform. a dump use
ORADEBUG DUMP dumpname level
For example for a level 4 dump of the library cache use
ORADEBUG SETMYPID
ORADEBUG DUMP LIBRARY_CACHE 4
EVENT command
To set an event in a process use
ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level
For example to set event 10046, level 12 in Oracle process 8 use
ORADEBUG SETORAPID 8
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
SESSION_EVENT command
To set an event in a session use
ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level
For example
ORADEBUG SESSION_EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
DUMPSGA
To dump the fixed SGA use
ORADEBUG DUMPSGA
DUMPVAR
To dump an SGA variable use
ORADEBUG DUMPVAR SGA variable_name
e.g.
ORADEBUG DUMPVAR SGA kcbnhb
which returns the number of hash buckets in the buffer cache
The names of SGA variables can be found in X$KSMFSV.KSMFSNAM. Variables in this view are suffixed with an underscore e.g.
kcbnhb_
PEEK
To peek memory locations use
ORADEBUG PEEK address length
where address can be decimal or hexadecimal and length is in bytes
For example
ORADEBUG PEEK 0x20005F0C 12
returns 12 bytes starting at location 0x20005f0c
POKE
To poke memory locations use
ORADEBUG POKE address length value
where address and value can be decimal or hexadecimal and length is in bytes
For Example
ORADEBUG POKE 0x20005F0C 4 0x46495845
ORADEBUG POKE 0x20005F10 4 0x44205349
ORADEBUG POKE 0x20005F14 2 0x5A45
WARNING Do not use the POKE command on a production system
IPC
To dump information about operating system shared memory and semaphores configuration use the command
ORADEBUG IPC
This command does not work on Windows NT or Windows 2000 (Oracle 9.2)
On Solaris, similar information can be obtained using the operating system command
ipcs -b
Dumping the SGA
In some versions it is possible to dump the entire SGA to a file
Freeze the instance using
ORADEBUG FFBEGIN
Dump the SGA to a file using
ORADEBUG SGATOFILE directory
Unfreeze the instance using
ORADEBUG FFRESUMEINST
This works in Oracle 9.0.1 and 9.2.0 on Solaris, but fails in both versions in Windows 2000
Oracle 10g第2版新特性之效能特性
檢查是否啟用了跟蹤
如果會話執行的任務和預期不符，或者執行速度比較慢，那麼大多數資料庫管理員的第一步是檢查等待事件。要構建配置檔案，您可能還需要長期跟蹤會話，那麼在 user_dump_dest 目錄中將生成一個跟蹤檔案。
現在，假設您在某段時間內對多個會話使用了端到端跟蹤，但現在不知道哪些會話處於跟蹤狀態。如何找出這些對話呢？
方法之一是對大量跟蹤檔案進行篩選，以提取 SID 和 Serial# 列並在資料庫的 V$session 檢視中進行匹配。毋庸質疑，這個過程比較複雜、困難並且容易出錯。Oracle 資料庫 10g 第 2 版中提供了一個更優秀、更簡單的方法：您所要做的只是檢視一個檢視，即 V$session。
新增了三個新列顯示跟蹤狀態：
sql_trace - 如果在會話中啟用了 SQL 跟蹤，則顯示 TRUE/FALSE
sql_trace_waits - 如果啟用了會話跟蹤，則可以讓跟蹤程式將等待資訊寫入跟蹤檔案，這對於診斷效能問題很有用。
sql_trace_binds - 如果會話使用繫結變數，則可以讓跟蹤程式將繫結變數值寫入跟蹤檔案。該列顯示 TRUE/FALSE。
當未開啟會話跟蹤時，如果選擇這些列：
select sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
from v$session
where username = 'HR'
輸出結果如下：
SID SERIAL# SQL_TRAC SQL_T SQL_T
---------- ---------- -------- ----- -----
196 60946 DISABLED FALSE FALSE
此處您可以看到，SID 為 196、Serial# 為 60946 的會話未啟用跟蹤。
現在，您可以對等待事件（而不是繫結變數）啟用跟蹤。可以使用程式包 dbms_monitor 啟用跟蹤。
begin
dbms_monitor.session_trace_enable (
session_id => 196,
serial_num => 60960,
waits => true,
binds => false
);
end;
/
現在，如果您要檢視會話資訊：
select sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
from v$session
where username = 'HR'
輸出結果如下：
SID SERIAL# SQL_TRAC SQL_T SQL_T
---------- ---------- -------- ----- -----
196 60960 ENABLED TRUE FALSE
注意，僅當使用程式包 dbms_monitor 中的過程 session_trace_enable 啟用跟蹤（而不是透過 alter session set sql_trace = true 或設定事件 10046）時，才會填充檢視 V$session。在以後的某個時間點上，如果您要查明哪些會話已經啟用了跟蹤，可以使用以上查詢執行此操作。
如果使用程式包 dbms_monitor 中的其他過程（如 SERV_MOD_ACT_TRACE_ENABLE 或 CLIENT_ID_TRACE_ENABLE）啟用了跟蹤，V$session 檢視將不顯示該資訊。相反，它們將記錄到另一個檢視 DBA_ENABLED_TRACES 中。可以將該檢視與其他相關資訊儲存連線在一起以檢視啟用了跟蹤的會話。例如，使用
SELECT *
FROM (SELECT SID, 'SESSION_TRACE' trace_type
FROM v$session
WHERE sql_trace = 'ENABLED')
UNION
(SELECT SID, t.trace_type
FROM v$session s, dba_enabled_traces t
WHERE t.trace_type = 'CLIENT_ID' AND s.client_identifier = t.primary_id)
UNION
(SELECT SID, t.trace_type
FROM v$session s, dba_enabled_traces t, v$instance i
WHERE t.trace_type = 'SERVICE'
AND s.service_name = t.primary_id
AND (t.instance_name IS NULL OR t.instance_name = i.instance_name))
UNION
(SELECT SID, t.trace_type
FROM v$session s, dba_enabled_traces t, v$instance i
WHERE t.trace_type = 'SERVICE_MODULE'
AND s.service_name = t.primary_id
AND s.module = t.qualifier_id1
AND (t.instance_name IS NULL OR t.instance_name = i.instance_name))
UNION
(SELECT SID, t.trace_type
FROM v$session s, dba_enabled_traces t, v$instance i
WHERE t.trace_type = 'SERVICE_MODULE_ACTION'
AND s.service_name = t.primary_id
AND s.module = t.qualifier_id1
AND s.action = t.qualifier_id2
AND (t.instance_name IS NULL OR t.instance_name = i.instance_name))
UNION
(SELECT SID, t.trace_type
FROM v$session s, dba_enabled_traces t, v$instance i
WHERE t.trace_type = 'DATABASE'
AND (t.instance_name IS NULL OR t.instance_name = i.instance_name))
輸出結果如下：
SID TRACE_TYPE
---------- ---------------------
136 SERVICE_MODULE
136 SERVICE_MODULE_ACTION
您可以看到，您已經對會話 136 的 Service Module 和 Service Module Action 啟用了跟蹤。但 DBA_ENABLED_TRACES 並未顯示繫結變數或等待事件。
以下是事件的有效trace級別：
Level 0 tracing被關閉。這相當於設定sql_trace=false。
Level 1 標準SQL trace資訊（SQL_TRACE=TRUE）。這是預設級別。
Level 4 SQL trace資訊加繫結變數值。
Level 8 SQL trace資訊加等待事件資訊。
Level 12 SQL trace 資訊，等待事件資訊，和繫結變數值。
兩個引數
alter session set timed_statistics = true;
alter session set max_dump_file_size = unlimited;
你能使用trace事件10046來跟蹤使用者會話或Oracle後臺程式。
會話位置:USER_DUMP_DEST
後臺位置:BACKGROUND_DUMP_DEST
跟蹤自己的會話：命令格式
enable
alter session set events '10046 trace name context forever, level 8';
disable
alter session set events '10046 trace name context off';
--如果你有安裝了的DBMS_SUPPORT包，你能使用以下的過程來開啟和關閉跟蹤：
-- To include Wait Event data with SQL trace (default option)
exec sys.dbms_support.start_trace;
-- To include Bind variable values, Wait Event data with SQL trace
exec sys.dbms_support.start_trace(waits => TRUE, binds=> TRUE)
-- Run your SQL script. or program to trace wait event information
-- To turn off the tracing:
exec sys.dbms_support.stop_trace;
如何跟蹤其他人的會話
如果你不能確信引數TIMED_STATISTICS和MAX_DUMP_FILE_SIZE是否對你想跟蹤的會話設定合適，你應該從V$SESSION得到SID和它的序列號（SERIAL#）。在開啟trace之前，你然後能做以下過程來合適得設定這些引數。
-- Set TIME_STATISTICS to TRUE for SID 1234, Serial# 56789
exec sys.dbms_system.set_bool_param_in_session( -
sid => 1234, -
serial# => 56789, -
parnam => 'TIMED_STATISTICS', -
bval => true);
-- Set MAX_DUMP_FILE_SIZE to 2147483647
-- for SID 1234, Serial# 56789
exec sys.dbms_system.set_int_param_in_session( -
sid => 1234, -
serial# => 56789, -
parnam => 'MAX_DUMP_FILE_SIZE', -
intval => 2147483647);
如果這些過程不能用於你的oracle版本（Oracle版本8.1.5和以下），你能使用ALTER SYSTEM SET 命令來設定這些引數。
接下來的步驟是在其他會話中去開啟trace，然後在你已經收集足夠的trace資訊之後關閉它。你能使用以下方法之一來處理:
方法1 使用DBMS_SUPPORT包過程
-- Enable 'level 12' trace in session 1234 with serial# 56789
exec dbms_support.start_trace_in_session( -
sid => 1234, -
serial# => 56789, -
waits => true, -
binds => true);
-- Let the session execute SQL script. or
-- program for some amount of time
-- To turn off the tracing:
exec dbms_support.stop_trace_in_session( -
sid => 1234, -
serial# => 56789);
2. 方法2 使用dbm_system
- Enable trace at level 8 for session 1234 with serial# 56789
execute dbms_system.set_ev( 1234, 56789, 10046, 8, '');
-- Let the session execute SQL script. or
-- program for some amount of time
-- To turn off the tracing:
execute dbms_system.set_ev( 1234, 56789, 10046, 0, '');
3.方法3 使用oradebug工具。你需要知道會話的OS程式ID(SPID)或Oracle程式ID(PID)。你能檢視他們在v$process檢視。假設你知道你想跟蹤的使用者名稱：
select s.username,
p.spid os_process_id,
p.pid oracle_process_id
from v$session s, v$process p
where s.paddr = p.addr
and s.username = upper('&user_name');
現在使用SQL*Plus以sysdba連線併發出以下命令：
alter system set timed_statistics=true;
oradebug setospid 12345;
--12345 是會話的OS程式id
Oradebug unlimit;
Oradebug event 10046 trace name context forever ,level 8;
--讓會話執行SQL指令碼或程式一段時間
--關閉trace
Oradebug event 10046 trace name context off;
在ORACLE10g1版本中，你能使用DBMS_MONITOR包過程來開啟基於SID，服務名，模組，或動作來跟蹤。基於動作的跟蹤使dba能跟蹤一個指定的商業功能。這是一個捕捉：過程要求DBA知道模組和動作名。
使用DBMS_MONITOR包來啟動對會話1234和serial#56789如下：
execute dbms_monitor.session_trace_enable(1234,56789,true,true);
-- Let the session execute SQL script. or
-- program for some amount of time
-- To turn off the tracing:
execute dbms_monitor.session_trace_disable(1234, 56789);
這些過程很像來自DBMS_SUPPORT包。我們推薦你在oracle10g版本1中使用DBMS_MONITOR包。
使用DBMS_MONITOR包用於服務，模組，和基於動作的跟蹤。
--開啟級別12跟蹤已知服務，模組，和動作
execute dbms_monitor.serv_mod_act_trace_enable(
'APPS1','GLEDGER','DEBIT_ENTRY',TRUE,TRUE,NULL);
--執行sql指令碼或程式一段時間
--關閉tracing
Execute dbms_monitor.serv_mod_act_trace_disable(
'APPS1','GLEDGER','DEBIT_ENTRY');
怎樣找到你的trace檔案
使用者會話的跟蹤檔案將被寫到USER_DUMP_DEST目錄，而後臺程式的跟蹤檔案將被寫到BACKGROUND_DUMP_DEST目錄。跟蹤檔案命名在許多平臺上包含.trc或TRC副檔名。
使用oradebug trace工具，找到你的跟蹤檔案是較容易的，因為專用服務的SPID數字也被寫到trace檔案。另外，你也能得到真正的跟蹤檔名如下：
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8
Statement processed.
SQL> oradebug tracefile_name
d:oracleadminor92udumpor92_ora_171.trc
從Oracle8.1.7開始，你能為你會話設定TRACEFILE_IDENTIFIER引數，使用ALTER SESSION命令，如下顯示：
alter session set tracefile_identifier='MyTrace';
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9399028/viewspace-681539/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE EVENT && ORADEBUGOracle
- oracle oradebug使用詳解Oracle
- oracle event 2 (zt)Oracle
- ORACLE _small_table_threshold與eventOracle
- ORACLE多個event設定方式Oracle
- oradebug的使用說明
- 用oradebug掛起程式
- [20180619]oradebug peek.txt
- oracle 推進scn（poke、gdb、event、bbed）方法Oracle
- oracle驗證設定的event是否生效：Oracle
- [20180625]oradebug peek 2.txt
- [轉載] Python程式——multiprocessing.Event()|Barrier（）Python
- [20210407]oradebug dump heapdump_addr.txt
- ORACLE TEXT(轉)Oracle
- ORACLE EVENTS(轉)Oracle
- Oracle Partitioning(轉)Oracle
- ORACLE DSI(轉)Oracle
- Oracle 轉MySqlOracleMySql
- [20190423]oradebug peek測試指令碼.txt指令碼
- Oracle 19c中的等待事件分類 Event WaitsOracle事件AI
- WIX是如何從CRUD轉換到Event Sourcing？
- oracle轉義字元Oracle字元
- Java初探Oracle(轉)JavaOracle
- ORACLE FREELIST HWM(轉)Oracle
- oracle列轉行Oracle
- oracle myintis 轉義Oracle
- Oracle轉換PostgresOracle
- oracle cache table(轉)Oracle
- Oracle審計(轉)Oracle
- Oracle Pipelined Table(轉)Oracle
- [20200120]oracle wait event "enq: SQ – contention" and DBA_DB_LINK_SOURCES.txtOracleAIENQ
- event_x ()、event_y ()、event_x_root ()、event_y_root ()
- Oracle實驗(02)：轉換 & 轉譯Oracle
- ORACLE系統概述(轉)Oracle
- Oracle 特殊字元轉義Oracle字元
- Oracle 查詢轉換Oracle
- ORACLE資料加密(轉)Oracle加密
- ORACLE備份策略(轉)Oracle
- Oracle Pipelined Table Functions(轉)OracleFunction