define prefix='gh'
set document off

doc

$Source: H:\SQL\RCS\latch_st.sql $
$Revision: 1.3 $
$Author: gharriso $
$Date: 1996/01/29 13:54:37 $

This SQL*PLUS script reports on some latch get and miss rates.

"% of gets" is the % of latch gets which were for this latch.

"% of misses" is the % of latch misses which were for this latch.

"Miss rate %" is the % of time a request for this latch did not 
succeed on the first try.

"spin succ %" is the %age of time that the latch could be got 
within the first spin_count tries

"Avg sleeps" is the average number of sleeps which occured once the 
latch could not be obtained after trying spin_lock times.

"Waits holding%" is the %age of times a process held the latch and went into a wait state.


Guy Harrison
gharriso@werple.mira.net.au

#

@stat_ctl

set termout off
set echo off
set verify off

drop table gh$latch_stat;

set feedback off

column name format a23 heading "Latch|Name"
column gets format 9.99EEEE  heading "Gets"    print
column misses format 99,990 heading "Miss|/1000"   noprint
column sleeps format 99,990 heading "Sleeps|/1000" noprint
column avg_sleeps format 99.0 heading "Avg|Sleeps" noprint
column first_spin_success format 99.0 heading "spin|gets|%" print
column miss_rate format 90.00 heading "Miss|rate%"
column sleep_rate format 90.00 heading "Sleep|rate%"
column pct_of_gets format 99.0 heading "% of|gets"
column pct_of_misses format 99.0 heading "% of|Miss" 
column pct_of_sleeps format 99.0 heading "% of|Sleep" 
column miss_flag heading "" noprint 
column waits_holding_pct format 99.0 heading "Waits|holding|%" noprint
column n_latch format 9999 heading "# of|ltch"


ttitle center 'ORACLE latch statistics:' instance_name right print_date skip center period_comment_ skip 2


spool off;
set termout on
spool &spool_dir.&instance_name..st3

undefine v_total_misses
undefine v_total_gets
undefine v_total_sleeps

variable v_total_misses number
variable v_total_gets number
variable v_total_sleeps number

BEGIN
	select sum(abs(gets+immediate_gets)),sum(abs(misses+immediate_misses)),sum(abs(sleeps))
          into :v_total_gets,:v_total_misses,:v_total_sleeps
	  from &prefix.$latch; 
END;
/

create table gh$latch_stat as 
select name,
       nvl(n_latch,1) n_latch,
       abs(gets+immediate_gets) gets,
       0 pct_of_gets,
       abs(misses+immediate_misses) misses,
       0 pct_of_misses,
       0 pct_of_sleeps,
       ' '                        miss_flag,
       sleeps,
       abs(sleep1)/decode(abs(gets+immediate_gets),0,1,abs(gets+immediate_gets))*100 sleep_rate,
       abs(misses+immediate_misses)/decode(abs(gets+immediate_gets),0,1,abs(gets+immediate_gets))*100 miss_rate,
       (spin_gets/decode(abs(misses+immediate_misses),0,1,abs(misses+immediate_misses)))*100 first_spin_success,
       sleeps/decode(abs(misses+immediate_misses)-spin_gets,0,1,abs(misses+immediate_misses)-spin_gets) avg_sleeps,
       waits_holding_latch*100/decode(abs(misses+immediate_misses),0,1,abs(misses+immediate_misses)) waits_holding_pct
 from &prefix.$latch l,
      (select name c_name,count(*) n_latch 
         from v$latch_children
        group by name) lc 
where l.name=lc.c_name (+)
  and l.gets !=0
/

update gh$latch_stat set
   pct_of_misses= misses/decode(:v_total_misses,0,1,:v_total_misses)*100 
/
update gh$latch_stat set
   pct_of_sleeps= sleeps/decode(:v_total_sleeps,0,1,:v_total_sleeps)*100 
/
update gh$latch_stat s set
   pct_of_gets= gets/decode(:v_total_gets,0,1,:v_total_gets)*100 
/

update gh$latch_stat
   set miss_flag='*'
 where (miss_rate > 1
        and pct_of_misses > 1)
    or sleep_rate>.5
/

select name,n_latch,gets gets,pct_of_gets, sleeps/1000 sleeps,misses/1000 misses,
       pct_of_sleeps,pct_of_misses,sleep_rate,miss_rate,miss_flag,
       first_spin_success,avg_sleeps,
       waits_holding_pct
 from gh$latch_stat
where pct_of_gets >.1 or pct_of_misses > .1 or pct_of_sleeps >.1
 order by sleeps desc,misses desc,gets desc
/

drop table gh$latch_stat;


prompt
prompt Notes:
prompt -------------------------------------------------------------------;
prompt
prompt Miss rates and Sleep rates should be less that 1% generally,  although
prompt higher rates may not matter if the latch accounts for a very small 
prompt %age of latch gets
prompt
prompt gets and misses include immediate gets and immediate misses
prompt
prompt To reduce contention for the redo allocation latch;
prompt try creating more latches (LOG_SIMULTANEOUS_COPIES)
prompt up to 2xCPU_COUNT.  If this fails,  try forcing 
prompt pre-build of redo entries by upping 
prompt LOG_ENTRY_PREBUILD_THRESHOLD
prompt
prompt To reduce contention for library cache latch,  reduce dynamic SQL.
prompt In 7.2+,  consider increasing _kgl_latch_copies
prompt
prompt To reduce contention for cache buffers lru chain,  increase 
prompt db_block_lru_latches
prompt 
prompt Increasing spin count may reduce latch free waits in V$SYSTEM_EVENT
prompt and may increase the spin succ% rate shown above at the cost of 
prompt increased CPU utilisation.
prompt ------------------------------------------------------------------;

spool off