Capturing Elapsed Time of a SQL Statement

Looking to create a dashboard for our BI Tool that will enable me to capture report run times and display a red/yellow/green light for our reporting sub system to end users. 

My first task is to create (and schedule) a sample report that calculates run time deltas. 


COLUMN start_time NEW_VALUE start
SELECT systimestamp(9) start_time FROM dual;

<your sql statement goes here>

COLUMN end_time NEW_VALUE end 
SELECT systimestamp(9) end_time FROM dual; 
Column elapsed_time NEW_VALUE elapsed 
SELECT TO_TIMESTAMP_TZ('&end', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') - TO_TIMESTAMP_TZ('&start', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') elapsed FROM dual;  

My time output looks like this:

START_TIME                         

-----------------------------------

4/18/2013 1:10:30.758782000 PM -06:

00                                 

                                                                                

1 row selected.

END_TIME                           

-----------------------------------

4/18/2013 1:10:30.873328000 PM -06:

00                                 

                                                                                

1 row selected.

old: SELECT TO_TIMESTAMP_TZ('&end', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') - TO_TIMESTAMP_TZ('&start', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') elapsed FROM dual

new: SELECT TO_TIMESTAMP_TZ('4/18/2013 1:10:30.873328000 PM -06:00', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') - TO_TIMESTAMP_TZ('4/18/2013 1:10:30.758782000 PM -06:00', 'MM/DD/YYYY HH12:MI:SS.FF9 AM TZH:TZM') elapsed FROM dual

ELAPSED                                           

--------------------------------------------------

+00 00:00:00.114546

                             

1 row selected.

Now you can capture this run-time  maybe add a few other columns of useful information, and store in a Table that a performance metric dashboard report reads from.