Test Harness for Measuring CPU and Consisent Gets of PL/SQL or SQL

Update NumRuns to number of executions you would like to measure. Update 'Start Timed Code' section to include the code you want to measure.

connect / as sysdba
grant select on v_$session to scott;
grant select on v_$sesstat to scott;
grant select on v_$statname  to scott;


connect scott/tiger

set serveroutput on
SPOOL timing.txt

SET SERVEROUTPUT ON
DECLARE
  NumRuns     NUMBER := 5;

  Time_Start  DATE;
  Time_Current NUMBER := 0;
  Time_Total   NUMBER := 0;

  CPU_Start   NUMBER;
  CPU_Current NUMBER := 0;
  CPU_Total   NUMBER := 0;
  
  CG_Start    NUMBER;
  CG_Current  NUMBER := 0;
  CG_Total    NUMBER := 0;
  
  
  FUNCTION GetStat2 (p_statname IN VARCHAR2) 
  RETURN NUMBER 
  IS
    retVal NUMBER;
  BEGIN
     retVal := 0;
     
      SELECT VALUE
        INTO retVal
        FROM v$session ss, v$sesstat se, v$statname sn
       WHERE se.statistic# = sn.statistic#
         AND UPPER(TRIM(NAME)) = UPPER(TRIM(p_statname))
         AND se.SID = ss.SID
        AND ss.SID = USERENV('SID');
     
     RETURN retVal;
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
         NULL;
       WHEN OTHERS THEN
         -- Consider logging the error and then re-raise
         RAISE;
  END GetStat2;
  
BEGIN

  dbms_output.put_line('Starting Compressed');
  dbms_output.put_line('Run#,Elapsed Time (secs),CPU Time (secs),Consistent Gets');

  FOR cnt IN 1..NumRuns
  LOOP


    DBMS_APPLICATION_INFO.SET_MODULE( 
        module_name => 'Timing', 
        action_name => 'Loop '||cnt||' of '||NumRuns);


    Time_Start := SYSDATE;
    CPU_Start  := getstat2('CPU used by this session');
    CG_Start   := getstat2('consistent gets');

    -- Start Timed Code
    UPDATE EMP SET SAL=SAL*1.10;
    COMMIT;
    -- End Timed Code

    Time_Current := (SYSDATE - Time_Start)*86400;
    CPU_Current  := (getstat2('CPU used by this session') - CPU_Start)/100;-- Centiseconds
    CG_Current   := getstat2('consistent gets') - CG_Start;

    Time_Total   := Time_Total + Time_Current;
    CPU_Total    := CPU_Total + CPU_Current; 
    CG_Total     := CG_Total + CG_Current;
     
    DBMS_OUTPUT.PUT_LINE(cnt||','||ROUND(Time_Current,2)
        ||','||CPU_Current||','
        ||CG_Current);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Avg,'||ROUND(Time_Total/NumRuns,2)||','
      ||ROUND(CPU_Total/NumRuns,2)||','
      ||ROUND(CG_Total/NumRuns,2));

END;
/