Friday, July 21, 2017

Enable trace with bind and wait for EBS user sessions, concurrent request

Enable trace with bind and wait for EBS user session, concurrent request.


Set this  profile option from user level for which you want to sql trace
Profile Option Name : Initialization SQL Statement - Custom
Level : User

Profile Option Value:
BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'TEST_RUN' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;


You can change the level value for below options.
Level Value
0 - No trace.
2 - Normal Sql Trace
4 - Trace with bind
8 - Trace with wait
12 - Trace with bind and wait

SQL to find trace file name for running concurrent request ( if trace enabled )

SELECT DISTINCT user_concurrent_queue_name "Manager",
                phase_code,
                request_id,
                s.inst_id,
                s.sid,
                s.serial#,
                s.status,
                s.sql_id,
                event,
                r.os_process_id,
                NVL (s.process, r.os_process_id) PROCESS,
                NVL (machine, logfile_node_name) MACHINE,
                ROUND (last_call_et / 60, 2) LAST_CALL_ET,
                gp.pid Process_id,
                gp.spid Server_PID,
                gp.tracefile TRACEFILE
  FROM gv$session s,
       gv$process gp,
       fnd_concurrent_requests r,
       fnd_concurrent_processes fcp,
       fnd_concurrent_queues_tl fcq
 WHERE     s.process(+) = r.os_process_id
       AND s.PADDR = gp.addr
       AND s.inst_id = gp.inst_id
       AND request_id = &req_id
       AND r.controlling_manager = fcp.concurrent_process_id
       AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
       AND phase_code = 'R';

   

No comments:

Post a Comment

Command to do active duplicate for Oracle Database

1. First login to target server 2. Validate tns connectivity between Source DB and Target DB 3. Prepare and validate space availability 4. S...