Difference between DBMS_UTILITY.GET_TIME and DBMS_UTILITY.GET_CPU_TIME, dbmsutility

Difference between DBMS_UTILITY.GET_TIME and DBMS_UTILITY.GET_CPU_TIME, dbmsutility

Comparison between DBMS_UTILITY.GET_TIME and DBMS_UTILITY.GET_CPU_TIME

Http://www.oracle-developer.net/display.php? Id = 307


Developers have been familiar with the DBMS_UTILITY.GET_TIME function. In common terms, test the interval between two time points in a program, that is, the time consumed by the program.
At 10 Gb, oracle added a DBMS_UTILITY.GET_CPU_TIME function. This new function is used to measure the CPU time used at two time points.
The following example demonstrates the differences between the following two functions:

Example 1: A cpu-intensive instance

This example does not contain I/O. This emphasizes CPU intensive operations. In this case, the GET_TIME and GET_CPU_TIME functions return almost the same time consumption.

DECLARE    b1 PLS_INTEGER;    b2 PLS_INTEGER;    e1 PLS_INTEGER;    e2 PLS_INTEGER;    n  PLS_INTEGER := 0; BEGIN    b1 := DBMS_UTILITY.GET_TIME();    b2 := DBMS_UTILITY.GET_CPU_TIME();    FOR i IN 1 .. 100000000 LOOP       --NULL;       n := n + 1;    END LOOP;    e1 := DBMS_UTILITY.GET_TIME() - b1;    e2 := DBMS_UTILITY.GET_CPU_TIME() - b2;    DBMS_OUTPUT.PUT_LINE( 'GET_TIME elapsed = ' || e1 || ' hsecs.' );    DBMS_OUTPUT.PUT_LINE( 'GET_CPU_TIME elapsed = ' || e2 || ' hsecs.' ); END;

 
GET_TIME elapsed = 175 hsecs.
GET_CPU_TIME elapsed = 174 hsecs.


The PL/SQL process is successfully completed.




Example 2: I/O intensive (I/O-bound)


This example is more representative for PLSQL programs. In this case, we can see that the time consumption of the two functions will be completely different.

DECLARE   b1 PLS_INTEGER;   b2 PLS_INTEGER;   e1 PLS_INTEGER;   e2 PLS_INTEGER;BEGIN   b1 := DBMS_UTILITY.GET_TIME();   b2 := DBMS_UTILITY.GET_CPU_TIME();   FOR r IN ( SELECT * FROM all_source ) LOOP      NULL;   END LOOP;   e1 := DBMS_UTILITY.GET_TIME() - b1;   e2 := DBMS_UTILITY.GET_CPU_TIME() - b2;   DBMS_OUTPUT.PUT_LINE( 'GET_TIME elapsed = ' || e1 || ' hsecs.' );   DBMS_OUTPUT.PUT_LINE( 'GET_CPU_TIME elapsed = ' || e2 || ' hsecs.' );END;/

GET_TIME elapsed = 324 hsecs.
GET_CPU_TIME elapsed = 273 hsecs.

The PL/SQL process is successfully completed.

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

Dylan Presents.