Trace Event 10046—The Extended SQL Trace
前面说的,要跟踪某个session的运行状况,找到其瓶颈,最好的方式就是定期的获取wait event的数据,将这些数据写入trace文件是一个很好的选择。我们可以设置session级的参数SQL_TRACE=TRUE,也可以使用trace event 10046获取更多的信息,这是SQL trace的扩展。
Trace level:
l Level 0 Tracing is disabled. This is the same as setting SQL_TRACE = FALSE.
l Level 1 Standard SQL trace information (SQL_TRACE = TRUE). This is the default level.
l Level 4 SQL trace information plus bind variable values.
l Level 8 SQL trace information plus wait event information.
l Level 12 SQL trace information, wait event information, and bind variable values.
启动trace event 10046
INSTANCE级:更改init.ora,添加EVENT参数后重启,不建议!
# This enables the trace event 10046 at level 8 for the instance.
# Restart the instance after this change is made to the init.ora file.
EVENT = "10046 trace name context forever, level 8"
推荐使用Session级的trace event 10046
Trace your own session
方法1
alter session set timed_statistics = true;
alter session set max_dump_file_size = unlimited;
-- To enable the trace event 10046 in Oracle 7.3 onwards
alter session set events ‘10046 trace name context forever, level 8’;
-- Run your SQL script. or program to trace wait event information
-- To turn off the tracing:
alter session set events ‘10046 trace name context off’;
方法2
$sqlplus ‘/ as sysdba’
SQL> start $ORACLE_HOME/rdbms/admin/dbmssupp.sql
Package created.
Package body created.
SQL> grant execute on DBMS_SUPPORT to username;
Grant succeeded.
SQL> connect username/pwd
SQL> -- To include Wait Event data with SQL trace (default option)
SQL> exec sys.dbms_support.start_trace;
PL/SQL procedure successfully completed.
SQL> -- To include Bind variable values, Wait Event data with SQL trace
SQL> exec sys.dbms_support.start_trace(waits => TRUE, binds=> TRUE)
PL/SQL procedure successfully completed.
SQL> -- Run your SQL script. or program to trace wait event information
SQL> -- To turn off the tracing:
SQL> exec sys.dbms_support.stop_trace;
PL/SQL procedure successfully completed.
Trace Someone Else’s Session
先调整参数
-- 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);
l 方法1:Use the DBMS_SUPPORT package procedures:
-- 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);
l 方法2:Use the DBMS_SYSTEM package procedure(oracle不建议此用法)
-- Enable trace at level 8 for session 1234 with serial# 56789
exec 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:
exec dbms_system.set_ev( 1234, 56789, 10046, 0, ‘’);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/55472/viewspace-425560/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/55472/viewspace-425560/