DBMS_UTILITY.GET_TIME – Oracle Database API to Calculate Elapsed Time

Oracle dbms_utility.get_time returns current time in millisecond. Divide it by 100 to get time in seconds.

DBMS_UTILITY.GET_TIME

The is utility package in Oracle. It provides sub-programs to analyze table, format stack, get CPU time, table to comma and comma to table conversion. Let’s check GET_TIME sub-program which returns the time in millisecond.

This API is helpful to get time taken by the intermediate procedure call.

Syntax

DBMS_UTILITY.GET_TIME

RETURN NUMBER;

Returns current time in millisecond

e.g

SELECT dbms_utility.get_time FROM DUAL;

 

Let’s assume you have program calling multiple procedures, modifying and querying the database. You can use this API to check how much time that subprocedure takes so that you can concentrate on them for tuning.

This is the way you can use it.

  1. Start
  2. Get current time(Start Time)
  3. Perform logical transaction
  4. Get current time(End Time)
  5. Subtract End Time – Start Time
  6. Divide it by 100 to get total time in milliseconds.
  7. Stop

 

Example

Below example illustrate the working of this API.

SET SERVEROUTPUT ON
DECLARE
CURSOR c_get_employee
IS
SELECT employee_id
,name
,department_id
,salary
FROM employees;
TYPE t_employee IS TABLE OF c_get_employee%ROWTYPE
INDEX BY BINARY_INTEGER;
l_employee t_employee;
l_chr_start_time VARCHAR2 (100);
l_chr_end_time VARCHAR2 (100);
l_chr_total_time VARCHAR2 (100);
BEGIN
l_chr_start_time := dbms_utility.get_time;
OPEN c_get_employee;
FETCH c_get_employee
BULK COLLECT INTO l_employee;
CLOSE c_get_employee;
l_chr_end_time := dbms_utility.get_time;
l_chr_total_time := l_chr_end_time - l_chr_start_time;
dbms_output.put_line ('Total time taken in seconds :- ' || l_chr_total_time/100);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Unexpected error :- ' || sqlerrm);  
END;
/
Total time taken in seconds :- .87
PL/SQL procedure successfully completed.

This is how above anonyms procedure works.

 

There is good discussion happening on asktom.oracle.com about this utility. I would recommend to read it. Also, you can refer this article for additional example.

Reference

Related Post