How to enable sql session tracing in oracle database

Enabling session tracing in oracle database

1. Identify sql client session to be traced

For example you are planning to trace the session for module ABC , identify the SPID for session e.g. 13762780

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set line 1000
col OSUSER for a10
col SID for 999
col USERNAME for a20
col MACHINE for a20
set pagesize 100
SELECT distinct
b.spid, sq.sql_id,,
a.LAST_CALL_ET/60 "Inac_Min",a.logon_time,c.event, sq.sql_text
v$session a,
v$process b,
v$session_wait c,
v$sqlarea sq
a.username IS NOT NULL
and a.username='GENEVA_ADMIN' and a.sql_hash_value=sq.hash_value
and (upper(a.module) like '%ABC%')
and c.sid=a.sid;

2. Enable trace

For example if spid 13762780 has to be traced

oradebug setospid 13762780

oradebug unlimit
oradebug Event 10046 trace name context forever, level 12
oradebug tracefile_name

3. disable trace

Incase session does not get disconnect the session tracing keep on filing trace file which may lead to panic utilization of file system

oradebug setospid 13762780

oradebug Event 10046 trace name context off

4. Analyzing trace file using tkprof

tkprof trace analyzer will extract the trace file generated to showcase execcution plan of each query executed in session

tkprof testdb_ora_13762780.trc tk_testdb_ora_13762780.out explain=test_mis/test_mis123 waits=yes sort=exeela

5. More methods of tracing

Following command can be executed to enable trace for current session tracing

alter session set tracefile_identifier='abc_trace';

alter session set sql_trace=TRUE;

6. More methods of enabling bulk tracing and analyzing trace with condition

In case you need to enable tracing on complete database , below trigger can be created which will trace each session connected on DB till trigger is in enable state

Note: If trigger is in invalid state then it will decline new connection to DB leading to outage

CREATE OR REPLACE TRIGGER trace AFTER logon ON database begin execute immediate 'Alter session set tracefile_identifier=''ABC_traces''';
execute immediate 'Alter session set events ''10046 trace name context forever, level 12'''; end;

One trace files are generated in bulk due to trigger you may use below code to search for ABC@TESTLAB002 in trace and generate tkprof for same

for i in `ls -lrt | grep -i ABC_traces.trc | awk '{ print $9 }'`
#echo $i
if [ `cat $i | grep -i ABC@TESTLAB002 | wc -l | awk '{ print $1 }'` -gt 0 ] ; then
echo $i
tkprof $i $i.out explain=test_mis/test_mis123 waits=yes sort=exeela

No comments:

Post a Comment