Tracing a single concurrent request EBS 11i r12

Tracing a single concurrent request

What do you do when you need to trace a concurrent request for troubleshooting or performance tuning purposes? Here we take a look at some most obvious methods and one which I found the most convenient in many cases.
There are some well-known methods I used to use most often:

1. The easiest one – “catch” the DB session while it’s executing and enable trace manually with DBMS_SESSION.START_TRACE_IN_SESSION, DBMS_MONITOR.SESSION_TRACE_ENABLE or other command you like best;
2. Enable tracing for the concurrent program using “Enable Trace” option inSystem Administrator responsibility -> Concurrent -> Program -> Define form.

However, there are some drawbacks to these methods: You may not be quick enough to enable the tracing manually or you won’t see the statements that were executed just after start of concurrent request as tracing was not yet enabled at the moment. Or, you will not get the information of waitevents and binds in the trace file if you use “Enable trace” option.
How to avoid these drawbacks? There’s a profile option “SQL Statement – Custom”, set it to a value: “BEGIN FND_CTL.FND_SESS_CTL(”,”, ”, ‘TRUE’,”,’ALTER SESSION SET TRACEFILE_IDENTIFIER=’||””||’USERID’ ||””||’ EVENTS =’||””||’ 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 ‘||””); END;” and you’ll get a trace file with all the waitevents and binds for all the user sessions. The statement can be adjusted to your needs, but remember – the profile option value field is just 240 characters long. But here’s another problem – all the user sessions are traced including self service, forms, scheduled CRs and submitted CR’s. You may end up with hundreds of trace files struggling to find the needed one.
As I later found out, there is a nice profile option “Concurrent: Allow Debugging“, which allows enabling of debugging/tracing levels just for a particular concurrent request upon submission. You can set it to “Yes” and instruct the user to submit the request once again and use the button “Debug Options”…


you’ll get a single trace file with a name {sid}_ora_{process_id}_{eBS_User}_CR{request_id}.trc in your DB udump directory.

� m t ��/ � P e session id for this concurrent request
accept v_sid
prompt memory usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# AND C.NAME like'%memor%' and a.sid='&v_sid';

prompt resource usage for this session
SELECT A.SID,A.USERNAME,B.VALUE,c.name FROM V$SESSION a,V$SESSTAT B,V$STATNAME C WHERE A.SID=B.SID
AND B.STATISTIC#=C.STATISTIC# and a.sid='&v_sid' order by b.value;


prompt this session waited on
select sid,event,wait_time,state from v$session_wait where sid='&v_sid' order by wait_time;


prompt current sql executing by this session
select a.sid,b.sorts,b.executions,b.loads,b.parse_calls,b.disk_reads,
b.buffer_gets,b.rows_processed,C.sql_text from v$session a,v$sqlarea b,V$SQLTEXT C
where a.sql_address=b.address and b.address=c.address and a.sid='&v_sid';


prompt sql which is taking more than 3mb in shared pool
prompt nosql should take morethan 1mb in shared pool.
prompt please ask the developers to tune the following sql statements
select name,
namespace,type,sharable_mem/(1024*1024) sharablemem,loads,executions,locks,pins,kept from v$db_object_cache
where SHARABLE_MEM>3000000;


prompt sort segments using by this session
SELECT s.username,s.sid,s.osuser,s.process,s.machine,u.extents, u.blocks,u.tablespace FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr order by extents;
and s.sid='&v_sid';

prompt current temp segments free in this instance
SELECT tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size FROM v$sort_segment;



prompt total system events at this time
select event,total_waits waits, total_timeouts timeouts, time_waited total_time from v$system_event order by total_waits;

prompt latch contention if thery is any
SELECT latch#, name, gets, misses, sleeps FROM v$latch WHERE sleeps>0 ORDER BY sleeps ;


prompt the latch which is sleeping
select name, sleeps,latch# from v$latch_children where sleeps>4 order by sleeps;


spool off
clear columns
clear breaks