Daily Essential SQL Queries For Oracle Apps DBA

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);

SELECT
inst_id,
sid,serial#,
'''' ||sql_id || ''',',machine,module,program,action,client_identifier,seconds_in_wait,status,
osuser,machine,blocking_session,seconds_in_wait,event,status,logon_time,username,
'alter system kill session ''' || sid || ','|| serial# || ','||'@'||inst_id||''' immediate;'
FROM gV$SESSION
WHERE BLOCKING_SESSION IS NOT NULL
AND TYPE='USER'
order by event,sql_id


select NVL(s1.username, s1.osuser) || '@' || s1.machine
        || ' ( SID=' || s1.sid || ' )  is Blocking '
        || NVL(s2.username, s2.osuser) || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status,s1.program,s1.event
from   gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where  s1.sid=l1.sid and s2.sid=l2.sid
and   l1.BLOCK=1 and l2.request > 0
and   l1.id1 = l2.id1
and   l2.id2 = l2.id2;

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 1:

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 check Running concurrent requests 2

select vs.inst_id,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                                             ---
      ,nvl(a.os_process_id,b.os_process_id) "OS"
      ,vs.sid
      ,vp.spid
      ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
      ,to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE
,to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
,f.user_name
      ,c.concurrent_program_name||' - '||
       c2.user_concurrent_program_name "program",vs.client_identifier,vs.module,vs.action,vs.event
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
    ,gv$session vs
    ,gv$process vp
    ,apps.fnd_user f
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 (+)
  and vs.inst_id = vp.inst_id
  and a.requested_by=f.user_id
  and c2.ZD_EDITION_NAME='SET1'
  and c.ZD_EDITION_NAME='SET1'
  and q.ZD_EDITION_NAME='SET1'
order by c.concurrent_program_name
/

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;

SQL Planner for SQL ID

select SNAP_ID,a.instance_number inst_id,
(select to_char(end_interval_time,'dd-MON-yyyy hh24:mi') from dba_hist_snapshot s where s.snap_id=a.snap_id and s.dbid=a.dbid and a.instance_number=s.instance_number) snaptime,
SQL_ID,EXECUTIONS_DELTA EXECUTIONS,PLAN_HASH_VALUE hash_value,ELAPSED_TIME_DELTA,
case when EXECUTIONS_DELTA>0 then round((ELAPSED_TIME_DELTA/1000)/EXECUTIONS_DELTA,2)  else 0 end "Avg Elapsed Time(s)"
from dba_hist_sqlstat a
where SQL_ID='&sql_id'
and EXECUTIONS_DELTA>0
order by SNAP_ID;

Standby Database sync with Current SCN

select scn_to_timestamp(current_scn) from v$database

Workflow Setup Details

select p.parameter_id, p.parameter_name, v.parameter_value, v.parameter_description,
v.default_parameter_value
from apps.fnd_svc_comp_param_vals_v v, apps.fnd_svc_comp_params_b p, apps.fnd_svc_components c
where --c.component_type = 'APPS_AUTH_AGENT'
 v.component_id = c.component_id
and v.parameter_id = p.parameter_id
order by p.parameter_name;

Find Current EDITION version of the database

select
    instance_name INSTANCE_NAME
  , decode(sys_context('userenv', 'current_schema'), 'SYSTEM', '*ALL*', sys_context('userenv', 'current_schema')) SCHEMA_NAME
  , sys_context('userenv', 'current_edition_name') EDITION_NAME
  , to_char(sysdate, 'YYYY-MM-DD HH24:MI') "SYSDATE"
from gv$instance

Find Workflow mailer log files

select fl.meaning,fcp.process_status_code,
 decode(fcq.concurrent_queue_name,'WFMLRSVC','maile r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name),
 fcp.concurrent_process_id,os_process_id, fcp.logfile_name
 from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
 where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
 and fl.lookup_type='CP_PROCESS_STATUS_CODE' and
 fl.lookup_code=fcp.process_status_code
 and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')
 order by fcp.logfile_name;

SELECT fcp.logfile_name
 FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup
 WHERE concurrent_queue_name in ('WFMLRSVC')
 AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
 AND fcq.application_id = fcp.queue_application_id
 AND flkup.lookup_code=fcp.process_status_code
 AND lookup_type ='CP_PROCESS_STATUS_CODE'
 AND meaning='Active';

Check the Workflow mailer service status


select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name, DECODE(fcp.OS_PROCESS_ID,NULL,'Not
Running',fcp.OS_PROCESS_ID) PROCID,
fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL,
fcq.ENABLED_FLAG ENABLED,
fsc.COMPONENT_NAME,
fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES
fcp, apps.fnd_svc_components fsc
where fcq.MANAGER_TYPE = fcs.SERVICE_ID
and fcs.SERVICE_HANDLE = 'FNDCPGSC'
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+)
and fcp.process_status_code(+) = 'A'
order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE;

Check the Profile changed recently for DEBUG Profile

select po.profile_option_name "NAME",
 po.USER_PROFILE_OPTION_NAME,
 decode(to_char(pov.level_id),
 '10001', 'SITE',
 '10002', 'APP',
 '10003', 'RESP',
 '10005', 'SERVER',
 '10006', 'ORG',
 '10004', 'USER', '???') "LEV",
 decode(to_char(pov.level_id),
 '10001', '',
 '10002', app.application_short_name,
 '10003', rsp.responsibility_key,
 '10005', svr.node_name,
 '10006', org.name,
 '10004', usr.user_name,
 '???') "CONTEXT",
 pov.profile_option_value "VALUE"
 from FND_PROFILE_OPTIONS_VL po,
 FND_PROFILE_OPTION_VALUES pov,
 fnd_user usr,
 fnd_application app,
 fnd_responsibility rsp,
 fnd_nodes svr,
 hr_operating_units org
 where (po.profile_option_name like '%AFLOG_ENABLED%' or po.profile_option_name like
 '%FND_INIT_SQL%')
 and pov.application_id = po.application_id
 and pov.profile_option_id = po.profile_option_id
 and usr.user_id (+) = pov.level_value
 and rsp.application_id (+) = pov.level_value_application_id
 and rsp.responsibility_id (+) = pov.level_value
 and app.application_id (+) = pov.level_value
 and svr.node_id (+) = pov.level_value
 and org.organization_id (+) = pov.level_value
 order by "NAME", pov.level_id, "VALUE";

SQL TEXT for concurrent Request

SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER, D.PROCESS, C.SPID,
 E.SQL_TEXT,d.sql_id
 FROM APPS.FND_CONCURRENT_REQUESTS A,
 APPS.FND_CONCURRENT_PROCESSES B,
 gV$PROCESS C,
 gV$SESSION D,
 gV$SQL E
 WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID
 AND C.PID = B.ORACLE_PROCESS_ID
 AND B.SESSION_ID = D.AUDSID
 AND D.SQL_ADDRESS = E.ADDRESS
 AND A.REQUEST_ID='&concurrent_request_id'