How to Monitor JDBC and JVM in eBusiness Suite

How to Monitor JDBC and JVM in eBusiness Suite

Use Oracle Support Note for complete script:
monitor_jdbc_conn.sql - Script to monitor JDBC connections in Apps eBusiness Suite (Doc ID 557194.1)

JDBC Connection Usage Per JVM Process:
select machine, process, count(*) from gv$session
where program like '%JDBC%'
group by machine, process
order by 1 asc;

Connection Usage Per Module
select count(*), module
from gv$session
where program like '%JDBC%'
group by module
order by 1 asc;

Idle connections for more than 3 hours:
select count(*),machine, program
from gv$session
where program like '%JDBC%'
and  last_call_et > 3600 *3
group by machine, program;

Inactive connections which last ran fnd_security_pkg.fnd_encrypted_pwd:
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%';