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

http://andreynikolaev.wordpress.com/2010/07/11/latch-get-instrumentation/

*/
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;
/