select decode(wfd.state, 0, '0 = Ready', 1, '1 = Delayed', 2, '2 = Retained', 3, '3 = Exception', to_char(substr(wfd.state,1,12))) State, count(*) count
from applsys.wf_deferred wfd group by wfd.state;
PROMPT <
select msg_state, count(*) from applsys.aq$wf_notification_out where msg_state in ('WAITING','READY', 'PROCESSED') group by msg_state;
PROMPT <
select corrid,decode(state,0, '0 = Ready',1, '1 = Delayed',2, '2 = Retained',3, '3 = Exception',to_char(state)) State,count(*) COUNT
from wf_deferred_table_m group by corrid, state;
Stuck thread
select s.inst_id node, s.sid,s.serial# , s.process, p.spid ,s.client_identifier username,
decode(substr(s.program,1,6),'JDBC T','JDBC','frmweb','FORM',s.program) program, substr(s.machine,1,10) machine,
s.module,trunc(s.last_call_et/60/60)||' hr '|| mod(round(s.last_call_et/60),
60)||' Min' lastCal,s.blocking_session blocker ,status
from gv$session s ,
gv$process p
where s.inst_id = p.inst_id and p.addr = s.paddr
and s.action like '%&Resp_name%'
and s.process='&Java_PID'
and s.machine='&machine'
and s.program like 'JDBC Thin Client'
order by 10 desc;
*********************************************************************************************
pid of frmweb
select s.inst_id node, s.sid,s.serial# , s.process, p.spid , s.action, s.machine, s.module, trunc(s.last_call_et/60/60)||' hr '|| mod(round(s.last_call_et/60), 60)||' Min' lastCal,
status from gv$session s ,gv$process p where s.inst_id = p.inst_id and p.addr = s.paddr and s.process = '&PID' order by 4,1;
***********************************************************************************************
prompt JDBC Connection Usage Per JVM Process
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select machine, process, count(*) from gv$session where program like '%JDBC%' group by machine, process order by 1 asc
prompt
prompt Connection Usage Per Module
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*), module from gv$session where program like '%JDBC%' group by module order by 1 asc
prompt
prompt Connection Usage Per process and module
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*), machine, process, module from gv$session where program like '%JDBC%' group by machine, process, module order by 1 asc
prompt
prompt Idle connections for more than 3 hours
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select count(*),machine, program from gv$session where program like '%JDBC%' and last_call_et > 3600 *3 group by machine, program
prompt
prompt Active connections which are taking more than 10 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select * from gv$session where program like '%JDBC%' and last_call_et > 600 and status = 'ACTIVE' order by last_call_et asc
prompt
prompt Statements from JDBC connections taking more than 10 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.process, s.sid, t.sql_text from gv$session s, gv$sql t where s.sql_address =t.address and s.sql_hash_value =t.hash_value and s.program like '%JDBC%'
and s.last_call_et > 600 and s.status = 'ACTIVE'
/
prompt
prompt Active connections which are taking more than 20 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select * from gv$session where program like '%JDBC%' and last_call_et > 1200 and status = 'ACTIVE' order by last_call_et asc
/
prompt
prompt Statements from JDBC connections taking more than 20 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.process, s.sid, t.sql_text from gv$session s, gv$sql t where s.sql_address =t.address and s.sql_hash_value =t.hash_value
and s.program like '%JDBC%' and s.last_call_et > 1200 and s.status = 'ACTIVE'
/
prompt
prompt Active connections which are taking more than 30 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select * from gv$session where program like '%JDBC%' and last_call_et > 1800 and status = 'ACTIVE' order by last_call_et asc
/
prompt
prompt Statements from JDBC connections taking more than 30 min to run
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.process, s.sid, t.sql_text from gv$session s, gv$sql t where s.sql_address =t.address and s.sql_hash_value =t.hash_value
and s.program like '%JDBC%' and s.last_call_et > 1800 and s.status = 'ACTIVE'
/
prompt
prompt Inactive connections which last ran fnd_security_pkg.fnd_encrypted_pwd
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select s.sql_hash_value, t.sql_text, s.last_call_et from gv$session s , gv$sqltext t where s.username = 'APPLSYSPUB'
and s.sql_hash_value= t.hash_value and t.sql_text like '%fnd_security_pkg.fnd_encrypted_pwd(:1,:2,:3%';
prompt =======================================================
************************************************************************************************************
sess_by_java_pid.sql
select s.inst_id node, s.sid,s.serial# , s.process, p.spid ,s.client_identifier username,
decode(substr(s.program,1,6),'JDBC T','JDBC','frmweb','FORM',s.program) program, substr(s.machine,1,10) machine, s.module,trunc(s.last_call_et/60/60)||' hr '|| mod(round(s.last_call_et/60),
60)||' Min' lastCal,s.blocking_session blocker ,status
from gv$session s ,
gv$process p
where s.inst_id = p.inst_id and p.addr = s.paddr
and s.process='&java_pid'
and s.program like 'JDBC Thin Client'
and s.last_call_et > 1800
order by 10 desc;
*************************************************************************************************
Framework Bundle Code level
SELECT codelevel,
decode((codelevel),
'C.7', '12.2.7 Framework Code Stack',
'C.6', '12.2.6 Framework Code Stack',
'C.5', '12.2.5 Framework Code Stack',
'C.4', '12.2.4 Framework Code Stack',
'B.5', '12.1.3 Framework Code Stack',
'B.4', '11.5.10 Framework Code Stack') stack
FROM AD_TRACKABLE_ENTITIES
WHERE abbreviation='fwk';
