1.获取跟踪文件路径:
oracle数据库获取trace文件方法:
10g
1.show parameter background_dump_dest
2.show parameter background_dump_dest
11
1.show parameter diag
2.select value from v$diag_info where name = 'Default Trace File';
10g和11g 通用
1.select name, value from v$parameter where name in ('user_dump_dest','background_dump_dest');
获取当前trace文件的脚本:
select c.value || '/' || d.instance_name || '_ora_' ||
a.spid || '.trc' ||
case when e.value is not null then '_'||e.value end trace
from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
and e.name = 'tracefile_identifier'
2.如何生成跟踪文件
2.1 跟踪级别:
玩转跟踪不了解跟踪级别是不行的,下面介绍Oracle可采用的跟踪接口:
Level 0 = No statistics generated
Level 1 = standard trace output including parsing, executes and fetches plus more.
Level 2 = Same as level 1.
Level 4 = Same as level 1 but includes bind information
Level 8 = Same as level 1 but includes wait's information
Level 12 = Same as level 1 but includes binds and waits
2.2 跟踪当前会话:
alter session set sql_trace=true;
执行相关sql
oracle数据库获取trace文件方法:
10g
1.show parameter background_dump_dest
2.show parameter background_dump_dest
11
1.show parameter diag
2.select value from v$diag_info where name = 'Default Trace File';
10g和11g 通用
1.select name, value from v$parameter where name in ('user_dump_dest','background_dump_dest');
获取当前trace文件的脚本:
select c.value || '/' || d.instance_name || '_ora_' ||
a.spid || '.trc' ||
case when e.value is not null then '_'||e.value end trace
from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
and e.name = 'tracefile_identifier'
2.如何生成跟踪文件
2.1 跟踪级别:
玩转跟踪不了解跟踪级别是不行的,下面介绍Oracle可采用的跟踪接口:
Level 0 = No statistics generated
Level 1 = standard trace output including parsing, executes and fetches plus more.
Level 2 = Same as level 1.
Level 4 = Same as level 1 but includes bind information
Level 8 = Same as level 1 but includes wait's information
Level 12 = Same as level 1 but includes binds and waits
2.2 跟踪当前会话:
alter session set sql_trace=true;
执行相关sql
alter session set sql_trace=false;
alter session set events '10046 trace name context forever,level 1';
执行相关sql
alter session set events '10046 trace name context off';
2.3 获取个人的sid,serail#
select sid, serial# from v$session where sid = (select sid from v$mystat where rownum=1);
获取系统级别的会话:
select s.sid,s.serial#,s.username,s.osuser
from v$session s,v$process p
where s.paddr=p.addr;
3.使用oracledebug 跟踪会话
3.1 使用ORADEBUG跟踪当前会话
oradebug只能跟踪SYS用户的当前SESSION,如下方式:
sys@MAA> oradebug setmypid
sys@MAA> oradebug unlimit
sys@MAA> oradebug event 10046 trace name context forever, level 12
sys@MAA> exec our code
sys@MAA> oradebug event 10046 trace name context off
sys@MAA> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_17641.trc
3.2 使用ORADEBUG跟踪其他会话
通过oradebug非常方便地跟踪其他会话,如下方式:
luocs@MAA> select pid, spid from v$process p, v$session s where p.addr = s.paddr and s.sid=(select sid from v$mystat where rownum=1);
PID SPID
---------- ------------------------------------------------
25 17678
指定跟踪SESSION的SPID(OS process)
sys@MAA> oradebug setospid 17678
Oracle pid: 25, Unix process pid: 17678, image: oracle@maa3.luocs.com (TNS V1-V3)
或者指定跟踪SESSION的PID(Oracle process ID)
sys@MAA> oradebug setorapid 25
Oracle pid: 25, Unix process pid: 17678, image: oracle@maa3.luocs.com (TNS V1-V3)
sys@MAA> oradebug unlimit
sys@MAA> oradebug event 10053 trace name context forever, level 1
sys@MAA> exec our code
sys@MAA> oradebug event 10053 trace name context off
sys@MAA> oradebug tracefile_name
4.使用autotrace 跟踪当前会话:
@MAA> set autotrace on
@MAA> set autotrace on explain
@MAA> set autotrace on statistics
@MAA> set autotrace traceonly
@MAA> set autotrace traceonly explain
@MAA> set autotrace traceonly explain statistics
@MAA> set autotrace off
我喜欢使用缩略方式,比如
@MAA> set autot trace exp stat
5.使用10053跟踪会话:
MAA> alter session set events '10053 trace name context forever, level 1';
MAA> alter session set events '10053 trace name context off';
MAA> oradebug event 10053 trace name context forever, level 1
MAA> oradebug event 10053 trace name context off
alter session set events '10046 trace name context forever,level 1';
执行相关sql
alter session set events '10046 trace name context off';
2.3 获取个人的sid,serail#
select sid, serial# from v$session where sid = (select sid from v$mystat where rownum=1);
获取系统级别的会话:
select s.sid,s.serial#,s.username,s.osuser
from v$session s,v$process p
where s.paddr=p.addr;
3.使用oracledebug 跟踪会话
3.1 使用ORADEBUG跟踪当前会话
oradebug只能跟踪SYS用户的当前SESSION,如下方式:
sys@MAA> oradebug setmypid
sys@MAA> oradebug unlimit
sys@MAA> oradebug event 10046 trace name context forever, level 12
sys@MAA> exec our code
sys@MAA> oradebug event 10046 trace name context off
sys@MAA> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/maa/maa/trace/maa_ora_17641.trc
3.2 使用ORADEBUG跟踪其他会话
通过oradebug非常方便地跟踪其他会话,如下方式:
luocs@MAA> select pid, spid from v$process p, v$session s where p.addr = s.paddr and s.sid=(select sid from v$mystat where rownum=1);
PID SPID
---------- ------------------------------------------------
25 17678
指定跟踪SESSION的SPID(OS process)
sys@MAA> oradebug setospid 17678
Oracle pid: 25, Unix process pid: 17678, image: oracle@maa3.luocs.com (TNS V1-V3)
或者指定跟踪SESSION的PID(Oracle process ID)
sys@MAA> oradebug setorapid 25
Oracle pid: 25, Unix process pid: 17678, image: oracle@maa3.luocs.com (TNS V1-V3)
sys@MAA> oradebug unlimit
sys@MAA> oradebug event 10053 trace name context forever, level 1
sys@MAA> exec our code
sys@MAA> oradebug event 10053 trace name context off
sys@MAA> oradebug tracefile_name
4.使用autotrace 跟踪当前会话:
@MAA> set autotrace on
@MAA> set autotrace on explain
@MAA> set autotrace on statistics
@MAA> set autotrace traceonly
@MAA> set autotrace traceonly explain
@MAA> set autotrace traceonly explain statistics
@MAA> set autotrace off
我喜欢使用缩略方式,比如
@MAA> set autot trace exp stat
5.使用10053跟踪会话:
MAA> alter session set events '10053 trace name context forever, level 1';
MAA> alter session set events '10053 trace name context off';
MAA> oradebug event 10053 trace name context forever, level 1
MAA> oradebug event 10053 trace name context off
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25462274/viewspace-2120620/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25462274/viewspace-2120620/