Terry Oracle (notes) session tracking (a): SQL_TRACE& EVENT 10046

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
SQL_TRACE/The 10046 event is provided by Oracle for SQL tracking method, the database problem diagnosis and solve daily is a very popular. But the trace file generated by the tkprof tools to generate a need for human reading format can be.
Usage of TKPROF (Terry) Oracle please refer to notes session tracking (two): TKPROF
Session tracking can track the session, other session and the entire database, but following the IO CPU database, the database caused great load, is not common, so there was never discussed examples of tracking the entire database and.
A, SQL_TRACE
In order to make the SQL_TRACE more obvious effect:
The initialization parameter TIMED_STATISTICS is set to True, otherwise some important information is not collected, the default is true.
Size limit set for the parameter setting of MAX_DUMP_FILE_SIZE trace files, starting from 9i, the default parameter values for the unlimited.
The 1 track of the current session
Start SQL_TRACE:
alter session set sql_trace=true;
Then you can execute SQL, the process has been chasing.
Close SQL_TRACE:
alter session set sql_trace=false;
2 track session, using the dbms_system.set_sql_trace_in_session package
The first query the current system of conversation:
select sid,serial#,username from v$session;
Start SQL_TRACE:
exec dbms_system.set_sql_trace_in_session(sid,serial#,true)
For a period of time, if it is determined that a function or module problems, can be conscious during the call, to ensure that can capture the problem of code.
Close SQL_TRACE:
exec dbms_system.set_sql_trace_in_session(sid,serial#,false)
Two, 10046 events
The 10046 events are internal events provided by Oracle, is to increase SQL_TRACE, set the following 4 levels can be:
Level 1: enabled the standard SQL_TRACE function, equivalent to SQL_TRACE.
Level 4: is equivalent to Level 1 + binding values(bind values).
Level 8: is equivalent to Level 1 + wait event tracking.
Level 12: is equivalent to Level 1 + Level 4 + Level 8.
Similar to SQL_TRACE, 10046 events in the global settings, also can be set at the session level.
The 1 track of the current session
Start 10046:
alter session set events '10046 trace name context forever';
alter session set events '10046 trace name context forever, level 8';
Shut down 10046:
alter session set events '10046 trace name context off';

2 track session, using the dbms_system.set_ev package, package options are(sid,serial#,event,level,username)
The first query the current system of conversation: select sid,serial#,sid from v$session;
Start 10046:
exec dbms_system.set_ev(sid,serial#,10046,8,'username');
Shut down 10046:
exec dbms_system.set_ev(sid,serial#,10046,0,'username'); 

Three, acquisition tracking file location (in fact, in user_dump_dest, in LS -ltr.):
SID_ora_PID.trc the trace file naming
SELECT d.value ||'/' ||Lower(Rtrim(i.INSTANCE, Chr(0))) ||'_ora_' ||p.spid ||'.trc' trace_file_name FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.statistic# = 1 AND s.sid = m.sid AND p.addr = s.paddr) p, (SELECT t.INSTANCE FROM v$thread t, v$parameter v WHERE v.name = 'thread' AND ( v.value = 0 OR t.thread# = To_number(v.value) )) i, (SELECT value FROM v$parameter WHERE name = 'user_dump_dest') d;

In several ways, track four:
The 1 track, this need not say what, in order to check some special SQL, you can use this approach.
2 track others session more effective, but how to locate the problem session?
1)top, It is easy to see the current use of resources more connections as well as PID (win.)
2)Through a PID lookup to the SID, serial#
select s.sid,s.serial# from v$session s,v$process p where s.paddr=p.addr and p.spid='14483';
3)Through the SID, serial# trace, and then the output tkprof file can be
3 in the environment when not used (overtime..) Only a few, session connection into the database (that is, only a few v$session connection), then click the foreground application specific module for trace.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Ellie at December 01, 2013 - 11:44 AM