Daily Essential Queries

Daily Essential SQL Queries For Oracle Apps DBA

Query to check Session wait :

If you observe any performance issues on database, long running concurrent requests etc. then first check for any events like "enq: TX - row lock contention","enq: TM Lock Contention",
"library cache pin" etc. by using below query and identify the session id (SID)

set lines 120
set pages 1000
col event for a30
select sid, event, p1, p2, p3, p1raw from v$session_wait
where event not like '%messag%' and event not in ('pipe get','PL/SQL lock timer','Streams AQ: qmn slave idle wait','Streams AQ: waiting for time management or cleanup tasks','Streams AQ: qmn coordinator idle wait')
and (wait_time=0 or state='WAITING')
order by 2;

Query to Check Locks on Database:

If you see an "enq: TX - row lock contention","enq: TM Lock Contention" then identify the locking session details with the help of below queries-

To Identify holding session -

select * from dba_blockers;

select sid,serial#,process,status,last_call_et,action from v$session where sid in (select * from dba_blockers);

To identify holder and waiting session id -

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


select /*+ rule */ 'SID '||a.sid||' is blocking the sessions '||b.sid from v$lock a, v$lock b where a.block=1 and b.request >0;

Query to check status of SID:

col status for a10;
col osuser for a15;
col program for a18;
col username for a15
col machine for a20
select sid,serial#,status,machine,osuser,program,username from v$session where sid=&sid
/

Query to get sql_text for given SID:

select sql_text
from v$sqltext, v$session
where address=sql_address
and   hash_value = sql_hash_value
and sid=&sid
order by piece
/

Query to get SPID from SID:

select spid,sid from v$process,v$session
 where paddr=addr
and sid=&sid
/

Query to get SID from SPID:

select spid,sid from v$process,v$session
 where paddr=addr
and spid=&spid
/

Query to get SID for the running Concurrent request ID:

SELECT a.request_id, c.spid,s.sid,s.serial#,s.inst_id
  FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
 WHERE a.request_id in ('1102852','1102853','1102854','1102847') -- Enter Request ID's here
   AND s.paddr = c.addr
   AND a.oracle_process_id = c.spid
   AND a.phase_code = UPPER ('R');

Query to get Concurrent RequestID from SID:

SELECT a.request_id, c.spid,s.sid,s.serial#,s.inst_id
  FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
 WHERE s.sid in ('&sid') -- Enter SID here
   AND s.paddr = c.addr
   AND a.oracle_process_id = c.spid
   AND a.phase_code = UPPER ('R');

Query to check library cache pin locks sessions:

connect /as sysdba

SELECT
  /*+ ordered */
  w1.sid waiting_session,
  h1.sid holding_session,
  w.kgllktype lock_or_pin,
  w.kgllkhdl address,
  DECODE(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held,
  DECODE(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested,
  DECODE(w1.state,'WAITING',w1.WAIT_TIME_MICRO,'Null') wait_time_in_sec
FROM dba_kgllock w,
  dba_kgllock h,
  gv$session w1,
  gv$session h1
WHERE (((h.kgllkmod != 0)
AND (h.kgllkmod     != 1)
AND ((h.kgllkreq     = 0)
OR (h.kgllkreq       = 1)))
AND (((w.kgllkmod    = 0)
OR (w.kgllkmod       = 1))
AND ((w.kgllkreq    != 0)
AND (w.kgllkreq     != 1))))
AND w.kgllktype      = h.kgllktype
AND w.kgllkhdl       = h.kgllkhdl
AND w.kgllkuse       = w1.saddr
AND h.kgllkuse       = h1.saddr
AND w1.state = 'WAITING'
AND w1.WAIT_TIME_MICRO/1000000 > 30;

Query to check Running concurrent requests:

set pages 1000
set lines 132 
col os form A7 head AppProc
col spid form a6 head DBProc
col program form A20 trunc
set pages 38
col time form 9999999.999 head Elapsed
col "Req Id" form 99999999
col "Parent" form a8
col "Prg Id" form 9999999
col qname head "Concurrent Manager Queue" format a27 trunc
col sid format 99999 head SID
set recsep off
select q.concurrent_queue_name || ' - ' || target_node qname 
      ,a.request_id "Req Id" 
      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
      ,a.concurrent_program_id "Prg Id"
      ,a.phase_code,a.status_code
      ,b.os_process_id "OS"
      ,vs.sid
      ,vp.spid
      ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
      ,c.concurrent_program_name||' - '||
       c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,v$session vs
    ,v$process vp
where a.controlling_manager = b.concurrent_process_id
  and a.concurrent_program_id = c.concurrent_program_id
  and a.program_application_id = c.application_id
  and c2.concurrent_program_id = c.concurrent_program_id
  and a.phase_code in ('I','P','R','T')
  and b.queue_application_id = q.application_id
  and b.concurrent_queue_id = q.concurrent_queue_id
  and c2.language = 'US'
  and vs.process (+) = b.os_process_id
  and vs.paddr = vp.addr (+)
order by 1,2;

Query to Identify which session is currently using TEMP Segments more than 1GB:

Please change the TEMP tablespace name as per your environment in below query.

SELECT a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
     (select block_size from dba_tablespaces where tablespace_name='TEMP2') d
    WHERE b.tablespace = 'TEMP2'
    and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    AND (b.blocks*d.block_size)/1048576 > 1024
    ORDER BY b.tablespace, 5 desc;

Query to find runtime and history for a concurrent program:

SELECT distinct fcp.concurrent_program_name || ': ' || fcpt.user_concurrent_program_name "Conc Program Name",
fcr.REQUEST_ID "Request ID",
to_char(fcr.ACTUAL_START_DATE,'dd-mm-yy hh24:mi:ss') "Started at",
to_char(fcr.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') "Completed at",
decode(fcr.PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') "Phasecode",
decode(fcr.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',
'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",fcr.argument_text "Parameters",
--substr(fu.description,1,25) "Who submitted",
--ROUND ((fcrsv.actual_completion_date - fcrsv.actual_start_date) * 1440,
--              2
 --            ) "Runtime (in Minutes)" 
round(((nvl(fcrsv.actual_completion_date,sysdate)-fcrsv.actual_start_date)*24*60),2) "ElapsedTime(Mins)"
FROM
apps.fnd_concurrent_requests fcr ,
apps.fnd_concurrent_programs fcp ,
apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_user fu, apps.fnd_conc_req_summary_v fcrsv
WHERE 
fcr.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
AND fcr.actual_start_date >= (sysdate-1) -- change the value of number of days hostory as required
--AND fcr.requested_by=22378
AND   fcr.PROGRAM_APPLICATION_ID = fcp.APPLICATION_ID
AND fcpt.concurrent_program_id=fcr.concurrent_program_id
AND fcr.REQUESTED_BY=fu.user_id
AND fcrsv.request_id=fcr.request_id
--AND fcr.request_id ='2260046' in ('13829387','13850423')
and fcpt.user_concurrent_program_name like '%XX%' -- Enter user concurrnent program name
order by to_char(fcr.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') desc;

Query to Determine Which Manager Ran a Specific Concurrent Request:

col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid'; --Enter request ID here

Query to find History of concurrent requests which are error out: 

SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2 -- Change this value as required
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;