Sunday, March 23, 2008

scripts to monitor jdbc connections in Apps 11i and R12

Print this post

 

Just cut and past the sql block below and save in a file named monitor_jdbc_conn.sql

To run the script, just execute:

sqlplus apps/apps
@monitor_jdbc_conn.sql

 
 

Rem
Rem monitor_jdbc_conn.sql
Rem
Rem    NAME
Rem      monitor_jdbc_conn.sql
Rem
Rem    DESCRIPTION
Rem      This shows JDBC connection utilization on database by machine, process
Rem      and module.
Rem
Rem    NOTES
Rem      Runs as apps or apps read only user
Rem
 

--
-- Set header information for all columns used
-- 
 
set lines 120
set pages 500
column module  heading "Module Name"  format a48;
column machine heading "Machine Name" format a25;
column process heading "Process ID"   format a10;
column inst_id heading "Instance ID"   format 99;
column inst_id heading "Program"   format a15;
column username for a10
column sid for 9999
column sql_text for a50
  
 

prompt =======================================================
prompt JDBC Connections
 
select to_char(sysdate, 'dd-mon-yyyy hh24:mi') Time from dual
/
prompt =======================================================
 

prompt
prompt JDBC Connection Usage Per JVM Process
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
select count(*), process from v$session
where program like '%JDBC%'
group by process
order by 1 asc
/
 

prompt
prompt Connection Usage Per Module
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
select count(*), module
from v$session
where program like '%JDBC%'
group by module
order by 1 asc
/
 
prompt
prompt Connection Usage Per process and module 
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
select count(*), process, module
from v$session
where program like '%JDBC%'
group by  process, module 
order by 1 asc
/
 
prompt
prompt Idle connections for more than 3 hours 
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
select count(*),machine, program 
from v$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 v$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 v$session s, v$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 v$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 v$session s, v$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 v$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 v$session s, v$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 v$session s , v$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 =======================================================

No comments: