latch_stats_10g.sql

/*
     This file is part of demos for "Contemporary Latch Internals"
        presentation at Hotsos Symposium 2011  v.21.02.2011
     Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru)
 
     Compute the latch statistics
     For Oracle versions 9.2-10.2
*/
set verify off
select name from v$latch where addr='&1'
union all
select name from v$latch_children where addr='&1';
SET SERVEROUTPUT ON
set timing on
 
DECLARE
   i               number;
   Samples         number:= 300;
   SampleFreq      number:= 1 / 10;   -- Hz;
   Ns   number:= 0;
   Nw   number;
   laddr raw(8);
   Lstat1 x$ksllt%ROWTYPE;
   Lstat2 x$ksllt%ROWTYPE;
   CURSOR Lstat(laddr raw) is select * from x$ksllt where addr=laddr;
   dgets number;
   dmisses number;
   rho number;
   eta number;
   lambda number;
   kappa number;
   W number;
   sigma number;
   Error_ varchar2(100):='';
   lname varchar2(100);
   level_ number;
   dtime number;
   U number:=0;
   ssleeps number;
   tau_s number;
   S number;
BEGIN
   laddr := HEXTORAW ('&1');
/*     CPU count */
   select value into Nw from v$parameter where name = 'cpu_count';
   if Nw != 1 then
      eta:= Nw/(Nw-1);
   else
      eta:=1;
      Error_ := Error_||' Single CPU configuration ';
   end if;
   Nw := 0;
/*     Beginning latch statistics */
   dtime := DBMS_UTILITY.GET_TIME();
   OPEN Lstat(laddr);
   FETCH Lstat into Lstat1;
   CLOSE Lstat;
/*     Sampling */
   FOR i IN 1 .. Samples
   LOOP
                /*   number of pocesses spinning and waiting for the latch */
       for Sample in (SELECT count(decode(ksllalaq,'00',null,1)) laq,
                              count(decode(ksllawat,'00',null,1)) wat  FROM x$ksupr
                   WHERE ksllalaq = laddr or ksllawat=laddr)
        LOOP
          Ns := Ns + Sample.laq;
          Nw := Nw + Sample.wat;
        END LOOP;
              /*    Is latch busy  */
       for Hold in (select 1 hold from x$ksuprlat where ksuprlat=laddr)
       loop
          U:=U+1;
          exit;
       end loop;    
     DBMS_LOCK.sleep (SampleFreq);
   END LOOP;
/*     End latch statistics */
   OPEN Lstat(laddr);
   FETCH Lstat into Lstat2;
   CLOSE Lstat;
   dtime:=(DBMS_UTILITY.GET_TIME()-dtime)*0.01; /* delta time in seconds */
/*     Compute derived statistics */
   dgets  := (lstat2.kslltwgt-lstat1.kslltwgt);
   dmisses:= (lstat2.kslltwff-lstat1.kslltwff);
     if(dgets>0)then rho := dmisses/dgets;
     else
            Error_:='Delta GETS='||dgets;
            rho:= -1;
     end if;  
   Ns := Ns/Samples;
   Nw:=Nw/Samples;
   U:=U/Samples;
   lambda:=dgets/dtime;
   W:= (lstat2.kslltwtt-lstat1.kslltwtt)/dtime*1.E-6;    /* wait time in seconds */
   select kslldnam,kslldlvl into lname,level_ from x$kslld where indx=lstat2.kslltnum;
   if(dgets>0) then
          S:=U/lambda;
   else
          Error_ := Error_||' Delta Gets='||dgets;
   end if;
   if(dmisses>0) then
          kappa:=(lstat2.kslltwsl-lstat1.kslltwsl)/dmisses;
          sigma:=(lstat2.ksllthst0-lstat1.ksllthst0)/dmisses;
   else
          Error_ := Error_||' Delta MISSES='||dmisses;
          kappa:=null;
          sigma:=null;
   end if;
   if(sigma>0) then  
          ssleeps:=(kappa+sigma-1)/kappa;
   else
          Error_ := Error_||'  Sigma='||sigma;
          ssleeps:=null;
   end if;
   if(length(Error_)>0 ) then
      DBMS_OUTPUT.put_LINE (' Error: '||Error_);
   else
      DBMS_OUTPUT.put_LINE (chr(10)||'Latch statistics  for  0x'||laddr||'   "'||lname||'"  level#='||level_||'   child#='||lstat2.kslltcnm);
      DBMS_OUTPUT.put_LINE ('Requests rate:       lambda=' || to_char(lambda,'999999.9')||' Hz');
      DBMS_OUTPUT.put_LINE ('Miss /get:              rho=' || to_char(rho,'9.999999'));
      DBMS_OUTPUT.put_LINE ('Est. Utilization:   eta*rho=' || to_char(eta*rho,'9.999999'));
      DBMS_OUTPUT.put_LINE ('Sampled   Utilization:    U='||to_char(U,'9.999999'));
      DBMS_OUTPUT.put_LINE ('Slps /Miss:      kappa=' || to_char(kappa,'9.999999'));
      DBMS_OUTPUT.put_LINE ('Wait_time/sec:       W=' || to_char(W,'999.999999'));
      DBMS_OUTPUT.put_LINE ('Sampled queue length L=' || to_char(Nw,'999.999999'));
      DBMS_OUTPUT.put_LINE ('Spin_gets/miss:  sigma=' || to_char(sigma,'9.999999'));
      DBMS_OUTPUT.put_LINE ('Sampled spin procs: Ns='||  to_char(Ns,'999.999'));
      DBMS_OUTPUT.put_LINE (chr(10)||'Derived statistics:');
      DBMS_OUTPUT.put_LINE ('Secondary sleeps ratio =' || to_char(ssleeps,'9.99EEEE'));
      DBMS_OUTPUT.put_LINE ('Avg latch holding time =' || to_char(S*1000000,'999999.9')||' us');
      DBMS_OUTPUT.put_LINE ('.        sleeping time =' || to_char(W/lambda*1000000,'999999.9')||' us');
      DBMS_OUTPUT.put_LINE ('.     acquisition time =' || to_char((Ns+W)/lambda*1000000,'999999.9')||' us');
      DBMS_OUTPUT.put_LINE ('.  avg latch free wait =' || to_char(W/(kappa*rho*lambda)*1000000,'999999.9')||' us');
      DBMS_OUTPUT.put_LINE ('.             miss rate=' || to_char(rho*lambda,'999999.9')||' Hz');
      DBMS_OUTPUT.put_LINE ('.           waits rate =' || to_char(kappa*rho*lambda,'999999.9')||' Hz');
      DBMS_OUTPUT.put_LINE ('.   spin inefficiency k=' || to_char(kappa/(1+kappa*rho),'9.999999'));   end if;
END;
/