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('¶meter_name',parameter_name)
and v.instance_number = a.instance_number
order by 1,2
/
======================================
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
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('¶meter_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
