#!/usr/bin/ksh
#set -xv
# This scripts contains also:
#---------------------------------------------------------------------------------
#-- Script:     latch_spins.sql, latch_sleeps.sql
#-- Purpose:    shows latch spin statistics
#-- Copyright:  (c) 1998 Ixora Pty Ltd
#-- Author:     Steve Adams, adapted to smenu by B. Polarski
# ----------------------------------------------------------------------
#
# B. Polarski : I added SQL extracted from the excellent Oracle wait interface
# from Shee, Deshpande, Gopalakrishanan - Oracle Press

function help
{
cat <<EOF

   All about latches:

    lat -s                 # latch statistics when there are misses
    lat -d <n>             # show lat difference between <n> seconds
    lat -a                 # latch statistics including latches without misses
    lat -n <nn>            # latch name and number, eventually for only one
    lat -m                 # latch misses from v\$latch_misses
    lat -sp                # latch spining
    lat -o                 # Show latch location
    lat -cbc               # List Cache buffer chain latches sorted by sleeps count
    lat -bh <RAW latch addr> # Show objects and touch count covered by latch raw addr
    lat -mis <latch name>  # show location of latch misses (use lat -n to exact get name)

    lat -c                 # children latch count and stats
    lat -e                 # children latch sleeping
    lat -i                 # children latch sleeping impact

    lat -p                 # Show number of latch sub pool
    lat -l  <sid>          # lock held on library cache objects (v\$access)
    lat -t                 # report current latch activity
    lat -w                 # Report Latch sleeps

EOF
exit
}
# ----------------------------------------------------------------------
ACTION=DEFAULT
TITTLE="Show Latch statistics"
WHERE=" where a.misses <> 0 "
if [ -z "$1" ];then
   help
fi
while [ -n "$1" ]
do
  case "$1" in
     -a ) unset WHERE ;;
     -c ) ACTION=CHILDREN ; TITTLE="Report latch types with child counts and distribution" ;;
     -e ) ACTION=SLEEP ;    TITTLE="Report children latch sleeping";;
     -i ) ACTION=IMPACT ;   TITTLE="Latch sleeps impact";;
     -l ) ACTION=LIB ;      TITTLE="lock held on library cache objects" ; FSID=$2; shift;;
     -s ) ACTION=DEFAULT ;;
     -m ) ACTION=MISS ;     TITTLE="latch misses from v\\\$latch_misses";;
     -n ) ACTION=NAME
          if [ -n "$2" ] ;then
              WHERE="where latch# = '$2'"
              shift
          else
              WHERE=
          fi
          TITTLE="Report latch name and id" ;;
     -o ) ACTION=LOCATION ; TITTLE="latch location";;
   -cbc ) ACTION=CBC ;      TITTLE="List Cache buffer chain latches sorted by sleeps count";;
    -bh ) ACTION=BH ;       TITTLE="List object and touch count protected by latch $2" ; LATCH_RAW_NR=$2 ; shift  ;;
   -mis ) ACTION=MIS ; TITTLE="show location of latch misses" ; shift; LATCH_NAME=$@ ; break;;
     -p ) ACTION=SUBPOOL ;  TITTLE="Show number of sub pool in shared_pool" ; S_USER=SYS ;;
    -sp ) ACTION=SPIN ;     TITTLE="Report spin gets";;
     -d ) ACTION=DIFF ;    SLEEP_TIME=$2 ;shift;;
     -t ) ACTION=ACTIVITY ; TITTLE="report current latch activity"  ;;
     -w ) ACTION=LATW ; TITTLE="Report Latch sleeps"  ;;
     -h ) help ;;
     *  ) echo "Unknown parameters $1" ; echo ; help ;;
  esac
  shift
done

. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi

# ...............................................................................................

# ------------------------------
# report current latch activity
# ------------------------------
if [ "$ACTION" = "LATW" ];then
SQL="
column spec        format a124 heading 'Message'
column evt         format a124 heading 'Message'
column name        format a24 heading 'Latch Name'
column event       format a40 heading 'Event name'
column waits_holding_latch   format 99999999 heading 'Wait     | holding latch'
column sleeps  format 99999999 heading 'Number|Sleeps'
column sw      format 999999 heading 'Seconds| Waiting'
column sid     format 9999 heading 'Sid'
set lines 190
SELECT w.sid ,  w.event,n.name, w.p3 Sleeps, w.seconds_in_wait sw
       , p1, p1raw, p2, p2raw $DETAIL_L $DETAIL_E
 FROM V\$SESSION_WAIT w, V\$LATCHNAME n
