Permalink
Find file
145716d Dec 29, 2015
176 lines (151 sloc) 5.52 KB
-----------------------------------------------------------------------------
--
-- Script: timer.sql
--
-- Author: Adrian Billington
-- www.oracle-developer.net
--
-- Package: TIMER
--
--
-- Purpose: Timing package for testing durations of alternative coding
-- approaches. Based on Steven Feuerstein's original timer package
-- but simplified and modified. Works for Oracle versions 8i and
-- above.
--
-----------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE timer AS
secs CONSTANT PLS_INTEGER := 1;
mins CONSTANT PLS_INTEGER := 2;
hrs CONSTANT PLS_INTEGER := 3;
days CONSTANT PLS_INTEGER := 4;
PROCEDURE snap (
show_stack_in IN BOOLEAN DEFAULT FALSE
);
PROCEDURE show (
prefix_in IN VARCHAR2 DEFAULT NULL,
format_in IN PLS_INTEGER DEFAULT timer.secs
);
END timer;
/
sho err
CREATE OR REPLACE PACKAGE BODY timer IS
/* Package (global) variables... */
g_last_timing PLS_INTEGER := NULL;
g_show_stack BOOLEAN := FALSE;
/******************* FUNCTION caller *********************/
FUNCTION caller RETURN VARCHAR2 IS
v_stk VARCHAR2(4000) := DBMS_UTILITY.FORMAT_CALL_STACK;
v_dpt PLS_INTEGER := 6;
v_pos PLS_INTEGER := 21;
v_dlm VARCHAR2(1) := CHR(10);
BEGIN
RETURN NVL(
SUBSTR(
SUBSTR(
v_stk,
INSTR( v_stk, v_dlm ,1,(v_dpt-1))+1,
INSTR( v_stk, v_dlm ,1, v_dpt) - (INSTR( v_stk, v_dlm, 1, (v_dpt-1)))-1
),
v_pos ),
'[unknown]' );
END caller;
/******************* PROCEDURE snap *********************/
PROCEDURE snap (
show_stack_in IN BOOLEAN DEFAULT FALSE
) IS
BEGIN
g_last_timing := DBMS_UTILITY.GET_TIME;
IF show_stack_in THEN
g_show_stack := show_stack_in;
DBMS_OUTPUT.PUT_LINE('[started ' || caller() || ']');
END IF;
END snap;
/******************* FUNCTION elapsed *********************/
FUNCTION elapsed RETURN NUMBER IS
BEGIN
RETURN DBMS_UTILITY.GET_TIME - g_last_timing;
END elapsed;
/******************* FUNCTION reformat *********************/
FUNCTION reformat (
input_in IN NUMBER,
format_in IN VARCHAR2 DEFAULT 9999900
) RETURN VARCHAR2 IS
BEGIN
RETURN TRIM(TO_CHAR(input_in, format_in));
END reformat;
/******************* FUNCTION remainder *********************/
FUNCTION REMAINDER (
input_in IN PLS_INTEGER,
modulus_in IN PLS_INTEGER,
format_in IN VARCHAR2 DEFAULT '900'
) RETURN VARCHAR2 IS
BEGIN
RETURN reformat(MOD(input_in, modulus_in), format_in);
END REMAINDER;
/******************* PROCEDURE show *********************/
PROCEDURE show (
prefix_in IN VARCHAR2 DEFAULT NULL,
format_in IN PLS_INTEGER DEFAULT timer.secs
) IS
/*
* Construct message for display of elapsed time. Programmer can
* include a prefix to the message and also ask that the last
* timing variable be reset/updated to save calling snap again.
*/
TYPE typ_rec_elapsed IS RECORD
( hsecs PLS_INTEGER
, secs PLS_INTEGER
, mins PLS_INTEGER
, hrs PLS_INTEGER
, days PLS_INTEGER
);
rec_elapsed typ_rec_elapsed;
v_elapsed_string VARCHAR2(128);
v_message VARCHAR2(512);
v_label VARCHAR2(128);
BEGIN
IF g_last_timing IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Timer not started.');
ELSE
/* Capture the elapsed time and format it into the "set timing on" format of SQL*Plus... */
rec_elapsed.hsecs := elapsed();
rec_elapsed.secs := TRUNC(rec_elapsed.hsecs/100);
rec_elapsed.mins := TRUNC(rec_elapsed.hsecs/6000);
rec_elapsed.hrs := TRUNC(rec_elapsed.hsecs/360000);
rec_elapsed.days := TRUNC(rec_elapsed.hsecs/8640000);
IF format_in = timer.secs THEN
v_elapsed_string := reformat(rec_elapsed.hsecs/100, '99999990.00') || ' seconds';
ELSIF format_in = timer.mins THEN
v_elapsed_string := reformat(rec_elapsed.mins)
|| ' minutes '
|| REMAINDER(rec_elapsed.secs,60)
|| ' seconds';
ELSIF format_in = timer.hrs THEN
v_elapsed_string := reformat(rec_elapsed.hrs)
|| ' hours '
|| REMAINDER(rec_elapsed.mins,60)
|| ' minutes';
ELSE
v_elapsed_string := reformat(rec_elapsed.days)
|| ' days '
|| REMAINDER(rec_elapsed.hrs,24)
|| ' hours';
END IF;
/* Build the message string... */
v_label := NVL(prefix_in, 'elapsed');
v_message := '[' || v_label ||'] '|| v_elapsed_string;
/* Output... */
IF g_show_stack THEN
DBMS_OUTPUT.PUT_LINE('[stopped ' || caller() || ']');
END IF;
DBMS_OUTPUT.PUT_LINE(v_message);
/* Reset... */
g_last_timing := NULL;
g_show_stack := FALSE;
END IF;
END show;
END timer;
/
CREATE PUBLIC SYNONYM timer FOR timer;
GRANT EXECUTE ON timer TO PUBLIC;