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

ainila

下面是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);  
  

0

微机原理闹危机，随机过程随机过，实变函数学十遍，汇编语言不会编！

jmton

与时俱进，你我共赴高潮!

samdung

俺从不写措字，但俺写通假字!

wanmin444

不在课堂上沉睡，就在酒桌上埋醉。

云中漫步

脱了衣服我是禽兽，穿上衣服我是衣冠禽兽!

wsjz_01

生活***好玩，因为生活老***玩我!