Concurrent tracing

******************CM_to_SID_PID** Concurrent Managers***************************************************************

http://linuxappsdba.blogspot.com/search/label/Concurrent%20Manager

 CONCSUB apps/apps SYSADMIN 'System Administrator' SYSADMIN CONCURRENT FND DEACTIVATE or ABORT

select v.request_id,s.sid, s.serial#, s.event, p.spid   from apps.fnd_conc_requests_form_v v,
v$process p,   v$session s   where v.request_id = '896967' and v.oracle_process_id = p.spid(+)  and p.addr = s.paddr(+)

select oracle_process_id from apps.fnd_concurrent_Requests where request_id in ('3112884','3111837','3110635','3111142');

select REQUEST_ID,OS_PROCESS_ID from fnd_concurrent_requests where OS_PROCESS_ID=3087;

 select sql_hash_value from gv$session where sid=4181
 select sql_fulltext,rows_processed from gv$sql where sql_id='3hbxr52kk5u42';

 select SQL_TEXT from v$sqlarea where HASH_VALUE=3295793396;

 select used_urec from v$transaction where addr = (select taddr from v$session where sid = 3899);

 select SECONDS_IN_WAIT from v$session where sql_hash_value=2405550420

 select last_analyzed from dba_tables where table_name='MSC_ST_SALES_ORDERS'

***If the Concurrent Request is there at the front end, but there is no process running at the O/S level, then run the following query

select a.CONCURRENT_REQUEST_ID ,b.NODE_NAME,b.LOGFILE_NAME from fnd_conc_pp_actions a,fnd_concurrent_processes b
where a.PROCESSOR_ID=b.CONCURRENT_PROCESS_ID and a.ACTION_TYPE=6 and CONCURRENT_REQUEST_ID=3922386;

select r.request_id, s.sid, s.serial#, g.concurrent_program_name
from applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_queues_tl qt,
applsys.fnd_concurrent_queues q,
applsys.fnd_concurrent_processes p,
applsys.fnd_concurrent_programs g,
gv$session s
where r.controlling_manager=p.concurrent_process_id
and q.application_id=p.queue_application_id
and q.concurrent_queue_id=p.concurrent_queue_id
and qt.application_id=q.application_id
and qt.concurrent_queue_id=q.concurrent_queue_id
and r.phase_code='R'
and qt.language in ('US')
and p.session_id=s.audsid
and g.concurrent_program_id=r.concurrent_program_id
and r.request_id in(6114233);

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1, request ;

select p.spid, s.inst_id,s.sql_hash_value from gv$session s,gv$process p where s.paddr=p.addr and s.sid=3435;

select used_urec from gv$transaction where addr = (select taddr from gv$session where sid = 3435);

select p.spid, s.inst_id from gv$session s,gv$process p where s.paddr=p.addr and s.sid=4065;
select inst_id,sql_id from gv$session where sid=&sid;
*************Tunning************
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
select * from table(dbms_xplan.display_awr('&sqlid'));

ALTER SESSION SET sql_trace = TRUE;
EXECUTE SYS.dbms_system.set_sql_trace_in_session (1694, 47200, TRUE);

update fnd_concurrent_requests
set status_code = 'X', phase_code='C'
where request_id =5655240;

select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,s.program,
s.status,logon_time,last_call_et from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct where
oracle_process_id=p.spid and s.paddr=p.addr and ct.concurrent_program_id=c.concurrent_program_id and request_id=&creq_id
/

OPP log/out files

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
  FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
   AND fcpp.action_type = 6
   AND fcpp.concurrent_request_id = &request_id;

---- give this query...
---- it will ask u the req id...
---- give the req id...
---- it will give the details about what it is doing ....
---- take the sid from that output.....
---- and run the following query

select sql_text from v$sqlarea where hash_value = (select sql_hash_value from v$session where sid =&sid)
and provide the sid....