WHERE  w.event not in ('rdbms ipc message')
   and w.p2 = n.latch# and latch# not in (1)
order by w.sid ;
"
# ------------------------------
# report current latch activity
# ------------------------------
elif [ "$ACTION" = "ACTIVITY" ];then
SQL="
set linesize 150
column name        format a24 heading 'Latch type'
column event       format a60 heading 'Event name'
column waits_holding_latch   format 99999999 heading 'Wait     | holding latch'
column sleeps  format 99999999 heading 'Number|Sleeps'
column sw      format 999999 heading 'Seconds| Waiting'
column sid     format 9999 heading 'Sid'

select b.sid, event, name, sleeps , sw , address from
v\$open_cursor a,
( SELECT w.sid,  w.event,n.name, SUM(w.p3) Sleeps, SUM(w.seconds_in_wait) sw
 FROM V\$SESSION_WAIT w, V\$LATCHNAME n
WHERE w.p2 = n.latch# and latch# not in (1)
GROUP BY w.sid, n.name, w.event ) b
where b.sid = a.sid
/
"
# ------------------------------
# Show diff latch
# ------------------------------
elif [ "$ACTION" = "DIFF" ];then
sqlplus -s "$CONNECT_STRING"    <<EOF
set linesize 120 pagesize 333 feed off head off
set serveroutput on size 999999
declare
 type s  is table of  number INDEX BY BINARY_INTEGER;
 type t  is table of VARCHAR2(50)  INDEX BY BINARY_INTEGER;
  v1 s;
  v2 s;
  m1 s;
  m2 s;
  t1 t;
  t2 t;
