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';
SET SERVEROUTPUT ON
set timing on

DECLARE
   i               NUMBER;
   Samples         NUMBER;
   SampleFreq      NUMBER;
   Ns   NUMBER;
   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;
   ssleeps number;
   tau_s number;
   S number;
BEGIN
   SampleFreq := 1 / 10;   -- Hz
   Samples := 300;
   laddr := HEXTORAW ('&1');
   Ns := 0;
   Nw := 0;
   U:=0;
   Error_:='';
/*     CPU count */
   select value into Ns from v$parameter where name = 'cpu_count';
   eta:= Ns/(Ns-1);
/*     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:=eta*rho/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:=0;
          sigma:=0;
   end if;
   if(sigma>0) then   
          ssleeps:=(kappa+sigma-1)/kappa;
   else 
          Error_ := Error_||'  Sigma='||sigma;
          ssleeps:=0;
   end if;

   if(rho>0) then
     tau_s:= W*(1-eta*(kappa+sigma-1)*rho**2)/(eta*rho*(W+rho*kappa)) -1;
   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')||' musec');
      DBMS_OUTPUT.put_LINE ('.        sleeping time =' || to_char(W/lambda*1000000,'999999.9')||' musec');
      DBMS_OUTPUT.put_LINE ('.     acquisition time =' || to_char((Ns+W)/lambda*1000000,'999999.9')||' musec');
      DBMS_OUTPUT.put_LINE ('context switch time estimation tau/S =' || to_char(tau_s,'999999.99'));
      DBMS_OUTPUT.put_LINE ('.  avg latch free wait =' || to_char(W/(kappa*rho*lambda)*1000000,'999999.9')||' musec');
   end if;
END;
/