Oracle Wait Interface学习笔记(2) OWI Componets (5)

(OWI学习笔记索引)

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/