Timer object
Timer object to simplify the routine task of capturing start and end times and displaying formatted results. Includes "stop", "resume", "restart", "elapsed" and "show" methods.
-- timer object to simplify routine task of capturing start and end times and
-- displaying formatted results, e.g:
-- mytimer.show('Elapsed so far');
-- might give
-- +0 00:00:05.15 Elapsed so far
--
-- Elapsed time is displayed as a constrained INTERVAL DAY(1) TO SECOND(2), which Oracle displays as
-- +[days] [hh]:[mi]:[ss.xx] (where 'xx' represents two decimal places)
--
-- Description is displayed by show() and can be set at any point i.e. via initialisation,
-- restart(), stop(), resume() or show().
--
-- William Robertson 22 Feb 2004, www.williamrobertson.net
--
-- 2004 02 29 Added resume() method and DBMS_APPLICATION_INFO calls.
define day_precision = 1
define second_precision = 2
create or replace type timer as object
( start_time timestamp
, end_time timestamp
, description varchar2(1000)
, status varchar2(7)
, last_resumed_time timestamp
, accumulated interval day to second
, constructor function timer
( p_description varchar2 default null )
return self as result
, member procedure restart -- Reset start time to now
( p_description varchar2 default null )
, member procedure stop -- Set stop time to now
( p_description varchar2 default null )
, member procedure resume -- Reset status to started: undoes stop without erasing start_time
( p_description varchar2 default null )
, member function elapsed -- Return elapsed time as interval value
return interval day to second
, member procedure show -- Display elapsed time with message
( p_description varchar2 default null )
, static function version -- Code repository version
return varchar2
)
not final
/
show errors
grant execute on timer to public;
create or replace public synonym timer for timer;
create or replace type body timer
as
constructor function timer
( p_description varchar2 default null )
return self as result
is
begin
-- Restart() method reinitializes the timer (see below):
restart(p_description);
return;
end;
member procedure restart
( p_description varchar2 default null )
is
begin
self.resume(p_description);
self.start_time := systimestamp;
self.last_resumed_time := null;
self.accumulated := null;
self.status := 'RUNNING';
if p_description is not null then
self.description := p_description;
dbms_application_info.set_action(p_description);
end if;
end restart;
-- Return the amount of time elapsed since either START_TIME or LAST_RESUMED_TIME.
-- If there is an ACCUMULATED interval, add that as well (timing was stopped and later resumed:
-- the elapsed time is stored as ACCUMULATED at the stop() event to allow it for a subsequent resume()).
member function elapsed
return interval day to second
is
-- Capture time-sensitive information first:
k_end_time constant timestamp := systimestamp;
k_start_time constant timestamp :=
case when self.last_resumed_time is not null then
greatest(self.last_resumed_time,self.start_time)
else
self.start_time
end;
k_base_elapsed constant interval day to second := k_end_time - k_start_time;
begin
if self.status = 'STOPPED' then
-- Clock has stopped: accumulated value will not change until timing is resumed:
return self.accumulated;
else
return (k_end_time - k_start_time) + nvl(self.accumulated, interval '0' second);
end if;
end elapsed;
-- Stop timer and calculate accumulated time.
-- Amend description, if specified.
-- Timer can later be restarted, or non-destructively resumed.
member procedure stop
( p_description varchar2 default null )
is
k_elapsed constant interval day to second := self.elapsed();
begin
-- Check that timer has not already stopped:
-- If so, ignore this call except to change description.
if self.status = 'STOPPED' then
null;
else
self.end_time := systimestamp;
self.accumulated := k_elapsed;
self.status := 'STOPPED';
end if;
if p_description is not null then
self.description := p_description;
end if;
end stop;
member procedure resume
( p_description varchar2 default null )
is
begin
self.last_resumed_time := systimestamp;
self.status := 'RUNNING';
if p_description is not null then
self.description := p_description;
dbms_application_info.set_action(p_description);
end if;
end resume;
member procedure show
( p_description varchar2 default null )
is
k_elapsed constant interval day(&&day_precision) to second(&&second_precision) := self.elapsed();
begin
if p_description is not null then
self.description := p_description;
end if;
dbms_output.put_line(k_elapsed || ' ' || self.description);
end show;
static function version
return varchar2
is
begin
return '1.1';
end version;
end;
/
show errors
set doc off
pro Demo using timer type:
set echo on serverout on size 1000000 format wrapped
declare
k_iterations constant pls_integer := 4;
v_interval interval day(&day_precision) to second(&second_precision);
v_overall_timer timer := new timer('Overall time for demo');
v_split_timer timer := new timer('Add times for two events using stop and resume methods');
v_loop_timer timer := new timer('Loop with ' || k_iterations || ' iterations');
v_detail_timer timer := new timer();
begin
dbms_output.put_line('...demo pausing 1 second...');
dbms_lock.sleep(1);
v_split_timer.stop();
v_split_timer.show('v_split_timer stopped');
dbms_output.new_line();
dbms_lock.sleep(1);
for i in 1..k_iterations loop
v_detail_timer.restart('Loop iteration #' || i);
dbms_lock.sleep(1);
v_detail_timer.stop();
v_detail_timer.show(); -- Can specify message on NEW, restart or show
v_overall_timer.show('running time so far');
end loop;
dbms_output.new_line();
v_loop_timer.show();
dbms_output.new_line();
v_split_timer.resume();
v_split_timer.show('v_split_timer resumed after the loop.');
dbms_output.new_line();
dbms_output.put_line('...demo pausing 1 second...');
dbms_lock.sleep(1);
v_interval := v_detail_timer.elapsed();
dbms_output.put_line
( chr(10)|| 'Captured time of last loop iteration "' ||
v_detail_timer.description || '": ' || v_interval );
dbms_output.new_line();
dbms_output.put_line('...demo pausing 1 second...');
dbms_output.new_line();
v_split_timer.show('v_split_timer resumed timing from where it left off.');
v_overall_timer.show('Entire demo');
end;
/
set echo off