latch_CPU_load.sql

/*
     This file is part of demos for "Contemporary Latch Internals" seminar v.24.08.2010
     Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru) 

     Compute the average number of CPUs busy for latch acquisition
     Only for Oracle 10g and below
     Discussion of latch get interface behind this script in
     
Latch get and spin instrumentation. The unknown knowns. V2

*/
SET SERVEROUTPUT ON
DECLARE
   i          BINARY_INTEGER;
   Samples    BINARY_INTEGER;
   SampleFreq      NUMBER;
   lavg   NUMBER;
BEGIN
   SampleFreq := 1 / 10;   -- Hz
   Samples := 100;
   lavg := 0;
   FOR i IN 1 .. Samples
   LOOP
	for lload in (SELECT COUNT(1) laq FROM x$ksupr
                   WHERE ksllalaq != HEXTORAW ('00'))
        LOOP
          lavg := lavg + lload.laq;
        END LOOP;
        DBMS_LOCK.sleep (SampleFreq);
   END LOOP;
   lavg := lavg / Samples;
   DBMS_OUTPUT.put_LINE ('Average lath acquisition load=' || lavg);
END;
/