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

Submitted by dave on Wed, 04/28/2010 - 16:11

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