CODEWITHSUNDEEP
sqlplsqloracle11gplsqldeveloper
user3283892
user3283892

Reputation: 1

Time taken by the Procedure

I am trying to find the time taken by the procedure to complete. I need to even track the difference in milliseconds/nanoseconds. I am looking to introduce some timestamp functions in between like

 start_time;
 ....<transactions>
 end_time;

using sysdate, I am unable to capture the time taken in seconds. Any help would be helpful.

Upvotes: 0

Views: 371

Answers (1)

Multisync
Multisync

Reputation: 8797

You may try DBMS_UTILITY.GET_TIME

This function determines the current time in 100th's of a second. This subprogram is primarily used for determining elapsed time. The subprogram is called twice – at the beginning and end of some process – and then the first (earlier) number is subtracted from the second (later) number to determine the time elapsed.

Syntax

DBMS_UTILITY.GET_TIME 
  RETURN NUMBER;

Return Values

Time is the number of 100th's of a second from the point in time at which the subprogram is invoked.

An example

Declare
  l_start number;
  l_end number;
Begin
  l_start := DBMS_UTILITY.GET_TIME ;
  -- transactions
  l_end := DBMS_UTILITY.GET_TIME ;
  dbms_output.put_line('elapsed: ' || (l_end - l_start) / 100 || 'secs');
END;

Upvotes: 3

Related Questions