下面是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);
转载于:https://blog.51cto.com/luoping/1097825