sql trace instruction

SQL trace is a kind of tools to trace the SQL statement, which is very powerful diagnostic tool.
Generally speaking ,there three steps
1 set up the range of trace for example trace the whole database or one session , and then use the relevent command to enable trace.
2 after a specified period of time , stop the trace
3 find out the trace file , and then format it, at last read and analyze it

The detail process
1 enable "SQL Trace"

A: enable the sql trace in the range of the whole database
Method : configure out parameter file , set sql_trace=true
Result: it results in tracing all the processes, including all the background processes and user processes
Note: This somehow leads some serious performance problem, so be carefully

B: enable the sql trace at the current session level
Method: sql> alter session set sql_trace=true;
stop tracing : alter session set sql_trace=false;
Normally ,we use the sql trace at the session level. after setting sql_trace=true, the system gonna start to trace the sql statement

C: trace another session
Method: through v$session , we need to know another session's sid and serial#
sql>select sid, serial#,username from v$session where username='****';
start tracing
sql>exec dbms_system.set_SQL_TRACE_in_session(9,437,true) ( 9 sid,437serial#)
stop tracing
sql>exec dbms_system.set_SQL_TRACE_in_session(9,437,false)

2 find , format, and read trace file
A: find
the trace file is located at position which the parameter "user_dump_desk" point to

1. if you wanna try to find the current session trace file , run this sql statement

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

2. if you wanna find another session trace file , try to find out the session' s sid and serial#, and then run

SELECT d.value'/'lower(rtrim(i.instance, chr(0)))'_ora_'p.spid'.trc' trace_file_name
from
( select p.spid from v$session s, v$process p
where s.sid=144 and s. SERIAL#=27 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,

B format

oringinal file is not readable , you have to use the "tkprof" tool to translate

SQL>$tkprof D:\ORACLE\PRODUCT\10.2.0\ADMIN\MYORACLE\UDUMP\hsjf_ora_1026.trc D:\ORACLE\PRODUCT\10.2.0\ADMIN\MYORACLE\UDUMP\hsjf_ora_1026.txt