Oracle中trace的几种
标签：杂谈 |
我们在Oracle中在做troubleshooting的时候，经常要去做跟踪来查错，那今天就介绍几种trace的方法。
在这之前，我先说说10046事件，那么大家可能都对这个事件比较熟悉了，实际上，我们做SQL TRACE的时候，Oracle内部就是使用这个事件来完成的，他包含几个级别的。如下表(trouble shooting Oracle Performance by apress 2006).
Levels of the Debugging Event 10046
Level Description
0 The debugging event is disabled.
1 The debugging event is enabled. For each processed database call, the following
information is given: SQL statement, response time, service time, number of
processed rows, number of logical reads, number of physical reads and writes,
execution plan, and little additional information.
4 As in level 1, with additional information about bind variables. Mainly, the data
type, its precision, and the value used for each execution.
8 As in level 1, plus detailed information about wait time. For each wait experienced
during the processing, the following information is given: the name of the wait
event, the duration, and a few additional parameters identifying the resource that
has been waited for.
12 Simultaneously level 4 and level 8.
Level Description
0 The debugging event is disabled.
1 The debugging event is enabled. For each processed database call, the following
information is given: SQL statement, response time, service time, number of
processed rows, number of logical reads, number of physical reads and writes,
execution plan, and little additional information.
4 As in level 1, with additional information about bind variables. Mainly, the data
type, its precision, and the value used for each execution.
8 As in level 1, plus detailed information about wait time. For each wait experienced
during the processing, the following information is given: the name of the wait
event, the duration, and a few additional parameters identifying the resource that
has been waited for.
12 Simultaneously level 4 and level 8.
其实这个10046就是我们Oracle定义的debug事件，Oracle还有很多其他的事件，可以到$ORACLE_HOME/rdbms/mesg/oraus.msg去看所有的事件。
那么了解了这个以后呢，我们去看看使用sql trace 的几种方式。
在10g以前的Oracle版本中，我们使用以下三种方式，可以做SQL TRACE，初始化参数SQL_TRACE，dbms_session.set_sql_trace，dbms_system.set_sql_trace_in_session。
那么在做trace之前，我们可以通过ALTER SESSION SET events '10046 trace name context forever, level 12'打开sql trace，或者通过ALTER SESSION SET events '10046 trace name context off'关闭它，注意不是设置成level 0。或者通过
dbms_system.set_ev(si => 127, -- session id
se => 29, -- serial number
ev => 10046, -- event number
le => 12, -- level
nm => NULL)
se => 29, -- serial number
ev => 10046, -- event number
le => 12, -- level
nm => NULL)
若要关闭使用
dbms_system.set_ev(si => 127, -- session id
se => 29, -- serial number
ev => 10046, -- event number
le => 0, -- level
nm => NULL)
se => 29, -- serial number
ev => 10046, -- event number
le => 0, -- level
nm => NULL)
如果你想知道你有哪些会话连接到数据库，就可以通过我们的v$session视图来查询
SELECT sid, serial#, username, machine
FROM v$session
WHERE type != 'BACKGROUND'
FROM v$session
WHERE type != 'BACKGROUND'
但是，在使用DBMS_SYSTEM这个包时请注意，这个包只有SYS用户有执行权限，如果其他人确实有做SQL TRACE的必要，可以将此权限赋予它，但是注意一定要谨慎，因为set_ev这个procedure还可以设置其他的事件，还有一种Oracle官方文档没有的办法，就是使用dbms_suppot包，这个包默认是不安装的，使用方法如下：
CONNECT / as sysdba
@?/rdbms/admin/dbmssupp.sql
CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;
GRANT EXECUTE ON dbms_support TO dba;
@?/rdbms/admin/dbmssupp.sql
CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;
GRANT EXECUTE ON dbms_support TO dba;
然后调用
dbms_support.start_trace_in_session(sid => 127,
serial => 29,
waits => TRUE,
binds => FALSE)
serial => 29,
waits => TRUE,
binds => FALSE)
停止sql trace
dbms_support.stop_trace_in_session(sid => 127,
serial => 29)
serial => 29)
以上是以前Oracle推荐使用的几种方法，现在在Oracle10g中，引入了一个dbms_monitor的包，这个包的可以让我们很方便的来进行trace或停止。
dbms_monitor.session_trace_enable(session_id => 127,
serial_num => 29,
waits => TRUE,
binds => FALSE)
serial_num => 29,
waits => TRUE,
binds => FALSE)
然后，我们可以通过在v$session里新加的sql_trace, sql_trace_waits, and sql_trace_binds三列就可以看到了。
SQL> SELECT sql_trace, sql_trace_waits, sql_trace_binds
2 FROM v$session
3 WHERE sid = 127;
SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
--------------- --------------- ---------------
ENABLED TRUE FALSE
2 FROM v$session
3 WHERE sid = 127;
SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
--------------- --------------- ---------------
ENABLED TRUE FALSE
若要停止trace，
dbms_monitor.session_trace_disable(session_id => 127,
serial_num => 29)
serial_num => 29)
这就是在10g中在会话级别使用的trace，除此之外，还可以在客户端级别设置trace，他可以记录处某一个客户端所做的事情。
dbms_monitor.client_id_trace_enable(client_id => 'helicon.antognini.ch',
waits => TRUE,
binds => FALSE)
waits => TRUE,
binds => FALSE)
然后可以查询到
SQL> SELECT primary_id AS client_id, waits, binds
2 FROM dba_enabled_traces
3 WHERE trace_type = 'CLIENT_ID';
CLIENT_ID WAITS BINDS
--------------------- ----- -----
helicon.antognini.ch TRUE FALSE
2 FROM dba_enabled_traces
3 WHERE trace_type = 'CLIENT_ID';
CLIENT_ID WAITS BINDS
--------------------- ----- -----
helicon.antognini.ch TRUE FALSE
停止trace：
dbms_monitor.client_id_trace_disable(client_id => 'helicon.antognini.ch')；
同样道理，在我们的模块级别，也可以打开和关闭trace
dbms_monitor.serv_mod_act_trace_enable(service_name => 'DBM10203.antognini.ch',
module_name => 'mymodule',
action_name => 'myaction',
waits => TRUE,
binds => FALSE,
instance_name => NULL)；
module_name => 'mymodule',
action_name => 'myaction',
waits => TRUE,
binds => FALSE,
instance_name => NULL)；
dbms_monitor.serv_mod_act_trace_disable(service_name => 'DBM10203.antognini.ch',
module_name => 'mymodule',
action_name => 'myaction',
instance_name => NULL)；
module_name => 'mymodule',
action_name => 'myaction',
instance_name => NULL)；
查询状态
SQL> SELECT primary_id AS service_name, qualifier_id1 AS module_name,
2 qualifier_id2 AS action_name, waits, binds
3 FROM dba_enabled_traces
4 WHERE trace_type = 'SERVICE_MODULE_ACTION';
SERVICE_NAME MODULE_NAME ACTION_NAME WAITS BINDS
---------------------- ------------ ------------ ----- -----
DBM10203.antognini.ch mymodule myaction TRUE FALSE
2 qualifier_id2 AS action_name, waits, binds
3 FROM dba_enabled_traces
4 WHERE trace_type = 'SERVICE_MODULE_ACTION';
SERVICE_NAME MODULE_NAME ACTION_NAME WAITS BINDS
---------------------- ------------ ------------ ----- -----
DBM10203.antognini.ch mymodule myaction TRUE FALSE
在数据库层：
dbms_monitor.database_trace_enable(waits => TRUE,
binds => TRUE,
instance_name => NULL)；
binds => TRUE,
instance_name => NULL)；
dbms_monitor.database_trace_disable(instance_name => NULL)；
SQL> SELECT instance_name, waits, binds
2 FROM dba_enabled_traces
3 WHERE trace_type = 'DATABASE';
INSTANCE_NAME WAITS BINDS
---------------- ----- -----
TRUE TRUE
2 FROM dba_enabled_traces
3 WHERE trace_type = 'DATABASE';
INSTANCE_NAME WAITS BINDS
---------------- ----- -----
TRUE TRUE
这些就是我们SQL trace中一般的方法，希望大家多多指教。