Useful EBS R12 Queries for Apps DBA

Below are some useful EBS R12 Queries for Apps DBA. We can modify these queries as per our requirement.

To find SQL ID, SQL_TEXT from Request ID / Others
=========================================
col oracle_process_id format a5 head OSPID
col inst_name format a10
col sql_text format a30
col outfile_tmp format a30
col logfile_tmp format a30
select /*+ ordered */
fcr.request_id,
fcp.user_concurrent_program_name
,      round(24*60*( sysdate - actual_start_date )) elapsed
,      fcr.oracle_process_id
,      sess.sid
,      sess.serial#
,      inst.inst_name
,      sa.SQL_ID
from   apps.fnd_concurrent_requests fcr
,      apps.fnd_concurrent_programs_tl fcp
,      apps.fnd_concurrent_processes cp
,      apps.fnd_user fu
,      gv$process pro
,      gv$session sess
,      gv$sqlarea sa
,      sys.v_$active_instances inst
where  fcp.concurrent_program_id = fcr.concurrent_program_id
and    fcp.application_id = fcr.program_application_id
and    fcr.controlling_manager = cp.concurrent_process_id
and    fcr.requested_by = fu.user_id (+)
and    fcr.oracle_process_id = pro.spid (+)
and    pro.addr = sess.paddr (+)
and    sess.sql_address = sa.address (+)
and    sess.sql_hash_value = sa.hash_value (+)
and    sess.inst_id = inst.inst_number (+)
and    request_id in (select request_id from fnd_amp_requests_v)
and sa.SQL_ID='2xzwjprnn80x3'
;

To Kill Any Inactive Session in RAC Database
========================================
select 'alter system kill session ''' ||c.sid||','||c.serial#||''||',@' || inst_id || '''' || ' immediate; ' from gv$session c
where program like 'frmweb%' and module like '%frm%' and seconds_in_wait > 18000 and c.status='INACTIVE' ;


Find Concurrent Request from sql_id from AWR Report
============================================
select c.request_id, status_code, phase_code, USER_CONCURRENT_PROGRAM_NAME,d.user_name requestor, s.sid,p.spid,s.process,s.osuser
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct, apps.fnd_user d
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id
and c.requested_by = d.user_id
and s.sid in (select sid from gv$session where sql_id='2xzwjprnn80x3');

Kill Inactive Forms Sessions
======================
set pagesize 1200;
set linesize 1200;
select 'kill -9 ' || p.spid from v$session s, v$process p where s.paddr = p.addr and s.sid in (select sid from v$session where status like 'INACTIVE' and logon_time < sysdate-0.33 and action like 'FRM:%');

Find details on any specific Inactive Program / Action 
===================================================
select distinct b.sid,b.serial# ,b.status,b.program,b.username,b.action,b.module,
to_char( b.logon_time, 'dd-MON-yyyy hh24:mi:ss' ) logon_time,
trunc( sysdate-b.logon_time ) "Dy",
trunc( mod( (sysdate-b.logon_time)*24, 24 ) ) "Hr",
trunc( mod( (sysdate-b.logon_time)*24*60, 60 ) ) "Mi",
trunc( mod( (sysdate-b.logon_time)*24*60*60, 60 ) ) "Sec"
from gV$access a,gv$session b, gv$process c
where a.sid=b.sid
and b.paddr=c.addr
and b.status='INACTIVE'
and (b.action like '%FRM%' or b.action like '%frm%' or b.program like '%TOAD%' or b.program like '%toad%' or b.program like
'SQL%' or b.program like '%sql%' or b.program like '%FRM%'
or b.program like '%frm%' or b.action like 'SQL%' or b.action like 'sql%' or b.action like 'TOAD%' or b.action like 'toad%')
and (trunc( mod( (sysdate-b.logon_time)*24,24)) >=12 or trunc( sysdate-b.logon_time )>=1);


Find Top CPU Consuming Inactive / Active Sessions
============================================
SELECT s.SID, s.serial#, p.spid AS "OS PID",s.username, s.status, s.module, st.VALUE/100 AS "CPU sec"
FROM gv$sesstat st, gv$statname sn, gv$session s, gv$process p
WHERE sn.NAME = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.SID = s.SID
AND s.paddr = p.addr
AND s.last_call_et > 18000
--and s.status='INACTIVE' and rownum < 25
ORDER BY st.VALUE DESC ;

To check pfile/spfile parameters Change History
======================================
set linesize 155
col time for a20
col parameter_name format a50
col value for a20
col snap_id for 9999999
break on instance skip 3
select a.snap_id,to_char(end_interval_time,'DD-MON-YY HH24:MI:SS') TIME, parameter_name, value
from dba_hist_parameter a, dba_Hist_snapshot b, v$instance v
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and parameter_name like nvl('&parameter_name',parameter_name)
and v.instance_number = a.instance_number
order by 1,2
/

To check all hidden parameters

======================================
set lines 200
col "Parameter" for a60
col "Session_Value" for a30
col "Instance_Value" for a30
SELECT a.ksppinm "Parameter",
       b.ksppstvl "Session_Value",
       c.ksppstvl "Instance_Value"
FROM   x$ksppi a,
       x$ksppcv b,
       x$ksppsv c
WHERE  a.indx = b.indx
AND    a.indx = c.indx
AND    a.ksppinm LIKE '/_%' escape '/'; 

10046 Trace with Binds & Waits for concurrent request
=============================================
1) select oracle_process_id from fnd_concurrent_requests where request_id='&req_id';
2) select inst_id,pid,addr from gv$process where spid='&oracle_process_id'; (provide spid from 1st query)
3) select sid,serial#,paddr,sql_id from gv$session where paddr='&addr' and inst_id='&inst_id'; (provide addr from 2nd query)  ## add other columns, if you would like to
4) select sql_fulltext from v$sql where sql_id='&sql_id' and inst_id='&inst_id';  ## add other columns, if you would like to

==== OR =====

3) oradebug setorapid <pid> (Provide pid – from 2nd query)
4) oradebug unlimit
5) oradebug event 10046 trace name context forever,level 12
6) oradebug tracefile_name (It will show the trace file location)
7) oradebug event 10046 trace name context off; (Execute this command once Concurrent request is completed)

Repeat above steps for as many concurrent requests.

==> tkprof <tracefile_name> <outfile_name> explain=apps/<pwd> sort=exeela,fchela sys=no