Friday, 18 August 2017
Oracle Technical

Calculate Elapsed Time using DBMS_UTILITY.GET_TIME

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

The DBMS_UTILITY is utility package in Oracle. It provides subprograms 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 sub procedure takes so that you can concentrate on them for tuning.

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.

Reference