--首先介绍下获得当前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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9399028/viewspace-681539/
8847
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
