DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, April 24, 2012

LATCH Free Sessions Monitoring and Troubleshooting




LATCH COUNT ON DATABASE:
set pagesize 5000
set lines 180
set long 5000
col username for a15
col osuser for a15
col program for a20
col "LOGON_TIME" for a23
col "LAST_CALL_HRS" for 99999.999
col status for a8
col machine for a15
col SQL_TEXT for a90
col P1TEXT for a10
col P2TEXT for a10
col P3TEXT for a10
col p1 for 9999999999999
col p2 for 9999999999999
col p3 for 9999999999999
col event for a50
col "LATCH_NAME" for a20

select event,count(event) "LATCH_COUNT" from v$session_wait having count(event)> 2 and event like '%latch%' group by event;


LATCH SESSIONS DETAIL:
col event for a10

select s.sid,username,osuser,program,machine,status,to_char(logon_time,'DD-MON-YYYY HH24:MI:SS') "LOGON_TIME",last_call_et/3600 "LAST_CALL_HRS",sw.event from v$session s,v$session_wait sw where s.sid=sw.sid and sw.event like '%latch%';


SQL_TEXT OF LATCH SESSIONS:

select s.sid,username,sql_text,sw.event,l.name "LATCH_NAME" from v$session s,v$session_wait sw,v$sqltext sq,v$latch l where s.sid=sw.sid and sq.address = s.sql_address and l.latch# = sw.p2 and sw.event like '%latch%' order by s.sid,piece;

No comments: