ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sql跟踪

sql跟踪

原创 Linux操作系统 作者:syzxlyx_cu 时间:2009-09-28 09:52:05 0 删除 编辑

一、三方法:
1、初始化参数sql_trace=true
2、dbms_session.set_sql_trace
3、dbms_system.set_sql_trace_in_session
这三种方法只能到level  1;
二、任意级别
1、alter session set
2、dbms_system .set_env
ALTER SESSION SET events '10046 trace name context forever, level 12';
ALTER SESSION SET events '10046 trace name context off';

dbms_system.set_ev(si => 127, -- session id
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)----disable trace


三、通过dbms_support包可授权其他人跟踪sql
CONNECT / as sysdba
@?/rdbms/admin/dbmssupp.sql
CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;
GRANT EXECUTE ON dbms_support TO dba;

10g里不用它,但装了后可以启用pl/sql警告
dbms_support.start_trace_in_session(sid => 127,
serial => 29,
waits => TRUE,
binds => FALSE)
The following PL/SQL call disables SQL trace for the session identified by ID 127 and serial
number 29. Both parameters have no default values.
dbms_support.stop_trace_in_session(sid => 127,
serial => 29);

10g里用dbms_monitor
dbms_monitor.session_trace_enable(session_id => 127,
serial_num => 29,
waits => TRUE,
binds => FALSE);

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
dbms_monitor.session_trace_disable(session_id => 127,
serial_num => 29);

Note that if Real Application Clusters is used, the procedures session_trace_enable and
session_trace_disable have to be executed on the instance where the session resides.
these procedures
can be used only if the session attribute client identifier is set.
The following PL/SQL call enables SQL trace at level 8 for all sessions having the client
identifier specified as a parameter. While the parameter client_id has no default value, the
parameter waits defaults to TRUE, and the parameter binds defaults to FALSE. Be careful,
because the parameter client_id is case sensitive.
dbms_monitor.client_id_trace_enable(client_id => 'helicon.antognini.ch',
waits => TRUE,
binds => FALSE)
The view dba_enabled_traces displays which client identifier SQL trace has been enabled
for, and which parameters were used to enable it, through the procedure
client_id_trace_enable. For example, after enabling SQL trace with the previous PL/SQL call,
the following information is given:
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
The following PL/SQL call disables SQL trace for all sessions having the client identifier
specified as a parameter. The parameter client_id has no default value.
dbms_monitor.client_id_trace_disable(client_id => 'helicon.antognini.ch');
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);
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
The following PL/SQL call disables SQL trace for all sessions using the session attributes
specified as parameters. All parameters have the same default values and behavior. as for the
procedure serv_mod_act_trace_enable.
dbms_monitor.serv_mod_act_trace_disable(service_name => 'DBM10203.antognini.ch',
module_name => 'mymodule',
action_name => 'myaction',
instance_name => NULL);

dbms_monitor.database_trace_enable(waits => TRUE,
binds => TRUE,
instance_name => NULL)
As for SQL trace at the client and component levels, the view dba_enabled_traces displays
which instance SQL trace has been enabled for, and which parameters it has been enabled
with, through the procedure database_trace_enable.
SQL> SELECT instance_name, waits, binds
2 FROM dba_enabled_traces
3 WHERE trace_type = 'DATABASE';
INSTANCE_NAME WAITS BINDS
---------------- ----- -----
TRUE TRUE
The following PL/SQL call disables SQL trace for a database. If the parameter
instance_name is set to NULL, which is also the default value, SQL trace is disabled for all instances.
dbms_monitor.database_trace_disable(instance_name => NULL)

CREATE ROLE sql_trace;
CREATE OR REPLACE TRIGGER enable_sql_trace AFTER LOGON ON DATABASE
BEGIN
IF (dbms_session.is_role_enabled('SQL_TRACE'))
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited';
dbms_monitor.session_trace_enable;
END IF;
END;
/


ALTER SESSION SET max_dump_file_size = unlimited;


You can use the command-line tool TRCSESS, which is available as of Oracle Database 10g,
to extract part of the information contained in one or more trace files, based on the logical
sections described earlier. To get a complete list of its arguments, run it without arguments.
trcsess [output=] [session=] [clientid=]
[service=] [action=] [module=]

output= output destination default being standard output.
session= session to be traced.
Session id is a combination of session Index & session serial number e.g. 8.13.
clientid= clientid to be traced.
service= service to be traced.
action= action to be traced.
module= module to be traced.
Space separated list of trace files with wild card '*' supported.
As you can see, it is possible to specify a session, a client ID, a service name, a module
name, and an action name as an argument. For example, to extract the information about
Action 12 from the trace file dbm10203_ora_24433.trc and write the output in a new trace file
named action12.trc, you can use the following command:
trcsess utput=action12.trc action="Action 12" dbm10203_ora_24433.trc

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20542911/viewspace-615686/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-09-28

  • 博文量
    125
  • 访问量
    124080