begin
   for c in ( select  a.latch#,b.name,a.gets,a.misses from v\$latch a, v\$latchname b where b.latch# = a.latch# order by 1 )
   loop
       v1(c.latch#):=c.gets;
       m1(c.latch#):=c.misses;
       t1(c.latch#):=c.name;
   end loop;
   dbms_lock.sleep($SLEEP_TIME);

   for c in ( select  a.latch#,b.name,a.gets,a.misses from v\$latch a, v\$latchname b where b.latch# = a.latch# order by 1 )
   loop
       v2(c.latch#):=c.gets;
       m2(c.latch#):=c.misses;
       t2(c.latch#):=c.name;
   end loop;
   DBMS_OUTPUT.PUT_LINE ('Name                             Gets Diff    Gets1         Gets2       Miss Diff   Miss1         Miss2');
   DBMS_OUTPUT.PUT_LINE ('-------------------------------- ------------ ------------- ----------- ----------- ------------- -----------') ;

    FOR i in v2.FIRST .. v2.LAST
    LOOP
        if (v2.exists(i)  ) then
            if (v1.exists(i)  ) then
                if  v2(i) != v1(i) then
                    DBMS_OUTPUT.PUT_LINE(rpad(t2(i),34,' ') || rpad(to_char(v2(i)-v1(i)),12,' ')|| rpad(to_char(v1(i)),12,' ') ||
                                          '  ' || rpad(to_char(v2(i)),12,' ')|| rpad(to_char(m2(i)-m1(i)),12,' ')|| rpad(to_char(m1(i)),12,' ') || '  ' || to_char(m2(i)) );
                end if ;
            else
                   DBMS_OUTPUT.PUT_LINE(rpad(t2(i),34,' ') || rpad(to_char(v2(i)),12,' ')|| '0           ' || '  ' ||  rpad(to_char(v2(i)),12,' ') ||rpad(to_char(m2(i)),12,' ')|| '0           ' || '  ' || to_char(m2(i)) );
            end if ;
        end if ;
    end loop ;

end ;
/
EOF
exit

# ------------------------------
# List latch misses location
# ------------------------------

elif [ "$ACTION" = "MIS" ];then
SQL="set feed on
select PARENT_NAME,location,NWFAIL_COUNT,SLEEP_COUNT,WTR_SLP_COUNT,LONGHOLD_COUNT
from v\$latch_misses where parent_name = '$LATCH_NAME' order by sleep_count desc ;
"

# ------------------------------
# List object from BH
# ------------------------------
elif [ "$ACTION" = "BH" ];then
SQL="prompt running against dba_segment, be patient ...
select /*+ RULE */ e.owner ||'.'|| e.segment_name  segment_name, e.extent_id  extent#,
  x.dbablk - e.block_id + 1  block#, x.tch, l.child#
from
  sys.v\$latch_children  l, sys.x\$bh  x, sys.dba_extents  e
where
  x.hladdr  = '$LATCH_RAW_NR' and e.file_id = x.file# and x.hladdr = l.addr and
  x.dbablk between e.block_id and e.block_id + e.blocks -1
  order by x.tch desc ;
"

# ------------------------------
# Library cache buffer chain latch
# ------------------------------
elif [ "$ACTION" = "CBC" ];then
# ------------------------------
SQL="select CHILD# , ADDR ,  GETS ,   MISSES , SLEEPS from
(select CHILD# , ADDR ,  GETS ,   MISSES , SLEEPS
from v\$latch_children
where name = 'cache buffers chains'
order by 5 desc, 4, 3,1)where rownum <30;
"
# ------------------------------
# Library cache locks
# ------------------------------
elif [ "$ACTION" = "LIB" ];then
# ------------------------------

SQL="break on sid
select sid,type, OWNER||'.'||object obj from v\$access where sid  = $FSID;"
# latch location
# ------------------------------
elif [ "$ACTION" = "LOCATION" ];then
SQL="prompt WTR_SLP_COUNT  : process slept while requesting the latch from this location
prompt SLEEP_COUNT    : process slept while the latch was held from this location
prompt LONGHOLD_COUNT : process slept because the latch was persistently held from this location for an entire spin cycle.
prompt
col sleep_count heading 'SLEEP_COUNT'
select location,parent_name,wtr_slp_count,sleep_count,longhold_count
from v\$latch_misses where sleep_count>0 order by wtr_slp_count,location;"

# ------------------------------
# children latch sleeping
# ------------------------------
elif [ "$ACTION" = "SLEEP" ];then
SQL=" SELECT name,addr, latch#, gets, misses, sleeps from (
SELECT a.name,b.addr, a.latch#, b.gets, b.misses, b.sleeps
FROM v\$latch a, v\$latch_children b WHERE b.sleeps>0 and b.latch# = a.latch#
 ORDER BY 5  desc,1) where rownum <50;
"

# ------------------------------
# Sub pool Latch setting
# ------------------------------
elif [ "$ACTION" = "SUBPOOL" ];then
SQL="set heading off
select 'Number of sub pool in shared pool (_kghdsidx_count) : '|| b.ksppstvl
     from x\$ksppi a,x\$ksppsv b where a.indx=b.indx and a.ksppinm='_kghdsidx_count';
set heading on
prompt
prompt LRU contents breakdown
prompt
select addr,kghluidx,kghlufsh,kghluops,kghlurcr,kghlutrn,kghlumxa
from x\$kghlu
/
prompt
prompt Sub Shared pool lache statistics distirbutions
prompt
select addr,name,gets,misses,waiters_woken from v\$latch_children
where name = 'shared pool'
/
"

# ------------------------------
# Latch miss
# ------------------------------
elif [ "$ACTION" = "MISS" ];then
SQL=" prompt
prompt LOCATION :  Location that attempted to acquire the latch
prompt NWFAIL   :  Number of times that no-wait acquisition of the latch failed
prompt SLEEP    :  Number of times that acquisition attempts caused sleeps
prompt
set feed on
select PARENT_NAME, "LOCATION", NWFAIL_COUNT, SLEEP_COUNT from
  v\$latch_misses where NWFAIL_COUNT > 0; "

# ------------------------------
# Latch sleeping
# ------------------------------
elif [ "$ACTION" = "IMPACT" ];then
SQL="select
  l.name,
  l.sleeps * l.sleeps / (l.misses - l.spin_gets)  impact,
  lpad(to_char(100 * l.sleeps / l.gets, '990.00') || '%', 10)  sleep_rate,
  l.waits_holding_latch, l.level#
from
  v\$latch  l where l.sleeps > 0 order by 2 desc; "

# ------------------------------
# Latch Spining
# ------------------------------
elif [ "$ACTION" = "SPIN" ];then
  SQL="select l.name, l.spin_gets, l.misses - l.spin_gets  sleep_gets,
lpad(to_char(100 * l.spin_gets / l.misses, '990.00') || '%', 13)  hit_rate
from v\$latch l where l.misses > 0 order by l.misses - l.spin_gets desc ;
prompt
select 'ALL LATCHES'  name, sum(l.spin_gets)  spin_gets, sum(l.misses - l.spin_gets) sleep_gets,
  lpad( to_char(100 * sum(l.spin_gets) / sum(l.misses), '990.00') || '%', 13)  hit_rate
from v\$latch  l where l.misses > 0 ;
"
# ------------------------------
# Latch by name and id
# ------------------------------
elif [ "$ACTION" = "CHILDREN" ];then
SQL="SELECT a.name, count(a.latch#) total, sum(b.gets) sg, sum(b.misses) sm, sum(b.sleeps) ss
FROM v\$latch a, v\$latch_children b
   WHERE b.latch# = a.latch# group by a.name, a.latch#  ORDER BY 1 asc ,5  desc;"

# ------------------------------
# Latch by name and id
# ------------------------------
elif [ "$ACTION" = "NAME" ];then
SQL="column name format a45
select latch#,name from v\$latchname $WHERE; "

# ------------------------------
# Default
# ------------------------------

elif [ "$ACTION" = "DEFAULT" ];then
    SQL=" select a.name fname, a.immediate_gets,a.immediate_misses,
a.gets gets, a.misses misses ,a.misses*100/decode(a.gets,0,1,a.gets) miss,sleeps
,to_char(a.spin_gets*100/decode(a.misses,0,1,a.misses),'990.9') cspins
,to_char(a.sleep1*100/decode(a.misses,0,1,a.misses),'990.9') csleep1
,to_char(a.sleep2*100/decode(a.misses,0,1,a.misses),'990.9') csleep2
,to_char(a.sleep3*100/decode(a.misses,0,1,a.misses),'990.9') csleep3
from v\$latch a  $WHERE order by 4 desc;
"
fi

# ...............................................................................................
sqlplus -s "$CONNECT_STRING" <<EOF


ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '   'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66
set linesize 80
set heading off pause off
set embedded on
set verify off

select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline , '$TITTLE (help: lat -h)' nline from sys.dual
/
set embedded on
set heading on
set feedback off
set linesize 132 pagesize 66
col parent_name  format a31 heading "Parent name "
col segment_name  format a45 heading "Segment name "
col location  format a36 heading "Location "
col where        format a35 heading "Where "
col NWFAIL_COUNT format 99999999999 heading "Nwfail "
col SLEEP_COUNT  format 99999999999 heading "Sleep "
col SLEEPS   format 999999 heading "Sleep "
col CSPINS      form a6 head 'Spin|gets' justify c
col CSLEEP1     form a6 head 'Sleep1' justify c
col CSLEEP2     form a6 head 'Sleep2' justify c
col CSLEEP3     form a6 head 'Sleep3' justify c
col type_num format 999 heading "TYPE|NUMBER"
col type_name format a45 heading "TYPE|NAME"
col parent format 9 heading "PARENT|LATCH"
col children format 999999 heading "CHILD|LATCHES"
col miss     form 90.999 head '% Miss'
col misses   form 99999999 head 'Misses' justify c
col fname     form a37 head 'Latch name'
col gets     form 99999999999 head 'Gets' justify c
col name format A38 head "Children latch name"
col total format 9,999,990 head "Total Latch|Present in System"
col sg format 9,999,999,990 head "Total Gets"
col sm format 9,999,999,990 head "Total Misses"
col ss format 9,999,999,990 head "Total Sleeps"
col immediate_gets format 99999990 heading 'IMMEDIATE|GETS'
col immediate_misses format 99999990 heading 'IMMEDIATE|MISSES'
col waits_holding_latch   format 99999999 heading "Wait     | holding latch"
col sleep_rate  format a10 heading "Sleep rate"
col impact      format 9999999999 heading "Impact"
col addr heading "Sub pool|Latch" justify c
col KGHLURCR heading "Recreatable" justify c
col KGHLUTRN heading "Transcient" justify c
col obj format a60 heading "Object"
col location format a35 heading "Location"
prompt
$SQL
prompt
exit
EOF