继续浏览精彩内容
慕课网APP
程序员的梦工厂
打开
继续
感谢您的支持,我会继续努力的
赞赏金额会直接到老师账户
将二维码发送给自己后长按识别
微信支付
支付宝支付

ORACLE TRACE 10046事件常见的几种方法

PIPIONE
关注TA
已关注
手记 1117
粉丝 149
获赞 703


    下面是10046事件的几个等级分别表示什么意思

Level 0 Tracing is disabled. This is the same as setting SQL_TRACE = FALSE. 

 

Level 1 Standard SQL trace information (SQL_TRACE = TRUE). This is the default level. 

 

Level 4 SQL trace information plus bind variable values. 

 

Level 8 SQL trace information plus wait event information. 

 

Level 12 SQL trace information, wait event information, and bind variable values. 

   下面是常见的几种方法

一、trace当前进程

 

 

1,event方法 

alter session set timed_statistics = true; 

alter session set max_dump_file_size = unlimited; 

alter session set events '10046 trace name context forever, level 8'; 

alter session set events '10046 trace name context off';     

 

2,dbms_support方法 

alter session set timed_statistics = true; 

alter session set max_dump_file_size = unlimited; 

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) 

exec sys.dbms_support.stop_trace; 

 

3,oradebug 方法 

alter system set timed_statistics = true; 

SQL> oradebug setmypid 

Statement processed. 

SQL> oradebug unlimit; 

Statement processed. 

SQL> oradebug event 10046 trace name context forever,level 8 

Statement processed. 

二,trace其它进程

  在trace其它进程前,先修改下面两个参数的值 -- Set TIME_STATISTICS to TRUE for SID 10, Serial# 118 

exec sys.dbms_system.set_bool_param_in_session(- 

sid => 10, - 

serial# => 118, - 

parnam => 'TIMED_STATISTICS',- 

bval => true); 

-- Set MAX_DUMP_FILE_SIZE to 2147483647 

-- for SID 10, Serial# 118 

exec sys.dbms_system.set_int_param_in_session(- 

sid => 10,- 

serial# => 118,- 

parnam => 'MAX_DUMP_FILE_SIZE',- 

intval => 2147483647); 

 

 

1,使用dbms_support方法 

 

-- Enable ‘level 12’ trace in session 10 with serial# 118 

exec dbms_support.start_trace_in_session(- 

sid => 10,- 

serial => 118,- 

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 => 10, - 

serial => 118); 

 

2,使用DBMS_SYSTEM  

-- Enable trace at level 8 for session 10 with serial# 118 

exec dbms_system.set_ev( 10, 118, 10046, 8, '');   

-- Let the session execute SQL script or-- program for some amount of time  

-- To turn off the tracing: 

exec dbms_system.set_ev( 10, 118, 10046, 0, ''); 

 

3,使用oradebug工具 

在使用oradebug工具前,我们得先查找到sess在操作系统上面的进程号,可以使用下面的SQL来查询 

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.sid=&sid; 

 

查看进程号后,使用下面的命令 

alter system set timed_statistics = true; 

oradebug setospid 6509;   

-- 6509 is the OS process id for the session 

oradebug unlimit; 

oradebug event 10046 trace name context forever, level 8; 

-- Let the session execute SQL script  

-- or program for some amount of time  

-- To turn off the tracing: 

oradebug event 10046 trace name context off; 

4,使用dbms_monitor 

exec dbms_monitor.session_trace_enable( -      

session_id => 10,-      

serial_num => 118, -      

waits => true, -      

binds => true); 

-- Let the session execute SQL script or -- program for some amount of time  

-- To turn off the tracing: 

exec dbms_monitor.session_trace_disable( -      

session_id => 10, -      

serial_num => 118);  

 

©著作权归作者所有:来自51CTO博客作者7343696的原创作品,如需转载,请注明出处,否则将追究法律责任

traceoradebugORACLE 基础


打开App,阅读手记
0人推荐
发表评论
随时随地看视频慕课网APP