Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 13 August 2013

Script: To Monitor TOP Latches Statistics & Wait Information

Display Latch Statistics
WITH latch AS (
    SELECT name,
           ROUND(gets * 100 / SUM(gets) OVER (), 2) pct_of_gets,
           ROUND(misses * 100 / SUM(misses) OVER (), 2) pct_of_misses,
           ROUND(sleeps * 100 / SUM(sleeps) OVER (), 2) pct_of_sleeps,
           ROUND(wait_time * 100 / SUM(wait_time) OVER (), 2)  pct_of_wait_time
      FROM v$latch)
WHERE pct_of_wait_time > .1 OR pct_of_sleeps > .1
ORDER BY pct_of_wait_time DESC;
Display Latch Statistics for Active Session History
WITH ash_query AS ( SELECT event, program, h.module, h.action,   object_name,
            SUM(time_waited)/1000 time_ms, COUNT( * ) waits, username, sql_text,
            RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
            ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited)) OVER (), 2)  pct_of_time
      FROM  v$active_session_history h
      JOIN  dba_users u  USING (user_id)
      LEFT OUTER JOIN dba_objects o
           ON (o.object_id = h.current_obj#)
      LEFT OUTER JOIN v$sql s USING (sql_id)
     WHERE event LIKE '%latch%' or event like '%mutex%'
     GROUP BY event,program, h.module, h.action,
         object_name,  sql_text, username)
SELECT event,module, username,  object_name, time_ms,pct_of_time, sql_text
FROM ash_query
WHERE time_rank < 11
ORDER BY time_rank;
Display information about Top Latches
SELECT l.latch#,, l.gets, l.misses, l.sleeps,
l.immediate_gets, l.immediate_misses, l.spin_gets
FROM   v$latch l
WHERE  l.misses > 0
ORDER BY l.misses DESC;
Display Latches wait information
SELECT  latch#,  name, gets, misses, sleeps
FROM    v$latch
WHERE   sleeps>0
ORDER   BY misses, sleeps;
Display Latche Children information
select  addr, latch#, gets, misses,sleeps
FROM    v$latch_children
WHERE   sleeps>0 and  latch# in (select unique p2                
from  v$session_wait                
where event ='latch free')
ORDER   BY sleeps;
Display the information of Latches has been waiting for
select * from    v$latchname
WHERE latch# in (select unique p2    
from  v$session_wait                
where event ='latch free');


Post a Comment