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:
Post a Comment